Lazy Schema Validation does not seem to work. PLS help!

Question

Post reply

Lazy Schema Validation does not seem to work. PLS help!

Brian Bunin

Mr or Mrs. 500

Points: 585
More actions
June 1, 2007 at 1:49 pm

#196056

I have a scenario where I need to create a SP in DB1 that references a

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

Brian Bunin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

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';

Brian Bunin Mr or Mrs. 500 Points: 585 More actions · Answer 2

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?

Brian Bunin