January 5, 2007 at 9:11 am
Hey gang,
The news of Synonyms was exciting and could save me from a lot of DYNO SQL and duplicate versions of scripts for multilbe DBs on the same server. But I want to use SQL vars and pass them into the sprocs to create the synonyms.
Below is a test run, but the create synonym fails with the variables. Can this be done?
And does anyone know where in the sys tables are they stored so you can make the usual "if exists drop synonym then create synonym" header block for the sql files.
thanks a million!
--======================= SYNONYM TEST =====================--
DECLARE
@SourceDBNameWithDot varchar(50)
DECLARE
@TargetDBNameWithDot varchar(50)
Set
@SourceDBNameWithDot = 'Source.'
Set
@TargetDBNameWithDot = 'Target.'
-- SET TABLE NAMES FOR ALL INSERTS
DECLARE
@Target_TableName nvarchar(100), @Source_TableName nvarchar(100)
SELECT
@Target_TableName = 'dbo.Target_WebLinks', @Source_TableName = 'dbo.Source_WebLinks'
-- Testing: these return a properly formed Table Name
SELECT @SourceDBNameWithDot + @Source_TableName
SELECT @TargetDBNameWithDot + @Target_TableName
CREATE
SYNONYM TargetTable
FOR
@TargetDBNameWithDot + @Target_TableName
CREATE
SYNONYM SourceTable
FOR
@SourceDBNameWithDot + @Source_TableName
SELECT
* FROM TargetTable
DROP
SYNONYM TargetTable
SELECT
* FROM SourceTable
DROP
SYNONYM SourceTable
Skål - jh
January 5, 2007 at 8:23 pm
I haven't tested this, but it seems your problem resides in the fact that you're using variables in the statement.
This should work
--======================= SYNONYM TEST =====================--
DECLARE
@SourceDBNameWithDot varchar(50)
DECLARE
@TargetDBNameWithDot varchar(50)
Set
@SourceDBNameWithDot = 'Source.'
Set
@TargetDBNameWithDot = 'Target.'
-- SET TABLE NAMES FOR ALL INSERTS
DECLARE
@Target_TableName nvarchar(100), @Source_TableName nvarchar(100)
SELECT
@Target_TableName = 'dbo.Target_WebLinks', @Source_TableName = 'dbo.Source_WebLinks'
-- Testing: these return a properly formed Table Name
SELECT
@SourceDBNameWithDot + @Source_TableName
SELECT
@TargetDBNameWithDot + @Target_TableName
-- DECLARE the SQL temp variable
DECLARE
@strSQL nvarchar(2000)
SELECT
@strSQL = 'CREATE SYNONYM TargetTable
FOR '
+ @TargetDBNameWithDot + @Target_TableName + '
CREATE SYNONYM SourceTable
FOR '
+ @SourceDBNameWithDot + @Source_TableName
EXEC
sp_executesql @strSQL
--
SELECT
* FROM TargetTable
DROP
SYNONYM TargetTable
SELECT
* FROM SourceTable
DROP
SYNONYM SourceTable
Let me know if it does, please!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 26, 2007 at 11:27 am
Looks like this is going to work.
Thanks a million Jason!
Skål - jh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply