Just Learned - SQL Server - Max Specifications Of Stored Procedure

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server - Max Specifications Of Stored Procedure

Oct 12 2011 3:56AM by Robert Dennyson

<pre class="brush: plain">
The maximum number of parameters in a stored procedure is 2100.
The maximum number of local variables in a stored procedure is limited only by available memory.
Depending on available memory, the maximum size of a stored procedure is 128 megabytes (MB).
</pre>

4 Comments

I wonder how a stored procedure with 2100 parameters would look like :)

Thanks, Robert for sharing!

commented on Oct 12 2011 11:21AM

Nakul Vachhrajani

4 · 33% · 10585

with XML nowadays, I don't know why anyone would go over 5.

commented on Oct 13 2011 6:51AM

mbova407

320 · 0% · 132

Hello!

Scenario: Windows XP, SQL Server 2005 Express, Apache 2.2, PHP 5.? I have a PHP report with HTML template calling a stored procedure which sends to me a message saying "Time out expired" after about 40 seconds without present any data. If I run the very same stored procedure in the SSMSE environment it always returns data and no message at all taking about 2 minutes possibly because the SSMSE defines its own parameters to interface the database.

Can You help me? Please?

Thanks in advance Dias Costa

commented on Oct 15 2011 5:37PM

dcosta

1912 · 0% · 9

Usually this type of problems (in many cases) are caused by different SET configurations. When SSMS opens a connection to the database, it automatically configures some SET options. You can view/change the default options from Tools > Options > Query Execution > SQL Server > ANSI and Advanced tabs.

Similarly, most client libraries (ADO.NET, OLEDB, ODBC etc) configure some SET options when a connection to the server is established. In most cases the performance difference you are seeing is caused by one of these.

In the same way, most client libraries have a time out option. If you are using ADO.NET, the default is 30 seconds. So if the query takes more than 30 seconds, the operation will time out. On SSMS, the default query timeout is 'unlimited' seconds. If you are using ADO.NET, I would suggest you increase the query timeout and retry.

Meanwhile, it is also important to check why the query takes 2 minutes. It might need some optimization efforts.

commented on Oct 15 2011 9:48PM

Jacob Sebastian

1 · 100% · 32004

Your Comment

Sign Up or Login to post a comment.

"SQL Server - Max Specifications Of Stored Procedure" rated 5 out of 5 by 18 readers