FIX: "Run-Time Error" Occurs with ExportData Method of BulkCopy Object When DataBase Name Contains Space (817848)


The information in this article applies to:

  • Microsoft Data Access Components 2.7 SP1
  • Microsoft SQL Server 2000 (all editions) SP3

SYMPTOMS

When you use the ExportData method of the BulkCopy object from SQL Distributed Management Objects (SQL-DMO), and the name of the database that you use in the method contains a space (for example, My Database), you receive the following error message at run time:

Run-time error '-2147221348(8004009c)':
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Database'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Database'.

RESOLUTION

To resolve this problem download MDAC 2.7 Service Pack 1 (SP1) Refresh. To download MDAC 2.7 SP1 Refresh, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

WORKAROUND

To work around this problem, remove the space in the name of the database. To do this, you can name the database as follows:
  1. Execute the following SQL command in SQL Query Analyzer.
    sp_renamedb 'My Database', 'MyDatabase'
  2. Modify the name of the database in the Visual Basic project.
  3. On the Run menu, click Start.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in MDAC 2.7 Service Pack 1 Refresh.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Start SQL Server Enterprise Manager.
  2. Expand your SQL Server node. Right-click Databases, and then click New Database.
  3. In the Name text box, type My Database, and then click OK.
  4. Create a table in My Database, and then name it MyTable.
  5. Insert records in MyTable.
  6. Start Microsoft Visual Basic 6.0, and create a new Standard EXE project. By default, Form1 is created.
  7. Drag a Button control from the Toolbox to Form1. By default, Command1 is created.
  8. On the Project menu, click References, and then click to select Microsoft SQL DMO Object Library.
  9. Double-click Command1, and then paste the following code in Command1_Click.
    'create the object 
Dim mySQLserver As SQLDMO.SQLServer
Dim myDatabase As SQLDMO.Database

Set mySQLserver = New SQLDMO.SQLServer
Set BulkCopy = New SQLDMO.BulkCopy
mySQLserver.EnableBcp = True

'connect to the SQL Server
mySQLserver.Connect "yourSQLServer", "YourLogin", "YourPassword"

    With BulkCopy
        
        .DataFileType = SQLDMODataFile_NativeFormat
        .MaximumErrorsBeforeAbort = 1
        .UseBulkCopyOption = True
        .UseExistingConnection = True
        'specify the path where the table is to be copied
        .DataFilePath = "C:\mytable.bcp"        
        'export the data from the table in My Database
        mySQLserver.Databases("My Database").Tables("MyTable").ExportData BulkCopy

    End With
    Note Replace yourSQLSever, YourLogin, and YourPassword, with your local SQL Server, and the logon name and logon password of your local SQL Server.
  10. On the Run menu, click Start.
  11. Click Command1. You receive the error message that is described in the "Symptoms" section of this article.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

299865 FIX: DMO BulkCopy Fails to Copy Data When Column Names Contain the Space Character

Modification Type:MajorLast Reviewed:12/3/2003
Keywords:kberrmsg kbSqlClient kbExport kbDatabase kbbug KB817848 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.