Wierd Excel Behaviour

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. 

SQL Server 2000 Documentation Bug

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

Using Openrowset to query csv and tsv files

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’);

DBF Creation via OLE-DB

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

SQL Server performance problem

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.

An End to Collation Blues

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.

Performance of dynamic sql

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.