“Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.40’ has been denied” and/or “Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server…”

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”.

And that solved hours of heartache!

Advertisements

Microsoft Dynamics CRM 2013: Google Maps instead of Bing Maps

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.

 


<html>
<head>
<script src="https://maps.googleapis.com/maps/api/js?sensor=false"></script>
<script>
function initialize() {
var map_canvas = document.getElementById('map_canvas');
var map_options = {
center: new google.maps.LatLng(-26.4420246,133.281323),
zoom: 4,
mapTypeId: google.maps.MapTypeId.ROADMAP
}

var map = new google.maps.Map(map_canvas, map_options)
var geocoder = new google.maps.Geocoder();
var address = window.parent.Xrm.Page.data.entity.attributes.get('address1_composite').getValue();
geocoder.geocode( { 'address': address}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK) {
map.setCenter(results[0].geometry.location);
map.setZoom(14);
var marker = new google.maps.Marker({
map: map,
position: results[0].geometry.location
});
} else {
//alert("Geocode was not successful for the following reason: " + status);
}
});
}
google.maps.event.addDomListener(window, 'load', initialize);
</script>

</head>
<body>
<div style="width: 100%; height: 100%;" id="map_canvas"></div>
</body>
</html>

Excel – Get Financial Quarter from Date

Just posting this here because I use this all the time. It is simple, self-explanatory and it works. It takes a cell’s (B2) date and displays it as YYYY – Qx

=IF(MONTH(B2)=7, YEAR(B2) & " - Q" & 1
,IF(MONTH(B2)=8, YEAR(B2) & " - Q" & 1
,IF(MONTH(B2)=9, YEAR(B2) & " - Q" & 1
,IF(MONTH(B2)=10, YEAR(B2) & " - Q" & 2
,IF(MONTH(B2)=11, YEAR(B2) & " - Q" & 2
,IF(MONTH(B2)=12, YEAR(B2) & " - Q" & 2
,IF(MONTH(B2)=1, YEAR(B2) & " - Q" & 3
,IF(MONTH(B2)=2, YEAR(B2) & " - Q" & 3
,IF(MONTH(B2)=3, YEAR(B2) & " - Q" & 3
,IF(MONTH(B2)=4, YEAR(B2) & " - Q" & 4
,IF(MONTH(B2)=5, YEAR(B2) & " - Q" & 4
,IF(MONTH(B2)=6, YEAR(B2) & " - Q" & 4))))))))))))

Run SQL proc from MS Access (my favourite way)

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.


Screen.MousePointer = 11
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDBName;Data Source=MyServerName"

cn.Open
cmd.ActiveConnection = cn
cmd.CommandText = "MySchema.MyProcName"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@queryDateParam", adDate, adParamInput, , cboQueryDate.Value) 'example input parameter
cmd.Parameters.Append cmd.CreateParameter("@ReturnMsg", adVarChar, adParamOutput, 500) 'example output parameter (see previous post for use example)
cmd.CommandTimeout = 0
cmd.Execute
MsgBox cmd.Parameters("@ReturnMsg").Value
cn.Close
Screen.MousePointer = 0

SQL procedure example for OPENROWSET with TRY CATCH and TRAN

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.


create proc proc_MyProcName (@ReturnMsg varchar(max) output) as begin

     --declare @ReturnMsg varchar(max) --for testing

     declare @dSQL nvarchar(max) -- use dynamic SQL to catch possible errors with linked CSV file
     set @dSQL = '
                  insert into tbl_MyTable
                  select *
                  from OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=D:\DBServerFolder\;HDR=Yes;FORMAT=Delimited(,)'', ''SELECT * FROM [MyImportFile.csv]'')
                 '
               -- for excel, use
               -- from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source="D:\DBServerFolder\TheFile.xlsx"; Extended properties=Excel 12.0'' )...Sheet1$
     begin try
          begin tran
          -- my other statements
          -- ... ... ...
          exec sp_executesql @dSQL
     end try

     begin catch
          if @@TRANCOUNT > 0 begin
               rollback tran
               set @ReturnMsg = 'An error ocurred and the transaction was rolled back.' + char(13) + char(13) + 'The SQL error msg is: ' + ERROR_MESSAGE()
               return -1
               --print @ReturnMsg --for testing
          end
     end catch

     if @@TRANCOUNT > 0  begin
          commit tran
          set @ReturnMsg = 'My success message.'
          return 0
          --print @ReturnMsg --for testing
     end
end

MSSQL: Find tables that need indexing then generate rebuild/reorganise script

I’ve slightly modified this, but I can’t remember exactly where it came from so I cannot take all the credit.

This script run against the specified database, will identify tables that are too fragmented and generate scripts to that you can copy and past to fix. Try it out!


use DatabaseNameGoesHere

SELECT

t.name,

ps.avg_fragmentation_in_percent,

'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +

CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +

CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END as IndexScript

FROM

sys.indexes AS ix

INNER JOIN sys.tables t ON t.object_id = ix.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id

INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count

FROM sys.partitions

GROUP BY object_id, index_id) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id

WHERE

ps.avg_fragmentation_in_percent > 10

AND ix.name IS NOT NULL

ORDER BY avg_fragmentation_in_percent DESC