Cross Database SQL Scripts

Question

I'm working on a project where we talk directly from one database to another. We have a master database where the client updates their website and then changes are pushed from one database to the other through numerous SQL scripts. The master database works as a preview site allowing them to see changes before pushing them to live.

At the moment we code the slave database name directly into SQL queries.

e.g. INSERT INTO [slave-database].dbo.TableName

This is an absolute pain however when branching out the development where we'll have multiple sets of the two databases. Any stored procs relevant to the development going on are updated to point at the slave database for this particular branch e.g. [branch5-slave-database].

This is not ideal since changes have to then be manually tweaked when pushing into the trunk (changing out the hardcoded database names).

Is there a better way to do this? The only solution I can see at the moment would be to create a SQL Server instance for each branch which would mean the database names can always stay the same across all branches. This would be an absolute pain though and would have quite an overhead. Is it possible to set a constant for the database name within SQL Server? Allowing us to just update a single value on the branch databases. Dynamic SQL is out of the question.

Best option I've managed to find is SQL Server 2005 Synonyms. They can't be used at database level so I have one for each object being referenced between databases. I'll then write a script which can quickly rebuild the Synonyms with a different database name. — Craig Bovis
– Craig Bovis, Commented Jul 7, 2009 at 10:44

John Sansom · Accepted Answer · 2009-07-07 09:47:34Z

1

Take a look at using the undocumented system stored procedure sp_MSforeachdb

sp_MSforeachdb

It allows you to process code against every database within the current instance. You could explicitly exclude system database should you with.

answered Jul 7, 2009 at 9:47

John Sansom

42k9 gold badges74 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Walter Mitty · Accepted Answer · 2009-07-07 11:49:10Z

0

Some DBMS dialects permit databases to be opened with a database alias at connect time. This gives you some of the same flexibilities that tables aliases or column aliases do in other parts of SQL.

In this case, you could issue a connect with the database alias of "slave" no matter what the actual connect string is. Then you wouldn't have to code actual slave database names into queries. You would, however, have to manage opening and closing various slaves in succession so as to avoid a collision in the name "slave".

Another alternative is to use table links, if your dialect supports them. You will face a similar problem in avoiding collisions.

answered Jul 7, 2009 at 11:49

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

Comments

Andomar · Accepted Answer · 2009-07-07 15:08:11Z

0

You can use dynamic Sql to change the name of the slave database. For example:

declare @query nvarchar(4000)
set @query = 'INSERT INTO [' + @slavedbname + '].dbo.TableName ...'
exec sp_executesql @query

You can store the name of the slave DB in a table and retrieve it from there:

select @slavedbname = value from Settings where name = 'SlaveDbName'

Or you could have a convention where it is equal to the current database plus _slave:

select @slavedbname = db_name() + '_slave'

answered Jul 7, 2009 at 15:08

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

piers7 · Accepted Answer · 2011-06-20 03:47:30Z

Sort of using dynamically generated Sql (as @Andomar suggests), there is no way of parameterising a database name at runtime in SQL.

However, when you execute the DDL to build the database you have many more options. Probably the easiest is to use the SQLCmd parameter syntax, supplying the slave database name as an argument when you execute the build script (see the SQLCmd doco). This could then be incorporated into your build/deploy script so doing a schema build for a given environment could be easily automated. I've done exactly this in the past for pretty much the scenario you describe.

Ultimately the best answer may be to take the content migration out of the database altogether, and put it into the application layer. This is generally a cleaner approach, and leads to additional benefits because the location of the two databases ceases to matter (they could be on seperate servers for example). Only you can decide whether this is overkill for your needs.

Collectives™ on Stack Overflow

Cross Database SQL Scripts

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related