linked server, error 4064

[JeffMachine]

I created a query that links 2 servers (used sp_addlinkedserver). I used this query for a Crystal report. Works fine if I run the report. I assume because I have admin rights. My users get a 4064 (failed to retrieve data) error code when running the report however. Any ideas on how to get rid of this error?

Thanks ...

 

[fredericofonseca]

probably the default database for that user is not set or is not correct

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JeffMachine]

Thanks for the response Frederico.

I'm not sure that's the issue though. If I remove the part of the query that accesses the linked server (it is a union query with the second part hitting the linked server) the users can run the report successfully. They just don't get any data from the second server.

 

[JeffMachine]

I figured out to do this for one user. If I run this command I can get it to work for jsmith:

EXEC sp_addlinkedsrvlogin 'pcisv002', 'false', 'domain\jsmith, 'report', 'report'

What would be the same command but that would work for all users? I don't want to have to run this for every current and future user.

Thanks ....

 

[JeffMachine]

This seems to have taken care of it:

EXEC sp_addlinkedsrvlogin 'pcisv002', 'false', @rmtuser = 'report', @rmtpassword ='report'