OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Linked_Server_Name" returned message "Could not find installable ISAM.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Linked_Server_Name".

The knowledgebase at support.microsoft.com indicates this error can be the result of missing files or wrong filepaths in the Windows registry, etc. Check out suggested fixes here: http://support.microsoft.com/kb/283881/en-gb

However, I have also experienced that you will also get this error if your @provstr= for the Linked_Server_Name is set to "Excel 97 workbook" instead of "Excel 8.0"

For Example:

EXEC master.dbo.sp_addlinkedserver @server = N'Your_Linked_Server_Name', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\MortgagesDatabase\MortgageProduct.xls', @provstr=N'Excel 97 workbook'

instead of:

EXEC master.dbo.sp_addlinkedserver @server = N'Your_Linked_Server_Name', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\MortgagesDatabase\MortgageProduct.xls', @provstr=N'Excel 8.0'

Rounding decimal or floating point numbers in C#

Example from stackoverflow: http://stackoverflow.com/questions/257005/how-do-you-round-a-number-to-two-decimal-places-in-c


decimal a = 1.994444M;

Math.Round(a, 2); //returns 1.99

decimal b = 1.995555M;

Math.Round(b, 2); //returns 2.00


Math.Round also has an overload for rounding-to-even:

Math.Round(a, 2, MidPointRounding.ToEven);

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Depending on your OS you could run odbcad32.exe on your SQL Server to bring up the Data Sources screen where you should be able to confirm what ODBC providers the server has installed (or not installed), as the case may be.


If you're using 64bit, then this error most likely results from the fact 64-bit SQL Server cannot use Jet, as there is no 64-bit OLEDB provider for Jet.


I came across a possible word-around, which could be to execute the following script in MSSMS, and then restart the MSSQLSERVER service:

EXEC

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

;

GO

EXEC

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

;

GO

Additional help can be found on this .Net forum: http://www.sqlservercentral.com/forums/Topic445277-149-1.aspx

Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet

You may get the following errors when you run a query on a Linked Server that is configured to use OLEDB 4.0 provider for Microsoft Jet:

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
OR
"Error 7399: OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed The Microsoft jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data."


These problems probably occur because the login account does not have access to the temporary folder of the SQL Server startup account, because the linked server query runs in the context of the login account. If you run a linked server query, SQL Server tries to create a temporary file data source name (DSN) in the temporary folder of the SQL Server startup account.

To work around this problem, you could try the following:

1. Log on to the computer by using the SQL Server start up account.
2. Create a folder named Temp in the operating system installation directory.
3. Permit full access to a non-administrator account on the Temp folder.
4. Set the value of the TEMP and TMP user variables of the SQL Server startup account to the newly created Temp folder. To do so, follow these steps:
a. Right-click My Computer, and then click Properties.
b. Click the Advanced tab, and then click Environmental Variables.
c. In the User variables for Logon User list, click TEMP, and then click Edit.
d. In theVariable Value box, type C:\Temp as the location of the new Temp folder, and then click OK.
e. Repeat steps c and d to set the value of the TMP variable.
f. Click OK two times.

5. Log off, and then log on to the computer by using SQL Server startup account.
6. Restart the SQL Server services.


However, I've also experienced that you may get these errors if the datasource (i.e. the file path supplied in the @datasrc parameter of the sp_addlinkedserver query command) does not exist, or is missing. For example, if you forget to copy the file into the folder where the data source parameter of your linked server points to.


http://support.microsoft.com/kb/814398

Removing Duplicate Rows from an Excel Spreadsheet

Microsoft has made this dead easy in MS Office 2010.
1. Open your spreadsheet to the worsksheet with duplicate rows
2. Switch to the 'Data' menu Ribbon
3. Click on 'Remove Duplicates'
4. This will open up a dialog where you can select the rows/columns with duplicates you want to remove.

Date additions (T-SQL)

Adding number of x to a date, where x can be: year=yy, month=m, dayofyear=y, day=d, week=wk, hour=hh, minute=mi, second=ss.

For example adding 260 days to New Year's day 2011:

print dateadd(d, 260, '2011-01-01')

 This should return Sep 18 2011

Calculate days since begninning of the Year (T-SQL)

print datediff(d, '2011-01-01', getdate())


Or get the difference between any two dates; format is datediff(d, ealier date, later date)
e.g.


print datediff(d, '1969-07-20', '2011-09-19')