June 23, 2008 at 9:42 am
I have a script generator that creates a script restoring our production databases to a staging server nightly, for reporting purposes. It checks for error conditions and corrects them, restores them, cleans out data that is unnecessary or potentially interfering with production environments, moves around some foreign key constraints, and sets permissions for the reporting users to access the data. I then take this script and run it nightly.
Here's the problem: the parser in SQL Server 2005 sees commands for a particular database ( say [customers] ) on line 1000+ that does not exist until line 400 (when I restore the [customers] database) and it pukes on me, telling me it can't find the entry point for 'customers' in sysdatabases... well, duh, it doesn't exist yet. The problem is, it fails the whole freaking script in pre-compile for this. If the script had been allowed to run, it would exist by the time that command came up, and if for some reason it didn't, I'm totally ok with it failing at that point; that's a genuine error that needs to be handled.
Is there any way to force the compiler to NOT error-check a script before running it?
June 23, 2008 at 9:49 am
Nope.
You would have to deal with it using batch separators. Using a semi-colon after the point in which you create the database may solve your issue. Depending on the client you are using to run the script, "GO" will probably solve the issue, but it drops variables out of scope, so it may cause you some other problems.
June 23, 2008 at 9:57 am
eh...
I use semicolons at the end of every command; I think it's good sql coding practice, especially if you are working with more than one rdbs/ programming language in your shop.
Problem is, the way the script generator works is a function that returns a NVARCHAR(MAX) string, which I schedule sp_executesql to run, and I don't think it does so well on batches; at least, it hasn't for me... it doesn't puke, it just doesn't seem to do anything after the first "GO".
Is there a way to make multiple batches in the same script run under a single sp_executesql statement?
June 23, 2008 at 10:03 am
No, the "GO" batch separator is actually part of the client tool. It cuts the batch into pieces.
You would have to cut the batches up yourself. However, that may not be too bad to deal with - especially since you only need two batches (at least to deal with this problem). Some string functions and a loop could do this pretty easily.
Here is another option - ensure the database is already there and use a DROP DATABASE before the CREATE DATABASE. This would be pretty easy to deal with also.
June 23, 2008 at 10:26 am
Well no gains without pains.
Best option will be to error check with the object_id exists againsts each object lookup in the script or redo the script to serialize the steps.
Maninder
www.dbanation.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply