May 4, 2013 at 7:30 am
Something about our production sql cluster is preventing the initial setup for Safenet encryption. The device sends sql to our prod cluster via jdbc driver. We know the sql arrives because we've captured it in profiler. The error returned is: microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set
The sql sent to sql 2005 enterprise cluster from Safenet via jdbc driver, captured in profiler:
Note paramater @p1 is set to null but the following call to sp_cursorprepexec places “NULL” as a parameter instead of variable @p1
Partial profiler capture:
set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
set transaction isolation level read committed
set implicit_transactions on
declare @p1 int
set @p1=NULLdeclare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'declare @database varchar(128), @owner varchar(128), @exitStatus int set @exitStatus = 1 exec master.dbo.ing_get_metadata_info @database out, @owner out, @exitStatus out; select @database, @owner, @exitStatus ',16,8193,@p7 output
select @p1, @p2, @p7
NULL
Prod sql configurations that can be considered non-standard or at least not default.
name run_value default_value
Agent XPs 1 0
clr enabled 1 0
Database Mail XPs 1 0
fill factor (%) 90 0
max degree of parallelism8 0
max server memory (MB)356352 2147483647
min server memory (MB)356352 0
Ole Automation Procedures1 0
remote admin connections1 0
show advanced options1 0
user options 20856 0
xp_cmdshell 1 0
User options for my sql connection to prod cluster:
21880
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL
XACT_ABORT
May 8, 2013 at 6:17 am
We found that having user option XACT_ABORT turned on was causing the problem. No one is sure how this got set in production since user options ( in sys.configurations ) is zero on most of our QA sql servers. We'll be turning this off soon -- its dynamic so could be turned back on quickly if there were issues, but it does wipe the execution plan cache when you change it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply