Clean solution for testing for the existance of temp tabel in sql server:
http://www.thescripts.com/forum/thread81409.html
The solution is:
IF OBJECT_ID(‘tempdb..#tmptab’) IS NOT NULL
(just in case that link goes away).
Tag: SQL Server
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.
Debugging SQL Server Stored Procedures
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.
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.
Creating Deadlocks
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.
Is there any means of explicitly creating a deadlock.
This could make tsting db code much easier.
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.
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.
