Calling SQL Server Stored Procedures: no results from SELECTfollowing INSERT

[bellahsk]

A Perl script using DBI calls a Stored Procedure on a SQL Server
database (connect, prepare, execute, fetchrow_arrayref, finish, etc).

The SP needs to:
- INSERT a record using arguments it got
- return result codes using a SELECT statement

If the SP SELECTs anything, even a constant value (e.g. SELECT "OK")
BEFORE the INSERT,
the calling Perl script gets the result.

If the SP SELECTs anything AFTER the INSERT,
no result comes back.
I do see in the DB that the INSERT succeeded though.

Any clue why INSERT might have such side effect?

 

[bellahsk]

The question is - why nothing can be received by the caller from
SELECTS that follow INSERT?
Is it possible that only one dataset may come back from SP, and that
INSERT results in an
empty dataset, in that way blocking results from following SELECTs?

 

[Skye Shaw!@#$]

The question is - why nothing can be received by the caller from
SELECTS that follow INSERT?
Is it possible that only one dataset may come back from SP, and that
INSERT results in an
empty dataset, in that way blocking results from following SELECTs?

What driver are you using?

Can you post your sp code?