Linked Server Problem - 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)

Linked Server Problem

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-16 : 14:22:03

Hi there guys
I have set up replication from one server to the next in sql 2000, I am trying to add a dts command onto the distributing servers exsisting dts task with no joy.

I cannot create a linked server in QA because it says the server already exsists but when I try to access the data remotely it comes up with a error 'data access is not configured'

So I set my server options to the following

exec sp_serveroption 'abc', 'data access', 'true'
and restarted my server aswell as checked the connection properties of the server I want to link to to accept remote connections.

But I still cant query my remote server.
Boo Hoo.

maranbe
Starting Member

28 Posts

Posted - 2006-08-17 : 02:58:16

hi,

you may need to check security setup on the linked server properties ...

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
if ain't broke, don't fix it...

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-17 : 09:57:19

If the server is already there, you shouldn't need to drop and re-add it. What happens when you try to make a remote call?

You may need to add a linked server login if it hasn't already been done. Read up on it in BOL. Here is some sample code:

--add login used for linked server connectivity
sp_addlinkedsrvlogin
@rmtsrvname = '<OtherServer>'
,@useself = 'false'
--,@locallogin = 'locallogin'
,@rmtuser = '<OtherServerLogin>'
,@rmtpassword = '<pw>'

--add login to remote server (this would be run on remote server)
sp_addlogin '<LoginName>', '<pw>'
sp_addsrvrolemember '<LoginName>', 'sysadmin'
sp_defaultdb '<LoginName>', 'master'

Be One with the Optimizer
TG

Thanks for the advice maranbe the above posted by TG was the solution I was looking for
cheers ....:>)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources