SQL Parser limitations?

[Rob]

Does anyone know what the limitations are in SQL Server 200x Query Parser?

When I try to parse a lengthy query in SQL Server 2005, I see the following
error:

===================================

Cannot parse script.

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)

------------------------------
Program Location:

at System.String.InternalSubString(Int32 startIndex, Int32 length,
Boolean fAlwaysCopy)
at System.String.TrimHelper(Char[] trimChars, Int32 trimType)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.GetSelectedTextSpan()
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptEditorControl.GetSelectedTextSpan()
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptAndResultsEditorControl.OnParseScript(Object sender, EventArgs a)

[Michael Coles]

Also how much memory do you have on this box dedicated to SQL Server? And
what's the version (SELECT @@version).

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Rob" <R...@discussions.microsoft.com> wrote in message
news:D14B6980-6C7A-47CD...@microsoft.com...

[Michael Coles]

That's the first time I've seen that error message. How long is this script
exactly and what is the platform, service pack/hot fix level you are on?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Rob" <R...@discussions.microsoft.com> wrote in message
news:D14B6980-6C7A-47CD...@microsoft.com...

[Rob]

Apparently, this issue/bug is documented here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269566#details

[Michael Coles]

Interesting. Looks like it affects sqlcmd also. Maybe you can break your
50 MB+ query into smaller chunks?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Rob" <R...@discussions.microsoft.com> wrote in message

news:A5B45095-7CE6-4741...@microsoft.com...

[Rob]

That's exactly what we had to do :-)

[Erland Sommarskog]

Michael Coles (ad...@geocodenet.com) writes:
> Interesting. Looks like it affects sqlcmd also.

I don't think SQLCMD is affected. Several years ago, it might have been
already during the beta of SQL 2005, I helped someone who had insanely
big SQL scripts. SSMS choked on fairly moderate sizes. SQLCMD had now
problems. Hm, if you have a 2GB script, 32-bit SQLCMD might get problems.
But there is a 64-bit version of SQLCMD.

The upper limits for SSMS are certainly quite tiny. And before you hit
those hard limits, you will find that performance is awful.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Rob (R...@discussions.microsoft.com) writes:
> Does anyone know what the limitations are in SQL Server 200x Query Parser?
>
> When I try to parse a lengthy query in SQL Server 2005, I see the
> following error:
>
>===================================
>
> Cannot parse script.
>
>===================================
>
> Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)

This is not an error from the SQL 2005 parser. This is an error from
SQL Server Mgmt Studio.

I see in the comment to the Connect item you filed, that your script
fails with SQLCMD too. As I said in another post, my experience is that
SQLCMD is able to handle much larger files than SSMS. Possibly, in could
be that you have one very big query batch. I know was able to get some
really big files through SQLCMD, but I think they had "go" after every
INSERT. (Or, hm, did I just put them in with a Perl script?)

But there is indeed a limit on the size of a query. According to
books online that is 65536 * network packet size. The default net
work packet size is 4096, which gives an upper limit of 258 MB.

The topic "Maximum Capacity Specifications for SQL Server" has
more details.

[Michael Coles]

I was reading the notes on the Connect item, looks like the SQLCMD issue was
just added today in fact (possibly by the OP on this thread?)

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CFB1F7A...@127.0.0.1...