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++.

OLEDB Provider Errors

I have an asp page that is using OLEDB to connect to SQL
Server 2K (We'll call it 2k1). It requests data from a
view on 2k1 which via linked server points to a an
instance of SQL Server 7.0 on a different box (We'll call
it 702). In running this command we receive very
sporadic errors. There are two in particular. Both are
regarding the transactions being used for the open
connection. Every thing I read points to using explicit
transactions which we are not. The error will last for
about 20 minutes and just disappear. The developers
stating that they are definately closing connections. I
don't know how to proceed.
Here are the errors.
1.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Connection is busy
with results for another command]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IOpenRowset::OpenRowset returned 0x80004005: ].
2.
Server: Msg 7392, Level 16, State 2,
Could not start a transaction for OLEDB
provider 'SQLOLEDB'. [OLE/DB provider returned message:
Only one transaction cant be active on this session.Are you using Transaction Object
try use the connection's tranaction objectsql

OLE DB DataSource w\ stored procedure not populating column metadata

I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.

I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.

-Jamie

|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up

OLE DB Command and Stored Procedure that returns value and/or error

Guys,

could someone please tell me : am I supposed to use the OLE DB

Command in a dataflow to call a stored procedure to return a value? Or

is it just supposed to be used to call a straightforward insert

statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a

dataflow I would like to effect an insert of a record to this table and

retrieve the identity value of the inserted record... and I'd like to

store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by

clicking on Add Column) I just get an error dialog that says "the

component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?

Thanks

PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ