Linked servers SQL 2000 to SQL 2000

Question

Linked servers SQL 2000 to SQL 2000

Sam Greene

SSCertifiable

Points: 5252
More actions
January 26, 2007 at 3:37 pm

#72616

Hi,
You think i would learn my lesson about pressing the preview button on a large post. ugh. dont to it - unless you copy your text.
anyway. I'm trying to create a bunch of linked servers with a proc - some code like this:
set @MYPROVSTR= 'DRIVER={SQL Server};SERVER=''' + @HOSTNAME + ''';UID=sql_dba_link;PWD=haXXmyserver;Initial Catalog=SQL_DBA'

Exec sp_addlinkedserver @server=@HOSTNAME,
@srvproduct='',
@provider='MSDASQL',
@provstr= @myprovstr

Exec sp_serveroption @HOSTNAME, 'data access', 'true'
Exec sp_serveroption @HOSTNAME, 'rpc', 'true'
Exec sp_serveroption @HOSTNAME, 'rpc out', 'true'
Exec sp_serveroption @HOSTNAME, 'collation compatible', 'true'

EXEC sp_addlinkedsrvlogin @HOSTNAME, 'false', 'sa', 'sql_dba_link', 'haXXmyserver'
Creates fine - 2 out of 15 servers actually link up. when browsing the tables in EM i get this message:
Error 7399: ole db provider 'msdasql' reported an error
invalid connection string attribute]
connectionopen(connect()).]
sql server does not exist or access denied.]
ole db error trace [ ole/db provider 'msdasql' idbinitialize::initialize returned 0x80004005

So I try an openrowset like this:
USE sql_dba
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=zabba09;UID=sql_dba_link;PWD=hackit',
sql_dba.dbo.SQLDBA_Servers) AS a
GO
Works fine. I've also created the sql_dba_link account with matching pwds on all server.
Thanks for your time!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Are those named instances? If yes try using port number along with instance name... also try connecting to the linked server from the source server using QA...

MohammedU
Microsoft SQL Server MVP

Minaz Amin SSChampion Points: 11052 More actions · Answer 2

Cause :

-> ' Double Hop ' issue authentication !

-> IPSec policy is enabled !

-> Named Instance then agreed with Uddin to force port number along with the instance name.

Link for more information

http://support.microsoft.com/kb/888228/en-us

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

Sam Greene SSCertifiable Points: 5252 More actions · Answer 3

Thanks for your help gentlemen,

The server is not a named instance.

When executing a select I get this:

select * from MyLinkedServer.sql_dba.dbo.SQLDBA_ServerInfo

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'MSDASQL' reported an error.

[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute]

[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).]

[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Sam Greene SSCertifiable Points: 5252 More actions · Answer 4

My current connection string is:

DRIVER={SQL Server};SERVER='ISERVER';UID=sql_dba_link;PWD=MyPwd;Initial Catalog=SQL_DBA

Where would I put the port information as seen below? from http://support.microsoft.com/kb/888228/en-us

[oledb]

; Hardcoded TCP OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=clientID;Data Source=tcp:TcpIpAddress,port

Lowell SSC Guru Points: 323518 More actions · Answer 5

I think it's because your server name has single quotes in it, which is invalid. i know you can use double quotes, but not single.

bad: DRIVER={SQL Server};SERVER='ISERVER';UID=sql_dba_link;PWD=MyPwd;Initial Catalog=SQL_DBA

good: DRIVER={SQL Server};SERVER=ISERVER;UID=sql_dba_link;PWD=MyPwd;Initial Catalog=SQL_DBA

good: DRIVER={SQL Server};SERVER="ISERVER";UID="sql_dba_link";PWD="MyPwd";Initial Catalog="SQL_DBA"

this works on my system, whena dding a server named "db1"declare @HOSTNAME varchar(30),

@MYPROVSTR varchar(500)

SET @HOSTNAME='db1'

set @MYPROVSTR= 'DRIVER={SQL Server};SERVER=' + @HOSTNAME + ';UID=sql_dba_link;PWD=haXXmyserver;Initial Catalog=SQL_DBA'

Exec sp_addlinkedserver @server=@HOSTNAME,

@srvproduct='',

@provider='MSDASQL',

@provstr= @myprovstr

Exec sp_serveroption @HOSTNAME, 'data access', 'true'

Exec sp_serveroption @HOSTNAME, 'rpc', 'true'

Exec sp_serveroption @HOSTNAME, 'rpc out', 'true'

Exec sp_serveroption @HOSTNAME, 'collation compatible', 'true'

EXEC sp_addlinkedsrvlogin @HOSTNAME, 'false', 'sa', 'sql_dba_link', 'haXXmyserver'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sam Greene SSCertifiable Points: 5252 More actions · Answer 6

Sam Greene

SSCertifiable

Points: 5252

February 7, 2007 at 1:40 pm

#688385

Lowell,

You sir, are much appreciated!

Thanks

Sam