August 26, 2015 at 10:38 am
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.
August 26, 2015 at 11:14 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 26, 2015 at 1:37 pm
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.
August 26, 2015 at 1:45 pm
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.
August 27, 2015 at 1:01 pm
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
August 27, 2015 at 1:04 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2015 at 1:32 pm
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.
August 27, 2015 at 1:52 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2015 at 2:10 pm
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?
August 27, 2015 at 2:20 pm
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.
August 27, 2015 at 2:28 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2015 at 2:42 pm
ok thanks a lot. I will give this a shot and mark it as a solution once it works 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply