Linked servers and OSQL utility.

Question

Post reply

Linked servers and OSQL utility.

deb-128405

SSC Veteran

Points: 275
More actions
August 17, 2005 at 1:26 am

#187047

Background:
I have server A linked to B and server B linked to A again. I am running an osql command from A to execute a query which actually runs on B.Now in the query it in turns access some tables from A. here is the query:
IN A:
-E -SB -dCopyDB -b -n -omsgs.txt -h -w250 -iReportXXX.sql (if u see the though the osql is run from A, the query is executed in B)
In ReportXXX.sql there is a store proc which in turns refers a table in A .It is accessed using fully qualified name ..i..e A.Dbname.dbo.table_name.
Problem:
Things were running smooth till we went ahead an installed another SQL instance on A. The default was running on sp3 and the second instance was initially running on sp1. We encountered a problem after this.
Executing sys_UpdateReportXXX...
Tue Aug 16 03:13:16 2005
Msg 17, Level 16, State 1, Server SQLOLEDB, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
*** ERROR Executing sys_UpdateReportXXX
Msg 17, Level 16, State 1, Server SQLOLEDB, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

We did the following to overcome the error:
1) Installed SQL SP4 on both the instances of both the servers
2) Verified access and permissions on both the machines
3) Verified server links on both the machines.we are able to run query from both the machines refering the tables in the other server .From QA it is fine but from OSQL it is giving problems.
4) Removed the second instance
5) Installed Firewall client on the SQL Server
6) Even tried replacing the dbnetlib.dll

But no help. Any thoughts on this will be appreciated.

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

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

deb-128405 SSC Veteran Points: 275 More actions · Answer 1

i would appreciate if some one could me help me on this.

Regards,

Deb

Sam Krueger SSC Enthusiast Points: 164 More actions · Answer 2

Throwing out things to try/check:

Has your firewall changed where it would deny port 1433 connections. Example Windows firewall can deny connection to the remote database. If it is turned on, check to make sure that you have an Execption for the Servname and port 1433

Have you tried dropping and re-creating the linked server?

In OSQL are you using -S Servername\Instance name

What is the error in OSQL, is it the same error?

Have you looked at: microsoft kb article on error: Potential causes of the "SQL Server does not exist or access denied" error messagehttp://support.microsoft.com/default.aspx?scid=kb;en-us;328306

deb-128405 SSC Veteran Points: 275 More actions · Answer 3

first of all thanks for the reply, as at last i got one reply

1) As i mentioned when i am running it through QA it is running fine, only when i am using osql it is giving problems.

2) i am connecting to the default and the instance was installed for a different reason post which i am facing this problem.

3)yes we tried re adding the linked servers.

4)yes i have gone through the above mentioned link.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Hi,

check the connection settings, do you connect with the same user in QA and osql?

karl

Best regards
karl

noeld SSC Guru Points: 96590 More actions · Answer 5

you are using -E on osql that would mean that you will need either to setup ACCOUNT DELEGATION in kerberos OR change the connection to use SQL authentication( -U -P) with permissions on the remote server

* Noel