Is there tsql to change the server connection for Mgmt Studio?

Question

Is there tsql to change the server connection for Mgmt Studio?

Marcia Q

SSCrazy

Points: 2648
More actions
September 1, 2009 at 2:16 pm

#156161

I am running scripts in Mgmt Studio.
I know that you can right click in the query window and select Connection --> Change Connection.
But is there anything I can put in my script to change my server connection.
You can list "Use " at the beginning to ensure that you are in the correct DB. I was hoping there was a similar command to ensure that I'm on the right server (ie. Connect to test server)

Viewing 15 posts - 1 through 15 (of 16 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Nope. There is no such command, so far as I know.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Nope.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

The closest you could come would be to do an IF statement checking the @@SERVERNAME and if it was right to run your code.. Thats about it..

CEWII

Marcia Q SSCrazy Points: 2648 More actions · Answer 4

Thanks, Elliott. That's a good suggestion that would be easy to implement.

I had a close call with running commands on the wrong server. I think I saw somewhere where you can change Mgmt Studio settings so that the screen background or something is a different color. I could color code production servers in one color and development in another.

Thanks to all for your replies.

Mike01 SSChampion Points: 11395 More actions · Answer 5

There is an SSMS tool pack that you can install as an addin to SSMS. That allows you to color code each connection. You can find it here http://SSMSToolPack.com

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi

You can set your query window to SQLCMD mode (menu -> Query -> SQLCMD) and use the ":connect" method

:connect MyServer1 -U MyUserName -P MyPwd

SELECT @@SERVERNAME

GO

:connect MyServer2\MyInstance

SELECT @@SERVERNAME

Greets

Flo

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Flo, I had never heard of that before. How did you come across it?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Accidentally 😛

Noticed the menu item as I started with SSMS 2005 some years ago and remembered that SQLCMD is the new command line tool for SQL Server 2005. Thought "command line" could mean some scripting options - and it did.

Here some general information about SQLCMD mode in SSMS:

Editing SQLCMD Scripts with Query Editor

Here a little command reference (scroll down to "sqlcmd Commands"):

sqlcmd Utility

It's also very helpful for some general scripts to avoid dynamic SQL like:

-- Set variable

:setvar AnyTable Tally

-- Do some very complicated analysis

SELECT COUNT(*)

FROM $(AnyTable)

Or command line executions:

-- Execute a command line

:!! bcp Sandbox.dbo.Tally OUT C:\Users\Flo\Temp\Test\test.bcp -S . -T -c

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Many thanks. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Interesting, going back a few years but if I remember rightly the :! (or something similar) allowed you to break out of isql in sybase and run Unix commands.

plus ca change!

---------------------------------------------------------------------

Tom_Sacramento SSCertifiable Points: 6296 More actions · Answer 11

Ths link is wrong - should be http://www.ssmstoolSpack.com (tools is plural) - otherwise you go to bogus page

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

Marcia Q SSCrazy Points: 2648 More actions · Answer 12

SQLCMD - This is great! Thank you.

I think it's interesting that it failed when I used the following command:

:connect MyServer1 -U MyUserName -P MyPwd

We use RSA, so I thought it was due to that. I tried it with my RSA password, my regular network password, and with -T. None of that worked. BUT when I just used this:

:connect MyServer1

It worked. I'm not sure why and whether that's good or bad.

Also for those wanting more info, this article is very interesting, along with the corresponding discussion posts.

http://www.sqlservercentral.com/articles/SQLCMD/66183/

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 13

Florian Reischl (9/2/2009)
Hi
You can set your query window to SQLCMD mode (menu -> Query -> SQLCMD) and use the ":connect" method
:connect MyServer1 -U MyUserName -P MyPwd
SELECT @@SERVERNAME
GO
:connect MyServer2\MyInstance
SELECT @@SERVERNAME
Greets
Flo

Now that is REALLY interesting...

Is there a way to use a cursor, or a foreach, and cycle through a list of database servers?

We have 100 branches here, and I often have to "Check" the data of 1 table, but for every of them.

That would be a really really useful tool. I have looked through Google, and couldn't find what I'm looking for, anyone has ever done that?

Thanks,

Cheers,

J-F

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 14

Hi Jacques

J-F Bergeron (9/3/2009)
Now that is REALLY interesting...
Is there a way to use a cursor, or a foreach, and cycle through a list of database servers?
We have 100 branches here, and I often have to "Check" the data of 1 table, but for every of them.
That would be a really really useful tool. I have looked through Google, and couldn't find what I'm looking for, anyone has ever done that?
Thanks,

Very nice idea! Sadly it seems to be impossible. Here we call this a "chicken or egg problem" (which was the first?).

The SQLCMD scripts work because the variables will be set before execution of the SQL script. You cannot set the SQLCMD variables within the SQL script because it is already executing.

But if you save your scripts as files and call them from batch (*.bat) files or another SQLCMD script.

If you have a script which checks the count of rows within one table you can use a script file which contains something like:

SET NOCOUNT ON;

SELECT @@SERVERNAME, COUNT(*) FROM $(TableName);

You can call this script from SSMS either directly (in SQLCMD mode):

!!sqlcmd -S .\Sql2k8 -E -i D:\Temp\Test\test.sql -v TableName="Tally"

or by using xp_cmdshell.

It's still much easier to maintain than huge dynamic SQL scripts in my opinion.

Greets

Flo