Altering table on Sql 2008

When I started working on Sql 2008, whenever I modify table using Management studio, it was throwing an error "Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created". I was little surprised since this is kind of basic functionality and what am I missing to modify the table.

After a while I found that there is a new setting added which needs to be disabled. Here is the steps to get this work

Go to Options

Go to Designers node deselect "Prevent saving changes that require table recreation"Once you save this setting, the modify table will work fine. Just thought it would help someone who is going to start using sql 2008 and dont have to run around this problem :-)

Dynamic Queries with SQL

There has been many situations we had to use dynamic queries. There are different ways to write a dynamic queries. I would like to point out some of the advantages and disadvantages of using dynamic queries.


Most of the time the common mistake using dynamic queries is that we use EXEC sp_executesql and pass the query directly by replacing the values. This works fine but if we have any parameters that we need to use in query will be passed as part of the query and not as a parameter. This would internally keep seperate cache in sql server each time when you run the queries.

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid=100'



This can be fine tuned as below

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid = @id', N'@id int',@id=100

The sql will keep the query on canche and when the query invioked next time same execution plan will be used. In the earlier case, the sql will create seperate execution plan each time we run the query.

One of the other disadvantage using dynamic queries without passing the values parameters is that it might lead to SQL Injection. When the values are appended with sql and passed to sp_executesql it can lead to injection. Lets take an example with the above query



if the @id is passed to sp as "0 or 1=1" then the dynamic sql after replacing will be



EXEC sp_executesql N'SELECT * from employees e where e.employeeid =0 or 1=1'



This will return all the employees. This will be a high chance when we pass the parameter string. In the case of int it may throw error while passing value to sp itself. But all these can be avoided if we use parameterized dynamic sql.



I found a detailed article on dynamic sql http://www.sommarskog.se/dynamic_sql.html

Strange Problem with Cold Fusion

I get to work on Cold fusion sometimes. I just wanted to state a strange problem we had faced recently. This was not the case since the day it was set up. The service started sending duplicate emails whenever a email is sent from cold fusion code all of a sudden.

Not all emails were duplicating but some of them did. While researching on the issue, from the log found that multiuple threads trying to send the same email

The log entry was like this

"Information","scheduler-1","10/27/09","16:05:20",,"Mail: 'New request in Queue'
From:'webmaster@myweb.com' To:'myemail@myweb.com' was successfully sent using [IP]"

"Information","scheduler-3","10/27/09","16:05:20",,"Mail: 'New request in Queue' From:'webmaster@myweb.com' To:'myemail@myweb.com' was successfully sent using [IP]"

One of the setting on cold fusion administrator site allows us to set maximum delivery threads. By default this will be set to 10. Just updated this to 1 and monitored for sometimes. This seems to be fixed the problem. Strange bug!!!!


Reference: http://www.talkingtree.com/blog/index.cfm/2006/2/1/Duplicate-Message-ID-identifiers

Loading data as XML from DB

In most of the applications, the data will be loaded as Dataset or datareader from Sql server from the back end DB. If we are going to look at service oriented approach, the services normally return xml. Most of the time we get data from SQl and convert to xml and send it. We could get the data directly from Db itself.

From the stored procedure here is my select statement

This statement selects the data from Department table which has columns DeptID,DeptName,IsActive

SELECT
1 AS TAG, NULL AS PARENT,
DeptID AS [Department!1!DeptID],
ISNULL(DeptName, '') AS [Department!1!DeptName],
FROM Department (NOLOCK)
WHERE IsActive = 'Y'
ORDER BY DeptName
FOR XML EXPLICIT, ROOT('Department')

The first two columns Tag and PARENT are meta coulmns. for more details on these tags you can check http://msdn.microsoft.com/en-us/library/ms189068.aspx

The xml returned would be

<Department> < DeptID="1" DeptName="Administration"/> < /Department >

Loading the Xml Data using C#

SqlConnection dbConn = new SqlConnection(connectionstring);
dbCommand.Connection = dbConn;
dbConn.Open();
XmlReader dbReader;
dbReader = dbCommand.ExecuteXmlReader();
dbReader.Read();
StringBuilder str = new StringBuilder();
do
{
str.Append(dbReader.ReadOuterXml());

}
while (dbReader.ReadState != ReadState.EndOfFile);
dbConn.Close();
return str.ToString();

Above statement will return the xml data returned from DB as a string format which can be returned from service directly.

But it is always a good practice to bind the data returned from DB to a schema for the datatype check and perorm the validation. The above method returns xml but does not have any infromation on the schema of the data. So this may not be suitable in all the situations.

ARITHABORT and Xml Datatype on SQL

While using XML datatype on Sql 2005 or 2008, I got into a strange problem. The sql queries used work perfectly when I run it on query analyzer but it used to fail when I put that on a stored procedure. The sql was using xml datatype and it was performing some xml operations.

After researching, I found that if we use xml datatype, we need to set ARITHABORT ON.
This is the requirement as stated here

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354563

"Currently, there is a reqiurement that ARITHABORT must be ON when you use XML methods and XQuery. However, as long as the compatibility level is >= 90,ANSI_WARNINGS on implies ARITHABORT ON, so this check is not needed. Many client APIs connect by default with this setting off, but ANSI_WARNINGS on,so users need to take extra precautions to deal with this and it can causeconfusion and misery. For instance, I recently encountered a case where someonerun into his Agent job failing because of this"


So make a point to set ARITHABORT ON whenever you use xml datatype on your stored procedures.

Accessing wsHttp WCF service from outside domain

The wsHttp binding of WCF service uses windows authentication, so using the service outside the domain is not a straight forward method.

Well, I had the WCF services created and deployed on my app server when I tried accessing it from other domain, it started throwing security exception, that's when I came to know the actual problem. The client credentials were not set when accessing it from the outside.

The easy way I found and implemented by keeping a service account which is a domain user. I have kept that user details in my configuration which is encrypted on client side and used as below

AdminService.AdminServiceClient objSvc = new AdminService.AdminServiceClient();

objSvc.Endpoint.Address = new System.ServiceModel.EndpointAddress(endpointaddress);
objSvc.ClientCredentials.Windows.ClientCredential.UserName = username;
objSvc.ClientCredentials.Windows.ClientCredential.Password = password;
objSvc.ClientCredentials.Windows.ClientCredential.Domain = domain;

While creating the wcf service client, assign username, password and domain which belongs to domain. After updating the clientccredentials, the client started working fine.

Configuring Identity IIS Impersonation for WCF

One of the common scenarios on most of the application would be accessing network shared folder for any files. With WCF, I had little problem figuring out how to access the network path since the IIS work thread is running under network services. The ASP.net Impersonation would not work for WCF.

I was using wsHttp binding and I have used a domain user to impersonate all the calls from the client side, even then the impersonation was not in effect. One of the change I tried on IIS was by changing identity of Application pool. For this I have created new app pool for my application. On the application pool simply change the identity to domain user account.

When changing the application pool identity to domain account, I found a problem, the application pool was getting shut down.When I check the event viewer, it was logged as

The identity of application pool, 'MyServices' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

and on the security tab there was one more log
Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 534
Reason: The user has not been granted the requested
logon type at this machine


When I searched for these errors, I got to know that the domain user which is configured for Identity should be added as IIS_WPG users group on the local group. Once the user is added to group, everything started working fine.

Reference: http://www.eventid.net/display.asp?eventid=534&eventno=10&source=Security&phase=1