Attempting to grow LOB beyond maximum

Question

I have the following code:

select @SQL = 'insert into #TempXML (XMLContent) select convert(xml,BulkColumn, 2) From openrowset(Bulk ''' + @Dir + '\FDistribContents_TMP.xml'', single_blob) [rowsetresults]'
exec(@SQL)

 The FDistribContents_TMP.xml file is 2.588.235 KB large.  When I execute the procedure I get the following error:

Msg 7119, Level 16, State 1, Line 1
Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

The database is in simple mode.  The tempDB should have enough room. 

What can I do to avoid this?

Answer 1

SQL Server does not have any data type capable of storing more than 2 gigabytes of data in a column of a row.

Databases (other than Express Editions) can store much more than 2 GB provided you have enough disk space, but the limitation on the amount of data in a column of a row applies.

Answer 2

BinaryCoder is correct.

I've seen one team handle this problem before.  Their solution was to create 2 LOB's and concatenate the results in the presentation layer.  Not elegant, but it'll get the job done.  You may also want to consider storing the data in a file and pointing to the file instead. 

---

Michelle Ufford | SQLFool.com | Please mark solved if I've answered your question :)

Answer 3

If you happen to be using SQL 2008, you can also use the FILESTREAM feature to store the data.  The size limit of a FILESTREAM blob is only limited by the size of the NTFS volume that is hosting the filestream data.

---

Aaron Alton | thehobt.blogspot.com