FIX: SQLDMO ExportData Method Fails If There Is a Space in the Database Name (308860)

SYMPTOMS

When you use the ExportData method of the table object from SQL Server Distributed Management Objects (DMO), if the name of the source database contains a space, an error occurs. For example, if "My Database" is the name of the source database, this run-time error message appears:

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'.

If you examine the SQL Profiler trace, you see the statements that follow. Note that there are no brackets to delimit the database name.

EXEC sp_bcp_dbcmptlevel My Database 
SET FMTONLY ON 
SELECT * FROM [dbo].[myTable] 
SET FMTONLY OFF 
EXEC My Database..sp_tablecollations 'My Database.[dbo].[myTable]'

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000 or the latest service pack for MDAC 2.6. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

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

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.

MORE INFORMATION

For clarification, this example demonstrates the problem:

Create a database named "My Database".
Create a table in "My Database" and name it "myTable". Insert several records into "myTable".

Create a Microsoft Visual Basic project and run the following code:

Dim mySQLserver As SQLDMO.SQLServer
Dim myDatabase As SQLDMO.Database

Set mySQLserver = New SQLDMO.SQLServer
mySQLserver.EnableBcp = True
mySQLserver.Connect "servername", "sa", ""
Set BulkCopy = New SQLDMO.BulkCopy
    With BulkCopy
        .DataFileType = SQLDMODataFile_NativeFormat
        .MaximumErrorsBeforeAbort = 1
        .UseBulkCopyOption = True
        .UseExistingConnection = True
        .DataFilePath = "C:\MyTable.bcp"
    End With      
mySQLserver.Databases("My Database").Tables("myTable").ExportData BulkCopy