Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Everything SQL
Browse by Tags · View All
SQL Server 27
#SQLServer 19
SQL Server 2012 12
BRH 10
Certification 9
Training 6
TSQL 6
SQL Azure 4
#BI 4
denali 3

Archive · View All
July 2011 11
January 2012 7
February 2012 5
March 2012 4
May 2012 3
October 2011 3
August 2011 3
November 2012 2
April 2012 2
November 2011 2

Jeff Wharton's Blog

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

Feb 16 2012 10:43PM by Jeff Wharton   

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!)

Tags: SQL Server, Stupidity,


Jeff Wharton
53 · 4% · 1171
4
 
 
 
0
Learned
 
0
Incorrect



Submit

6  Comments  

  • Thank-you, Jeff! I generally have a practice to run all SQL Server instances under the same domain account. However, whenever I take up responsibility of a new server, I almost always forget to check for similar permissions across the board and end up with non-functioning OPENROWSET queries! Thank-you for taking the time out to document this behaviour and share it. This post is now in my favourites :)

    commented on Feb 17 2012 11:12AM
    Nakul Vachhrajani
    4 · 36% · 11624
  • I prefer to script out setting up new servers/instances. Each service runs under its own AD account. It's also nice to be able to deploy an instance of SQL with a click and know EXACTLY how it's set up. If I have to do ad-hoc imports from Excel, I find that it's actually easier to use Access or SSIS as a bridge than to use OPENROWSET, but your mileage may vary.

    commented on Feb 22 2012 3:19PM
    Marc Jellinek
    95 · 2% · 586
  • Jeff, thanks for the blog. I am having a similar issue.

    What is the path of the TEMP directory? C:\Users\sqladmin\AppData\Local\Temp (where sqladmin is the domain account under which sql server runs). Unfortunately for me the this TEMP directory has full permissions for sqladmin, but the Stored Proc with the OpenRowSet does not work.

    The Excel file actually resides on the a shared drive. When the Excel file is stored on the local drive (of the development machine) the stored proc runs fine, but when I try to access the Excel file from the share ... nothing. Further, when I use a SQL login with admin privileges it works fine, but when using Windows Authenticated domain logins (even Administrative logins), its a no go. Very strange.

    Any comments would be helpful.

    Mark

    commented on Mar 19 2012 9:16AM
    Mark973
    2834 · 0% · 3
  • It sounds like the sqladmin account doesn't have the appropriate permissions on the share drive. Also try and use a UNC for the share

    commented on Mar 19 2012 4:28PM
    Jeff Wharton
    53 · 4% · 1171
  • Jeff thanks for the comments. The sqladmin acount has permission to read the excel file on the shared drive. The stored proc references the file using a UNC.

    Let me elaborate a moment. The stored proc will run under the the sqladmin account from ssms and from scheduled jobs in SQL Server Agent (hence the permissions are correct). The problem that I am having is when I try to run the stored proc from a C# program using an authenticated login in the connection string. It won't work for any domain user with administrator privileges. It WILL work for a SQL login with adminstrative priviledges. It's strange. (Really what I'm going for is for a domain user with non-administative privileges to run the thing ... but one step at a time.)

    At this point I'm pretty sure this issue is beyond the scope of your blog, but just in case you have seen this before ...

    Thanks again for the yoru comments. And keep up the good work.

    commented on Mar 20 2012 8:52AM
    Mark973
    2834 · 0% · 3
  • "using an authenticated login in the connection string"

    For Windows Autentication (Integrated Security=True), you cannot specify a UserID and Password in a connection string (they are ignored if you do supply them). Only SQL Server Authentication allows you to specify one.

    If you are using Windows Authentication (Integrated Security=True), your C# code will execute under the context of the currently logged on user therefore that user needs to be granted access to C:\Users\sqladmin\AppData\Local\Temp

    commented on Mar 20 2012 4:57PM
    Jeff Wharton
    53 · 4% · 1171

Your Comment


Sign Up or Login to post a comment.

"OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses" rated 5 out of 5 by 4 readers
OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]