Skip to Content

Error in sp_autoformat

Hi All,

Getting below stated error while executing, please help me to understand this , why I am going through

Version :

Adaptive Server Enterprise/15.7/EBF 23820 SMP SP132 /P/RS6000/AIX 6.1/ase157sp131x/3919/64-bit/FBO/Tue Jan 20 04:15:36 2015
1> sp_autoformat @fulltabname='monOpenObjectActivity'
2> go
Msg 102, Level 15, State 181:
Server 'SYBASE', Line 3:
Incorrect syntax near '('.
Msg 19206, Level 16, State 1:
Server 'SYBASE', Procedure 'sp_autoformat', Line 729:
(2): Execute immediate SQL failed. SQL statement is: INSERT #colinfo_af(_clid#af,_clname#af,_usrtype#af,
                        _type#af,_typnm#af,_colen#af,_auto#af,_slc#af,_sorder#af,_asname#af,_mbyte#af,_maxlen#af)
                SELECT -1,_clname#af,0,0,'',0,0,0,0,'',0 , isnull(max(isnull(char_length(convert(varchar(80),DBID)),4)),1) *sign(_clid#af/1)*sign (1/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),ObjectID)),4)),1) *sign(_clid#af/2)*sign (2/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),IndexID)),4)),1) *sign(_clid#af/3)*sign (3/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),InstanceID)),4)),1) *sign(_clid#af/4)*sign (4/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),DBName)),4)),1) *sign(_clid#af/5)*sign (5/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),ObjectName)),4)),1) *sign(_clid#af/6)*sign (6/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),LogicalReads)),4)),1) *sign(_clid#af/7)*sign (7/_clid#af)
+isnull(max(isnull(char_length(convert(varcha
(return status = 102)

OR

1>
2>
3> sp_autoformat monOpenObjectActivity
4> go
Msg 102, Level 15, State 181:
Server 'SYBASE', Line 3:
Incorrect syntax near '('.
Msg 19206, Level 16, State 1:
Server 'SYBASE', Procedure 'sp_autoformat', Line 729:
(2): Execute immediate SQL failed. SQL statement is: INSERT #colinfo_af(_clid#af,_clname#af,_usrtype#af,
                        _type#af,_typnm#af,_colen#af,_auto#af,_slc#af,_sorder#af,_asname#af,_mbyte#af,_maxlen#af)
                SELECT -1,_clname#af,0,0,'',0,0,0,0,'',0 , isnull(max(isnull(char_length(convert(varchar(80),DBID)),4)),1) *sign(_clid#af/1)*sign (1/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),ObjectID)),4)),1) *sign(_clid#af/2)*sign (2/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),IndexID)),4)),1) *sign(_clid#af/3)*sign (3/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),InstanceID)),4)),1) *sign(_clid#af/4)*sign (4/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),DBName)),4)),1) *sign(_clid#af/5)*sign (5/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),ObjectName)),4)),1) *sign(_clid#af/6)*sign (6/_clid#af) +isnull(max(isnull(char_length(convert(varchar(80),LogicalReads)),4)),1) *sign(_clid#af/7)*sign (7/_clid#af)
+isnull(max(isnull(char_length(convert(varcha
(return status = 102)
1>
2>
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 28, 2017 at 09:23 AM

    sp_autoformat uses a variable (@sqlstr) to build a SELECT statement; this SELECT statement is then executed via a exec(@sqlstr) call.

    If you were to output the contents of @sqlstr for your invocation you'd find you're dealing with a truncated SELECT statement. The SELECT statement is being truncated because @sqlstr is defined as varchar(8000), while the desired SELECT statement requires more than 8000 characters.

    You should be able to get around this issue by modifying sp_autoformat to bump up the declaration size of @sqlstr:

    -- change declaration statement from:
    
    , @sqlstr         varchar(8000)
    
    -- to:
    
    , @sqlstr         varchar(16384)

    Obviously (?) this won't do any good if you ever find yourself with a table who's resulting SELECT statement is more than 16,384 characters.

    If you've got a tech support contract with SAP I'd suggest you open a case and request that sp_autoformat (at a minimum) be updated as above. For completeness sake it probably wouldn't hurt for them to see what it would take to get sp_auformat to work for SELECT statements greater than 16,384 characters.

    Add comment
    10|10000 characters needed characters exceeded

    • This is a new/different error (12036) ... which doesn't match the write-up from the ASE manuals for 12036 ... odd.

      Is this with the original version of sp_autoformat, or have you modified sp_autoformat?

      -------------

      Is 'SYBASE' the name of your dataserver?

      Are you submitting these commands via isql?

      What happens if you run the following:

      sp_autoformat sysobjects
      go
  • Feb 28, 2017 at 10:05 AM

    Not sure what you're asking re: syntax error; @sqlstr contains an incomplete SELECT statement so the parser generates the error when it tries to parse the incomplete SELECT statement. For example, what do you get if you run the following:

    declare @cmd varchar(1000)
    select @cmd = 'select count(' -- incomplete SELECT statement
    exec(@cmd)
    go

    As for modifying sp_autoformat (it's located in the sybsystemprocs database) ... extract a copy of the source (ddlgen; defncopy plus sp_helprotect - for permissions; extract from installmaster), update per my previous post, and load into sybsystemprocs. If a bit squeamish about replacing the default sp_autoformat, call your version sp_autoformat_pkp, test it, and if it works then reload as sp_autoformat.

    I also don't have (direct) access to a SAP tech support account at the moment soooo ... perhaps someone from SAP will see this thread and open a case or ... DBAs will just update their copy of sp_autoformat as they see fit.

    Add comment
    10|10000 characters needed characters exceeded

    • As always, Mark has excellent suggestions.

      I have normally followed the "sp__" (i.e. two underscores) naming convention to quickly identify "custom" version of stored procedure(s).

      To the credit of Sybase and now SAP, system stored procedure SQL was always available (barring a few sensitive ones). So if needed you are free to tweak them. The only caveat is of course when you get the newer version you have to tweak your custom code appropriately.

      Cheers.

      Avinash

  • Feb 28, 2017 at 12:19 PM

    Feature requests can be submitted through http://ideas.sap.com/SAPASE

    Cheers,
    -bret

    Add comment
    10|10000 characters needed characters exceeded