Justin Cooney

Web Development Tips and Examples

recent posts

about

I am a Senior Applications Programmer / Analyst with years of experience developing enterprise solutions using the Microsoft technology stack including C#, VB.NET, ASP.NET, AJAX, IIS and SQL Server. I specialize in Web application development with a focus on building secure systems, integrating applications, and designing robust database structures.

Fatal error 682 – SQL Server 2008 Linked Server Bulk Update Issue

This week Luvy and I encountered an interesting issue with SQL Server. We were working on a project that we ported over to SQL Server 2008. As part of the work we were working on a scheduled stored procedure that selects into a temporary table and then does a fairly standard cross server bulk update.

The surprise came when we tried to run the procedure and kept encountering a strange error neither of us had ever seen before, and that was completely non-descriptive. Specifically it was:

Warning: Fatal error 682 occurred at July 15 2011 11:01AM. Note the error and time, and contact your system administrator.

When we opened the SQL Server Logs we saw similarly vague logs stating:

Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

Since we were working with a large procedure it took a while to identify the bulk update statement as the culprit. Then as we ran various tests of the statement, each time scaling down the scope of what it was doing, we became more and more confused as to why the update statement would not work. Strangely enough the statement seemed to work when run from SQL Server 2005 to the SQL Server 2000 table.

Naturally from the error message both of us thought the SQL Server 2008 server itself might be corrupt. Looking for help online did not provide us with any useful suggestions for how to deal with this. However when we ran DBCC CHECKDB against the database everything looked fine.

We submitted the question on Twitter and a helpful user @banerjeeamit indicated that the problem was due to indexes we had on the tables we were connecting.

Sure enough when we rebuilt the indexes on the SQL Server 2000 table and added a non-clustered index to the temporary SQL Server 2008 table the Bulk update statement ran without any problems!

I thought this was an interesting issue and definitely was not clearly covered online. I hope our findings in fixing this issue will help someone experiencing similar isses.

Posted in , ,

7 responses to “Fatal error 682 – SQL Server 2008 Linked Server Bulk Update Issue”

  1. google Avatar
    google

    I liked your article is an interesting technology
    thanks to google I found you

    Reply
    1. Justin Cooney Avatar
      Justin Cooney

      Thanks for your comment, I’m glad you found the article interesting.

      Reply
  2. Andre Maakal Avatar
    Andre Maakal

    Hi. We had a similar error and rebuilding or adding indexes did not help.
    Eventually we fixed it this way:

    Step1: Create a new “system dsn” data source on the SQL 2008 Server. Use the “SQL Server” driver and not the “SQL Server Native Client 10.0″.

    Step2: Create a new linked server on the SQL 2008 server to use the above driver. Use this script:

    sp_addlinkedserver @server= ”, @srvproduct = ”, @provider= ‘MSDASQL’,
    @datasrc = ”

    Step3: Edit this new linked server (Management Studio go to Server Objects / Linked Server) and correct the security settings if needed.

    Test your query using the new linked server.

    Reply
    1. Justin Cooney Avatar
      Justin Cooney

      Hi Andre, very interesting, thanks for sharing your experiences dealing with this error. I’m glad you were able to find a solution to it! It appears that the error can come up due to a variety of reasons, but it can be frustrating to debug due to the general lack of information about it.

      Reply
  3. Andre Maakal Avatar
    Andre Maakal

    The above “sp_addlinkedserver” should read:

    sp_addlinkedserver @server= ‘enter name linked server to sql2000’, @srvproduct = ”,
    @provider= ‘MSDASQL’,
    @datasrc = ‘the name of the data source created in step 1’

    Reply
  4. Darain Avatar
    Darain

    Wow, after all these years it seems that your details here are still the only valid explanation to this rare error occurrence. I have almost the exact query conditions as yours – SQL Server 2008, temp table, and bulk updating to linked legacy SQL server.
    I have tested the whole operation with a condition that updates only one record and it works but extremely slow – take a few seconds for one record! it already indicates something is not right. When trying conditions to update more records, the error pops.
    After Seeing your posting, I move the whole query onto the legacy server itself and suddenly the update process succeeded and run lightning fast – as usual SQL standard.
    The indexing problem on linked server in this operation is quite apparent. Thank you for the information. Or I might have to spend a few days on this error.

    Reply
    1. Justin Cooney Avatar
      Justin Cooney

      Hi Darain, I am glad that the explanation was useful to you, it certainly is a strange bug. Hope you have a great day!

      Reply

Leave a comment