I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is SQL Server 2012.

When the stored procedure is a simple select, the third-party tool receives the result set, but when it accesses a temporary table first, it does not. For example, the following two stored procedures return the same result from SSMS, but only the first can be called successfully from the tool.

What I don't understand is how these two functions are different from an external perspective. They both take the same parameters, and the last query yields the same result.

Where is the stored procedure executed?

--This one works when called both ways
CREATE PROC [dbo].[sp_GetUsers] 
    Select Top 1000 userId 
    from Users

--This only works from SSMS. 
CREATE PROC [dbo].[sp_ComplicatedGetUsers] 

    If OBJECT_ID('tempdb..#TMPUsers') IS NOT NULL
        DROP TABLE #TMPUsers

    Select Top 1000 userId 
    INTO #TMPUsers
    from Users 

    Select * from #TMPUsers
  • but only the first can be called successfully from the tool you will have to tell us what 'unsuccessfull' means. an error occurs? What error? Feb 21, 2014 at 10:45
  • Sorry - left that out, because I was more interested in the general problem of how this is defined and where it is running. For example, if I run define a stand-alone function in C#, the signature is very formally defined in both parameters and return type, it would be run in the caller's context unless specifically launched on a thread, etc. I am new to interesting DB programming, and am trying to get a handle on what is defined and how when using a Sproc.
    – ElizabethH
    Feb 21, 2014 at 10:51
  • The exact error is "the procedure does not return a result set"
    – ElizabethH
    Feb 21, 2014 at 10:52
  • it must mean the procedure is not defined as you posted here. Do sp_helptext 'sp_ComplicatedGetUsers' and see what the engine sees as the procedure definition. Most likely the final select is not part of it. Feb 21, 2014 at 10:56
  • 4
    Add SET NOCOUNT ON in the proc (first line). It could be that the 'tool' sees the first message (xxx rows affected) and assumes that's the entire result set. Feb 21, 2014 at 10:58

1 Answer 1


Your 'tool', whatever it is, is not worth using. It does not follow the TDS protocol. Basically what happens it gets back a result from the server and stops short when parsing it, assuming the first entry is also the last entry. Whatever this tool does, I'm sure there is a decent replacement out there.

As to answer your question: the two procedures have different results, they are not identical from outside. The second incarnation creates additional 'results', in the form of the 'xxx rows affected' messages from the SELECT ... INTO... statement. This breaks your tool. NHibernate is notorious for similar problems, it breaks if the 'xx rows affected' is missing after an update (ie. if you do the update using a stored procedure that has SET NOCOUNT ON).

Pass along to the tool developers that they need to read the ODBC specifications about Processing Results:

Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure.

  • Regarding the tool ... probably so, but it isn't likely to go away in the near future, so we'll have to keep babying it along for a while yet. Thank you for your help.
    – ElizabethH
    Feb 21, 2014 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.