Querying Tables and Views on a Linked Server

Congratulations to Chris Hedgate and Marcos Kirchner. Chris Hedgate won first prize of $100 for the best solution to the March Reader Challenge, "Enforcing Restricted Uniqueness on Columns." Marcos won second prize of $50. Here's a recap of the problem and the solution to the March Reader Challenge.

Related: Querying Database Tables and Views and Linked Servers

Problem

John is a database architect for a company that uses a sales application populated with customer data from various data sources. The customer data is stored in a SQL Server 2000 database table called Customers. The following column shows some of the columns in the table:

CREATE TABLE Customers(  CustomerId int identity(1,1) NOT NULL primary key,  CompanyCode varchar(10) NULL  /* ... other columns ...*/)

The CompanyCode column isn’t always present in the data that SQL Server imports into the table. In some cases, the value can be empty or NULL. John wants to enforce uniqueness on the CompanyCode column—but only for non-null values—and exclude empty values. How can John enforce uniqueness with minimal coding and without affecting the legacy applications that provide the customer data?

Solution

John can’t define a unique constraint on the CompanyCode column because SQL Server doesn’t allow multiple NULL values or multiple empty values. He can enforce the uniqueness programmatically by using a trigger, but this technique requires modifying bulk insert processes so that they will fire the trigger. This technique can affect the insert/update operations’ performance. SQL Server 2000 has a feature called indexed vie w , which lets users create indexes on certain views. Once an indexed view is created, any changes to the base table will result in automatic maintenance of the indexes on the view. John can leverage this functionality to create a view that selects non-null and non-empty CompanyCode values from the Customers table. The following code shows the view definition:

CREATE VIEW Customers_CompanyCodesWITH SCHEMABINDINGASSELECT CompanyCodeFROM dbo.CustomersWHERE CompanyCode IS NOT NULL AND CompanyCode  '

John can also create a unique clustered index on the CompanyCode column of the Customers_CompanyCodes view. This action indirectly creates a constraint that enforces uniqueness on the CompanyCode values in the Customers table. The script below shows how the enforcement of the uniqueness property on the indexed view prevents bad data from appearing in the Customers table:

CREATE UNIQUE CLUSTERED INDEX idx_Customers_CompanyCodeON Customers_CompanyCodes(CompanyCode)GO-- SucceedsINSERT INTO Customers(CompanyCode) VALUES(NULL)INSERT INTO Customers(CompanyCode) VALUES(NULL)INSERT INTO Customers(CompanyCode) VALUES(')INSERT INTO Customers(CompanyCode) VALUES(')INSERT INTO Customers(CompanyCode) VALUES('A')-- FailsINSERT INTO Customers(companycode) VALUES('A')GO-- SucceedsUPDATE Customers  SET companycode = NULLWHERE companycode = 'GO-- FailsUPDATE Customers  SET companycode = 'A'WHERE companycode IS NULLGO

APRIL READER CHALLENGE:

Now, test your SQL Server savvy in the April Reader Challenge, "Querying Tables and Views on a Linked Server" (below). Submit your solution in an email message to [email protected] by March 16. 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

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 1The 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)