DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 290646 - Last Review: October 31, 2003 - Revision: 3.2

This article was previously published under Q290646
BUG #: 46409 (SHILOH_bug)
BUG #: 56030 (SQLBUG_70)

On This Page

SYMPTOMS

When you use the SQLColAttribute function to return SQL_DESC_SCHEMA_NAME, SQL_DESC_TABLE_NAME, SQL_DESC_BASE_TABLE_NAME, or SQL_DESC_CATALOG_NAME descriptor information, incorrect values may be returned when the table name contains a period.

CAUSE

ODBC uses periods to delimit SQL Server objects in the format Computer.Database.Owner.Object.

RESOLUTION

SQL Server 2000

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211  (http://support.microsoft.com/kb/290211/EN-US/ ) INF: How to Obtain the Latest SQL Server 2000 Service Pack

SQL Server 7.0

To work around this problem, do not use periods within the names of SQL Server objects.

Microsoft Data Access Components (MDAC)

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
300635  (http://support.microsoft.com/kb/300635/EN-US/ ) 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.

SQL Server 2000

This problem was first corrected in SQL Server 2000 Service Pack 1.

Microsoft Data Access Components (MDAC)

This problem was first corrected in Microsoft Data Access Components 2.6 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Paste the following code in a new .cpp file:
    #include <windows.h>
    #include <stdio.h>
    #include <tchar.h>
    
    #include "sql.h"
    #include "sqlext.h"
    #include "odbcss.h"
    
    int main()
        {
        // ODBC handles
        SQLHENV     henv = NULL;
        SQLHDBC     hdbc = NULL;
        SQLHSTMT    hstmt = NULL;
    
    	char szData[100];
    	short l;
     	//These must be modified for your specific DSN
        PTSTR       szDataSource = _T("localserver");
        PTSTR       szUID = _T("UserName");
        PTSTR       szPWD = _T("Password");
    
    	PTSTR		szDropTable = _T("if exists (select * from dbo.sysobjects where id = object_id(N'[my.table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [my.table]");
    	PTSTR		szCreateTable = _T("Create Table [my.table] (col1 int)");
    	PTSTR       szSQLSelect = _T("Select * from dbo.[my.table]");
    
        // Initialize the ODBC environment.
        if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv) == SQL_ERROR)
            goto EXIT;
    
        SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3, SQL_IS_INTEGER);
    
        // Allocate a connection handle and connect to the data source.
        if (SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) == SQL_ERROR)
            goto EXIT;
    
        if (SQLConnect(hdbc, (SQLTCHAR*) szDataSource, SQL_NTS, (SQLTCHAR*) szUID, SQL_NTS, (SQLTCHAR*) szPWD, SQL_NTS) == SQL_ERROR)
            goto EXIT;
    
        // Get a statement handle and execute a Transact-SQL SELECT statement.
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) == SQL_ERROR)
            goto EXIT;
    
    	if(SQLSetScrollOptions( hstmt, SQL_CONCUR_VALUES, SQL_SCROLL_DYNAMIC, 1) == SQL_ERROR)
    		goto EXIT;
    
    	if (SQLExecDirect(hstmt, (SQLTCHAR*) szDropTable, SQL_NTS) == SQL_ERROR)
            goto EXIT;
    
    	if (SQLExecDirect(hstmt, (SQLTCHAR*) szCreateTable, SQL_NTS) == SQL_ERROR)
            goto EXIT;
    
    
    	if (SQLExecDirect(hstmt, (SQLTCHAR*) szSQLSelect, SQL_NTS) == SQL_ERROR)
            goto EXIT;
    
    
    
    	if (SQLColAttribute(hstmt, 1, SQL_DESC_SCHEMA_NAME , &szData, 100, &l, NULL) ==SQL_ERROR)
    		goto EXIT;
    
    	_tprintf(_T("SQL_DESC_SCHEMA_NAME = %s, should be dbo\n"),szData);
    
    	if (SQLColAttribute(hstmt, 1, SQL_DESC_TABLE_NAME , &szData, 100, &l, NULL) ==SQL_ERROR)
    		goto EXIT;
    
    	_tprintf(_T("SQL_DESC_TABLE_NAME = %s, should be my.table\n"),szData);
    
    	if (SQLColAttribute(hstmt, 1, SQL_DESC_BASE_TABLE_NAME , &szData, 100, &l, NULL) ==SQL_ERROR)
    		goto EXIT;
    
    	_tprintf(_T("SQL_DESC_BASE_TABLE_NAME = %s, should be my.table\n"),szData);
    
    	if (SQLColAttribute(hstmt, 1, SQL_DESC_CATALOG_NAME, &szData, 100, &l, NULL) ==SQL_ERROR)
    		goto EXIT;
    
    	_tprintf(_T("SQL_DESC_CATALOG_NAME = %s, should be database specified in dsn\n"),szData);
    
    EXIT:
        if (hstmt != NULL)
            {
            SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
            }
    
        if (hdbc != NULL)
            {
            SQLDisconnect(hdbc);
            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
            }
    
        if (henv != NULL)
            {
            SQLFreeHandle(SQL_HANDLE_ENV, henv);
            }
    
        return (0);
        }
    					
  2. Compile the code and run it. The results are as follows:
    SQL_DESC_SCHEMA_NAME = my, should be dbo
    SQL_DESC_TABLE_NAME = table, should be my.table
    SQL_DESC_BASE_TABLE_NAME = table, should be my.table
    SQL_DESC_CATALOG_NAME = dbo, should be database specified in dsn
    					

APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbmdac260sp1fix kbsqlserv2000sp1fix KB290646
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support