Syntax Errors when Re-Creating Procedure

  • Like2SQL

    SSCommitted

    Points: 1662

    Hello,

    We regularly restore a database from one environment to another. The destination environment follows it's own naming convention. So I run a script which replaces the source schema names (and references to those schema names within stored procedures) with destination names. (I obtained the script from here: http://www.sqlservercentral.com/blogs/bit-barbarian/2013/08/13/renaming-all-references-inside-stored-procedures-and-functions-can-be-migraine-worthy-without-a-little-help/> ). I did not tweak it.

    When I run the script above, it throws out the new code which I should then run to re-compile the SP's. My problem is that the code it throws out is FULL of syntax errors of different types. I have tried outputting the results to grid, text and file and encounter the same issue. I have tried this on different machines, same issue. I think it's a problem with the way SSMS query engine outputs code. My problem is similar to http://www.sqlservercentral.com/articles/sp_helptext/125886/ but not exactly the same.

    I suppose some of you have run into this problem before. Any insight as to why this is happening and how to correct it would be appreciated.

  • ScottPletcher

    SSC Guru

    Points: 98441

    I'd take a different approach. How about using synonyms to re-direct from the old schema name to the new one? Then you wouldn't have to directly change any of the code at all.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Luis Cazares

    SSC Guru

    Points: 183633

    That process will change all the text that contains your database name. So, unless you have something really unique, it might cause problems and won't be safe to execute this code.

    I'll support Scott's recommendation on using Synonyms.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Ed Wagner

    SSC Guru

    Points: 286982

    I also agree with Scott's approach. If you use synonyms, then all you have to update after you restore it are the synonyms themselves.

  • Like2SQL

    SSCommitted

    Points: 1662

    Thanks for the suggestions guys. I looked into synonyms. Can I create synonyms for schema names? I ask because the MS link below seems to indicate that I can NOT create a synonym for a schema name.

    https://msdn.microsoft.com/en-us/library/ms187552(v=sql.105).aspx

  • ScottPletcher

    SSC Guru

    Points: 98441

    No, you'd need to use a synonym for each object. But it's easy enough to generate the code to create the synonyms for all tables in the current db.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Like2SQL

    SSCommitted

    Points: 1662

    Ok so please tell me if I am understanding this correctly: Say all tables & stored procedures in my source environment follow the schema names "source." and in my destination environment it is "dest." So after I restore to the destination environment, I will have to

    (a) First change all objects which begin with source.xyz to dest.xyz. (It is a requirement to have destination schema names be different than source). This will take care of the object names, but the code in the SP's still points to source.xyz.

    (b) So to circumvent that, I will have to create synonyms for all objects named source.xyz and point these to dest.xyz

    Is this correct? If so, how can I go about creating the synonyms? I have a couple of hundred SP's so that's a lot of synonyms.

  • ScottPletcher

    SSC Guru

    Points: 98441

    Nah, I was thinking of something like this to generate the commands to run. If you want, you could even actually run the code instead of just generating it.

    DECLARE @new_schema_name nvarchar(128)

    DECLARE @old_schema_name nvarchar(128)

    SET @new_schema_name = 'dest'

    SET @old_schema_name = 'source'

    SELECT 'CREATE SYNONYM [' + @new_schema_name + '].[' + o.name + '] FOR [' + @old_schema_name + '].[' + o.name + '];'

    FROM sys.objects o

    WHERE

    o.parent_object_id = 0 AND

    o.schema_id = (SELECT s.schema_id FROM sys.schemas s WHERE s.name = @old_schema_name)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Like2SQL

    SSCommitted

    Points: 1662

    ok thanks, I will give it a shot. So sorry if this is a silly question, but after I have created the synonyms, any stored procedure which references source schema names within it's code will be run against the destination schema names correct?

  • Ed Wagner

    SSC Guru

    Points: 286982

    The synonyms reference database objects, so if you use the two-part naming conventions as Scott suggested, then that's what they'll resolve to when the code runs.

  • ScottPletcher

    SSC Guru

    Points: 98441

    Yes, if I had done the synonym names in the correct order :-D. I think I got them backwards in the script:

    Edit: That is, after you run this code:

    CREATE SYNONYM source.tablename1 FOR dest.tablename1

    When SQL reads T-SQL code containing "FROM source.tablename1", it will recognize that as a synonym name and replace it with its "FOR" reference, or "dest.tablename1".

    SELECT 'CREATE SYNONYM [' + @old_schema_name + '].[' + o.name + '] FOR [' + @new_schema_name + '].[' + o.name + '];'

    FROM sys.objects o

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Like2SQL

    SSCommitted

    Points: 1662

    ok thanks a lot. I will give this a shot and mark it as a solution once it works 🙂

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply