specify server in sql script

Question

I am writing a sql script for sql server 2008 where I place a use statement at the beginning that specifies the database the script should be run against:

use [my_database]

As I have different environments where the same database exists, eg dev, qa, prod, is there any way I can specify in the script the environment the script is for, either by server name or ip address or by any other mechanism.

Answer 1

You can put the SQL Management Studio in SQLCMD mode and specify the server with the :CONNECT myserver statement.

You can switch on command mode by clicking on the option in the pic below

Your script would then look something like this

    :CONNECT devserver
    use [my_database]
    SELECT * FROM my_table

You can even make the query window switch servers during execution

    :CONNECT devserver
    use [my_database]
    SELECT * FROM my_table
    GO
    :CONNECT uatserver
    use [my_database]
    SELECT * FROM my_table

To connect with a specific user and password you can specify this as follows

    :CONNECT devserver -U myUser -P myPassword
    use [my_database]
    SELECT * FROM my_table

There are actually a number of options you can specify which are well documented on msdn.

Answer 2

That's a CONNECTION setting, not a parameter within the script.

You can run the same script in different environments via batch file or powershell script if desired, or you could set up linked servers, but you can't just say

USE SomeOtherServer

There are security and networking implications as well.