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.
| Author |
Topic |
|
rohitmathur11
Yak Posting Veteran
77 Posts |
 Posted - 2010-02-04 : 05:54:28
|
| Hi,I need to write one stored procedure ..which can get data from from which is on different server and insert that data in to current database.. the one way is to use linked servers ...can we use some ad hock connection between severs ...like in stored procedure only we will give remote server ip user name password and connect to remote database get data and close connection .. how to do this in one stored procedure.. any idea... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 06:00:29
|
| yup you can do that. See OPENROWSET in books onlinehttp://msdn.microsoft.com/en-us/library/ms190312.aspx |
 |
|
|
rohitmathur11
Yak Posting Veteran
77 Posts |
Posted - 2010-02-05 : 04:31:04
|
| I worte one stored procedure for getting data from remote database ..The work of this procedure is to take data from remote database tables and insert into current database table ..ALTER PROCEDURE ABCAS BEGIN INSERT INTO Currentdb_table SELECT A.column1,A.column2,B.column1from Remotedb_table1 A JOIN Remotedb_table1 BFROMOPENROWSET('SQLNCLI','Database=dbIDsAndNames;Uid=sa;Pwd=nosecurity;',..... where to mentaion the remote table names ...What is ..SQLNCLI ..i am not using Linked server ..... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-05 : 04:41:16
|
| If you are familiar with LinkedServer you might be better off with OPENDATASOURCE rather than OPENROWSETOtherwise see SQL Documentation for OPENROWSET - you need to include a query in OPENROWSET, not just the connection string. |
|
|
|
rohitmathur11
Yak Posting Veteran
77 Posts |
Posted - 2010-02-05 : 05:16:20
|
| I know linked server ..how to implement and use it ..but for current requirement ...i need to set up some ad hoc connection...like when stored procedure executes connection open and afterexecution it will close ...so i am searching for some method to do this ...any idea ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-05 : 05:31:49
|
| Which is why I suggested OPENDATASOURCE because it is used link a LInkedServer, but allows you to set up an adhoc connection (without having to make a LinkedServer)." any idea?Look at the examples in the SQL Documentation |
|
|
|
|
|
|
Different server communition