Tuesday 24 February 2009

SQL Server Debugging

I have tried this out today and to be able to step through a stored procedure after it has been called by a .net application is fantastic. This can save so much time now I know what to do.

The key to this is the server explorer in Visual Studio - I should be in the habit of always setting up a connection to SQL server when working on a database project.

Four steps:
1) Attempt to step into a stored procedure and receive warning that certain opptions have to be set on the machine. OK this and it will do it automatically. (I did not get the exact messagage)
2) In Project properties, debug tab,  Check "Enable SQL Server Debugging"
3) In server explorer, right click and open a stored procedure. Set a break point
4) In connection strings, add Polling=false; to string

The the application branches into the stored procedure you can step through it like any other piece of code, even inspecting values of TSQL variables within the procedure.

This could save a lot of time if I get used to these steps.

I need to watch that I have a means of setting connection polling only for testing, and having a different setting for the released versions of the code.
One possibility is to check processes to see if visual studio is running. Then only have pooling set to true if it is not, otherwise explicitly set it to false.

This works at Scotts but not on my home PC, I need to troubleshoot this because this functionality is so useful. You can even step through stored procedures on their own.

Wednesday 18 February 2009

Exporting to CSV, Opening In Excel, Special Character Problem

When creating a csv file from VB.net, then opening this file in Excel using the file association method (ie process.start(file.csv)) special characters such as Nestlé are not recognised by MS Excel. Instead it is shown as Nestlé 
When opening the same CSV file in notepad the foreign characters are shown correctly.

However, if the Import feature is used in Excel, and the encoding is specified as UTF-8 then the data is displayed correctly.

By default, the vb.net streamwriter will use UTF-8 encoding. 
In UTF-8 the two-byte sequence 0xC3 0xA9 = é
In Windows-1252 0xC3 0xA9 = Ã©

To solve this problem I could write the data as an Excel file using COM. However, this ties the application to a particular version of Office and could break if the company upgrades from Office 2003 to 2007.
Instead I have changed the encoding within vb.net.
stwriter = New StreamWriter(File.Create(FilePath), System.Text.Encoding.UTF32)
Using UTF32 did cause Excel to handle foreign characters correctly, although at the cost of automatically splitting the data into columns. 
The TextToColumns feature I added into a macro that automatically selected the first column, split the data into columns and autofit the columns to the contents.