The Ass Beating for Using Web Services Directly from SQL Server 2005

An ass beating is waiting for the .NET developer who gets the Microsoft story wrong. It follows that, for the last few days, I have been pummeled, losing many precious hours, because of this incorrect summarizing statement based on my generous ignorance:

SQL Server 2005 exposes Web service endpoints that are designed to easily facilitate decoupled calls from a wide variety of cross-platform clients.

Now that I have been beaten severely, I get the correct message:

SQL Server 2005 exposes Web service endpoints that are designed to easily facilitate decoupled calls from any client that understands what a DataSet is—and what SqlTypes are (see Simple WSDL in the table below).

The emphasis is on the word, easily. You want hard? Okay, when you want to make your life miserable then you can try to avoid the DataSet as the primary means of getting your data back from a SQL Server Endpoint (see table of references below). Ouch! Damn! Since I’m staggering around punch drunk, the random bullets below represent my efforts to regain my balance:

I’m looking for a tutorial that shows step by step how to not use a DataSet as a return value.
In an effort to debug my code-generated Web service on the actual server, I thought it would be “easy” to install C# on the SQL Server Business Intelligence Development Studio version of Visual Studio 2005. This was not the case. C# (and others) does not show up in the “Installed products” list and I am unable to find the C# project type in the New Project dialog. Update: reapplying Visual Studio 2005 SP1 solved this problem.
Debugging the generated Web service code is still a mystery to me (see table of references below). This discourages me from taking advantages of the partial classes of the Web service and is the direct cause for my SqlEndpointRequest class that takes the not-easy-to-debug Web service as an argument.The table below sketches out the offer you can’t refuse (you, being a .NET developer):

“[Http Endpoint and FOR XML—Return XmlElement in Native XML WebServices in SQL Server 2005](http://davidhayden.com/blog/dave/archive/2006/04/22/2922.aspx)”	David Hayden: “A question I received on Http Endpoint is if the DataSet is the only way to return data from an Http Endpoint. The answer to this is no, but a couple of things are coming into play here.” The answer is actually, ‘yes.’ Keep reading…
“[SQL 2005 Endpoint: Web Method using FORMAT = NONE isn’t working](http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/838008093831/inc/-1)”	The horrible truth: “I’m fairly certain that you have to write your own [WSDL](http://www.w3.org/TR/wsdl) when you use format = None…” Now “easy” is thrown out the Windows window.
[CREATE ENDPOINT (Transact-SQL)](http://msdn2.microsoft.com/en-us/library/ms181591.aspx)	This is a faily lengthy bit of MSDN documentation. But it’s not like Microsoft is hiding the horrible truth. Searching for the text, “The FORMAT=NONE option has the following conditions for use:,” on this page will take you the heart of this “challenge”: First, the SQL object representing the Web service method call cannot be a user-defined function. Second, “Either the query must be a single-statement FOR XML query (Multiple FOR XML statements are not allowed with this response format.); or the output must consist of a single column that has the name `XML_F52E2B61-18A1-11d1-B105-00805F49916B` and of the type `nvarchar`.”
“[Identifying system endpoints in SQL Server 2005](http://blogs.msdn.com/sql_protocols/archive/2005/10/14/480842.aspx)”	This article provides the way to investigate the SQL Server for Web service endpoints. This is the query: `select name, endpoint_id from sys.endpoints`
“[SQL Server 2005 Native Web Services; Endpoints](http://sqlsvr.wordpress.com/2007/10/16/sql-server-2005-native-web-services-endpoints/)”	This article may “prove” that SQL Server 2005 endpoints can easily return custom XML using the FOR XML syntax. But the code shows that this “easy” XML comes from the [GetXml()](http://msdn2.microsoft.com/en-us/library/system.data.dataset.getxml.aspx) method of, you guessed it, the `DataSet` object (very strange code).
“[Using WSDL](http://msdn2.microsoft.com/en-us/library/ms175476.aspx)”	This may be the SQL Server Developer Center article I’m looking for—or this hides a new pummeling! This shows that, by using a `?wsdlsimple` query string, you can return “a short simplified version of the default WSDL that maps all SQL Server system data types to native XSD types. This format is returned when you use `http://server/endpoint_path?wsdlsimple` as the URL to connect to the endpoint.” This almost gets me back to my orginal story—because XSD types are supported by ‘wide variety of cross-platform clients.’ This Simple WSDL technique returns an array of XmlNode objects—and one of the nodes contains the XML version (serialized) of our `DataSet`. So ‘easy’ makes a little room for ‘hard’ when your non-.NET client has to parse `DataSet` XML. This abuse is not as horrible as the other “easy” alternatives.
“[Implementing Custom WSDL Support](http://msdn2.microsoft.com/en-us/library/ms188321.aspx)”	This is where it gets hard. Since the documentation is designed not to explain what is about to happen, by the time to get a few paragraphs into “[Using the Custom WSDL Application](http://msdn2.microsoft.com/en-us/library/ms175577.aspx),” it becomes clear to you that you need to enable CLR support for SQL Server and install a custom assembly. I did see a SQL-only version of something similar to this but both alternatives are not generic (in a ‘custom’ kind of way) and are lengthy and complex.
The “[Getting Started Tutorial](http://msdn2.microsoft.com/en-us/library/ms734712.aspx)” for Windows Communication Foundation.	Avoiding Web services for SQL Server means going all the way back to [ASMX land](http://msdn.microsoft.com/msdnmag/issues/04/12/ServiceStation/) or going way, way forward into Windows Communication Foundation (WCF).

Comments

Ed, 2007-12-06 00:22:59

Shouldn't the database stay inside the firewall? Why bother when you can write an web front end that can deliver the data? Maybe it would be better for caching on the web front also..

BTW, I heard your potatoes instructions are being passed about email chains...thanks for the advice.

rasx(), 2007-12-06 05:12:12

My mother's cooking was genius!

Microsoft (and it's lawyers) promise that SQL Server Endpoints are secure (especially in an Intranet setting).

Keith Elder, 2007-12-11 20:54:16

I feel your pain and laughed my ass off when I read your title. Damn funny. Anyone investigating these should use precaution. For example the purpose of a service is to be able to be consumed regardless on language. It is my understanding that SQL Server web services can only be used based on active directory credentials which means a calling language like PHP or Ruby isn't going to be able to invoke the service since anonymous authentication isn't allowed.

The other thing when I played with it that turned me off was how ill formatted the information was. I couldn't get over the dataset issue.

Lastly, services should be a service, provide a service, not just return data. I couldn't get over that either. Good luck and keep the hilarious titles coming :)

rasx(), 2007-12-12 18:41:36

I found out that there is a way to avoid a DataSet even as XML---more on this soon...

rasx()