Trouble shooting cursor

  • I am wondering if Cursors translate query statements, I am getting alerts for high CPU usage(Session CPU time is 1685.042 seconds for session ###). The process is running on a SQL2008 Server, but originating on a sql2000 server, via a linked server call(i assume) application name is simply 'Microsoft SQL Server', Unfortunately my organization in the past used a very generic sql userid and so the credentials do not not narrow down where this comes from. I ran a trace on the process, which runs for 1.5 hours. and this is the code i am catching that repeats. and based on this code i can't locate the code/job/process submitting this. This just started two days ago, and no know claims to have changed anything.

    the tbl0172 portion of this query is what i find troubling, does a cursor do some sort of translation on column names?

    SELECT Tbl1072."LASTDAYWORKED_I" "Col1719",Tbl1072."BENADJDATE" "Col1718",Tbl1072."EMPLCLAS" "Col1677",Tbl1072."EMPLOYID" "Col1676" FROM "TECH"."dbo"."UPR00100" Tbl1072

    declare @p1 int

    set @p1=-1

    declare @p2 int

    set @p2=NULL

    declare @p5 int

    set @p5=557064

    declare @p6 int

    set @p6=98305

    declare @p7 int

    set @p7=NULL

    exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT Tbl1399."ADRSCODE" "Col2627",Tbl1399."EMPLOYID" "Col2626",Tbl1399."PHONE1" "Col2634",Tbl1399."ZIPCODE" "Col2633",Tbl1399."STATE" "Col2632",Tbl1399."CITY" "Col2631",Tbl1399."ADDRESS3" "Col2630",Tbl1399."ADDRESS2" "Col2629",Tbl1399."ADDRESS1" "Col2628" FROM "CAN"."dbo"."UPR00102" Tbl1399',@p5 output,@p6 output,@p7 output

    select @p1, @p2, @p5, @p6, @p7

  • sorry please delete, I mis-read question.

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • This looks very like the type of code LYNQ \ Nhibernate generates. It may not be that someone has coded a cursor but the application is generating cursor code.

    MCITP SQL 2005, MCSA SQL 2012

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

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