Stored procedure fails when called from VB

[levinll]

I have a real mystery here. I have a stored procedure that works when I call it from SQL Server Query Analyzer, but returns EOF when I call it from my VB code. I even took the code that makes up the stored procedure and try running that from VB with the same results.

Here's the SQL code I'm using:

select * from GetProcessedTimecards where Header_ID in (select distinct Header_ID from GetEditedProcessedRecords where WeekEnding < '1/3/2003' and EditDate between '1/3/2003' and '1/10/2003')

I'm running the SQL code from within my VB app by using a persistent connection to my database. Here's what my VB code looks like:

Set rc = New ADODB.Recordset

With rc
.ActiveConnection = cnOnlineTimecard
.CursorLocation = adUseServer
.Source = SSQL
.Open
End With

Any idea why this would work in SQL Query Analyzer but not in VB would be greatly appreciate it.

 

[tlbroadbent]

Try adding SET NOCOUNT ON to the SP before other query statements. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[levinll]

I add &quot;SET NOCOUNT ON&quot; to my stored procedure ?

 

[tlbroadbent]

Do you mean you have already done that or are you asking if that is what I meant? If you are asking, then the answer is &quot;yes, add the line to the SP.&quot; If you've already tried that, maybe could you post the SP code and the VB code that executes the SP? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[levinll]

That worked, but I don't know why it did. Care to enlighten me ?

 

[tlbroadbent]

SET NOCOUNT ON eliminates the &quot;Rows affected&quot; messages that SQL Server normally displays. Those messages are returned as an additional result set to the client program. This requires special handling in the VB program. It is much easier to just eliminate the messages. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.