Connect to another sql server database from stored Procedure

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

Latest Posts