Connect to another sql server database from stored Procedure

Posted by Jpchoudhari under Sql Server on 9/13/2011 | Points: 10 | Views : 16637 | Status : [Member] | Replies : 4
Hi,

How can we connect and retrieve data from another sql server database from stored procedure.

I am calling this stored procedure from my application which will bring some data from another database and depending upon that will submit result to application.

Kind Regards,
Jay



Responses

Posted by: Sathya4260 on: 9/13/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Use the below code,
declare @db varchar(50),
declare @parameter varchar(20)
sp_executesql('use ' + @db '; exec spname '' + @parameter + ''')

Hope this helps, mark this as answer if this solved your question

Sathish Kumar S

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jpchoudhari on: 9/13/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

My db is on different server. how to specify server by IP name and also database credentials.

Kind Regards,
Jay

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathya4260 on: 9/13/2011 [Member] Starter | Points: 25

Up
0
Down
make both the servers linked by using sp_addlinked server and use the query as fallows
select * from server2.dbname.dbo.tablename


Sathish Kumar S

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathya4260 on: 9/13/2011 [Member] Starter | Points: 25

Up
0
Down
Enjoy here is the full code:

sp_addlinkedserver @server='Server2',@srvproduct=' ',

@provider='MSDASQL',@datasrc=NULL,@location=NULL,
@provstr='driver={SQL Server};database=DB;server=Server1;uid=Server1loginname;pwd=Server1password;',
@catalog='Table'

--"Server2" is the name which you can use in the query.

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'Server2', @useself = 'false', @locallogin = 'Server1loginname', @rmtuser = 'server2loginname', @rmtpassword = 'server2pass'
GO


select * from Server2.Table


Sathish Kumar S

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response