Developer Rants

The following is the explanation of a very odd excel behaviour: 

http://support.microsoft.com/kb/194124/EN-US/

The problem comes when using sql server stored procedures to read data in from an Excel spreadsheet.

The symptom is that Excel will guess the datatype of the columns. If it guesses wrong you get null instead of the data.

I have a general rule of thumb about Excel: if you let it guess it will almost always guess wrong.

Why could microsoft not have added extra connection parameters that allow the caller to specify the data types?

This would not break existing code and would save a lot of heartache.

I have found a workaround:

Export the sheet to tsv and then reimport.

This looses all of the formatting that is getting in the way. 

I have found out this is how sql servers internal functions work.

This is one of very few places that I have found books online to be wrong.

Books online can be infurating in that even a search for the exact term required will return 50 articles above the one you actually need! 

SQL-92 niladic function Value returned 
CURRENT_TIMESTAMP       Current date and time. 
CURRENT_USER    Name of user performing insert.
SESSION_USER    Name of user performing insert.
SYSTEM_USER     Name of user performing insert.
USER    Name of user performing insert.
Books online is wrong here

The following will read a csv file. 

SELECT * FROM OPENROWSET (’MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\data;’, ‘SELECT * from test.csv’);

The following will read a tsv file (with a txt extension).

SELECT * FROM OPENROWSET (’MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\data;’, ‘SELECT * from test.txt’);

This is a link to an article on DB Base file creation via OLE-DB.

The following is the important part:

OleDbConnection conn = new OleDbConnection(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:tempdbase;Extended Properties=DBASE IV;”);

OleDbCommand cmd = new OleDbCommand(“CREATE TABLE aDBaseFile (col1 Integer,col2 Double)”, conn);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

The following is the equivalent in VBA

Sub foo()
‘ Add a reference to c:program filescommon filessystemadomsado15.dll
Dim c As New Connection
c.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:tempdbase;Extended Properties=DBASE IV;”
c.Open
c.Execute (“CREATE TABLE DBF3 (col1 Integer,col2 Double)”)
c.Close
End Sub

This is a link to a SQL Server sp4 problem and a suggested soltion.

SQL Server sp3 had a bug in which indexes with non-integer numeric values could miss values in a select - especially when comparing differing precision.

The solution that they implemented can result in table scans - causing a huge performance penalty in areas that were unaffected by the problem. I known that missing data is bad - but forcing table scans is a rather severe penalty.

Debugging a stored procedure from Query Analyser can be very useful.
That is if you can get it to work.
It is sensitive to DCOM permission settings.

This is an article on how to setup the DCOM settings that will allow the SQL Server debugger to work.

I know this is a wierd thing to do given that SQL server is designed to prevent deadlocks.
Is there any means of explicitly creating a deadlock.
This could make tsting db code much easier.

Collation in SQL Server 2000 is oftern a minefield.
The collation must exactly match on a join or the query will fail if both fields have a defined
collation.

There are lots of absolutely identical collations (for example the default UK and US settings are identical yet incompatible).

There is no easy way of correcting the databases collation other than using DTS to completely copy the database.

The following is the solution to all collation blues:

COLLATE database_default
This needs to be after the appropriate join :

    select a
    from foo
    where bar1 = bar2 COLLATE database_default

This eliminates the problem.



You would expect that dynamic sql, that is a string constructed in a stored procedure and then executed would be slower than the equivalent as a stored procedure. However I was surprised to find that an application become 7 times faster by removing this.

The dynamic sql allowed the database to be accessed to be specific at call time. However at any given time only one other database was being used. By replacing the appropriate stored procedures at change over time we obtain a substantial performance benefit.

I got caught by another localizaion feature of SQL Server today.
SQL Server is not aware of regional settings.

This can be a real pain. If your machine uses say German regional settings and your database returns 1.01  if you use variant conversion routines (hey I am still using ado - we all can’t be on the bleeding edge) this can arrive as 101

This is not what is wanted. This has brought me to question where in a data access layer should the localization go?