NOCOUNT and linked servers

Published 11 July 09 04:16 PM | MartinBell
The other day I was looking at calling remote stored procedures on a linked server when I hit upon this problem or feature, depending on you point of view!!

An example is if you create a loopback linked server called localserver and a procedure such as:

USE AdventureWorks;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Production].[usp_getLongestManufactureProducts]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [Production].[usp_getLongestManufactureProducts]

CREATE PROCEDURE [Production].[usp_getLongestManufactureProducts]

      SELECT TOP 10 ProductID, Name, ProductNumber, DaysToManufacture
      FROM [Production].[Product]
      ORDER BY DaysToManufacture DESC


The procedure will SET NOCOUNT ON, and if you execute this procedure in the query window of management studio (which will SET NOCOUNT OFF by default):

[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

you do not get the number of rows affected message,
but if you run this on the linked server using the command:

EXEC [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

the number of rows affected message is returned. I tried this on SQL 2008, SQL 2005 and SQL 2000 and all exhibited the same behaviour.

This of course would cause a problem if you have a client application that didn’t expect the DONE_IN_PROC message to be returned.

I wanted to use synonyms to reference the remote procedure so that the application would not require any changes when accessing a remote database, but it looked like the solution would not be that simple! e.g.

CREATE SYNONYM [Production].[usp_getLongestManufactureProducts] FOR [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

(NOTE: this is where using a loopback server in the example can cause problems, create the synonym in a different database or schema) .

Erland Sommarskog pointed out to me that If you did a similar thing with an insert statement, you will not get a rows affected message. Therefore the message is not generated from the procedure itself, but because it is using the linked server.

Unfortunately there is no settable option for the linked server to suppress the rows affected message, therefore the only way to suppress the message would be to SET NOCOUNT ON locally i.e.

EXEC [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;

One possible solution would be if you could set this as an option on the linked server and this would mean the least amount of work for the DBA (always a good reason!), so I raised a connect item for this additional feature. This does have some drawbacks as it would be similar to setting the default connection properties so that NOCOUNT is always ON and as I described in my previous article, this is not always possible. So what is the solution?

My final solution was to create a shell procedure that contained two statements, the first was SET NOCOUNT ON, the second called the remote procedure e.g.

CREATE PROCEDURE [Production].[usp_getLongestManufactureProducts]
      EXEC [LocalServer].[Adventureworks].[Production].[usp_getLongestManufactureProducts] ;

Here is a script to create shell procedures for all remote procedures, it uses template parameters which you change change in SSMS using the “Specify Values for Template Parameters” on the Query menu.

Filed under: ,


# Alvin Ashcraft’s Morning Dew » Dew Drop (Weekend Edition) – July 11-12, 2009 said on July 12, 2009 02:28 AM:

Pingback from  Alvin Ashcraft’s Morning Dew » Dew Drop (Weekend Edition) – July 11-12, 2009

# Martin Bell UK SQL Server MVP said on July 24, 2009 06:57 PM:

In my posting about NOCOUNT and linked servers I provided a script to create the necessary shell procedures that enabled NOCOUNT to be set ON and avoiding the problems of ROWCOUNTs being returned by linked servers. One of the problems I encountered when

This Blog

SQL Blogs