2064376 - The rowcount setting is inaccurate | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

2064376 - The rowcount setting is inaccurate

Symptom

  • When we run set rowcount in a stored procedure from one of our user accounts the set rowcount remains after the stored procedure execution has completed.
  • Creating a procedure for execution(test_proc)/granting exec permission to test_login, attaching a non existing object_id  to procid column of syslogins table for ANY login (say test_login) and then executing test_proc and running any SQL after test_proc execution as that “test_login” id .
  • Result will always be # of rows as set in rowcount parameter in test_proc.
  • To reproduce:
    • 1> sp_addlogin test_login, sybase
      2> go
      Password correctly set.
      Account unlocked.
      New login created.
      (return status = 0)

      1>use tempdb
      3> go

      1> create table test_table (col1 int, col2 char(3), col3 varchar(25))
      2> go

      1> insert into test_table values(1,'ttt','test')
      2> go
      (1 row affected)

      1> insert into test_table values(2,'tt1','tester')
      2> go
      (1 row affected)

      1> insert into test_table values(2,'tt2','testing')
      2> go
      (1 row affected)
       
      1>  create proc test_proc as
      2>   begin
      3>   set rowcount 1
      4>  select * from test_table
      5>   end
      6> go
      1> grant exec on test_proc to public
      2> go
      1> grant select on test_table to public
      2> go
      1>
      2> exec  sp_configure "allow updates", 1
      3> go
       Parameter Name                 Default     Memory Used Config Value
               Run Value    Unit                 Type
       ------------------------------ ----------- ----------- ------------
               ------------ -------------------- --------------------
       allow updates to system tables           0           0            1
                          1 switch               dynamic
       
      (1 row affected)
      Resulting configuration value and memory use have not changed from previous
      values: new configuration value 1, previous value 1.
      (return status = 0)

      1> update master..syslogins set procid = 1234 where name = "test_login"
      2> go
      (1 row affected)
       

  • LOGIN as test_login and do following :
    • C:\Users\i825830>isql -Utest_login -Psybase
      1> use tempdb
      2> go
      1> exec test_proc
      2> go
       col1        col2 col3
       ----------- ---- -------------------------
                 1 ttt  test
       
      (1 row affected)
      (return status = 0)

      1> select * from test_table
      2> go
       col1        col2 col3
       ----------- ---- -------------------------
                 1 ttt  test
       
      (1 row affected)

  • Result set only shows # of rows as set by rowcount parameter in test_proc. Instead of the 3 rows it really has.
  • When logging as ANY other login, no result truncation.

Read more...

Environment

  • SAP Adaptive Server Enterprise/15.7/EBF 21521 SMP SP102/P/x86_64/EnterpriseLinux/ase157sp101/3445/64-bit/FBO/Sat Jul 13 02:28:53 2013
  • This also occurs on SAP Adaptive Server Enterprise 16.0

Product

SAP Adaptive Server Enterprise 15.7

Keywords

login; procedure , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.