sp_addlinkedserver - SQL Server Forums

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

SQL Server Administration (2000)

sp_addlinkedserver

Author

Topic

Blin
Starting Member

36 Posts

Posted - 2004-10-27 : 13:07:19

I wanted to be able to select data from server A and insert into server B, so in server B I ran this procedure:

sp_addlinkedserver @server ='BSCCMARS'
, @srvproduct = ''
, @provider = 'SQLOLEDB'
, @datasrc = 'BSCCMARS'
, @location = ''
, @provstr = 'BSCCMARS'

(1 row(s) affected)

and then did (still in server B):

insert into dbnameB.dbo.rawdata select * from
bsccmars.dbnameA.dbo.rawdata

and I got this error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].

Did I input anything wrong in sp_addlinkedserver? Please help.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-27 : 16:36:46

your @provstr is invalid, try this instead:

EXEC sp_addlinkedserver @server='BSCCMARS'
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='BSCCMARS'

@provstr is usually something like:

@provstr ='DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=sapassword'

this is only used for other providers (other than SQLOLEDB).

-ec

Blin
Starting Member

36 Posts

Posted - 2004-10-27 : 22:24:51

I set it to NULL and it worked. Thanks for your help, eyechart!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources