30

I'm using ms sql 2008 and trying to create a database name that references another database. For example 'Dev', 'Test', 'Demo' would be database names that i could reference from my multiple config files, but each name would point to another database such as 'db20080101' or 'db20080114'.

[Edit]Some of the configs are for applications that i control the code and some aren't (ex. MS Reporting service datasource file configs)[/Edit]

It seems that sqlserver only supports synonyms for View,Table,Sproc, or Function. And Alias' are for table and column names.

Is there a way to do this that i missed in the docs? Any one have any suggestions on a workaround?

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
Kevin McKinley
  • 599
  • 1
  • 5
  • 13
  • 2
    +1. In our case we have to do a DB-to-DB call; the target server differs based on Test vs. Production, and we don't want to have to always remember to change all references with each release – STW Jun 03 '09 at 02:11

4 Answers4

4

use 3 part notation and alias up to the table, example

select * from tempdb.dbo.sysobjects a
join master.dbo.sysobjects b on a.id = b.id
SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • i tried this for one of the tables and it worked great. however, i believe i would have to create a synonym in the new db for each table, function, view, and sproc in the original db? i've got 5 or 6 config files, so i think changing them will be less overhead. – Kevin McKinley Jan 14 '09 at 21:55
2

There is a way to simulate this using a linked server. This assumes you have two SQL servers with the same set of databases one for development/test and one live.

  1. Open SQL Server Management Studio on your development/test server
  2. Right click Server Objects > Linked Servers
  3. Select New Linked Server...
  4. Select the General page
  5. Specify alias name in Linked server field - this would normally be the name of your live server
  6. Select SQL Native Client as the provider
  7. Enter sql_server for Product Name
  8. In Data Source specify the name of the development server
  9. Add Security and Server Options to taste
  10. Click OK

The above is for SQL Server 2005 but should be similar for 2008

Once you've done that you can write SQL like this:

SELECT * FROM liveservername.databasename.dbo.tablename

Now when your scripts are run on the development server with the linked server back to itself they will work correctly pulling data from the development server and when the exact same scripts are run on the live server they will work normally.

1

I've done something similar to this using another config file.

The new config file maps your generic name to all of the information needed to connect to that database (db name, user name, password, etc.) and then your connection function takes your generic name as an argument.

db.config:

DEV_DB_NAME = db20080101
DEV_DB_USER = dev_user
DEV_DB_PASS = dev_pass
TEST_DB_NAME = db20070101
TEST_DB_USER = test_user
TEST_DB_PASS = test_pass

connection code:

db_connection get_connection(string prefix) {
    db_connection db_conn = new db_connection;
    string db_name = get_config_value(config_path, prefix + "_DB_NAME");
    string db_user = get_config_value(config_path, prefix + "_DB_USER");
    string db_pass = get_config_value(config_path, prefix + "_DB_PASS");

    db_conn.connect(db_name, db_user, db_pass);

    return db_conn;
}

Then you just call get_connection() with your db alias as the argument.

Ryan Ahearn
  • 7,886
  • 7
  • 51
  • 56
  • good thought, but some of the applications in use aren't mine and i cant control how they generate the connection string (ie MS Reporting Services). i just updated my question to reflect this. – Kevin McKinley Jan 14 '09 at 21:56
  • yeah, this will only work when you control the code. good luck finding your answer, post it back here if/when you do. – Ryan Ahearn Jan 15 '09 at 05:27
0

I know this probably will not help in all situations, but you still have the option of using views. You can insert, delete, update, select into a view as long as it has a proper identity key (Primary Key). If you point it to another database, you should drop and recreate to get the different schema (in case you're working between production and test while making changes to the schema in test and/or production.

Synonyms are useful for when you're going to another database and have a 3 or 4 part name, but when you want to make it so you can have a set name, a linked server will also work which will let you use a fixed name if the table names are the same in both databases and you're just pointing between prod and test.

Lauren Glenn
  • 11
  • 1