We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run integrity checks, and also so that our dev environment has reasonably fresh data, we restore LIVE's database backups on QA daily and then run integrity checks.

It all works well but for one issue: We need QA to maintain its own versions of the all the stored procedures. We develop new SPs and modify existing SPs on QA, and test them there. Only after they have been tested do we push them over to LIVE. In order to retain QA's versions on QA, what I have been doing is scripting the CREATE PROCEDURE commands which are saved to files. After the databases are restored, all the procedures are dropped. The CREATE PROCEDURE scripts are then executed, recreating all the databases' SPs as they were on QA prior to the restore from backup. For a time this worked well, although the occasional disappearance of a SP raised eyebrows. However, this week the process has not worked, as several SPs disappeared each day, escalating to 20 disappearances yesterday and--ugh--over 100 this morning.

A look through the job history log indicates the nature of the problem:

Msg 102, Level 15, State 1, Server QA, Procedure pln_tradepaper, Line 196  Incorrect syntax near 'GO'.  
Msg 102, Level 15, State 1, Server QA, Procedure pln_tradepaper, Line 230  Incorrect syntax near 'END'.
Msg 102, Level 15, State 1, Server QA, Procedure pln_tradepaper_type, Line 187  Incorrect syntax near 'GO'.  
Msg 2714, Level 16, State 1, Server QA, Procedure pln_tradepaper_type, Line 199  There is already an object named '#temp1' in the database.  
Msg 137, Level 15, State 2, Server QA, Procedure pln_tradepaper_type, Line 201  Must declare the scalar variable "@group".  
Msg 102, Level 15, State 1, Server QA, Procedure pln_tradepaper_type, Line 211  Incorrect syntax near 'END'.  
Msg 102, Level 15, State 1, Server QA, Procedure msg_upsert_asset, Line 46  Incorrect syntax near 'GO'.  
Msg 137, Level 15, State 2, Server QA, Procedure msg_upsert_asset, Line 64  Must declare the scalar variable "@client_id".

And so forth, up to the limit for the log's message. I have run the command that generates the CREATE script for this particular database, and there are no syntax issues in it of any kind. Starting tomorrow and until this is resolved, I will be retaining the scripts as they are generated; unfortunately, they have been deleted after running up to this point, so I cannot rule out the possibility that the script did have syntax errors.

The command that generates the CREATE script is this:

bcp "SELECT definition + char(13) + 'GO' FROM db1.sys.sql_modules s INNER JOIN db1.sys.procedures p ON [s].[object_id] = [p].[object_id]" queryout "D:\scripts\SP_db1.sql" -S qa -U "sa" -P "password1" -t -w

I have only two ideas as to what might be the issue. My leading suspicion is that a bcp has a character limit on the fields which causes db1.sys.sql_modules.definition to be truncated. This would explain why the errors started with Incorrect syntax near 'GO' (the previous module's description was truncated before END, so we had a GO without a BEGIN being closed off; the other errors cascaded from there). MAX(LEN(definition) for this database is 15134. However, the data type for db1.sys.sql_modules.definition is nvarchar(max), which as I read the documentation has no default limit.

The only other idea is that somehow I am not calling bcp correctly. Since I'm only selecting a single field, I turned the default \t field separator off by using -t with no argument. The default row separator \n should be fine. Perhaps I should not be specifying Unicode with -w, or something else is missing.

If it makes any difference, the CREATE scripts are executed with

sqlcmd -S QA -U "sa" -P "password1" -d db1 -i "D:\scripts\SP_db1.sql"

I would appreciate any ideas anyone could provide as to why this issue is occurring. I am not opposed to a different approach to persisting SPs across--I haven't been able to shake the feeling that this is somewhat of a Rube-Goldberg-like solution.

closed as too localized by Mark Storey-Smith, dezso, RolandoMySQLDBA, Max Vernon, Marian Jun 20 '13 at 10:41

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    -1 Check the output file contents yourself before asking others to solve your problem. – Jon Seigel Jun 14 '13 at 22:42
  • @JonSeigel, I have run the step that generates the CREATE scripts and find no errors in them--but of course there is no guarantee that the script is exactly the same each time. – mdoyle Jun 17 '13 at 13:43
  • Why all the down votes, folks? Too wordy, or what? – mdoyle Jun 17 '13 at 19:50

The first problem that I see is that you are concatenating Unicode and non-Unicode values together.

A better way of dealing with this would be to keep the stored procedures in some sort of source control then deploy the stored procedures from source control after the database is restored.

Or better yet restore the database you are testing to a different name than the dev/qa database. Developers and QA people don't typically need production data, and depending what business you are in you probably don't want them having actual customer data.

  • Thanks for the response. Could you clarify a bit on the concatenation of Unicode and non-Unicode? The dev team has specifically asked for a fresher dataset more frequently. It doesn't have to be every day, though, so I may implement your suggestion of restoring to a different name, and then once a week or so change the names (assuming I don't find the actual problem, which seems increasingly likely). – mdoyle Jun 17 '13 at 13:49
  • We decided instead to keep an ALTER PROCEDURE script going with all the changes made to the stored procedures that haven't been pushed live. This runs after the restore of the databases. It's a bit more hands-on than I was hoping for, but it should work. This is basically your idea of using some sort of source control. – mdoyle Jun 17 '13 at 19:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.