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 |
|
Xerxesthefirst
Starting Member
2 Posts |
 Posted - 2009-12-18 : 10:19:07
|
| Hi,I am trying to use linked servers in MS SQL Server to read from one database (using a remote ip adress) and copy the information into another one (which is local).This script is used to create the linked servers. However, maybe one of the paramaters is incorrect.DSN is used to define ‘NewYork’ which is the name of the DB.exec sp_addlinkedserver@server='linkedsql', @srvproduct= '',@provider='MSDASQL', @datasrc='NewYork' ,@location='DRIVER={MSDASQL};SERVER=NewYork;UID=LightCity;PWD=salesorder' This script is used to check if it is working.EXEC sp_tables_ex 'linkedsql',NULL,NULL, 'NewYork'The first script works, the second gives an error, meaning that no connection is made to the ‘remote’ database.I wonder if the IP adress needs to be included at server, or that something else might be wrong.Some errors which show up are:OLE DB provider "MSDASQL" for linked server "linkedsql" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).".OLE DB provider "MSDASQL" for linked server "linkedsql" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]De SQL-server bestaat niet of de toegang tot de server is geweigerd.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "linkedsql2".Thanks for your advice! |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-12-18 : 15:48:48
|
| What does the code for sp_tables_ex look like?Terry-- Procrastinate now! |
 |
|
|
Xerxesthefirst
Starting Member
2 Posts |
Posted - 2009-12-21 : 06:56:41
|
| Hi all,The problem is solved. What I changed was this: server objects -> Linked servers -> properties on the connection. I changed the security tab, to make login using remote-connection with this uid and this pwd.That did the trick for me.Thanks again for your reactions! |
|
|
|
|
|
|
|
|
Linked server using remote db