Friday, 9 March 2012

Table Valued Parameter

There was  a situation where we wanted to use table variable in dynamic sql join statements. However attempting to write below code deesnt works:

declare  @sql as nvarchar(1000)

declare  @tablevar as table(name nvarchar(10),age int)

insert into @tablevar values ('johny',66)

exec sp_executesql N'select * from @tablevar', N'@tablevar table', @tablevar = @tablevar

Above statement will throw following Error Message:
--Msg 156, Level 15, State 1, Line 1
--Incorrect syntax near the keyword 'table'.
--Msg 1087, Level 15, State 2, Line 1
--Must declare the table variable "@tablevar"

In SQL Server 2008 there is a new feature of user defined table types. We can create a table type of the the desired schema and then declare a variable of that type. Variable created of type user defined table types are called table valued parameters (TVP). TVP parameters could be passed to stored procedures and functions. These variable can also be used in dynamic sql statements.

create type tabletype as table(name nvarchar(10),age int)

declare @tablevariable as tabletype

insert into @tablevariable values ('poonam',66)

exec sp_executesql N'select * from @tablevariable',N'@tablevariable tabletype
READONLY',@tablevariable = @tablevariable

Please note the usage of "READONLY" option which is compulosry while passing a table valued parameter as a parameter.

Reference URL:

Length for data type nvarchar(max) in ADO.Net parameter collection

I had a SQL Server based stored procedure with an output parameter of type nvarchar(max). Issue we faced was what length of this variable length parameter should be set in the ADO.NET parmater collection.
Answer is length for such data types could be set to -1 in the parameters collection as follows:

oDatabase.AddOutParameter(oDbCommand, "@RegionNotDeleted", DbType.String,-1);

Protocol Violation Exception raised while using PHP webserivce in .net project

In one of our 3.5 project we were using PHP services. Whenever a request to PHP service was made we were alternatively getting following exception:
"System.Net.WebException: The server committed a protocol violation. Section=ResponseStatusLine"

After googling and refering to related sites we tried setting useUnsafeHeaderParsing in web.config to true. But this didnt solve this issue.

<httpWebRequest useUnsafeHeaderParsing = "true" />

Reason for the above exception was that the request headers were  changing in the consequtive requests and this was not validated by HTTP 1.1 protocol. To resolve this we created a custom class that inherits the proxy class (auto-generated by Visula Studio). In this class WebRequest method was overridden and the HTTP protocol version was changed to 1.0 and the property KeepAlive was set to false. In HTTP/1.1 a keep-alive-mechanism was introduced, where a connection could be reused for more than one request. By setting KeepAlive to false a new connection was created with every request.

public class checkSiteUserEmailBindingCustom: wsPHPWebsite.checkSiteUserEmailBinding
            private static PropertyInfo requestPropertyInfo = null;

            public checkSiteUserEmailBindingCustom() { }

            protected override System.Net.WebRequest GetWebRequest(Uri uri)
                // Retrieve underlying web request
                System.Net.HttpWebRequest webRequest = (System.Net.HttpWebRequest)base.GetWebRequest(uri);
                webRequest.KeepAlive = false;
                webRequest.ProtocolVersion = System.Net.HttpVersion.Version10;

                return webRequest;

If we drill down the web services classes all the classes directly/indirectly belongs to System.Web.Services.Protocol namespace. Base class for all XML web service client proxies is WebClientProtocal class. This is an abstract class and contains a virtual method GetWebRequest. 
WebClientProtocal class is inherited by HTTPWebClientProtocol. This is an abtract base class for all XML Web service client proxies that use the HTTP transport protocol. This class is further inherited by  System.Web.Services.Protocols.SoapHttpClientProtocol. This is the  class which proxies derive while using SOAP protocol. This class Overrides HttpWebClientProtocol.GetWebRequest(Uri).

Reference URLs: