[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • 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>

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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