|
Home > Archive > Microsoft SQL Server forum > March 2005 > Re: Can someone explain this behaviour?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Can someone explain this behaviour?
|
|
| Uri Dimant 2005-03-30, 9:41 am |
| 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
| |
| Erland Sommarskog 2005-03-30, 9:41 am |
| 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
| |
| Uri Dimant 2005-03-30, 9:41 am |
| 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
| |
| Igor Raytsin 2005-03-30, 9:41 am |
| Uri Dimant wrote:
Uri,
I tried to add the database name -
SELECT COUNT(*) FROM tempdb.dbo.Orders_TMP
But it did not work on my server :(
Igor
> 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
>
>
>
> 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...
>
>
> server
>
>
>
>
| |
| Erland Sommarskog 2005-03-30, 7:04 pm |
| 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
|
|
|
|
|