Re: Can someone explain this behaviour?

Igor
In addition
Inside the transaction spesify name of the database and it will work

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT

Perhaps SQL Server verified the new table (SELECT * INTO)  by SELECT which
is was remarted in the second example

(Deferred name resolution does not apply to  linked
> tables.)

Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?





"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
 news:Xns96295474102Y
azorman@127.0.0.1...
> Igor Raytsin (igorray@sympatico.ca) writes: 
>
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
 server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Uri Dimant (urid@iscar.co.il)  writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?

It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Erland
Ok, I got it, but how do  you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?






"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
 news:Xns96298185A423
CYazorman@127.0.0.1...
> Uri Dimant (urid@iscar.co.il) writes: 
>
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Uri Dimant (urid@iscar.co.il)  writes:
> Ok, I got it, but how do  you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?

I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp