“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…”

by

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!

Microsoft Dynamics CRM 2013: Google Maps instead of Bing Maps

by

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

by

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)

by

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

by

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

by

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

Ultra-simple PHP Thumbnail Web Gallery

by

I used this once when I wanted to share a bunch of photos that I took on a group holiday. Years later, this is still my favourite simple option. All you need is a for this to work is a folder called ‘thumbs’ and a folder called ‘images’ (guess what goes where) – just make sure the file names are identical in each. Then simply make your index.php for the root folder out of the below code and you are done!

<?php
if ($handle = opendir('thumbs')) {
      $list = array();
    while (false !== ($file = readdir($handle))) {
            array_push($list, $file);
            sort($list);
    }
     closedir($handle);

    foreach ($list as $filename) {
            if ($filename == '.' || $filename == '..') {
                  //do nothing
            } else {
                  echo '<a href="images/'.$filename.'"><img src="thumbs/'.$filename.'" border=0></a>';
            }
      }
}
?>

Record MS Access field changes to in change log table

by

I just wrote this little gem which is dynamic enough to withstand all the fields that I am yet to add to my form. It records changes to textboxes and comboboxes. It even takes the displayed content of the comboboxes rather than the bound data so that is end-user friendly.

Steps are:

1. Create the table (mine is in SQL then linked in Access hence the difference in name).

CREATE TABLE [dbo].[tbl_Members_ChangeLog](
[ChangeLog_ID] [int] IDENTITY(1,1) NOT NULL,
[Store_ID] [int] NOT NULL,
[ChangedBy] [varchar](50) NULL,
[DateTime] [smalldatetime] NULL,
[Field] [varchar](50) NULL,
[OldValue] [varchar](500) NULL,
[NewValue] [varchar](500) NULL,
CONSTRAINT [PK_Members_ChangeLog] PRIMARY KEY CLUSTERED
(
[ChangeLog_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Members_ChangeLog] ADD CONSTRAINT [DF_Members_ChangeLog_DateTime] DEFAULT (getdate()) FOR [DateTime]
GO

2. Added the fOSUserName() function to your VBA module to get the windows user from here: http://access.mvps.org/access/api/api0008.htm

3. Add this code to catch the changes. You can call it from your form’s BeforeUpdate Sub:

Sub UpdateChangeLog()
Dim sSql As String
Dim rs As Recordset
Dim OldVal As String, NewVal As String

Debug.Print “—-”
‘cycle through text and combo boxes
For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If (ctl.Value <> ctl.OldValue) Or (Nz(ctl.OldValue, “”) = “” And ctl.Value <> “”) Then
If ctl.ControlType = acTextBox Then
OldVal = Nz(ctl.OldValue, “”)
NewVal = Nz(ctl.Value, “”)
ElseIf ctl.ControlType = acComboBox Then

‘get text for combobox lookup old value
If Nz(ctl.OldValue, “”) = “” Then
OldVal = “”
Else
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.OldValue
OldVal = rs.Fields(1)
rs.Close
End If

‘get text for combobox lookup new value
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.Value
NewVal = rs.Fields(1)
rs.Close

End If

Debug.Print ctl.Name
sSql = “INSERT INTO dbo_tbl_Members_ChangeLog (Store_ID, ChangedBy, Field, OldValue, NewValue) ” & _
“VALUES (” & Me.Store_ID & “, ‘” & fOSUserName() & “‘, ‘” & ctl.ControlSource & “‘, ‘” & OldVal & “‘, ‘” & NewVal & “‘);”

DoCmd.RunSQL sSql

End If
End If

Next ctl

Set rs = Nothing
End Sub

SSRS Query for Report Executions

by

I whipped up this query to put in a report to give me a list of the executions of our SQL Server Reporting Services reports and their run counts with the user and additional details being available on a drill down. It’s a simple query and very easy to adapt.

SELECT C.[Path], C.CreationDate, CU.UserName as CreatedBy, C.ModifiedDate, MU.Username as ModifiedBy,  E.Username as RunBy, E.TimeStart as DateTimeRun, DATEDIFF("ss", E.TimeStart, E.TimeEnd) as ExecutionTime, E.Parameters as RunParameters

FROM [Catalog] C 
 LEFT JOIN Users CU ON C.CreatedById = CU.UserID
 LEFT JOIN Users MU ON C.ModifiedById = MU.UserID
 LEFT JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE C.[Type] = 2 -- reports only
ORDER BY C.[Path], RunBy, E.TimeStart