Connecting && Querying_Bit Urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Connecting && Querying_Bit Urgent

Author

Topic

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 02:44:51

Hi All ..... I need an urgent help ... I would be grateful if your brain can yield this solution.

Actually I am willing to Connect a remote SQL Server and fetch data according to my Query, but want to do everything from the SQL Query Analyzer Window ..... The Query is ready and is giving me the result when I test it on a test clone database of the remote DB which I want to connect ... The Only Thing I need is to connect the REMOTE DATABASE and fire the query there ......... through my local Query analyzer window ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 02:47:36

"through my local Query analyzer window ..."
Don't quite get what you want here. Can't you connect to the remote database directly by specifying the remote SQL Server IP / Name from the Query Analyzer ?

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 02:53:17

>>"through my local Query analyzer window ..."
>>Don't quite get what you want here. Can't you connect to the remote database directly by >>specifying the remote SQL Server IP / Name from the Query Analyzer ?

Actually I am totally ignorant about the commands/query how to connect the remote SQL Server Database through Query Analyzer ... can you please guide me through ...

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 02:58:11

and also {after connecting to the Remote database} how to fire the query to the remote database ... is it by the command exec <..... SQL Query ..... >

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 03:02:44

1. run Query Analyser
2. at the window "Connect to SQL Server",
- SQL Server, enter the remote SQL server IP Address or server_name\instance_name
3. Once you are connected, any query you execute will be running against the remote database. You don't have to use exec, just direct "select * from table" will do.

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 03:09:16

suppose the remote SQL server IP is <1.2.3.4> .... the database name is <myowndb> ..... the login name is <rudra> .... the password is <narayan> ... then can you please provide the the lines which I should wirte to my SQL Query analyzer window so that it can connect to the Remote database prior to execute my query .... I am soo sorry for Buging ... Thanks in advance :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 03:20:57

it is not a command in Query Analyser.

When you first run Query Analyser, a "Connect to SQL Server" will pop up.

Or, if you Query Analyser is running, click on menu File - Connect, the "Connect to SQL Server" window will appear. Input the information there

SQL Server : 1.2.3.4
choose SQL Server authentication
Login Name : rudra
Password : narayan

After connected, you can change database to myowndb by
1. choose the database from the drop down combo box on top center of the window
2. or use the use command
use myowndb
and press <F5> key.

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 04:23:21

Thanks KH ... I will try this .... I was out to Lunch .. Just back to seat and saw your comment ... many thanks again :)

rd_innovatives
Starting Member

18 Posts

Posted - 2006-09-14 : 04:28:27

No ... actually not that .... I mean ... I have opened SQL Query Analyzer .... he query analyzer is open in front of me .... and it points to my local database ... { now I need to connect to remote Database by entering some lines in the analyzer window .... then after the connection is established ... I want to execute my own query there .... Can you please provide me the lines which are to be written in the sql query analyzer by which i can connect to the remote database .... }

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-09-14 : 16:23:29

rd,

KH did exactly that in his last post. Did you follow his instructions?

If following the instructions exactly doesn't work, the remote database may not be visible to you.

Edit: Forgot to mention this, but decided I should. You don't TYPE anything into Query Analyzer to connect to the remote (or any other) database; you do it by the exact steps that KH provided.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-14 : 17:14:52

If you want to connect to a remote database while connected to your local database, then you need to setup a linked server on the local database server. The linked server would point to the remote server. You could then use the four part naming convention of the remote object, like this:

SELECT ...
FROM LinkedServerName.DatabaseName.ObjectOwner.ObjectName

Tara Kizer

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-18 : 07:39:11

rd,

Carefully read what KH has mentioned. Just do that to get your stuff done.

You dont have to enter anything in Query Analyzer to get connected to that remote Server. The only thing you need type is your SQL after you are ready with the QA window with the database of your interest.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 08:06:06

If the processing which you want to do is infrequent, then you can use OPENDATASOURCE() as follows:

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

Otherwise set up a remote server as a linked server using
sp_addlinkedserver like this:

EXEC sp_addlinkedserver 
    'Server2',
    N'SQL Server'

and then fire query as suggested by Tara !

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources