I have a process that creates SP's which reference objects on other servers which are not yet created. The refernces are in dynamic sql, but the dyn sql is called by non dynamic exec statement in the sp's.
Of course, when the sp is generated sql validates the dynamic ref's which dont exist yet.
Can that behaviour be stopped or worked around so that sp's that are perfectly fine can go through and will work downstream?
is this inside a SSIS package? if yes, you can set delay validation property to true for delaying validation step until runtime when objects actually get created
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Can you post an example of the issue - dynamic sql shouldn't be validated.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
create SP AS declare @sql = "some sql referencing a DB on another server which is not yet created but will be after the db containing this SP is created"
exec(@sql)
I believe the dynamic does get validated at the exec call.
>> I believe the dynamic does get validated at the exec call. Shouldn't.
Are you sure you're creating the SP rather than executing it (maybe by getting the query plan)?
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
create proc ttt as declare @s varchar(100) select @s = 'select * from Me.test.dbo.xxx' exec (@s)
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Is there a way to turn off validation?
Posted - 12/13/2012 : 23:08:15
