Web Services and SQL Server 2005 - sqlbatch - HTTP Endpoint - BATCHES = ENABLED
by David Hayden ( Sarasota .NET Developer )
There is so much political correctness when it comes to the new features in SQL Server 2005 that it makes one squirm when talking about sqlbatch. SQL Server 2005 allows you to create Http Endpoints, which are essentially XML Web Services without IIS. You can expose stored procedures as XML web services, which allows your client applications to bypass the whole need for IIS and a web developer like me, :( , to get at the rich information in your SQL Server 2005 database. I have talked about Http Endpoints in two previous articles:
One of the options you have when creating Http Endpoints is to enable batches. By enabling batches, you are essentially enabling ad hoc queries. Obviously, all SQL Server Security applies, which means you can't get access to securables for which you don't have privileges. To enable sqlbatch, just set BATCHES = ENABLED during the creation of your Http Endpoint:
CREATE ENDPOINT AW_Contacts
STATE = Started
AS HTTP
(
PATH = '/Contacts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'GetContacts'
(NAME = 'AdventureWorks.dbo.GetContacts'),
BATCHES = ENABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = DEFAULT
)
When you do this, you will see a new web method exposed by your WSDL
sqlbatch(string BatchCommands, ref SqlParameters[] Parameters);
Yeah, this bad boy allows you to shoot any ad hoc T-SQL to your liking at the database, which means you are no longer limited to the stored procedure, GetContacts, mentioned above. You are essentially limited by your account privileges in SQL Server 2005.
Once again, I am merely explaining the facts and opening the door to opportunities. This BATCHES = ENABLED option provides a lot of power, and with power comes a lot of responsibility. Sqlbatch certainly gives the developer a lot more opportunity and flexibility on the client-side as to what type of queries can be sent to the database via the Http Endpoint.
Source: David Hayden ( Sarasota .NET Developer )
Filed: SQL Server
SQL Server 2005 Tutorials