Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 SPROCs 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 yeilds the same result.
Where is the Sproc executed?

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

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

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

    Select Top 1000 userId 
    INTO #TMPUsers
    from Users 

    Select * from #TMPUsers
share|improve this question
    
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? –  Remus Rusanu Feb 21 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 at 10:51
    
The exact error is "the procedure does not return a result set" –  ElizabethH Feb 21 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. –  Remus Rusanu Feb 21 at 10:56
3  
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. –  Remus Rusanu Feb 21 at 10:58
show 2 more comments

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
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 at 11:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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