June 9, 2008 at 12:47 pm
Hello, we have an application which uses JDBC to pass prepared statements to our database. We definf several variables as strings but when the code is traced in SQL Server it looks like the following:
declare @P1 int
set @P1=180151509
declare @P2 int
set @P2=1
declare @P3 int
set @P3=1
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, N'select transition_seq, current_state, selling_outlet_id, event_timestamp, modified_by, last_order_event, sales_associate, order_type, comments, billing_cust_id, order_date from tbl_orders where order_id = @P1 and retailer_id = @P2 and transition_seq = (select max(transition_seq) from orders where order_id = @P3 and retailer_id = @P4)', @P2 output, @P3 output, @P4 output, N'@P1 nvarchar(4000) ,@P2 int ,@P3 nvarchar(4000) ,@P4 int ', N'00295060808001234117274777', 102, N'00295060808001234117274777', 102
select @P1, @P2, @P3, @P4
Why is it declared as an int and then a nvarchar? This seems to be causing performance issues with page locking. Is there some sort of configuration setting that causes this? Can we make it use a varchar instead of a nvarchar? I just noticed in another database on this same server it is using varchar instead of nvarchar. Any ideas?
Thank you
-David
Best Regards,
~David
June 9, 2008 at 1:17 pm
I had the 'pleasure' of working with a jdbc app a while back. From what I recall there is some property of the jdbc driver that determines whether things are varchar or nvarchar. I can't be more help than that unfortunately.
I had to change a few columns ion my DB to nvarchar to get around the conversion problems
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2008 at 1:37 pm
Gail, thank you for the reply. This has led me to the following link which explains this issue:
https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=2690
-David
Best Regards,
~David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply