problem with linked server

Question

problem with linked server

martellianz

SSC-Addicted

Points: 423
More actions
September 24, 2008 at 2:25 am

#77091

dear all,
i have a problem when i want to access data from another table in another server..i use sp_addlinkedserver and sp_addlinkedsrvlogin..
example :
EXEC sp_addlinkedserver
@server = 'kawi',
@srvproduct = '',
@provider = 'SQLOLEDB',
@datasrc = 'smartbiz'
my server's name is kawi and the database is smartbiz..
2nd step i used
EXEC sp_addlinkedsrvlogin 'kawi', 'false', NULL, NULL, NULL
but when i use this syntac
select * from kawi.smartbiz.dbo.BudgetBySalesman
there is some error msg
'Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]'
anyone can help me???
my 2nd server name is SVR2 dan database is"smartbiz2"
regards

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

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

smackie1973 SSC Enthusiast Points: 136 More actions · Answer 1

It's woth checking that you can see the remote tables via Enterprise Manager.

Expand Security, Linked Servers, then the link you have created and select tables. If your link is working ok, you will see all the remote tables.

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 2

Hi,

You should make the @useself parameter to TRUE this will impersonate local logins to connect to remote server.

exec sp_addlinkedsrvlogin 'remotesrv', 'true'

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

martellianz SSC-Addicted Points: 423 More actions · Answer 3

dear all,

i already use the command EXEC sp_addlinkedsrvlogin 'kawi', 'true'

but the error msg is still the same and i already expand the security login to sql server and windows login also..any other way to access the table in another server?thanks alot

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 4

Hi,

Just noticed that "smartbiz" is a database and not server is it? It is supposed to be instance name... Below is the sample code

EXEC sp_addlinkedserver

@server = 'Payroll',

@srvproduct = '',

@provider = 'SQLOLEDB',

@datasrc = 'SQLNT001'

GO

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

martellianz SSC-Addicted Points: 423 More actions · Answer 5

yap,"smartbiz" is a database name and the server name is "kawi".....

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 6

martellianz (9/25/2008)
yap,"smartbiz" is a database name and the server name is "kawi".....

As Sakthi mentioned, you should use server name, not the database name, as the data source.