Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

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>
Read More..   [9 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Robert Dennyson
11 · 14% · 4420
18
 
1
 
17
 
0
Incorrect
 
 
0
Forgotten



Submit

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 · 36% · 11606
  • with XML nowadays, I don't know why anyone would go over 5.

    commented on Oct 13 2011 6:51AM
    mbova407
    323 · 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
    1995 · 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% · 32225

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
SQL Server - Max Specifications Of Stored Procedure , 5.0 out of 5 based on 18 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]