The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
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 wrote:
> Igor Raytsin (igorray@.sympatico.ca) writes:
>>The following script is reproducing the problem assuming you have
>>Northwind database on the server.
>>Please note it gives you the error message on line 12.
>
> 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.)
Thank you for your reply Erland,
In other words - those procedures are never got compiled without a error (or warning),
but SQL Server ignores them if it is not in a transaction. Right?
Thanks,
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
> 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:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> 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|||Igor Raytsin (igorray@.sympatico.ca) writes:
> In other words - those procedures are never got compiled without a error
> (or warning), but SQL Server ignores them if it is not in a transaction.
> Right?
This particular error only occurs when you are in a transaction. I can't
really say why, but apparently the fact that the single-machine transaction
suddenly makes a linked reference 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:Xns96298185A423CYazorman@.127.0.0.1...
> 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 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
>>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:Xns96295474102Yazorman@.127.0.0.1...
>>Igor Raytsin (igorray@.sympatico.ca) writes:
>>
>>>The following script is reproducing the problem assuming you have
>>>Northwind database on the server.
>>>Please note it gives you the error message on line 12.
>>
>>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:
> 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
No comments:
Post a Comment