Microsoft KB Archive/294180

= BUG: ADO: Append Cannot Be Used with OLE DB Provider For SQL Server if the Name of the Table Contains Spaces =

Article ID: 294180

Article Last Modified on 8/24/2001

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194

-



This article was previously published under Q294180



SYMPTOMS
When you use the SQL Server OLE DB to modify a table whose name contains spaces, and you use a catalog to obtain table information from the database, and you then attempt to use the Append method with ADO, you may receive the following error message:

Incorrect syntax near the keyword 'add'.



CAUSE
A table name that contains spaces must be enclosed in quotation marks (&quot;&quot;) or brackets ([]). After it obtains catalog information on a table whose name contains spaces, ADO does not enclose the table name with quotation marks or brackets when Append calls are made.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
 In Microsoft Visual Basic, create a Standard EXE project. Form1 is created by default. In Form1, create two command boxes.  In Form1, paste the following code: Private Sub Command1_Click On Error GoTo errorhandler Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim Idx As New ADOX.Index Dim Cnn1 As New ADODB.Connection strTable = &quot;[tbl wthspc]&quot; 'Here we are setting the table name ourselves so brackets will be passed 'to the SQL server as the part of our query, so you will not get an error.

'Change the server name, user ID, and password here. Cnn1.ConnectionString = &quot;Provider=SQLOLEDB;Server=ServerName;UID=UserID;PWD=Password;database=DatabaseName;&quot;

Cnn1.Open Set cat.ActiveConnection = Cnn1 tbl.Name = strTable tbl.Columns.Append &quot;ChmpIdx1&quot;, adVarWChar, 50 tbl.Columns.Append &quot;ChmpIdx2&quot;, adVarWChar, 10 cat.Tables.Append tbl Test cat, tbl Set tbl = Nothing Set cat = Nothing Cnn1.Close Set Cnn1 = Nothing Exit Sub errorhandler: MsgBox Err.Description,, Err.Source End Sub Private Sub Test(cat As ADOX.Catalog, tbl As ADOX.Table) On Error GoTo errorhandler1 Dim Idx As New ADOX.Index Dim str As String Idx.Name = &quot;index01&quot; Idx.Unique = True Idx.PrimaryKey = True Idx.Columns.Append &quot;ChmpIdx1&quot; tbl.Indexes.Append Idx MsgBox &quot;index created successfully&quot; Set tbl = Nothing Exit Sub errorhandler1: MsgBox Err.Description,, Err.Source

End Sub

Private Sub Command2_Click On Error GoTo errorhandler1 Dim Cnn1 As New ADODB.Connection Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Dim Idx As New ADOX.Index Dim str As String strTable = &quot;tbl wthspc&quot; 'Change the server name, user ID, and password here. Cnn1.ConnectionString = &quot;Provider=SQLOLEDB;Server=ServerName;UID=UserID;PWD=Password;database=Pubs;&quot; Cnn1.Open 'Here we are calling a catalog function to get catalog information. Set cat.ActiveConnection = Cnn1 Set tbl = cat.Tables(strTable) Idx.Name = &quot;index02&quot; Idx.Unique = True Idx.PrimaryKey = True Idx.Columns.Append &quot;ChmpIdx1&quot; tbl.Indexes.Append Idx MsgBox &quot;index created successfully&quot; Set tbl = Nothing Set cat = Nothing Cnn1.Close Set Cnn1 = Nothing Exit Sub errorhandler1: MsgBox Err.Description,, Err.Source

End Sub

 Add a reference to the '''ADO Ext. 2.5 for DDL and Security object library''' in the project. Change the SQL Server alias, User ID, and password in the code. Run the project and click the first command box. This creates a table with spaces in the name (tbl wthspc) on the server, and it also creates the index01 index on the table because the table name is enclosed in brackets. If you trace this call in SQL Profiler, you see the table name as [tbl wthspc].</li> Click the second command box. When you click this box, an attempt is made to append the index02 index on the table. In this step, you obtain the catalog information from the database, and because ADO does not enclose the table name in quotation marks or brackets, you receive an error message.</li></ol>

If you trace this call in SQL Profiler, you receive the following SQL statement: alter table tbl wthspc add constraint index02 primary key nonclustered(ChmpIdx1) This statement shows that ADO does not add the required quotation marks or brackets around the tbl wthspc table name and that SQL Server is generating a syntax error.

Keywords: kbbug kbsqlprog kbpending KB294180

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.