Exception 156 then 16954 on calling procedure

Question

Exception 156 then 16954 on calling procedure

Ronnie_Doggart

SSC Veteran

Points: 299
More actions
October 1, 2010 at 7:16 am

#148917

I have a stored proc which when called in a customer database generates a trace like below:
RPC:Starting
Exception 156
Exception 16954
SP:CacheMiss
SP:CacheMiss
SP:stmtStarting
...
RPC:Complete
RPC Output Parameter
The procedure appears to succeed in that it returns the required data, I would just like to track down why it is giving the two execptions every time it is called.
Thanks
Ronnie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

16954 is related to cursors. Do you have one within the procedure? If so, something is going south with it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ronnie_Doggart SSC Veteran Points: 299 More actions · Answer 2

Here is a copy of the code:

CREATE PROCEDURE [dbo].[GetImptInstByNameID]

@aImportNameVARCHAR(100),

@aInstanceIDNUMERIC(18),

@aId NUMERIC(18) OUTPUT

AS

SET @aId = -1

BEGIN

SELECT @aId = IMPORTBUNDLEID

FROM IMPORTINSTVERSION

WHERE IMPORTNAME = @aImportName

AND INSTANCEID = @aInstanceID

END

There is no cursor.

Thanks

Ronnie

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 3

You're right. Is IMPORTINSTVERSION a UDF possibly?

It's odd that such a simple, straight-forward proc is getting cache misses. Unless I'm missing something, that should be sitting in cache waiting for you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ronnie_Doggart SSC Veteran Points: 299 More actions · Answer 4

No its a table:

CREATE TABLE [dbo].[IMPORTINSTVERSION](

[INSTANCEID] [numeric](18, 0) NOT NULL,

[IMPORTNAME] [varchar](50) NOT NULL,

[IMPORTBUNDLEID] [numeric](18, 0) NOT NULL

)

The procedure does not generate the exceptions on other customer databases, just with one customer.

Thanks

Ronnie

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 5

Ronnie_Doggart (10/1/2010)
No its a table:
CREATE TABLE [dbo].[IMPORTINSTVERSION](
[INSTANCEID] [numeric](18, 0) NOT NULL,
[IMPORTNAME] [varchar](50) NOT NULL,
[IMPORTBUNDLEID] [numeric](18, 0) NOT NULL
)
The procedure does not generate the exceptions on other customer databases, just with one customer.
Thanks
Ronnie

Well, then I'm stuck. Paranoia dictates you might want to run a consistency check.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ronnie_Doggart SSC Veteran Points: 299 More actions · Answer 6

Thanks,

I will ask the customer DBA to do that.

Ronnie