none
Executing SQL directly; no cursor. RRS feed

  • General discussion

  • Hi,

    Any one knows how to get rid of the "Executing SQL directly; no cursor." message. It happen in all versions of SQL Server 2005/2008

    Below is the code for testing. 

    Thanks,

    Shawn

     

    create table dbo.test (a int, b varchar(20))
    go
    create procedure [dbo].[test_UPD]
    @b char(20),
    @where1 int
    as
    begin
     update dbo.test with(rowlock,updlock) set b = @b
      where a = @where1
    if @@rowcount <> 1 raiserror ('Multirow update not allowed.',16,10)
    end
    go
    grant execute on [dbo].[test_UPD] to public
    go
    insert into test values (1,'a')
    select * from test
    go
    EXEC test_UPD 'b',1
    go
    /* Use sp_cursoropen */
    declare @cursor_handle int,
            @stmt nvarchar(2000),
            @scroll_options int,
            @concurrency_options int,
            @rowcount int,
            @paramdef nvarchar(2000),
            @param_value1 int 
    set @cursor_handle=NULL
    set @stmt = N' EXEC test_UPD @P1,@P2'
    set @scroll_options=20484
    set @concurrency_options=8194
    set @rowcount=0
    set @paramdef = N'@P1 char(20),@P2 int'
    exec sp_cursoropen @cursor_handle output,@stmt,@scroll_options output,@concurrency_options output,@rowcount output,@paramdef,'c',1
    select @cursor_handle, @scroll_options, @concurrency_options, @rowcount

    • Moved by Tom PhillipsModerator Monday, June 28, 2010 3:48 AM TSQL Question (From:SQL Server Database Engine)
    Friday, June 25, 2010 10:36 PM

All replies

  • I don't get that message when I execute the above.

    (on SQL Express 2008, all I have here sorry)

    I do get it if I add another statement, exec sp_cursorclose @cursor_handle, and then I get the message first, before any other messages.  That's weird.  And then the close complains that the handle is not valid.  I don't grok that.  Have to try a fetch on it first or something?

    Of course one would ask, why are you doing this, and I assume you're trying to debug a sequence that comes from some ADO app.  I haven't used these SP's for anything, certainly not in many years, so can't really say more.  Good luck!

    Josh

     

    Friday, June 25, 2010 11:00 PM
  • What is your real problem?

    Books Online says this about sp_cursoropen & co:


       These system stored procedures will show up in SQL Server Profiler
       traces of ADO, OLE DB, and ODBC applications that are using API server
       cursors. They are intended only for the internal use of the SQL Server
       Native Client OLE DB provider and the SQL Server Native Client ODBC
       driver. The full functionality of these procedures is available to the
       applications through the use of the cursor functionality of the
       database APIs. Specifying the procedures directly in an application is
       not supported.   
    I can understand the message; there is no way to run a cursor over stored procedure that performs an update.

    If your application is using ADO and this message causes you problem,
    switch to using client-side cursors; that's usually to recommend anyway.

    -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Monday, June 28, 2010 6:43 AM
  • Hi,

    The real problems is that the application update fails. The application (not ADO) send an exec test_UPD (?,?) via ODBC native client to SQL Server where ? denotes a bind variable. Then I got the "Executing SQL directly; no cursor". Usually this message acts like a warning and the stored procedure executes successfully. But with one of our SQL server, it issued that message and then stopped at RPC:starting and did not go further to exec the stored procedure.

    I could not find any difference in SQL Server setup. They all set up the same way. 

    The example I provided is a simplified version of what's going on in order to let you see the problem. The actual statement showed in the profiler is below.

    Can you tell me why you wouldn't think it's SQL Server bug? Or how to work around it?

    Thanks,
    Sha

    RPC: Starting
    declare @p1 int
    set @p1=0
    declare @p3 int
    set @p3=20484
    declare @p4 int
    set @p4=8194
    declare @p5 int
    set @p5=0
    exec sp_cursoropen @p1 output,N' EXEC [cnf2_UPD] @P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57,@P58,@P59,@P60,@P61,@P62,@P63,@P64,@P65,@P66,@P67,@P68,@P69,@P70,@P71,@P72,@P73,@P74,@P75,@P76,@P77,@P78,@P79,@P80,@P81,@P82,@P83,@P84,@P85,@P86,@P87,@P88,@P89,@P90,@P91,@P92,@P93,@P94,@P95,@P96,@P97,@P98,@P99,@P100,@P101,@P102,@P103,@P104,@P105,@P106,@P107,@P108,@P109,@P110,@P111,@P112,@P113,@P114,@P115,@P116,@P117,@P118,@P119,@P120,@P121,@P122,@P123,@P124,@P125,@P126,@P127,@P128,@P129,@P130,@P131,@P132,@P133,@P134,@P135,@P136,@P137,@P138,@P139,@P140,@P141,@P142,@P143,@P144,@P145,@P146,@P147,@P148,@P149,@P150,@P151,@P152,@P153,@P154,@P155,@P156,@P157,@P158,@P159,@P160,@P161,@P162,@P163,@P164,@P165,@P166,@P167 
    ',@p3 output,@p4 output,@p5 output,N'@P1 char(1),@P2 numeric(3, 0),@P3 numeric(4, 0),@P4 bit,@P5 bit,@P6 bit,@P7 bit,@P8 bit,@P9 bit,@P10 bit,@P11 numeric(10, 2),@P12 numeric(10, 2),@P13 char(1),@P14 char(12),@P15 numeric(7, 3),@P16 numeric(4, 0),@P17 numeric(7, 3),@P18 bit,@P19 bit,@P20 bit,@P21 bit,@P22 bit,@P23 bit,@P24 bit,@P25 bit,@P26 bit,@P27 bit,@P28 bit,@P29 bit,@P30 numeric(1, 0),@P31 numeric(1, 0),@P32 numeric(1, 0),@P33 numeric(1, 0),@P34 numeric(4, 0),@P35 numeric(4, 0),@P36 numeric(4, 0),@P37 numeric(4, 0),@P38 char(1),@P39 char(1),@P40 char(1),@P41 char(1),@P42 bit,@P43 bit,@P44 bit,@P45 bit,@P46 bit,@P47 bit,@P48 bit,@P49 bit,@P50 bit,@P51 bit,@P52 bit,@P53 bit,@P54 bit,@P55 bit,@P56 bit,@P57 bit,@P58 bit,@P59 bit,@P60 bit,@P61 bit,@P62 bit,@P63 bit,@P64 bit,@P65 bit,@P66 bit,@P67 char(12),@P68 char(40),@P69 char(12),@P70 char(40),@P71 numeric(2, 0),@P72 numeric(2, 0),@P73 numeric(2, 0),@P74 numeric(2, 0),@P75 numeric(7, 3),@P76 numeric(3, 0),@P77 numeric(3, 0),@P78 numeric(3, 0),@P79 numeric(3, 0),@P80 bit,@P81 numeric(4, 0),@P82 char(8),@P83 bit,@P84 char(4),@P85 char(1),@P86 bit,@P87 char(2),@P88 char(2),@P89 char(2),@P90 char(2),@P91 bit,@P92 bit,@P93 bit,@P94
    bit,@P95 char(1),@P96 char(1),@P97 char(1),@P98 char(1),@P99 char(1),@P100 char(1),@P101 char(1),@P102 char(1),@P103 char(1),@P104 char(2),@P105 char(1),@P106 bit,@P107 char(1),@P108 bit,@P109 char(1),@P110 numeric(8, 0),@P111 numeric(8, 0),@P112 numeric(1, 0),@P113 datetime,@P114 char(3),@P115 numeric(2, 0),@P116 numeric(1, 0),@P117 numeric(1, 0),@P118 numeric(1, 0),@P119 numeric(1, 0),@P120 char(2),@P121 bit,@P122 bit,@P123 bit,@P124 bit,@P125 bit,@P126 bit,@P127 numeric(2, 0),@P128 bit,@P129 bit,@P130 char(3),@P131 char(3),@P132 char(3),@P133 char(3),@P134 char(8),@P135 char(8),@P136 char(8),@P137 char(8),@P138 bit,@P139 char(2),@P140 char(3),@P141 char(2),@P142 char(2),@P143 numeric(4, 0),@P144 numeric(4, 0),@P145 numeric(4, 0),@P146 numeric(4, 0),@P147 char(2),@P148 char(2),@P149 char(2),@P150 char(2),@P151 char(2),@P152 char(16),@P153 numeric(6, 2),@P154 char(16),@P155 numeric(6, 2),@P156 char(16),@P157 numeric(6, 2),@P158 char(16),@P159 numeric(6, 2),@P160 numeric(1, 0),@P161 numeric(3, 0),@P162 char(2),@P163 datetime,@P164 char(1),@P165 varchar(2000),@P166 char(1),@P167 char(1)','1',0,365,0,0,0,0,0,0,0,NULL,NULL,'1',NULL,50.000,20,50.000,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,1,NULL,NULL,NULL,120,NULL,NULL,NULL,'1',NULL,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,0,NULL,NULL,0,NULL,NULL,'1 ',NULL,0,0,0,0,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1 ',NULL,1,'T',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,4,1,8,1,'01',0,0,0,0,0,0,NULL,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,'00',NULL,'00','00',NULL,NULL,NULL,NULL,'00','02','00','00','00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'00','Jun 24 2010 11:17:26:810AM','f',NULL,'1','e'
    select @p1, @p3, @p4, @p5

    Error: 156, Severity: 15, State: 1
    Incorrect syntax near the keyword 'update'.
    Error: 16954, Severity: 10, State: 1
    Executing SQL directly; no cursor.

     

     

     

    Monday, June 28, 2010 2:44 PM
  • My first reflection is why do you run a stored procedure with a server-side cursor? My experience of direct ODBC programming is limited, so I cannot
    assist, but I would either request the corresponding thing to a client-
    side cursor (if this concept exists in ODBC), or say that I'm not expect any result set at all.

    But there is another thing that attracts my eyes:

    Error: 156, Severity: 15, State: 1
    Incorrect syntax near the keyword 'update'.
    Error: 16954, Severity: 10, State: 1
    Executing SQL directly; no cursor.
    What is that syntax error about? Isn't that the real error? What does this stored procedure do? Does it employ dynamic SQL?


    -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Monday, June 28, 2010 8:43 PM
  • Incorrect syntax near the keyword 'update' is not the real error. You can prove it by running the scripts posted in my first post above. If you execute the stored procedure itself it works fine.

    Calling to server side cursor is not something that I can control. What I can control is to submit  exec test_UPD (?,?) via ODBC native client to SQL Server where ? denotes a bind variable. The tool makes a procedural call to the server via ODBC.

     

    Monday, June 28, 2010 9:34 PM
  • (shawnnnnnn) writes:

    Incorrect syntax near the keyword 'update' is not the real error. You
    can prove it by running the scripts posted in my first post above. If
    you execute the stored procedure itself it works fine.
    So you mean that the syntax error is a by-product of the changed cursor
    type?

    Running your first example proves nothing. The actual call you make seem
    to produce a syntax error, and in that case this is what you should investigate, don't suppressing of an informational message.

    I would suggest that you run the call to cnf2_UPD directly from Management Studio, that is remove all the wrapping added by sp_cursoropen.

    Calling to server side cursor is not something that I can control. What
    I can control is to submit  exec test_UPD (?,?) via ODBC native client
    to SQL Server where ? denotes a bind variable. The tool makes a
    procedural call to the server via ODBC.
    As I said, I have little experience with ODBC programming, but from Profiler I've seen ODBC applications to submit calls to SQL Server without using sp_cursoropen. I looked a litte in Books Online, and it seems that you
    should use SQLSetStmtAttr to set SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_OFF.
    (In fact, this is said to be the default, so you may be setting something
    you should not set.)


    -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Tuesday, June 29, 2010 6:39 AM