steps to create a linked server

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 2005 Forums

SQL Server Administration (2005)

steps to create a linked server

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2007-05-07 : 23:55:45

hi,

what are the steps to create a linked server, I have tried unsuccesfully:
The scenario is the following:
From server A I want to be able to execute a select statement to a table in server B like
select * from [server B].myRemoteDb.dbo.myremTable

I am administrator of server A but not of server B. I tried the

sp_addlinkedserver syntax but have not been succesful, so I would be grateful if somebody could just list the steps necessary to link Server B from Server A,

thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-08 : 01:26:09

Can you post the sp_addlinkedserver query which you tried? Also you need to have permissions on the database on ServerB for accessing the data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

heze
Posting Yak Master

192 Posts

Posted - 2007-05-08 : 09:30:33

Harsh, Im doing:
EXEC sp_addlinkedserver
@server = 'myTest1',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'myActualServer'

and the message is:
OLE DB provider "MSDASQL" for linked server "myTest1" returned message "[Microsoft][ODBC SQL Server Driver]Timeout expired".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "myTest1".

thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-08 : 09:34:29

Are you using Windows Authentication for connecting to linked server?

Try this:

EXEC sp_addlinkedserver @server = 'myTest1', @srvproduct = '', @provider = 'MSDASQL', @datasrc = NULL, @location = NULL,@provstr = 'DRIVER={SQL Server};Server=myActualServer; Initial Catalog=master;Integrated Security=SSPI;'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

heze
Posting Yak Master

192 Posts

Posted - 2007-05-08 : 09:46:10

yes H, Im using Windows authentication,

I tried:

EXEC sp_addlinkedserver
@server = 'myTest1',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=myActualServer;
Initial Catalog=master;Integrated Security=SSPI;'

successfully and when I executed;

select * from [myTest1].myDb.dbo.myTable

this is what I got:

OLE DB provider "MSDASQL" for linked server "myTest1" returned message "[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute".
OLE DB provider "MSDASQL" for linked server "myTest1" returned message "[Microsoft][ODBC SQL Server Driver]Timeout expired".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "myTest1".

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources