Limit on size of text parameter type in stored procedures?

Hi,
I use stored procedures in combination with openxml. XML text is sent to the
stored procedures as a "text" type parameter.
Is there a limit on the size that this can be? I intend to pass very large
XML documents so its important for us that there is no size limit.
Thanks!
The size limit of text and ntext parameters is 2GB. If you show me an XML
document of that size, I will show you with high probability a design
mistake :-).
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I use stored procedures in combination with openxml. XML text is sent to
> the
> stored procedures as a "text" type parameter.
> Is there a limit on the size that this can be? I intend to pass very large
> XML documents so its important for us that there is no size limit.
> Thanks!
>
|||That's ample! Our files range from 20mb to 100mb. We are planning to make
extensive use of stored procedures and OPENXML - so I just wanted to check
SQL Server would cope with our requirements.
Thanks a lot for the info!!!!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The size limit of text and ntext parameters is 2GB. If you show me an XML
> document of that size, I will show you with high probability a design
> mistake :-).
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
large
>
|||Note that if you are using 20mb to 100mb documents with OpenXML, that you
will have to make sure that your SQL Server instance will have at least x mb
(size of doc) * 4 (avg overhead of DOM) * 8 (1/8th usage limit for OpenXML
in SQL Server) memory available to get acceptable performance. For such
large documents in SQL Server 2000, we normally recommend to use the SQLXML
Bulkload object instead of OpenXML.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:e4Ln1CJKFHA.2524@.TK2MSFTNGP10.phx.gbl...
> That's ample! Our files range from 20mb to 100mb. We are planning to make
> extensive use of stored procedures and OPENXML - so I just wanted to check
> SQL Server would cope with our requirements.
> Thanks a lot for the info!!!!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...
> large
>