Post reply

Synonym for Db

  • November 23, 2009 at 9:25 pm

    #140202

    Hi

    I would like to know if we can create synonym for database name either in SQL 2008 or SQL 2005

    My requirement is: the query select from db1 and insert into db2.

    While I do not know what is the name of db1 /db2 in production env/other env.

    So if I can create a synonym my problen would be resolved!

    Thanks

    Khushbu

  • November 23, 2009 at 9:43 pm

    #1083250

    nope, sorry; synonyms are for Objects...Tables, Views, Stored procedures, functions, etc.

    you can make a synonym for a table on db1.databasename.dbo.tablename, but not the server, database nor schema.

    you could use a linked server however, making MyLinkedServer point to the other server, and with a decent username/password discover the databasenames to find the real name for db1, if you need to switch servers then change the definition (drop and recreate) of the linked server and scan that server instead.

    you could also use dynamic SQL to find and replace a "default" SQL statement with the correct objectnames and execute THAT.

    I'm still confused though.

    you know there are other servers to connect to... how are you going to update? what are you going to update? scanning each database for a specific table?

    if you explain what you are trying to accomplish, we might offer a better solution to whatever you are trying to do.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 23, 2009 at 10:00 pm

    #1083255

    Thanks for the quick response.

    I have added the server as linked server.

    But my proc looks like

    insert into server2.db2.dbo.tbl2

    (select col1 from server1.db1.dbo.tbl1 )

    here server2 is linked server.

    Now in dev env the db name are different hence hard coding them is problematic. I can make dynamic queries but it has its own problem (+ it will take huge code to get modified which i dont want)

    Hence I was thinking of making synonym for db1/db2. The table names are known.

    One workaround I can think of is to make whole dbname.dbo.tbl1 as synonym but it would be great help if only db name alias/synonym is possible to make.

Viewing 3 posts - 1 through 2 (of 2 total)

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