Running SQL Server 2000 Queries in SQL Server 2005

Congratulations to Kenny and Alejandro Mesa. Kenny won first prize of $100 for the best solution to the April Reader Challenge, "Querying Tables and Views on a Linked Server." Alejandro won second prize of $50. Here’s a recap of the problem and the solution to the April Reader Challenge.

Problem:

Michael is a database consultant who’s working on a project at a client’s site. He needs to create a SQL Server 2000 data warehouse that integrates data from the company’s other databases. Michael plans to use the linked server feature to write some of the scripts that will migrate the data and perform distributed queries.

One data source that he must integrate is an IBM DB2 database. He used the IBM OLE DB Provider for DB2 to create a linked server called DB2_SRVR1 for the database. There are certain tables and views that he wants to query from the DB2 server on an ad hoc basis or use in his scripts, but when he tries to query one such table using the following four-part name

SELECT * FROM DB2_SRVR1.DB_CAT.SALES.SALES_SUMMARY   

he gets the following error

Msg 7314, Level 16, State 1, Line 1 
The OLE DB provider "IBMDADB2" for linked server "DB2_SRVR1" does not contain the table 
""DB_CAT"."SALES"."SALES_SUMMARY"". The table either does not exist or the current user 
does not have permissions on that table.   

Help Michael do the following:
1. Find the list of tables or views as exposed by the data source that he can easily query from SQL Server on a linked server.
2. Quickly determine the correct way to specify the four-part name for the DB2 table (i.e., the catalog and schema part of the four-part name).

Solution:

Michael can determine the list of tables that he can query through the linked server by running the sp_tables_ex system stored procedure. The syntax of the system stored procedure call will look like:

EXEC sp_tables_ex 'DB2_SRVR1'  

This stored procedure returns a result set that contains the following columns (only the relevant columns are shown):

TABLE_CAT - Catalog name for the table as represented on the remote data source 
TABLE_SCHEM - Schema name for the table or owner of the table 
TABLE_NAME - Name of the table   

Depending on the remote data source, one or both of the catalog and schema names can be empty (NULL) and Michael can use this information to write the distributed queries. For example, if Michael creates a linked server connection to an Excel file, then the stored procedure will return the names of the worksheets that can be queried from SQL Server.

MAY READER CHALLENGE:

Now, test your SQL Server savvy in the May Reader Challenge, "Running SQL Server 2000 Queries in SQL Server 2005" (below). Submit your solution in an email message to [email protected] by April 20. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

David is a DBA who manages several SQL Server 2000 database servers. His current project is to test his applications against a database server running SQL Server 2005. As part of the evaluation, David needs to verify queries and stored procedure calls made from some of the applications that collect various statistics in the production environment. One particular SQL Server 2000 database query produces an error on the SQL Server 2005 server. The following snippet shows the query and error:

SELECT r.name, p.runid, p.attr, CAST(p.value AS int) AS measure 
FROM ProcStats AS p 
JOIN Runs AS r 
ON r.id = p.runid  
WHERE p.attr = r.primary_attr AND CAST(p.value AS int) > 20000  
/*
Server: Msg 245, Level 16, State 1, Line 1 
Syntax error converting the varchar value '1/1/2000' to a column of data type int.  
*/ 

The query uses the sample data in the following code:

CREATE TABLE Runs ( 
id int NOT NULL identity primary key, name varchar(30) NOT NULL, primary_attr varchar(30) 
NOT NULL 
)
CREATE TABLE ProcStats ( 
id int NOT NULL identity primary key, runid int NOT NULL references Runs(id), attr varchar(30) 
NOT NULL, value varchar(30) NOT NULL 
)   
INSERT INTO Runs values ('r1', 'a2') 
INSERT INTO Runs values ('r2', 'a2')  
INSERT INTO ProcStats values( 1, 'a1', '1/1/2000' ) 
INSERT INTO ProcStats values( 2, 'a1', '1/1/2000' )   
INSERT INTO ProcStats values( 1, 'a2', '8348' )  INSERT INTO ProcStats values( 2, 'a2', '192487' ) 

Without making any schema changes help David to do the following:
1. Identify the cause of the error.
2. Rewrite the query so that he can eliminate the error and ensure that the query runs on both SQL Server 2005 and 2000.