Skip to Content
0

Error in sp_autoformat

Feb 28, 2017 at 08:44 AM

143

avatar image

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>
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mark A Parsons Feb 28, 2017 at 09:23 AM
0

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.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Mark,

Always pleasure to get replied by you :)

Msg 102,Level15,State181:
Server 'SYBASE',Line3:
Incorrect syntax near '('.
  • What is the meaning of first part of the error message, as it states syntax error which actually doesn't exists ?
  • It is a system proc , can I change this as you directed above ?

(as not always good to mess with the things inside the master db)

AND

As I don't have tech support case with SAP, so I'd request you to please, take it forward to get it corrected/modified in the the future patches(SPs)

Regards

-pankaj

0

Hi Mark,

Again getting error something like above, can you throw some light on it :|

3>select*from monProcessSQLText
4> go
Msg 12036,Level17,State1:
Server 'SYBASE',Line3:Message too long.Length=33841166.(0rows affected)1> sp_autoformat monProcessSQLText
2> go
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 12036,Level17,State1:
Server 'SYBASE',Line1:Message too long.Length=33841166.
Msg 19206,Level16,State1:
Server 'SYBASE',Procedure'sp_autoformat',Line725:(2): Execute immediate SQL failed.SQLstatementis: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),SPID)),4)),1)*sign(_clid#af/1)*sign(1/_clid#af)+isnull(max(isnull(char_length(convert(varchar(80),KPID)),4)),1)*sign(_clid#af/2)*sign(2/_clid#af)+isnull(max(isnull(char_length(convert(varchar(80),ServerUserID)),4)),1)*sign(_clid#af/3)*sign(3/_clid#af)+isnull(max(isnull(char_length(convert(varchar(80),BatchID)),4)),1)*sign(_clid#af/4)*sign(4/_clid#af)+isnull(max(isnull(char_length(convert(varchar(80),LineNumber)),4)),1)*sign(_clid#af/5)*sign(5/_clid#af)+isnull(max(isnull(char_length(convert(varchar(80),SequenceInLine)),4)),1)*sign(_clid#af/6)*sign(6/_clid#af)+isnull(max(isnull(char_length(convert(varchar(255),SQLText)),4)),1)*sign(_clid#af/7)*sign(7/_clid#af)+0FROM#colinfo_af,monProcessSQLText
(returnstatus=12036)1>

regards

pankaj

0

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
0
Mark A Parsons Feb 28, 2017 at 10:05 AM
0

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks a ton Mark, got it. :)

Regards

-pankaj

0

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

0
Bret Halford
Feb 28, 2017 at 12:19 PM
0

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

Cheers,
-bret

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you!! Bret

It's a kind of new planet for every ASE freak :)

regards

0