• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

length of sql 12 statement with sybase

I would just like to know if there is a max limit to the amount of characters you could put in a sql 12 statment for sybase?
2 Solutions
Joe WoodhousePrincipal ConsultantCommented:
There are a number of limits that apply here, but it isn't a simple situation.

If this is ASE 12.0, then the maximum size of any one expression is 255 bytes. This is increased to 16384 bytes in ASE 12.5.

In all versions of ASE, there is a server-wide configurable parameter called "stack size". Its default setting depends on your platform and operating system. This is the size of the buffer used to hold the context of each user connection. If a query is larger than can fit in the stack space, an error will be returned. This limit typically is in the range of approx. 50Kb - 100Kb (depending on ASE version & platform).

There are also other limits such as maximum number of tables in the query (including temporary tables), number of entries in an IN clause, etc. Most of these limits were increased in ASE 12.5.x.

So there's no simple answer other than to say yes, there are a number of limits that apply, but the only one that is a hard limit on the number of characters in your SQL overall is the "stack size" parameter.
v_wall78Author Commented:
How would change the default setting of the stack size on a sunOS v. 5.8?
I really appreciate your help.
sp_configure "stack size", <x>

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

BTW, what DO you mean by a "sql 12 statement"?

I do not know if this is still the case, but I believe there was a limit of 256 lines of code in a store procedure. (but, I could be wrong about this).

A *long* time ago there was a limit on the size of the *plan* for the stored procedure,
which was stored in sysprocedures.  The plan also happened to grow everytime
the procedure was recompiled, so would eventually hit the limit and have to be rebuilt.
The limit was 64 pages - I doubt you would hit it with anything as small as 256 lines
of code, but it may be what you are thinking of.  The limit was removed in the 10.0
release. From the "What's New in 10.0" document:

Stored Procedure Size Limits Removed

Release 10.0 of SQL Server eliminates the size limit for compiled stored procedures and
batches. To accommodate this change, the maximum amount of text allowed for a stored
procedure has been increased to 16 Mb. This increase also applies to objects whose text is
stored in the syscomments system table, such as triggers, views, defaults, rules, and
Joe WoodhousePrincipal ConsultantCommented:
(Sorry for not responding sooner, I stopped receiving notifications from EE for some reason.)

Bret has already given you the correct command for changing the stack size. Note that depending on the version of ASE you're running, this might need Sybase to be restarted. You'll only be able to make the change if you have SA_ROLE.

Good luck!
Joe WoodhousePrincipal ConsultantCommented:
That doesn't seem fair; I'd suggest a 250/250 split.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now