NOCOUNT and linked servers
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;
GO
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]
GO
CREATE PROCEDURE [Production].[usp_getLongestManufactureProducts]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 ProductID, Name, ProductNumber, DaysToManufacture
FROM [Production].[Product]
ORDER BY DaysToManufacture DESC
END
GO
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):
EXEC [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) .
MVP 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.
SET NOCOUNT ON ;
EXEC [LocalServer].[AdventureWorks].[Production].[usp_getLongestManufactureProducts] ;
SET NOCOUNT OFF ;
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]
AS
BEGIN
SET NOCOUNT ON ;
EXEC [LocalServer].[Adventureworks].[Production].[usp_getLongestManufactureProducts] ;
END
GO
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.