Sunday, March 25, 2012

Accessing Different Servers

If I have 2 registered servers, what syntax allows me to access both servers
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"

> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>

No comments:

Post a Comment