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

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:

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:

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.

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.

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.

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