To Manipulate Tables

Congratulations to Ihor Bobak, an MCP and chief software architect for UKEESS Software House in Lviv, Ukraine, and Ing. Marek Skotnica, a programmer for NH Ostrava in the Czech Republic. Ihor won first prize of $100 for the best solution to the November Reader Challenge, "To Manipulate Tables." Marek won second prize of $50. Here’s a recap of the problem and the solution to the November Reader Challenge.

Problem:

Peter is the database architect for a company that builds accounting applications using SQL Server 2000 and 7.0. The applications have two databases, APP and ACCT, that reside on different SQL Servers or instances. The application code connects only to the APP database; Peter accesses the ACCT database primarily through distributed queries from stored procedures. Peter knows the ACCT database server’s name only at setup or installation time, so when he initially implemented the code, he used dynamic T-SQL and distributed queries as the following example shows:

-- Get name of ACCT database server from configuration table:SET @AcctSrvr = '[Name_Of_Srvr_containing_ACCT_DB]'EXEC(‘SELECT * FROM OPENQUERY(' + @AcctSrvr + ', "SELECT * FROM ACCT..users'')')

During setup, Peter uses the system stored procedure sp_addlinkedserver to create and configure the linked server for the ACCT database based on user input. Peter finds that approach tedious and hard to maintain, especially for complex queries. He wants to create views on remote tables that he can easily manipulate. Help Peter:

Solution:

By using a predefined name that doesn’t conflict with other linked server names on the server, Peter could eliminate the need to retrieve the ACCT database server’s name. Then, in his distributed queries he can reference the linked server name as parameters to OPENQUERY or in the four-part names of objects. Peter needs to use sp_addlinkedserver to define the linked server for the database differently. Currently, he creates the linked server entry during setup with the following call:

EXEC sp_addlinkedserver 'Name_Of_Srvr_containing_Acct_DB'   -- Provided by user

In this call, the name of the server containing the ACCT database is the same as the name of the linked server entry in the SQL Server containing the ACCT database. If the user provides only the linked server name, then by default, SQL Server creates the linked server entry as a SQLOLEDB provider and considers the SQL Server’s network name to be same as the linked server name. However, Peter can use the optional parameters to modify the sp_addlinkedserver call. He can then use a predefined name for the linked server name, pointing it to the name provided by the user.

The following stored procedure call shows how to use non-default parameters to configure the linked server entry:

EXEC sp_addlinkedserver    @server = 'MyAppAcctSrvr', -- This is the predefined ACCT server name for internal purposes.        @srvproduct = 'MSSQL',-- Product name, because it’s a mandatory parameter        @provider = 'SQLOLEDB',-- Provider to be used for linked server        @datasrc = 'Name_Of_Srvr_containing_Acct_DB',    -- Provided by user        @catalog = 'ACCT'-- Optionally specify name of initial catalog

With this approach, Peter eliminates the applications need to know where the ACCT database is physically located. And he can reference the name MyAppAcctSrvr in his code, so he can use the predefined name in his queries and create views on the remote tables, thereby avoiding dynamic SQL. The following code performs the distributed query as requested in the problem:

SELECT * FROM [MyAppAcctSrvr].ACCT.dbo.users

Peter can also create the linked server connection by specifying a different network name for the SQL Server. The default network name for linked servers that are using a SQL Server provider is the same as the linked server name. Here are the calls to create the same linked server entry MyAppAcctSrvr with a different network name:

EXEC master..sp_addlinkedserver     @server = 'MyAppAcctSrvr';EXEC master..sp_setnetname     @server = 'MyAppAcctSrvr',     @netname = ' Name_Of_Srvr_containing_Acct_DB';

Finally, Peter can use the Client Network Utility on the SQL Server machine to set up a network alias for his application server that points to the server’s IP address on the network. With this approach, Peter can create a network alias called MyAppAcctSrvr in the Client Network Utility on the SQL Server machine and he can use the name MyAppAcctSrvr to create the linked server entry.

December Reader Challenge:

Now, test your SQL Server savvy in the December Reader Challenge, "Maintaining Information" (below). Submit your solution in an email message to [email protected] by November 20. SQL Server MVP 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.

Alan is a programmer for a company that hosts several SQL Server 2000 data warehouses. He has a stored procedure that executes as part of a SQL Server Agent T-SQL job. The stored procedure performs a series of operations that execute other stored procedures or data-manipulation statements in a particular order. SQL Server performs, within one transaction, all the changes the stored procedure makes. In case of errors raised from code via the T-SQL RAISEERROR statement, the entire transaction gets rolled back by the database engine on the server.

Alan wants to maintain status information for each stored procedure executed, despite no matter what the outcome of the transaction is. He wants to record the information in a table at the end of the transaction. Assume that he plans to store the status information in a table that has the following structure:

CREATE TABLE StatusInfo (     RunID int NOT NULL,     Stage varchar(50) NOT NULL,     StartTime datetime NOT NULL,     EndTime datetime NOT NULL,     Status tinyint NOT NULL)

Help Alan implement the logic to maintain the status information irrespective of the transaction’s outcome.