In T-SQL, (using ssmse to run this .sql script would be best) ... How to access two running sql instances from one script?

Question

1.00/5 (1 vote)

See more:

I'm guessing that using sqlcmd in some form from inside this script is going to be one of the answers but sitting here with ssmse open and a "new query" ready to be written ... there's gotta be some switch command in T-SQL where I can move between the instances.

Ultimately I wish to run a broad spectrum effect on each of several databases and composit the results.

All I can think of doing right now is to use "sqlcmd -i" naming the instance then another .sql script, to completion tabulating data, then run it again on the next instance ...

I believe the local->remote->local again is ok with that?

[EDIT_01]

I see sp_addlinkedserver in the towering list of things available in the programmability scripts to check out. All I might have to do is add the name of the "remote" database after linking in the "remote" server, right?

Posted 10-Oct-12 9:03am

RedDk

Updated 10-Oct-12 9:10am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2012-10-10T10:00:00

Ok,

As can be seen from a post similar to this one, answered by Mehdi, I was getting FAIL on the technique as applied in the article. Today, I still couoldn't get the Quackit method to work. And as I stated there is some disparity between that article and ssmse help on "sp_addlinkedserver".

But Mehdi's clue is what guided me to this sto-proc so he deserves credit even though using the help was how the solution is implemented:

A simple T-SQL statement in the local instance script with connection focal to "COMPUTER_A\localServer":

USE master
GO
EXEC sp_addlinkedserver
N'COMPUTER_A\closeServer'
N'SQL Server';
GO

Makes the Server Objects "Linked Servers" on localServer instance dropdown under the new COMPUTER_A\closeServer -> Catalogs list light up like a christmas tree with fullsome goodness!

And then all that one needs to do is QUERY using the TABLE name.

(caveat emptor:

And mighty strangely at that ... trying to QUERY a table using this form

[data].[dbo].[essential]

Won't do. Only

[dbo].[essential]

Works. )

[EDIT_FINAL]

And intuitively the BEST way to query the table

SELECT * FROM [COMPUTER_A\closeServer].[data].[dbo].[essential]

[END EDIT_FINAL]