Prev: Problems with SS2K OPENROWSET accessing a dBase file on W2008
Next: Export Query into Files of 60k Records
From: Rich on 8 Jan 2010 12:19
I am trying to link a sql server to another sql server using
sp_addlinkedserver. Here is what I am doing but no luck:

First I add myself as an sa in the remote server userID='joe' pwd='abc'

then I do this in master of the primary server

EXEC sp_addlinkedserver
@server = 'svrRemote',
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = 'DRIVER={SQL Server};SERVER=serverDEF;UID=joe;PWD=abc;'

after running this sp I can see svrRemote in the LinkedServer section of
Enterprise Manager. But when I try to open it - I get the message "server
doesn't exist or access denied".

So I run


sp_addlinkedsrvlogin 'svrRemote'

but still nothing. What do I need to do to create this linked server?

Thanks

Rich





From: Rich on 8 Jan 2010 16:03
I solved my problem. I was misspelling the remoter servername. Here is what
I did:


EXEC sp_addlinkedserver 'myRemoteServer' --spelled correctly

then

exec sp_addlinkedsrvlogin 'myremoteServer,'false',NULL,'joe','abc'


now I can read data on the linked server from the primary server as follows

--from primary server

select count(*) from myremoteServer.DBonRemoteServer.dbo.tbl1

returns a count of rows





"Rich" wrote:

> I am trying to link a sql server to another sql server using
> sp_addlinkedserver. Here is what I am doing but no luck:
>
> First I add myself as an sa in the remote server userID='joe' pwd='abc'
>
> then I do this in master of the primary server
>
> EXEC sp_addlinkedserver
> @server = 'svrRemote',
> @srvproduct = '',
> @provider = 'SQLOLEDB',
> @provstr = 'DRIVER={SQL Server};SERVER=serverDEF;UID=joe;PWD=abc;'
>
> after running this sp I can see svrRemote in the LinkedServer section of
> Enterprise Manager. But when I try to open it - I get the message "server
> doesn't exist or access denied".
>
> So I run
>
>
> sp_addlinkedsrvlogin 'svrRemote'
>
> but still nothing. What do I need to do to create this linked server?
>
> Thanks
>
> Rich
>
>
>
>
>
 | 
Pages: 1
Prev: Problems with SS2K OPENROWSET accessing a dBase file on W2008
Next: Export Query into Files of 60k Records