connect to unknow remote server using stored procedure

Question

connect to unknow remote server using stored procedure

TheM3Guy

Newbie

Points: 4
More actions
May 27, 2005 at 7:43 pm

#89269

Hello,
I need to connect to a remote server using stored procedure but there is one problem, I do not know the name of the server. It will be necessary to connect to different servers at different times (one at a time ) therefore I have to obtain the name of the server somehow before I can establish connection. @@ServerName would be part of the script but need some help.
If anyone can post a sample script I would be very thankful!
Your ideas will be highly appreciated.

Thanks in advance

Viewing 4 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

if you are going to be connecting dynamically, i think it is better to use an application to determine and connect to the server, and then execute whatever code you were planing on running;

you really need to provide more information. you can only determine @@Servername after you've connected, so you'd need the server's ip address , perper username and p[assword in order to login; is this in intranet situation where you'd be using a domain administrator login?

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!

TheM3Guy Newbie Points: 4 More actions · Answer 2

It is actually an interface wich connects SQL Server through MS Project to a different database(shich can change location) and transfers data between them.

Yes it is domain administrator login.

I was told, if connection is initiated from client db and "addlinkedserver" used, it is possible.

Thanks

Lowell SSC Guru Points: 323546 More actions · Answer 3

well, the syntax for sp_addlinkedserver is here or in BOL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',                                @provider='SQLOLEDB', @datasrc='S1\instance1'

but i couldn't follow the logic of how the server you connect to would be dynamic.

i can imagine it might be one of a list of servers, but not truely dynamic; your description made it sound lke the chicken or the egg...after you connect, you could add the server? A client could conect to a server, true, but are you assuming the client that connected has a SQL server instance, then? is that what you are trying to do?

I kind of thought this sounds like you want to push updated MS project information about, but wouldn't you know where to pusht he data to? each laptop of a group of traveling users, for example? how is the server you want to connect to dynamic?

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!