Issue while fetching data on execution of stp

Question

Issue while fetching data on execution of stp

saum70

SSC Eights!

Points: 993
More actions
May 3, 2016 at 5:26 am

#307070

Hi,
One of our application coded in VC++(VS2008R2) uses CRecordSet::OpenEx method to fetch records from the database (MS SQL 2008R2). When deployed in one of the live environment(cluster setup exists), and after many successful execution of the procedures, one stored procedure failed.This store procedure has a simple select statement that returns data from a user table.
On checking the profiler, we found that sp_cursoropen was implicitly called and that procedure was executed using values as shown below
Declare @p1 int
Set @p1 = 0
Declare @p3 int
Set @p3 = 4104
Declare @p4 int
Set @p4 = 8193
Declare @p5 int
Set @p5 = 0
Declare @p7 int
Set @p7 = NULL
exec sp_cursoropen @p1 output,N'EXEC @P1=STP_Name @P2', @p3 output, @p4 output, @p5 output, N'@P1 int OUTPUT,@P2 int', @p7 output,1
Select @p1,@p3,@p4,@p5,@p7
However we are unable to trap the error code or message.
More surprising is that this calling method successfully runs in other live environment(cluster setup exists) and the application works properly.
Please guide as to how can we debug this and run our application successfully.
Regards,
Saumik Vora

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

since you don't have the error yet, i'm leaning towards checking things that are different in the other environment, like ANSI_NULLS and QUOTED_IDENTIFIER.

maybe the procedure was compiled or is running with some session settings that are not the same; that can happen if it was deployed from an app or sqlcmd in one instance, but SSMS in test for example.

first, check if they proc shows up with settings different than SSMS: this compares the saved compilation settings to your SSMS session settings: other session settings are not saved as a compile option

SELECT Quotename(schema_name(schema_id)) + '.'

+ Quotename(NAME) AS ObjectName,

defz.uses_ansi_nulls AS 'ANSI_NULLS',

defz.uses_quoted_identifier AS 'QUOTED_IDENTIFIER',

Sessionproperty('ANSI_NULLS') AS SessionAnsiNulls,

Sessionproperty('QUOTED_IDENTIFIER') AS SessionQuotedIdentifier

FROM sys.objects procz

INNER JOIN sys.sql_modules defz

ON procz.object_id = defz.object_id

WHERE defz.uses_ansi_nulls <> Sessionproperty('ANSI_NULLS')

OR defz.uses_quoted_identifier <> Sessionproperty('QUOTED_IDENTIFIER')

you can pull out your current settings in SSMS, and compare them to the settings in your c++ app:

SELECT '--CurrentSettings' UNION ALL

SELECT ' SET ANSI_NULLS ' + CASE WHEN SESSIONPROPERTY('ANSI_NULLS') = 1 THEN 'ON; ' ELSE 'OFF;' END AS cmd UNION ALL

SELECT ' SET ANSI_PADDING ' + CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 1 THEN 'ON; ' ELSE 'OFF; ' END UNION ALL

SELECT ' SET ANSI_WARNINGS ' + CASE WHEN SESSIONPROPERTY('ANSI_WARNINGS') = 1 THEN 'ON; ' ELSE 'OFF; ' END UNION ALL

SELECT ' SET ARITHABORT ' + CASE WHEN SESSIONPROPERTY('ARITHABORT') = 1 THEN 'ON; ' ELSE 'OFF; ' END UNION ALL

SELECT ' SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') = 1 THEN 'ON; ' ELSE 'OFF; ' END UNION ALL

SELECT ' SET NUMERIC_ROUNDABORT ' + CASE WHEN SESSIONPROPERTY('NUMERIC_ROUNDABORT') = 1 THEN 'ON; ' ELSE 'OFF; ' END UNION ALL

SELECT ' SET QUOTED_IDENTIFIER ' + CASE WHEN SESSIONPROPERTY('QUOTED_IDENTIFIER') = 1 THEN 'ON; ' ELSE 'OFF; ' END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saum70 SSC Eights! Points: 993 More actions · Answer 2

Hi Lowell,

Thanks for replying. However i am not sure if settings of ANSI_NULL or ANSI_PADDING exists in C++ (as my colleague informed me). Hence how the comparison can be done. Though we will compare these settings between successful enviornment and non successful one.

Also the stored procedure was recompiled in the same enviorment. Further we have also found that all the stored procedure called by CRecordSet method gives error.

Hope to get the solution

saum70 SSC Eights! Points: 993 More actions · Answer 3

Hi,

I tried to run the above mentioned cursor independently in T-SQL query and found below error

Executing SQL directly; no cursor

A severe error occurred on the current command. The result, if any, should be discarded