Q How can a SQLXML Web Service-returned .NET Framework DataSet be consumed in ASP.NET?

A Microsoft® SQLXML is an ISAPI extension for Microsoft Internet Information Services (IIS) which maps HTTP requests for XML data to SQL Server™ 2000 (see http://msdn.microsoft.com/sqlxml). With SQLXML 3.0, stored procedures can be exposed as .NET Framework DataSets returning XML-based Web Services. To do so, you'll want to bind these DataSets in an ASP.NET Web form. Here you'll learn how to use the SQLXML user interface to expose a stored procedure as a Web Service, add a Web reference in Visual Studio® .NET to the Web Service, bind the resulting DataSet to an ASP.NET DataGrid, and show just how easy it is to do all this.

In order to get started on your server, you will need to be running IIS on Windows® 2000, Windows XP Professional, or Windows Server 2003; SQL Server 2000; SQLXML 3.0 SP1 (which you can download from the SQLXML Web site at http://msdn.microsoft.com/sqlxml); and SOAP Toolkit 2.0 SP2 (which you can download from the SOAP Web site at http://msdn.microsoft.com/SOAP).

In this example, a single stored procedure from the Northwind sample database will be exposed.

To begin, click on Start | Programs | SQLXML 3.0 | Configure IIS Support to open the IIS Virtual Directory Management for SQLXML 3.0. Expand the MMC snap-in to the Default Web site node, right-click, and select New | Virtual Directory to open the New Virtual Directory properties page.

Next, name the new virtual directory "NorthwindWebSvc" and point it to C:\NorthwindWebSvc or another folder. From the Security tab, select "Use Windows Integrated Authentication" and then from the Data Source tab, select the Northwind database. Because SOAP must post its request to the XML Web Service, make sure "Allow Post" is checked in the Settings tab.

Next, from the Virtual Names tab, type "SOAP" in the name textbox and select SOAP from the type dropdown. Then you should point the path to C:\NorthwindWebSvc\SOAP\ or another directory. The directory's Access Control List can be used to restrict access to the Web Service.

Finally, click Save and then Configure (see Figure 1), then click the [...] button to select a stored procedure. You can select SalesByCategory. The method name will be generated based on the stored procedure name. Make sure the "Output as" option has Single dataset selected, then click Save, and then click OK.

The Northwind XML Web Service is now configured and has one available method—SalesByCategory. In the next step, you'll bind the returned dataset to a DataGrid in an ASP.NET Web Form.

At this point you need to add a Web Reference in a Visual Studio .NET Web Forms Project. Once you have added the reference, Visual Studio .NET will generate a proxy class to all available methods in your SQXML Web Service. First, open a new Visual Studio .NET ASP.NET Web Forms Project in your language of choice. Then from the Solution Explorer of your new project, expand to the references node, right-click, and select Add Web Reference (see Figure 2). In the Add Web Reference Dialog enter http://localhost/NorthwindWebSvc/SOAP?wsdl into the Address field and then click Add Reference.

Now you'll need to bind to the ASP.NET Web Form DataGrid. Open Webform1.aspx, which was automatically generated when you created the new project. Drag and position a new DataGrid anywhere in the Web form. Then double-click in the Web form to open the codebehind class, locate the Page_Load routine, and paste in the following code: Save WebForm1.aspx and open Web.config. Add the following identity element with impersonate set to true so that ASP.NET can pass on the current users credentials to SQL Server: Finally, build and browse WebForm1.aspx to enjoy the results.

Q I have a really large database and I want to select specific data from it. I am considering the following options: I can make views and select the data I need from them, I can use stored procedures, or I can talk directly to the tables or to the views. Is there another way I can speed up data retrieval on the client?

A Well, if you don't need the return set all at once—for example, if you're paging through it on a Web page—you have a couple of options available to you.

First, you could use a unique key to keep track of your location in the table. Do something like the following: Here, 50 is the number of rows you want to display on each page. You retrieve @lastkey from the final row of each set returned. You could also return header- or summary-level data, then allow the user to drill down into it. For example, instead of returning all the details for all orders in an orders table, try to just return the header info and provide the functionality to further drill into an individual order. That can greatly reduce the amount of data you have to return to the client in one shot and thereby speed it up while still providing the option to get more info on the items of interest.

Q Is there COM support for SQLXML Managed Classes? If so, how is it implemented? In other words, are COM wrappers built for a Microsoft .NET Framework implementation, or vice versa?

A The SQLXML managed provider is a wrapper around SQLXMLOLEDB. So use the SQLXMLOLEDB provider with ADO for COM. SQLXML Bulk Load only provides a COM interface.

All other SQLXML functionality that is not SOAP-based is included in the ADO Stream/ICommandStream object model via COM. The SQLXMLOLEDB provider just creates the XML on the client side, gives variations to the FOR XML modes, and allows pass-through of a recordset returning a stored procedure.

SOAPSQL requires a virtual directory and is not exposed directly for a COM call. SQLXML SOAP is only implemented over HTTP. But the configuration tool to set up the virtual directories and the SOAP method mappings is fully automated through COM Automation. Take a look at the SQLXML 3.0 docs (http://msdn.microsoft.com/sqlxml) for more information.

Q I have an XML file that will not fit into a SQL varchar field. How can I load it? I see that sp_xml_preparedocument can take ntext as a parameter, but I cannot figure out how to do that.

Suppose my XML data is in an ntext column in a table, like this: How do I get that into sp_xml_preparedocument so that I can parse and use the XML in an OPENXML statement?

A You can pass the XML file content to the server as an ntext parameter to a stored procedure that then uses sp_xml_preparedocument and OpenXML in order to extract the information into the relational tables.

Unfortunately, SQL Server 2000 has no easy way to get an ntext value from a table into a parameter, regardless of XML. There are two other workarounds: use nested EXECs or use the OLE Automation stored procs to externalize and reimport the data via OLE DB.

Q If I create a SQLXML virtual directory in IIS (with sqlxmlx.dll 1.0), set the authentication with SQL Server using a SQL user, and allow template queries, then under the IIS MMC snap-in set the authentication mode for the virtual directory as "Windows Authentication," the NTFS security for template XML files gives everyone access. If I log onto a machine with a local machine user account and execute the template query, I expect an "access denied" error, but it didn't fail. Why not?

A When SQLXML is configured to use SQL Server authentication, it sets the IIS authentication to be anonymous. When you set the IIS authentication type to Windows, make sure that the anonymous authentication is turned off; otherwise the Windows authentication doesn't have much effect. Since template file access is not restricted, it does not matter how you log in.

Q I need some advice on how to implement the following scenario. I want to use SQL Server in a clustered configuration with a Storage Area Network (SAN) back end. Also, I want to use an active/passive configuration where the passive node is to build online analytical processing (OLAP) cubes from the data on the active node while in normal operation. I want to use split backup on the SAN side to create a separate read-only mirror, then mount the split mirror on the passive node as a separate volume to conduct online analytical processing.

Can this be done? If so, how? Finally, are there any problems related to this solution?

A OLAP is not cluster aware. It can be made a generic cluster service in a server cluster, but it's not the same as the default SQL Server 2000 failover clustering. See Knowledge Base article 308023 for more information.

Also, you are describing two instances; by your definition, it would be active/active. You can use Network Load Balancing (NLB) to make Analysis Services available, too (see Creating Large-Scale, Highly Available OLAP Sites). One issue that you didn't mention is how the OLAP cubes are being queried. That must be done from the same virtual server that is doing the OLAP cube processing. What you cannot do (which is what everyone would like to do, but can't) is to have one node in the cluster doing partition processing while another is performing queries. The OLAP data folder can only be accessed by one server in the cluster at a time.

There is no problem with doing this: In effect, each system is providing the backup of the other.

That will work very well and if you follow the instructions for the Analysis Services setup in the previously cited Knowledge Base article, you will have no problem. You could replicate the data from the "active" node to the other node (another instance that is not clustered) and build cubes on the nonclustered instance. Then the OLAP piece wouldn't failover, but the Online Transaction Processing (OLTP) piece would.

However, it is probably (for now at least) better to put OLAP on nonclustered boxes, use NLB to make it available, and cluster your main data store, which is your OLTP repository. Otherwise, more would need to be known about the whole architecture.

Q I read that SQL Server 2000 always tries to register a virtual server in Active Directory® first, then it tries Windows NT® LAN Manager (NTLM). (See Knowledge Base article 303411.) Does this mean that when you failover a SQL Server 2000 server in a Windows NT domain it takes longer because you can't talk to the Active Directory controller? I noticed the failover on SQL Server 2000 in my production environment was a lot slower than my live one which is authenticating to Active Directory. Does SQL attempt to re-register upon failover? What would be some reasons that failover would take longer in one environment than another, all other things (hardware, network, and so on) being equal?

A Upon failover, SQL Server stops on one node and starts on the other. Upon startup, SQL Server will attempt to register a Service Principal Name (SPN) in the Active Directory. If this fails because of the reasons in the Knowledge Base article you cited, it doesn't mean that SQL Server can't talk with a domain controller. If that were the case, SQL Server might not be able to start at all because the SQL Server startup account couldn't authenticate with the domain. One thing that could affect failover time is the length of recovery when SQL Server starts up, such as transactions needing to be rolled forward or back.