how to run a sql from other servers without using linked servers

Question

Post reply

how to run a sql from other servers without using linked servers

deepak.a

SSCertifiable

Points: 5330
More actions
August 9, 2011 at 1:47 am

#242739

Hi All,
how to run a sql from other servers without using linked servers ?
Thanks & Regards
Deepak

Viewing 13 posts - 1 through 12 (of 12 total)

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

M&M

SSC-Insane

Points: 21700

August 9, 2011 at 3:07 am

#1365688

you can try using SSIS

M&M

deepak.a SSCertifiable Points: 5330 More actions · Answer 2

is there any way we can execute in SSMS

Regards - Deepak

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

in SSMS you can directly connect to the target server , you could use Sqlcmd as well.

Jayanth Kurup[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

deepak.a (8/9/2011)
Hi All,
how to run a sql from other servers without using linked servers ?
Thanks & Regards
Deepak

You can use SSIS which I prefer or OPENROWSET or OPENQUERY.

For better, quicker answers on T-SQL questions, 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/

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Jayanth_Kurup (8/9/2011)
in SSMS you can directly connect to the target server , you could use Sqlcmd as well.

[font="Arial Black"]HOW?[/font] That's the question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 6

don't forget Central Management Server is SSMS 2008...that allows you to run the same command on multiple servers.

In the end, each server will require a connection in order to pass the command and retrieve the results.

As everyone has identified previously, if you are shoe-horning everything into SSMS, you are limited to CMS, linked servers, openrowset and openquery.

if you step outside of SSMS, you cna cosnider powershell, or creating an application to do the work. SSIS is one of those very powerful applications.

it all depends on what you awant to do;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

deepak.a SSCertifiable Points: 5330 More actions · Answer 7

Jayanth_Kurup (8/9/2011)
in SSMS you can directly connect to the target server

in the Query window i need to run sql script not by connecting another server in SSMS. I mean without connecting to another server.

Jayanth_Kurup (8/9/2011)
you could use Sqlcmd as well.

yeah in SQL CMD mode we can connect and run the SQL script , do you have other alternatives ?

Regards - Deepak.A

deepak.a SSCertifiable Points: 5330 More actions · Answer 8

Welsh Corgi (8/9/2011)
deepak.a (8/9/2011)
Hi All,
how to run a sql from other servers without using linked servers ?
Thanks & Regards
Deepak
You can use SSIS which I prefer or OPENROWSET or OPENQUERY.

yes we can use SSIS , how to call the package ? from the stored procedure ?

deepak.a SSCertifiable Points: 5330 More actions · Answer 9

Lowell (8/9/2011)
don't forget Central Management Server is SSMS 2008...that allows you to run the same command on multiple servers.

what is the minimum permission required for CMS ?

it all depends on what you awant to do;

like i want to check the table counts , we do have same tables in differenet Database (i.e., Distributed database ) , i want campare the tables.

Regards - Deepak

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 10

deepak.a (8/9/2011)
Jayanth_Kurup (8/9/2011)
in SSMS you can directly connect to the target server
in the Query window i need to run sql script not by connecting another server in SSMS. I mean without connecting to another server.
Jayanth_Kurup (8/9/2011)
you could use Sqlcmd as well.
yeah in SQL CMD mode we can connect and run the SQL script , do you have other alternatives ?
Regards - Deepak.A

In order to execute any kind of T sql on any server (local or remote) you will need to be able to connect to it ) , If your able to connect then any of the above methods would work be it , SSMS query window , SSIS , SQL CMD etc.

There are plenty of alternatices which one would be best depends on what your trying to achieve.

Jayanth Kurup[/url]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 11

Jayanth_Kurup (8/9/2011)

deepak.a (8/9/2011)

Jayanth_Kurup (8/9/2011)
There are plenty of alternatices which one would be best depends on what your trying to achieve.

I would add something too Registered Servers

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Manoj Bhopale SSC Rookie Points: 47 More actions · Answer 12

Use management studio and give target server name in the server text box, if not connects, give port number as well. it will work.