connec to different database server from a stored

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

Transact-SQL (2000)

connec to different database server from a stored

Author

Topic

raju2647
Starting Member

22 Posts

Posted - 2005-12-28 : 23:53:16

Hi,

i need to connect to another database from a stored procedure.i will be assigning the databasename ,username and password from the application which is in .net.The application will connect to the current database .i will passs the DBname,user and pwd to the sp in the current database.From inside the stored procedure i need to connect to a database in a different MSQL server.i need to retrieve data from other DB nad fill data into the current DB.

Could anyone tell me how to do this

Thanks
Raju

raju2647
Starting Member

22 Posts

Posted - 2005-12-29 : 00:03:08

Hi
can i use a connection string inside an Stored procedure to connect to different database .

Can anyone tell me what CONNECT TO stands for

Raju

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 00:08:05

Hi,
first you need to add a link between both the servers.
use sp_addlinkedserver to add a link server.
then with in your stored procedure you can select the table of another server\database using openquery

select * from OPENQUERY ( linked_server_name , 'select * from db2.tablename' )

refer BOL for more info on linked server and openquery.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 00:49:58

Also look for OpenRowSet and OpenDataSource

Madhivanan

Failing to plan is Planning to fail

raju2647
Starting Member

22 Posts

Posted - 2005-12-29 : 01:39:50

thank you guys . i was able to connect to the database.
can i set the connection time.Is there any problem if i drop and create the linked server frequently.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 01:54:16

yes you can set that using sp_serveroption.
sp_serveroption 'server name' ,'connect timeout','value'

Until unless u are accessing it.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-29 : 02:10:00

You can also access the remote SQL server using

SELECT *FROM [servername\instancename.]DatabaseName.dbo.TableName

This helps in case the database server has got multiple instance and you want to refere once of them.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources