Geek Answers Handbook

Use conditional expression database

I need to execute a script on two different servers having different db names, but the same tables and data. For this, I tried to use the following instruction.

if (@@servername= 'srvrA') begin use dbA end else begin use dbB end

But for srvrB, he says the dbA database does not exist.

Can someone help me achieve this?

+4
source share
2 answers

Another option

Sort of:

 sp_addlinkedserver @server= N'srvrA', @srvproduct= N'', @provider= N'SQLNCLI', @datasrc= N'srvrA'; sp_addlinkedsrvlogin @rmtsrvname = 'srvrA' , @useself = 'FALSE' , @locallogin = 'your_local_login' , @rmtuser = 'your_remote_login' , @rmtpassword = 'your_password'

And then your script will look like

 DECLARE @@srvname nvarchar(10) = 'srvrA' IF (@@servername = @@srvname) BEGIN SELECT * FROM srvrA.dbA.your_schema.your_table END ELSE BEGIN SELECT * FROM srvrB.dbB.your_schema.your_table END
+1
source

You can achieve your goal if you enable SQLCMD mode. Switch SQLCMD mode from the Query menu in sql management studio. Then the following script will work.

 if (@@servername = 'srvrA') begin :setvar dbname dbA use $(dbname) end else begin :setvar dbname dbB use $(dbname) end
-1
source

More articles:


All Articles