June 1, 2007 at 1:49 pm
table in DB2 before DB2 is created.
DB2 is actually referenced via a linked server, so that we do not have
to recreate everything if/when it moves to another server.
It seems that Lazy Schema Validation would allow me to create this
proc without it checking the linked server for validation.
Unfortunately, it does not work. Could someone at least tell me WHY
it's not working and perhaps tell me what I need to do to make it
work?
I get the following error when I try to create the SP
Msg 7314, Level 16, State 1, Procedure TestProc, Line 4
The OLE DB provider "SQLNCLI" for linked server "lnkDB2" does not contain the table ""DB2"."dbo"."Table1"". The table either does not exist or the current user does not have permissions on that table.
Sample code below.
USE Master
GO
IF EXISTS(SELECT * FROM SYSSERVERS WHERE srvname = 'lnkDB2')
exec sp_dropserver 'lnkDB2', N'droplogins'
EXEC sp_addlinkedserver
@server = 'lnkDB2'
, @srvproduct = ''
, @provider='SQLOLEDB'
, @datasrc= @@SERVERNAME
EXEC sp_serveroption
'lnkDB2', 'data access', 'true'
EXEC sp_serveroption
'lnkDB2', 'lazy schema validation', 'true'
GO
--****************************************
USE TestDB
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TestProc') )
DROP PROCEDURE dbo.TestProc
GO
CREATE PROCEDURE dbo.TestProc
AS
SELECT T.Key_String
FROM lnkDB2.DB2.dbo.Table1 T
GO
June 4, 2007 at 11:02 am
Read here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d76cd143-8db6-439d-928d-37c393c2783f.htm
validation | If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema. If this option is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table has changed between query compilation and execution. You may want to set this option to true when distributed partitioned views are being used against a linked server that is running SQL Server. A specified table that participates in the partitioned view may not be actually used in a specific execution of a query against the view. Therefore, deferring the schema validation may improve performance. |
sp_serveroption [@server = ] 'server' ,[@optname = ] 'option_name' ,[@optvalue = ] 'option_value' ;
EXEC sp_serveroption 'LinkedServer', 'lazy schema validation', 'true';
June 4, 2007 at 11:09 am
First off, thanks for the reply!
I've read thru that.
If I understand it correctly, I should be able to create a proc that references at table on a linked server that does not yet exist, IF I set that to true. The error should not occur unless the sp is executed before the table is created. Is this not correct?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply