asked
Sproc returns data in SSMS, none to VB6 client
Please help. I've inherited an old app which needs some TLC. One stored procedure runs awfully slow in its current state. So, in a couple places where **it used to do this**: `INSERT INTO @tableVariable1` `SELECT field1, field2, ... FROM fn_oldFunctionToPullData(@var1,@var2)` ... I've updated it to **do this, instead**: `CREATE TABLE #tempTable1 (field1,field2,...)` `INSERT #tempTable1` `EXEC dbo.usp_newStoredProcToPullData @var1,@var2` `INSERT INTO @tableVariable1` `SELECT field1, field2, ... FROM #tempTable1` The rest of the sproc, including the final SELECT statement, has not changed. This really speeds up the sproc. And, when I run the old and new sprocs in SSMS, the resultsets are identical. But, when I ALTER the stored procedure with the new code and try to test with the client app, no records are returned to the client. Any idea what could be causing that? I'm completely stumped.
Long time ago since I did some serious client programming. But I think I recall something similar happening when I had inserts and a finishing select - that also the inserts returned some kind of result sets to ADO, so that the first recordset is an empty one as a result from an insert. Try either to do use NextRecordset method of the ADODB.Recordset object, or even simpler, try adding **SET NOCOUNT ON** as the first statement of the procedure. That will take away any informative (yet annoying) recordset from the result. Again: Long time ago since I did client coding. Much longer since VB6. So I might be completely off in my answer. If so: My apologies.
Indeed, it seems the culprit was an additional recordset. A clue was in SSMS's Messages window where, above the usual "(n row(s) affected)" message, there was a new one: "The join order has been enforced because a local join hint is used." Thanks to your answer, now I know that's apparently sent to the client in a recordset. That message and the problem hadn't existed previously because the join hint was contained in (a View called by) a _Function_ (fn_oldFunctionToPullData above). When I replaced the Function with a _SProc_ (dbo.usp_newStoredProcToPullData above), the message showed up. Rather than update the client app to skip over the first recordset and use the second, I hoped to eliminate or suppress the message. First, I tried removing the join hint but, that dramatically increased the execution time. Then, neither SET NOCOUNT ON nor SET ANSI_WARNINGS OFF had any effect. Finally, when I added "OPTION (FORCE ORDER)" to the query containing the join hint, the message went away, the client app was able to display the data again, and the execution time was not affected. Success! So, thank you, Magnus, for the quick answer and for pointing me in the right direction.
10 People are following this question.
