Oledb stored procedure rowset binding

I have a strange problem with an OleDB call to a stored procedure that
returns a rowset.
Only the first time I execute the query, after I restart SqlServer, my
program crashes because
the rowset is empty. All next calls (after restarting my program, of
course) run successfully.

The context is:
1) if I do not bind the output rowset, my program doesn't crash
2) If I run the call without the binding and then I run the call with
the binding, it doesn't crash
3) It is not the first query I do in my session
4) the call crashes only if in the SP there are some inserts: it is a
well known problem with OleDB, but in many
other cases I have fixed it setting NOCOUNT to ONgigi (gigisoave@.libero.it) writes:
> I have a strange problem with an OleDB call to a stored procedure that
> returns a rowset.
> Only the first time I execute the query, after I restart SqlServer, my
> program crashes because
> the rowset is empty. All next calls (after restarting my program, of
> course) run successfully.
> The context is:
> 1) if I do not bind the output rowset, my program doesn't crash
> 2) If I run the call without the binding and then I run the call with
> the binding, it doesn't crash
> 3) It is not the first query I do in my session
> 4) the call crashes only if in the SP there are some inserts: it is a
> well known problem with OleDB, but in many
> other cases I have fixed it setting NOCOUNT to ON

Since you don't post any code, it is very difficult to tell. Then again,
even if you had, it would probably still have been difficult to tell.

First, exactly what API are you using? The OLE DB API itself? OLE DB
consumer templates? Something else? Which language do you use?

Next, let me try to see if I understand the scenario:

1) You start your machine.
2) SQL Server starts.
3) You start your application.
4) It runs some queries, and then it comes to this procedure that does
not return a rowset.
5) Your program crashes.
6) You restart your application, and now everything works fine.

Here I assumed that SQL Server ran locally. What happens if you have
SQL Server on a remote machine? Does the app still crash on first
access of SQL Server restart? What if you reboot the client machine
and run again?

Finally, while the start and stop of SQL Server could have something
to do with it, it could also be as trivial as buffer overrun, assuming
that you are programming in C++.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I can answer all your questions:

1) If I connect to a remote sqlserver the situation remains the same
2) I use AOleDB consuner template from VisualC++, but I know the
problem is in call to ICommand::Execute at oledb level.
3) when I restrat the server, the connection and the application are
closed.
4) Your scenario description is good.
Bye and thank you

> Since you don't post any code, it is very difficult to tell. Then again,
> even if you had, it would probably still have been difficult to tell.
> First, exactly what API are you using? The OLE DB API itself? OLE DB
> consumer templates? Something else? Which language do you use?
> Next, let me try to see if I understand the scenario:
> 1) You start your machine.
> 2) SQL Server starts.
> 3) You start your application.
> 4) It runs some queries, and then it comes to this procedure that does
> not return a rowset.
> 5) Your program crashes.
> 6) You restart your application, and now everything works fine.
> Here I assumed that SQL Server ran locally. What happens if you have
> SQL Server on a remote machine? Does the app still crash on first
> access of SQL Server restart? What if you reboot the client machine
> and run again?
> Finally, while the start and stop of SQL Server could have something
> to do with it, it could also be as trivial as buffer overrun, assuming
> that you are programming in C++.