MS SQL SERVER READ EXCEL FILE (EXCEL 2010, 2013) - OPENROWSET (x64) - (T-SQL)

There are some challenges that you might experience reading excel files running SQL server on x64 machines. Usually, everything works fine on 32-bit systems, but apparently there are some issues on 64-bit system though.
Here is the most often error appearing and definitely it doesn't tell us to much about the real cause of the problem:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. 

SYSTEM REQUIREMENTS:

The following solutions could be helpful in the most cases, so follow all of these steps and requirements as well.
Requirements for 64-bit system are:

1. Remove all x32 Office and Microsoft Access Database Engine x32 from your machine (sometimes both of these should be uninstalled to get )
2. Install Microsoft access database engine 2010 x64: http://www.microsoft.com/en-ca/download/details.aspx?id=13255
3. Optionally, install x64 Office if you previously uninstalled it in step 1

ENABLE AD HOC QUERIES AND OLEDB PROPERTIES

/*AD HOC QUERIES*/
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Office 2010: 
/*OLEDB PROPERTIES*/
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

Office 2013: 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1



OPENROWSET QUERIES

Office 2010: 
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\temp\test.xlsx;HDR=NO;IMEX=1', 'SELECT * FROM [Sheet1$]')

Office 2013: 
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 'Excel 12.0;Database=c:\temp\test.xlsx;;HDR=NO;IMEX=1', [Sheet1$])

ADITIONAL TROUBLESHOOTING
It's not recommending, try running SQL Server Management Studio as Administrator (Right Click and Run As Administrator)


Mike C.    

MS SQL Server 2012 - Certification Path

Microsoft certification has been here for years and it's always a good opportunity for everyone who works with MS SQL or even with related technologies. It's not all about certification but definitely it brings a few different benefits:

• Education, skill improvement and better competency using SQL and related technologies 
• Job searching advantage - it can be requirement for DB, development (DB, desktop, web), reporting and similar positions. Even if that's not one of the requirements for certain positions certificated professionals usually find jobs easier, they have advantage by applying for better positions and apparently they are qualified for better salary ranges.
• Company benefits - company that Microsoft Certificated Professionals (MCP) works for can redeem some good benefits through Microsoft Partnership Network.
• MCPs have access to many of Microsoft resources and communities available online

All these should encourage you as a MS SQL specialist to start your certification, upgrade your current skills and maintain continuous learning.

CERTIFICATION OVERVIEW

Here is a breakthrough of MS SQL 2012 certification starting from lower levels which will be requirements for following higher levels:

1. Microsoft Technology Associate (MTA) - entry academic level recommended to students, entry level professionals with no experience or very basic knowledge of SQL technology and databases
2. Microsoft Certified Solutions Associate (MCSA) - for those who wants to be qualified for positions: Database developer or Database analyst
3. Microsoft Certified Solutions Expert (MCSE) - 2 tracks are available: Data Platform and Business Intelligence. This is expert level designed for developers, database analysts and database architects
4. Microsoft Certified Solutions Master (MCSM) - high level Masters and Architects

CERTIFICATION RESOURCES

Here are some useful links where you can explore certification paths and learning resources:

• Microsoft Learning - SQL 2012 Certification
• Microsoft Certification Roadmap
• Interactive Certification Roadmap
• Microsoft Virtual Academy
• Microsoft Books and Training Kits
• Classrooms, E-Learning materials, Practice Tests, Microsoft Online Resources

EXAM SCHEDULING
This should be pretty straight forward task where you can schedule your exam online by few clicks selecting the nearest examination center in your area (there are so many of them). Currently (Dec 2013), the good thing is if you failed the exam there is a free retake promotion which can save you some money and this is really appreciate initiative introduced by Microsoft.


SQL SERVER 2012 EXAMS

To earn specific certificates you will need pass one or more exams which is showed below.

Microsoft Technology Associate (MTA)

• Database Fundamentals (70-364) 

MCSA: SQL Server

• Querying Microsoft SQL Server 2012 (70-461)
• Administering Microsoft SQL Server 2012 Databases (70-462)
• Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463)

MCSE: Data Platform

• MCSA: SQL Server 2012 - requirements for this level of certification (see above)
• Developing Microsoft SQL Server 2012 Databases (70-464)
• Designing Database Solutions for SQL Server 2012 (70-465)

MCSE: Business Intelligence

• MCSA: SQL Server 2012 - requirements for this level of certification (see above)
• Implementing Data Models and Reports with Microsoft SQL Server 2012 (70-466)
• Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467)

MCSM: DATA PLATFORM

• MCSM: Data Platform Knowledge exam (88-986)
• MCSM: Data Platform Lab exam (88-987)

Explore the variety of resources, other exams and of course have a fun.


Mike C.

GET THE LIST OF ALL DATABASES/DATABASE SIZE (T-SQL)

There are so many situations when you need to know what is actually happening on your SQL server instances, especially which databases your are hosting at your servers.

It sounds like an easy task for everyone who is familiar with SQL Management Studio: Expand your server instance in Object explorer --> Expand "Databases folder". This is what I can see on my test instance:

Sometimes, that's not enough because you are interested not just in database list, but in more advanced properties. There is a GUI for that as well if you click on individual database and selecting "Properties" you will get enough for your admin purposes. Here is another approach which will give you all databases with extended properties in just one query using system catalog views:

SELECT * FROM sys.databases

For better understanding of system catalog views check for online help:
http://technet.microsoft.com/en-us/library/ms190334.aspx


This looks like raw data report about individual data properties, but be free to select those which you are the mostly interested in and that's what exactly I did here adding few of them together with databases sizes. Database size is not located in sys.databases catalog and for this purpose we will need to join it with sys.master_files view. Firstly, you can check which columns are included in these 2 views and here are simple queries:

SELECT * FROM sys.databases
GO

SELECT * FROM sys.master_files

The following queries can be used as a good DBA tool for getting info about database statuses and data files:

/*CTE function*/
WITH DbSize
AS
  (
   SELECT
     database_id,
     [type],
     Size = size * 8.0 / 1024
FROM sys.master_files
)

/*Get database list together with database size ordered by db size descending*/
SELECT
    database_id,
    name,
    create_date,
    [compatibility_level],
    collation_name,
    state_desc,
    [recovery_model_desc],
    DataFileSizeInMB = (SELECT SUM(size) FROM DbSize WHERE TYPE = 0 and DbSize.database_id = db.database_id),
    LogFileSizeInMB = (SELECT SUM(size) FROM DbSize WHERE TYPE = 1 and DbSize.database_id = db.database_id)
FROM sys.databases db
ORDER BY DataFileSizeInMB DESC

My recommendation for using the script from above is maybe to create SQL Services online report, so you as a DBA can always go there and see what's happening with your databases. Also, these queries can be adjusted with other DB properties.


Mike C.