Attempting to grow LOB beyond maximum RRS feed

  • 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]'

     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?

    Thursday, April 2, 2009 9:32 PM

All replies

  • 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.
    Friday, April 3, 2009 2:03 AM
  • 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 :)
    Friday, April 3, 2009 2:23 AM
  • 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
    Friday, April 3, 2009 3:12 AM