Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2012-12-13 : 23:08:15
|
| 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?--PhB |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-14 : 02:26:16
|
| 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2012-12-14 : 07:11:50
|
| Not in ssis.--PhB |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-14 : 07:38:38
|
| 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. |
|
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2012-12-14 : 09:00:05
|
| Generally it's like this:Process 1 adds SP with the following:create SPAS 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.--PhB |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-14 : 09:48:29
|
| >> 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. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-14 : 09:53:47
|
| This works on my machinecreate proc tttasdeclare @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?