Transact SQL – TroubleShooting Linked Server Errors

Background

Linked Servers offer a good way to communicate across two SQL Servers.

Also, they allow adequate, albeit cumbersome, work arounds to same server queries, as well.

 

Query

Original Query

Here is a test query that I was running….


use [tempdb]
go

if object_id('tempdb..#Procedure2') is not null
begin
	drop table #Procedure2
end

select *
FROM OPENQUERY
(
      [LOOPBACK]
	, 'set fmtonly off; exec [HRDB].[dbaCRM].[GetCSRAppointmentsLog] @RegionID = -1, @periodStart = ''2014-21'' , @periodEnd = ''2014-08-20'' '
)

 

 

 

Error Message

 


Msg 7357, Level 16, State 2, Line 10
Cannot process the object "set fmtonly off; exec [csTrafficSchool].[dbaCRM].[GetCSRAppointmentsLog] @RegionID = -1, @periodStart = '2014-21' , @periodEnd = '2014-08-20' ". The OLE DB provider "SQLNCLI" for linked server "LOOPBACK" indicates that either the object has no columns or the current user does not have permissions on that object.

Explanation

I knew I needed more that more than “Msg 7357, Level 16, State 2, Line 10”.  But, Googled for it anyways.

The come back was to add “set fmtonly off;“.

I already had that.

And, so what to do.

Workaround

SQL Server Profiler

Ran Profiler and found the problem…

SQLServerProfiler-2016-0516-0119PM

Errors

  1. User Error Message :- 1
    • Text Data :- Error converting data type varchar to datetime
    • Error :- 8114
    • Severity :- 16
    • State :- 1
  2. Exception
    • Error: 7357, Severity: 16, State: 2
  3. User Error Message :-2
    • Text Data :- Cannot process the object “set fmtonly off; exec [HRDB].[dbaCRM].[GetCSRAppointmentsLog] @RegionID = -1, @periodStart = ‘2014-21’ , @periodEnd = ‘2014-08-20’ “. The OLE DB provider “SQLNCLI” for linked server “LOOPBACK” indicates that either the object has no columns or the current user does not have permissions on that object.
    • Error :- 7357
    • Severity :- 16
    • State :- 2

Explanation

  1. Of course the useful error, “Error converting data type varchar to datetime“, was the one hidden

 

Revised Query

Here is the correct query….


use [tempdb]
go

if object_id('tempdb..#Procedure2') is not null
begin
	drop table #Procedure2
end

select *
FROM OPENQUERY
(
      [LOOPBACK]
	, 'set fmtonly off; exec [HRDB].[dbaCRM].[GetCSRAppointmentsLog] @RegionID = -1, @periodStart = ''2014-07-21'' , @periodEnd = ''2014-08-20'' '
)

Explanation

Unfortunately, the date was not fully expressed in the original code

 

Hidden Errors

Other hidden errors:

  1. When the target database name is not specified in the SQL; ad that database is different that the catalog specified during the Linke Server creation or default for the Login

 

Errors Exposed

One is not completely in the dark, as certain errors are exposed.

Exposed Errors includes:

  • Msg 9514, Level 16, State 1, Line 36
    Xml data type is not supported in distributed queries. Remote object ‘OPENQUERY’ has xml column(s).

 

Summary

Every time I am stubborn with SQL, it wins.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s