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).
Random outpourings of a software developer
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).
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.
This is an article on how to setup the DCOM settings that will allow the SQL Server debugger to work.
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.
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.