Create Something Everyday

A Husband, a Dad, a Data Warehouse Architect… Making it all work together.

In order to resolve this issue use the following steps:

Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied. You must access this provider through a linked server.

  • Run the Following T-SQL

    USE [master]

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

    GO

     

  • Then  drill down into the registry and make the following change:

     

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Providers\Microsoft.ACE.OLEDB.12.0

     

  • Switch disallowadhocAccess value to 0


     

  • Rerun the query with the user that was initially denied access and it works!!

    select *

    from openrowset(‘Microsoft.ACE.OLEDB.12.0’,

    ‘Excel 8.0;Database=\\test\all\spreadsheets\YouWant.xlsx’,

In the following example I’ve created an SSIS that audits the transfer of data each step of the way. The main transformations used for this were:

  • Row Count
    • The row count was used to validate the number of records
  • Conditional Split
    • Was used to split out rows with a specific Header value. In this case “H”
  • Aggregate
    • Was used to sum the specific values

 
 


 
 


 
 

Here are the results. I used data viewers to confirm the specific rows:


 
 

 Here are the results of the aggregate:


As a DBA who service numerous customers and clients. An issue that crops up relatively often is the need for more disk space. As an organization you do have options:

  1. Trim your data
  2. Buy new disks
  3. Truncate your data (Yeah right!!)

For most organization these are truly not options at all. With the release of SQL Server 2008 one feature that can immediately address this concern with minimal side effects is backup compression. Here are some stats collected against the adventureworks database:

 
 

The compressed backup took 4 seconds less and was 75% smaller.

 
 


Uncompressed

Processed 23016 pages for database ‘AdventureWorks2008’, file ‘AdventureWorks2008_Data’ on file 1.

Processed 36 pages for database ‘AdventureWorks2008’, file ‘FileStreamDocuments’ on file 1.

Processed 1 pages for database ‘AdventureWorks2008’, file ‘AdventureWorks2008_Log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 23053 pages in 10.842 seconds (16.610 MB/sec).

 
 

Compressed

Processed 23016 pages for database ‘AdventureWorks2008’, file ‘AdventureWorks2008_Data’ on file 1.

Processed 36 pages for database ‘AdventureWorks2008’, file ‘FileStreamDocuments’ on file 1.

Processed 2 pages for database ‘AdventureWorks2008’, file ‘AdventureWorks2008_Log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 23054 pages in 5.975 seconds (30.142 MB/sec).

 
 

 
 

Now think about the potential gain once this technology is applied to vldb (very large databases). There is an extreme amount of potential savings in disk space just by utilizing disk compression. With the release of SQL Server 2008 R2 not only are these features available in the Enterprise Edition but these features are also available in Standard Edition.