OPENDATASOURCE or OPENROWSET linked server Error fix

This drove me mental for a while.

If you are trying to open an external file such as a TXT, XLS, CSV using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

The Errors:

OLE Database provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE Database provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


The fix:

I tried all the suggestions about SP2, restarting services, setting flags in the SQL settings etc but all to no avail.

What you need to do is visit the link below and download the

2007 Office System Driver: Data Connectivity Components download

Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

SERVER\Server Objects\Linked Servers\Providers

The new driver is called:

Microsoft.ACE.OLEDB.12.0

All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

Your new connection string should look something like this:

OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
''Data Source=C:\data\;
Extended Properties="Text;HDR=No;FMT=Delimited"'')...['+ @FileN +'#CSV]'

Voila! That should sort you out.

Converting HTML to aspx tip

I need to convert some html to aspx. On the page were loads of images and as i moving stuff into master pages it was going to totally scre the directories up.

As you may know with a server control you can use the sqiggle/tilda (~) in a path so the server resolves the actual path for you when it generated the page.

What you can do to save a lot of time is rather than change all the image controls, for example, is just add a runat="server" to the html control and then do a search and replace on the image url to add the ~. This allows the existing alt text etc to be used without having to convert to a server control - which has different naming conventions for the attributes.

Find out what stored procs have changed

Been ages since ive posted on here as ive been in a cave working on a big project! Ive finally come out and found time to post something useful i found.

I wanted to know what had changed in a database in the last x days to check for things before i did a backup/restore so found the following queries useful!

This fella will tell you the stored procedures that have been modified in the last x days:

SELECT NAME ProcName, create_date Created, modify_date Modified
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 3 --Change 7 to any other day value
ORDER BY 3 DESC

This fella will tell you the stored procedures that have been created in the last x days:

SELECT NAME ProcName, create_date Created, modify_date Modified
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 3
ORDER BY 2 DESC

Very handy!

Fantastic List of Top Tips for Reporting Services

As ive begun digging deep into Reporting Services for a new project ive been hunting around for good resources. In doing so came across this list of extremely well put together tips, tricks and best practices.

Some great things in here that i will be definitely putting to good use.

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx