Connect MSSQL remotely by Query Analyzer

[ankitmathur]

Hi,

I wish to know the syntax for connecting to a remote MSSQL 2000 DB Server via Query Analyzer.

Actually my requirement is to use the connection string to connect to the remote server, execute a SQL Statement on the remote server & save the resultset in a table on my local machine.

Can anybody helpme ?

[iburyak]

Did you try something like this?

Insert into some_table
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
   pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

or this

insert into some_table
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).Northwind.dbo.Categories

[ankitmathur]

Thanks a lot Dear.

Your code was exactly what I needed. I wasn't able to understand how to create a connection which your code helped me to.

Thank again.

Ankit Mathur

Did you try something like this?

Insert into some_table
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

or this

insert into some_table
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories