FIX: SQL Server May Stop Without Any Information by BULK INSERT

Article translations Article translations
Article ID: 249119 - View products that this article applies to.
This article was previously published under Q249119
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 56117 (SQLBUG_70)
Expand all | Collapse all

On This Page

Symptoms

If all of the following conditions are true, executing a Transact-SQL BULK INSERT may cause the SQL Server to stop responding or cause a stack overflow or access violation (AV):
  • A table has an int data type field and some other data type fields. At least one field follows the int data type field.
  • A format file is used.
  • The field is defined as the fixed data length in the format file.
  • Only the space character is in the data file.
If SQL Server stops responding, you must restart SQL Server. This problem does not occur in the bulk copy program (BCP) utility.

Workaround

To work around this problem, do any one of the following:
  • Make sure a value other than the space character is specified in the data file.

    -or-
  • Use the field terminator in the data file and format file.

    -or-
  • Use the BCP utility instead of BULK INSERT.

Status

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

More information

Steps to Reproduce the Problem

The following is a case where SQL Server may stop responding without any information:
  1. Create a data file named Test1.txt containing only two space characters and a <CRLF>.
  2. Create a format file named Test1.fmt containing the following:
       7.0
       2
       1       SQLCHAR       0       1       ""                        1     c1
       2       SQLCHAR       0       1       "\r\n"                    2     c2
    					
  3. Execute the following script with Query Analyzer; SQL Server may stop responding without any information:
    use tempdb
    GO
    
    create table test (
     [c1] [int],
     [c2] [char] (1)
    ) ON [PRIMARY]
    GO
    
    BULK INSERT test FROM 'c:\test1.txt'
    WITH (FORMATFILE='c:\test1.fmt')
    GO
    						
    At this time, no information is recorded in the error log.
The following is a case where the stack overflow (or AV) happens:
  1. Create a data file named Test2.txt containing 13 space characters and a <CRLF>.
  2. Create a format file named Test2.fmt containing the following:
       7.0
       2
       1       SQLCHAR       0       12      ""                        1     c1
       2       SQLCHAR       0       1       "\r\n"                    2     c2
    					
  3. Execute the following script with Query Analyzer; and the stack overflow or AV occurs:
    use tempdb
    GO
    
    create table test (
     [c1] [int],
     [c2] [char] (1)
    ) ON [PRIMARY]
    GO
    
    BULK INSERT test FROM 'c:\test2.txt'
    WITH (FORMATFILE='c:\test2.fmt')
    GO
    						

Properties

Article ID: 249119 - Last Review: November 2, 2013 - Revision: 3.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB249119

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com