December 10, 2007 at 2:15 am
Hi.
Normally we change connection to another server by Right click or click (Change Connection button)
Is there an SP or T-SQL that can be used to change connection?
Ronsi
December 10, 2007 at 3:43 am
Switch your SSMS settings to SQLCMD and you'll get a whole bunch of new statements available.
SSMS Query pane
tabl Query --- choose SQLCMD mode
:connect yyyyy
is the statement you're looking for.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 10, 2007 at 2:56 pm
I tried what you suggested .. and it seemed to work .. at first. But now for some reason I'm getting the following result:
Connecting to servername...
Disconnecting connection from servername...
And my connection is left connected to the orriginal server.
Also is there any security to stop people from using the !! commands? I can restrict xp_cmdshell but this seems to bypass that security.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 10, 2007 at 3:37 pm
Kenneth Fisher (12/10/2007)
I tried what you suggested .. and it seemed to work .. at first. But now for some reason I'm getting the following result:Connecting to servername...
Disconnecting connection from servername...
And my connection is left connected to the original server.
Also is there any security to stop people from using the !! commands? I can restrict xp_cmdshell but this seems to bypass that security.
Ok I seem to have answered some of my own questions. The !! isn't an issue because it runs the command on your local machine not the server.
And the :connect only seems to change the connection during the specific run, then it changes back to the original connection. My question now is how do I use :connect to permanently change the connection for that query window. Or is it even possible.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 10, 2007 at 6:18 pm
Thank you... it works..
but when I execute
:connect MyServer
use MyDB
sp_help customer
go
OR
:connect MyServer
use MyDB
sp_helptext spGetCustomer
go
notes:
customer is a table
spGetCustomer is an SP
I've error like this
Connecting to MyServer...
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sp_help'.
Disconnecting connection from MyServer...
September 28, 2009 at 5:55 pm
Add EXEC in front of your stored procedure calls
July 9, 2010 at 1:49 pm
You could also add a GO after the use statement
:connect MyServer
use MyDB
go
sp_helptext spGetCustomer
go
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 9, 2010 at 2:29 pm
It's safest to always add EXEC before stored proc names.
Because only the first statement in a batch has the EXEC "implied".
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply