“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

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

SSRS Query for Report Executions

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

Get values from an SQL Stored Procedure into Excel (VBA macro)

I’m using the below function in an Excel macro to get the results of a simple stored procedure into an Excel spreadsheet. The function needs the connection string defined within it, but this can easily setup to be dynamic in the function parameters if needed. Also I’m only using one parameter, but I have no doubt that you create a multi-value function parameter to take several stored proc parameters.

In your macro, simply call the function and it will populate cells with your stored proc results beginning at the current active cell.

Function insertStoredProcValues(spName As String, strParameter As String)

    Dim Conn As ADODB.Connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim sConnect As String

    '--DEFINE CONNECTION STRING HERE---------------------------------------------------------
    sConnect = "driver={sql server}; server=MYSERVERNAME; Database=MYDATABASENAME; UID=MYUSERNAME; PWD=MYPASSWORD;"
    '----------------------------------------------------------------------------------------

    'Establish connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = sConnect
    Conn.Open

    'Open recordset
    Set ADODBCmd = New ADODB.Command
    ADODBCmd.ActiveConnection = Conn
    ADODBCmd.CommandText = spName
    ADODBCmd.CommandType = adCmdStoredProc
    ADODBCmd.Parameters.Refresh
    ADODBCmd.Parameters(1).Value = strParameter
    Set rs = ADODBCmd.Execute()

    'Loop through recordset and place values
    rs.MoveFirst
    Do While rs.EOF = False
        For i = 0 To rs.Fields.Count - 1
            ActiveCell.Value = rs.Fields(i).Value   'insert value into cell
            ActiveCell.Offset(0, 1).Activate        'move to next cell for next value
        Next i
        ActiveCell.Offset(1, -i).Activate           'move to next row for next record
    rs.MoveNext
    Loop

    'Clean up
    rs.Close
    Set rs = Nothing
End Function