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.