Link to Different SQL Database

12 May 2005 12:50 PM

Agnes

I had connect another SQL server VIA VPN , the server name is IP e,g
123.123.123.123
I can read the data sucessfully,
However, I want to insert some data from my local server.
sp_addlinked server '123.123.123.13'
sp_linkedserver (I can see it)

then select * from [123.123.123.123].database.dbo.myTable
I got an error said 'SQL Server does not exist or access denied'

Does any one can help , Thanks

12 May 2005 10:07 PM

João Araújo

Hi!
After adding the linked server you should tell SQL server which user on
local server may access remote server with system procedure
sp_addlinkedsrvlogin (see on BOL for details).
One useful idea is to add a name to the linked server in spite of the IP
address.
Hope that helps.
João Araújo

Show quote

"Agnes" <ag***@dynamictech.com.hk> escreveu na mensagem
news:OU2n3EvVFHA.2700@TK2MSFTNGP12.phx.gbl...
>I had connect another SQL server VIA VPN , the server name is IP e,g
> 123.123.123.123
> I can read the data sucessfully,
> However, I want to insert some data from my local server.
> sp_addlinked server '123.123.123.13'
> sp_linkedserver (I can see it)
>
> then select * from [123.123.123.123].database.dbo.myTable
> I got an error said 'SQL Server does not exist or access denied'
>
> Does any one can help , Thanks
>
>

13 May 2005 3:34 AM

Agnes

I had process sp_addlinkedserverlogin , but still got error.
BTw, how can i alias the linked server name ?
THanks
Show quote

"João Araújo" <joaoribeiroaraujo_DOMINIOIOL_@nospam.PT> ¼¶¼g©ó¶l¥ó·s»D:OF7DQ8zVFHA.3***@TK2MSFTNGP14.phx.gbl...
> Hi!
> After adding the linked server you should tell SQL server which user on
> local server may access remote server with system procedure
> sp_addlinkedsrvlogin (see on BOL for details).
> One useful idea is to add a name to the linked server in spite of the IP
> address.
> Hope that helps.
> João Araújo
>
> "Agnes" <ag***@dynamictech.com.hk> escreveu na mensagem
> news:OU2n3EvVFHA.2700@TK2MSFTNGP12.phx.gbl...
>>I had connect another SQL server VIA VPN , the server name is IP e,g
>> 123.123.123.123
>> I can read the data sucessfully,
>> However, I want to insert some data from my local server.
>> sp_addlinked server '123.123.123.13'
>> sp_linkedserver (I can see it)
>>
>> then select * from [123.123.123.123].database.dbo.myTable
>> I got an error said 'SQL Server does not exist or access denied'
>>
>> Does any one can help , Thanks
>>
>>
>
>

13 May 2005 6:30 AM

John Bell

Hi

Try something like:

EXEC sp_addlinkedserver @server='Remote_Svr', @srvproduct='',
@provider='SQLOLEDB',
@datasrc='123.123.123.123'

You can then use Remove_Svr as the name in your query. It sounds like
the login you are using does not have the correct permissions. You may
want to make sure that DTC is running on the server (although I would
expect a different message). You could use profiler on the remote
server to see what is happening.

John

Agnes wrote:

> I had process sp_addlinkedserverlogin , but still got error.
> BTw, how can i alias the linked server name ?
> THanks
> "João Araújo" <joaoribeiroaraujo_DOMINIOIOL_@nospam.PT>

> > Hi!
> > After adding the linked server you should tell SQL server which
user on
> > local server may access remote server with system procedure
> > sp_addlinkedsrvlogin (see on BOL for details).
> > One useful idea is to add a name to the linked server in spite of
the IP
> > address.
> > Hope that helps.
> > João Araújo
> >
> > "Agnes" <ag***@dynamictech.com.hk> escreveu na mensagem
> > news:OU2n3EvVFHA.2700@TK2MSFTNGP12.phx.gbl...
> >>I had connect another SQL server VIA VPN , the server name is IP
e,g
> >> 123.123.123.123
> >> I can read the data sucessfully,
> >> However, I want to insert some data from my local server.
> >> sp_addlinked server '123.123.123.13'
> >> sp_linkedserver (I can see it)
> >>
> >> then select * from [123.123.123.123].database.dbo.myTable
> >> I got an error said 'SQL Server does not exist or access denied'
> >>
> >> Does any one can help , Thanks
> >>
> >>
> >
> >

20 May 2005 8:11 AM

Renjith

Hi

Why should u put the IP Address in SELECT Statement , u can use the link
server Name ??
U can also use the OPENQUERY for this as

SELECT * FROM OPENQUERY('SQL STATAEMENT')

Renjith

Show quote

"Agnes" wrote:

> I had connect another SQL server VIA VPN , the server name is IP e,g
> 123.123.123.123
> I can read the data sucessfully,
> However, I want to insert some data from my local server.
> sp_addlinked server '123.123.123.13'
> sp_linkedserver (I can see it)
>
> then select * from [123.123.123.123].database.dbo.myTable
> I got an error said 'SQL Server does not exist or access denied'
>
> Does any one can help , Thanks
>
>
>