I’m running SQL2008R2. I have a stored procedure that uses OPENROWSET and everything is fine… because I am an administrator.

A user with all the usual and appropriate permissions would run this query (via an Access fronted) and receive the error: “Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.40’ has been denied…”

The SQL provider is setup properly and ad hoc queries are enabled etc. but adding a DWORD registery key with a 0 value called “DisallowAdhocAccess” to the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0 got rid of this error. NOTE: MSSQLSERVER is my instance name.

The user was then presented with the error: “Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server…”

To fix this error, the executing user apparently must have access to the temp folder of the account running the instance on the SQL server. Let’s say my service account is called MYDOMAIN\MySvcSQL. On the SQL server, I granted the executing user’s group write access to “C:\Users\MySvcSQL\AppData\Local\Temp”.

I’ve always liked Google Maps better than Bing Maps, but after loading some test data into our new CRM, I was not happy with Bing because it was not finding a surprisingly large number of addresses. I’m not sure if it was the addresses with apartment numbers i.e. 1/2 Example Street, or those addresses without a country listed, but ALL of our addresses and people are in Australia so we have no use for the Country field.

All that aside, I think Google Maps looks better, is more accurate and has a very useful feature in a CRM in street view.

I’m very new to CRM, but to use Google maps, simply create a HTML web resource with the code below and then insert that web resource onto your Account or Contact form. This code is adaptable in the obvious spots. Without an address it defaults to Australia with a zoom level of 4. It uses the address1_composite field and if you want to debug failed addresses, uncomment the alert. Enjoy. I did.

This is my new favourite way to call SQL procedure using VBA (in my case, from MS Access). You will note the use of the ReturnMsg parameter – I get my text for success, info or errors directly from the procedure. For an example, see the previous post on this blog.

I am posting this example for two reasons. Firstly, it is a great template that I have been using a lot of late so my blog is naturally a good place to store such things. But I also wanted to get this example public because of the time I spent myself looking for a way to catch errors when using the OPENROWSET linked server. As you will see, the answer is in dynamic SQL.