OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses

Ever been in the situation where you’re so fixated on debugging an error that you fail to see the solution which has been staring you in the face the whole time?  Me too.

The other day I was trying to import data from an Excel spreadsheet into a SQL Server table using OPENROWSET (something I’ve done numerous times before) but I just couldn’t get it to work on our development server (I could get it to work on my development machine though).  My code was quite simple:

INSERT INTO Cruise.Pricing
SELECT CruiseCode, Rate, Fare
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
     'EXCEL 12.0;Database=d:\ozdata\PricingData.xlsx; 
     HDR=YES;IMEX=1','SELECT CruiseCode, Rate, Fare FROM [sheet1$]');

Yet SQL Server kept throwing the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". 
Msg 7303, Level 16, State 1, Line 1 
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Now I really do love developing with Microsoft products but what the heck does “Unspecified Error” mean?  Come on! Really!  How difficult can it be to provided decent error messages.  Anyway, I digress.

Like any good developer, I launched Google and searched for the above error which resulted in a number of hits; The first of which was the article Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Unspecified error” (dated 11 September 2011) which was indeed related to my search however the solution was to open SSMS with Administrator rights (Hint #1).  Just as I was about to close the article, I saw the following comment posted by Yves Forget at the bottom (Hint #2):

Were fixed a similar problem on Windows 2003 server by allowing read/write access to the users executing the query to the temp directory of the user running SQL Server (on Win 2003, it would look like c:\Documents and settings\SQLDBA\Temp). This is because SQL Server copies the file to that directory before opening it (probably to ensure exclusive access to the file).

Not been able to do the same on Windows Server 2008, however.

I found the comment interesting however didn’t pay much attention to it as we’re running Windows Server 2008 and the comment stated this fix worked for 2003 but not 2008.

Over the next hour or so I read countless articles, most of which suggested to run the following commands in SQL Server (which despite already being run on the server, were run again):

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
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

Some even suggested that the TEMP directory permissions (discussed in the 1st article I read) can cause problems on x86 machines however I dismissed these comments as we are running x64 (Hint #3).  Besides, it works on my 64bit development machine.  Fed up with playing this game, I went to lunch.

BTW, some of you are probably already thinking “Why didn’t he just use SQL Server’s Import and Export Wizard?”. Well, the wizard uses SSIS and SSIS is a 32bit application which means that the 64bit Microsoft Office 12.0 Access Database Engine OLE DB Provider (a.k.a. Microsoft ACE OLE DB 12.0 Provider) is not available in the wizard (refer to my previous post on Consuming Microsoft Access or Microsoft Excel Data Sources in 64-bit Environments).  That’s why I couldn’t use the wizard.

Anyway, during my break I convinced myself that the only plausible explanation as to why I was experiencing these problems was that the provider must have become corrupt and all I needed to do was reinstall it.  Well, I was wrong.  I still got the same unhelpful error message.

(Insert long break here)

I bet you’ve already guessed what the problem was.  Yep, it was a permissions problem. 

During a tidy-up of the accounts on the development server, I had changed the SQL Server service to use a local account instead of my domain account (Yes I know).  This meant that when I executed the OPENROWSET statement in SSMS, it was executing under the context of my domain account which did not have write access to the TEMP directory of the SQL Server service account (BTW, the instance of SQL Server on my development machine was still running under my domain account.  That’s why it didn’t fail there i.e. The service account and the account SSMS was launched under were the same therefore the required permissions were already in place). 

I understand why SQL Server needs to use a temporary directory however I don’t understand why there’s a requirement to set permissions on the SQL Server service accounts TEMP directory manually. Surely there’s a better way to handle this requirement/limitation (and surely there’s a better error message other than “Unspecified Error”!)

– Sometimes you have to stop and smell the roses (or get smacked in the face with a cricket bat!)

 

Advertisements

About Mr. Wharty's Ramblings
Jeff holds a Master’s Degree in Systems Development (.NET Stream) and a Master’s Degree in Database Design and Management (SQL Server). Jeff is also a MCT specialising in SQL Server technologies and holds MCPD, MCITP, MCDBA and MCSD certifications.

11 Responses to OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses

  1. Sathish DV says:

    Hi Wharty,

    Thanks for nice article with clear explanation.

    I too faced same problem because our Application App Pool runs under NetworkService Account and SQL Server runs under some domain account. So change the App pool to the same domain account and everything worked fine. But I can see this issue only related to SQL Server 2012. Before SQL 2012 upgrade we were using SQL 2008 in which it worked fine with different service account. Is there any changes related to this in SQL Server 2012?

    Thanks!

  2. Pingback: From openrowset | Lunchtrac

  3. Sean Gephardt says:

    Thanks for pointing this permissions issue out, as I’ve been banging my head against the desk all day, trying most of the solutions you as well found.

  4. Darrel says:

    Heya just wanted to give you a brief heads up and let you know a few
    of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different internet browsers and both show the same outcome.

    • Mr. Wharty's Ramblings says:

      Hi Darrel,

      Can you please let me know which ones as they all seem to be loading correctly in the browsers I’ve just used (IE, Chrome and Safari)

      Cheers
      Jeff

  5. Howdy! I hope you do not mind but I decided to submit your site:
    http://blog.wharton.com.au/2012/02/16/openrowset-t-sql-sometimes-you-have-to-stop-and-smell-the-roses/ to
    my internet directory website. I used, “OPENROWSET (T-SQL)” as your blog
    headline. I hope this is alright with you.
    In the event that you’d like me to change the title or perhaps remove it completely, e-mail me at edmundjarman@gawab.com. Thanks.

  6. Paweł Nowak says:

    hello

    Very nice article. I’m facing similiar problem on 32-bit win XP with MSSQL 2008. During research I was able to get to the same point with permissions. After switching SQL to LOCAL SYSTEM user everything started working. Unfortunatelly after SQL service restart i’m getting same error. After restarting SQL Server and SQL Browser sometimes ( random ) I’m able to run the OPENROWSET querry ( tested – working fine )

    P.S : Excel file is closed of course 🙂

  7. I’m not really understanding how you corrected this… I’m running the SQL Server 2008 R2 as its own domain account, then I’m running the query from a different computer using Windows authentication. Can you expend on how you’re changing the temp permissions? I’m missing something still…

  8. Pingback: sittingbouren web design

  9. Pingback: chiromagic.com

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: