Strange Web Services Exception

One of the application I work on started playing up recently.  The application communicates with one of our customers systems via web services.  Due to technical reasons on their side they are changing the platform that their web services run on.  This has meant that we have had to make some changes to accomodate their changes and get our application talking to the new web services correctly.  Once released into their testing environment some of the web services call started to throw a System.ServiceModel.CommunicationException:

System.ServiceModel.CommunicationException: There was an error in serializing body of message checkIssueDeliveryRequest: 
'Unable to generate a temporary class (result=1).  error CS2001: Source file '
C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error CS2008: No inputs specified  '.  
Please see InnerException for more details. ---> System.InvalidOperationException: Unable to generate a temporary class (result=1).  
error CS2001: Source file 'C:\Windows\TEMP\rjsmbtjy.0.cs' could not be found  error 
CS2008: No inputs specified       
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)   
....
....

It turns out the problem is down to the fact that the User account that the Application Pool associated with the web services website does not have read and write permissions to the c:\windows\temp directory as described here http://support.microsoft.com/kb/322886.  Below is a more detailed step by step approach that I to solve the issue.

• Open IIS Manager
• Click on the web services application node of the problem web services website
• Click on Basic Settings... to see what Application Pool is being used (in my case it was ASP.NET v4.0 Integrated)
• Click the Application Pools node and select the Application Pool associated with your web services website
• Click on Advances Settings... and check the Process > Identity to get the account that is being used
• Open an explorer window and browse to c:\windows\temp
• Right click on the folder and click properties
• Select the Security tab and  click Edit:
• If the account already appears under "Group or user names:", highlight the account by clicking on it and ensure that the read and write checkboxes are checked
• If it is not already listed click Add... to add the account before ensuring that the read and write permission are checked for that account

That's it for now.

T-SQL: Scripting tables and Named constraints

In a number of projects that I am currently working on I have had to make schema changes.  When creating these schema changes we generally create changes scripts that get run as part of a version upgrade.  The change scripts will generally be applied either on application startup or by a command line application that will run any new upgrade scripts and update a version number in that database.  To get an idea of how this can be done have a look at how FluentMigrator works.

I don't particularly like creating new tables in SQL Server Management Studio and then using the script generator to create a create table script as I find the resulting scripts are always pretty verbose.
Writing a create table script is pretty straight forward.  Here is an example:

CREATE TABLE [dbo].[Employee]
(
  EmployeeID int not null PRIMARY KEY,
  FirstName varchar(100) not null,
  LastName varchar(100) not null
)

The problem with this script is that the primary key is not explicitly named.  So how do we go about explicitly naming the primary key constraint and for that matter any other constraint.  Here is an example that demonstrates how to do this for primary keys, foreign keys and unique constrains:

CREATE TABLE [dbo].[JobTitle]
(
 JobTitleID int not null 
  CONSTRAINT [PK_JobTitle] PRIMARY KEY (JobTitleID),
 Name varchar(100) not null,
 [Description] varchar(2000) null
)

CREATE TABLE [dbo].[Employee]
(
  EmployeeID int not null 
 CONSTRAINT [PK_Employee] PRIMARY KEY (EmployeeID),
  FirstName varchar(100) not null,
  LastName varchar(100) not null,
  IDNumber varchar(30) not null 
 CONSTRAINT [UC_IDNumber] UNIQUE (IDNumber),
  JobTitleID int null 
 CONSTRAINT [FK_Employee_JobTitle] REFERENCES [dbo].[JobTitle]
)

One thing that is not demonstrated in this code snippet is how to add a second named constraint onto a field.  It is pretty straight forward.  I have specifically layed out the example with the constraints indented on the line below the field they relate to.  Notice that there is no comma before the CONSTRAINT keyword.  To add another constraint that applies to the same field, you just add another constraint keyword and continue.  I have indented the constraints and put them on their own line so that the code is more readable in the case where there are multiple constraints on any one field.

SQLCMD and BACKUP - watch out for this

Yesterday, I was working creating a rake task for backing up a database.  As I am still pretty new to ruby I decided to see if I could get the backup code working with sqlcmd from the command line first.  I created a backupdb.sql file to do the backup and then called it from the command line.

This is what the backupdb.sql looked like:

BACKUP DATABASE $(database)  
    TO DISK = $(backupfilename)

Not much to it!
I then executed the following sqlcmd command:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="mydb.bak"

When I just specified the database and backupfilename the backup worked fine with the backup file being written to the default SQL Server backup folder.  I then changed the backupfilename to include a path as I wanted to specify where the backup file should be saved. This is the command I executed:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="E:\sqlbackups\mydb.bak"

This time I got the following error:
Msg 102, Level 15, State 1, Server ANDREW-PC, Line 3 Incorrect syntax near 'E:'.
After a lot of fiddling around I figured out what I needed to do to fix the problem. The problem was that the backupfilename needs to be unicode.  See below for the fixed backupdb.sql

BACKUP DATABASE $(database)  
    TO DISK = N'$(backupfilename)'