Linked Servers Accessibility Question

[Skiboy]

I have 2 SQL2000 Servers (A and B) that are on the same Domain and are reciprocally linked (via Linked Server functionality).

SQLServer A and SQLServer B use an identically named user account (remote_user) to remotely access each other. This account is a member of the System Administrator's Server role on both machines.

My Problem is as follows:

I can either use a 4-part naming convention query or OPENQUERY to access data on SQL Server B from SQL Server A.

HOWEVER, I can only use the OPENQUERY functionality to access data on SQL Server A from SQL Server B. If I use a 4-part naming convention query I get an "Unknown SQLOLEDB error". My problem is that I need to parse variables through this query so it is imperative that I use the 4-part query rather than OPENQUERY (openquery does not support variables).

Any assistance would be greatly received.

cheers

Skiboy

 

[TheBugSlayer]

Both statements below should return the same resultset:

select * from [A].[pubs].[dbo].[employee]
SELECT * FROM OPENQUERY([A], 'SELECT * FROM pubs.dbo.employee')

Unless there is an issue with the security context. You are talking about the 4-part name, but notice that in the OPENQUERY statement the first argument is the server name so you don't need to repeat it in the SQL statement.

 

[Skiboy]

Thanks for the reply BugSlayer.

However, my conumdrum is not a SQL code syntax issue. It is the fact that one Server A I can use either 4-part naming convention or OPENQUERY (to access Server B) and on Server B I can only use OPENQUERY (to access Server A).... despite the fact that I have identically named and priveleged remote user accounts for the 2 linked Servers.

I think that you are onto something with regards to the Security context, but I have exhausted my resources to debug this issue.

Also. I can't just simply delete and re-create the accounts just yet as these are Production Servers with HEAVY SLA agreements for Uptime (I have previously been reserved to doing my tinkerings at 4am!!!)

thanks

Skiboy