June 12, 2015 at 9:11 am
I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).
I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.
This is the sample code we are working with:
SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\etworkserver\Folder1\Folder2\MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X
I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?
But if I RDC into TEST1, I cannot run the query. I get this error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
But wait! It gets better. I can run the query as SA from TEST1.
And of course, the developer can't run it either.
And it works fine in the production server.
I'm thinking the basics are there but something isn't right in some permission somewhere. But I'm out of ideas where to look.
Anyone else fought through this and won?
Thanks!
Norman
June 12, 2015 at 2:11 pm
Why don't they just use SSIS ? Even the import/export wizard in SSMS might be a better choice. I suspect a registry permissions issue, but I could easily be way off base... Given SSIS, that's much too easy a solution to make diving into the alternative worthwhile.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 12, 2015 at 2:26 pm
n.heyen (6/12/2015)
I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.
This is the sample code we are working with:
SELECTX.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\etworkserver\Folder1\Folder2\MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X
I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?
But if I RDC into TEST1, I cannot run the query. I get this error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
But wait! It gets better. I can run the query as SA from TEST1.
And of course, the developer can't run it either.
And it works fine in the production server.
I'm thinking the basics are there but something isn't right in some permission somewhere. But I'm out of ideas where to look.
Anyone else fought through this and won?
Thanks!
Norman
You're "just" having classic privs problems. From BOL...
OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider.
The easiest thing to do is to make sure that the database is owned by "sa" and then write a stored procedure that uses "EXECUTE AS OWNER" in it.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2015 at 2:27 pm
sgmunson (6/12/2015)
Why don't they just use SSIS ? Even the import/export wizard in SSMS might be a better choice. I suspect a registry permissions issue, but I could easily be way off base... Given SSIS, that's much too easy a solution to make diving into the alternative worthwhile.
Heh... you don't mind if I seriously disagree with that, do ya? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 9:18 am
Thanks for the suggestion Jeff. I did find the db owner was changed to me after I refreshed the contents from a backup. (Have to remember to do this after restores to be sure.)
But there is no change in the behavior, I can run it from my desktop but no on the server itself. Sort of strange.
I haven't tested to see if creating an SP and running with "EXECUTE AS OWNER". I'm pretty sure that would work but isn't going to be accepted. Might even be a best practice but I've got little to no influence over how the development team does their jobs. So it comes down to me being able to make this work on test like it does in production.
And to the gentleman who suggested using SSIS or import/export, please see the above paragraph...
s there another place that I can look for differences?
Thanks,
Norman
June 15, 2015 at 9:26 am
n.heyen (6/15/2015)
Thanks for the suggestion Jeff. I did find the db owner was changed to me after I refreshed the contents from a backup. (Have to remember to do this after restores to be sure.)But there is no change in the behavior, I can run it from my desktop but no on the server itself. Sort of strange.
I haven't tested to see if creating an SP and running with "EXECUTE AS OWNER". I'm pretty sure that would work but isn't going to be accepted. Might even be a best practice but I've got little to no influence over how the development team does their jobs. So it comes down to me being able to make this work on test like it does in production.
And to the gentleman who suggested using SSIS or import/export, please see the above paragraph...
s there another place that I can look for differences?
Thanks,
Norman
It may be that the login that the SQL Server Service uses doesn't have the same privs as you.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 10:10 am
Good suggestion but I forgot to mention that was one of the first things I checked. Both are running under the same AD account.
Like I said, I'm really stumped and out of ideas. I really wish it didn't work in production, then I could respond with "use SSIS" and be done with it.
There is a difference between test and prod. Test is running Windows 2012R2 and prod is running Windows 2008 R2. Anything that changed at the OS level that would cause this?
Thanks,
Norman
June 15, 2015 at 1:38 pm
Jeff Moden (6/12/2015)
sgmunson (6/12/2015)
Why don't they just use SSIS ? Even the import/export wizard in SSMS might be a better choice. I suspect a registry permissions issue, but I could easily be way off base... Given SSIS, that's much too easy a solution to make diving into the alternative worthwhile.Heh... you don't mind if I seriously disagree with that, do ya? 😉
For you Jeff, never... Your knowledge consistently exceeds mine. Although... given what needs to be done, and the speed with which a reasonably skilled SSIS person could do what's needed, it just seems like it would be a LOT faster to just SSIS it. I was looking at that requirement and thinking "I'm done in less than 10 miinutes". In hindsight, I probably should have suggested a possible change in NTFS permissions on the share or the folder structure where the spreadsheet is located, and with the benefit of your post, I'd also look to see that the Service ID has access to that share and folder as well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 4:13 pm
sgmunson (6/15/2015)
Jeff Moden (6/12/2015)
sgmunson (6/12/2015)
Why don't they just use SSIS ? Even the import/export wizard in SSMS might be a better choice. I suspect a registry permissions issue, but I could easily be way off base... Given SSIS, that's much too easy a solution to make diving into the alternative worthwhile.Heh... you don't mind if I seriously disagree with that, do ya? 😉
For you Jeff, never... Your knowledge consistently exceeds mine. Although... given what needs to be done, and the speed with which a reasonably skilled SSIS person could do what's needed, it just seems like it would be a LOT faster to just SSIS it. I was looking at that requirement and thinking "I'm done in less than 10 miinutes". In hindsight, I probably should have suggested a possible change in NTFS permissions on the share or the folder structure where the spreadsheet is located, and with the benefit of your post, I'd also look to see that the Service ID has access to that share and folder as well.
I was thinking the "done in less than 10 minutes" thing for using the ACE drivers plus the time that I save not having to maintain an SSIS instance and it's privs or do backups there or... 😀
Shifting gears, ol' friend, I'm working on a "killer" article for how to import complex spreadsheets that change every day, week, or month. Nope... not just talking about the data changing either. I gave a talk about it at my local PASS chapter and you should have seen the jaws drop. It was a huge amount of fun in the "I didn't know you could do that with "just" T-SQL" area. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 6:00 pm
Jeff Moden (6/15/2015)
sgmunson (6/15/2015)
Jeff Moden (6/12/2015)
sgmunson (6/12/2015)
Why don't they just use SSIS ? Even the import/export wizard in SSMS might be a better choice. I suspect a registry permissions issue, but I could easily be way off base... Given SSIS, that's much too easy a solution to make diving into the alternative worthwhile.Heh... you don't mind if I seriously disagree with that, do ya? 😉
For you Jeff, never... Your knowledge consistently exceeds mine. Although... given what needs to be done, and the speed with which a reasonably skilled SSIS person could do what's needed, it just seems like it would be a LOT faster to just SSIS it. I was looking at that requirement and thinking "I'm done in less than 10 miinutes". In hindsight, I probably should have suggested a possible change in NTFS permissions on the share or the folder structure where the spreadsheet is located, and with the benefit of your post, I'd also look to see that the Service ID has access to that share and folder as well.
I was thinking the "done in less than 10 minutes" thing for using the ACE drivers plus the time that I save not having to maintain an SSIS instance and it's privs or do backups there or... 😀
Shifting gears, ol' friend, I'm working on a "killer" article for how to import complex spreadsheets that change every day, week, or month. Nope... not just talking about the data changing either. I gave a talk about it at my local PASS chapter and you should have seen the jaws drop. It was a huge amount of fun in the "I didn't know you could do that with "just" T-SQL" area. 🙂
I'll look forward to it, as this; for me anyway; definitely falls into the category of "I didn't know you could do that with just T-SQL". I just haven't had much chance to review the new features in SQL 2012 or 2014, much less even think about 2016. Now that I know you're a Michigander (result of another thread), I'll have to figure out when I could come all the way across the state to a Southfield PASS meeting, or find out if you ever head west towards Grand Rapids or the Lake Michigan lakeshore...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2015 at 8:25 pm
sgmunson (6/15/2015)
I'll look forward to it, as this; for me anyway; definitely falls into the category of "I didn't know you could do that with just T-SQL". I just haven't had much chance to review the new features in SQL 2012 or 2014, much less even think about 2016. Now that I know you're a Michigander (result of another thread), I'll have to figure out when I could come all the way across the state to a Southfield PASS meeting, or find out if you ever head west towards Grand Rapids or the Lake Michigan lakeshore...
The Kalamazoo User Group rolled snake-eyes recently but a new one started up in Lansing. Maybe we could meet there during one of their meetings. Of course, if you saw the need, perhaps I could do a freebee presentation at your company.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2018 at 9:51 am
I'm importing a CSV file and have same error... I have followed the steps stated on the thread like 'Uninstall the 32 bit version then install 64 bit version...'reconfigure distributed connection ...
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0x9ec DBC 0x1cb8158 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0x9ec DBC 0x1cb8158 Text'.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};', 'SELECT * FROM \\filepath\filename.csv');
I'm sysadmin on the server 🙂
August 14, 2018 at 10:23 am
proxybon 7787 - Tuesday, August 14, 2018 9:51 AMI'm importing a CSV file and have same error... I have followed the steps stated on the thread like 'Uninstall the 32 bit version then install 64 bit version...'reconfigure distributed connection ...
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0x9ec DBC 0x1cb8158 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5d8 Thread 0x9ec DBC 0x1cb8158 Text'.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};', 'SELECT * FROM \\filepath\filename.csv');I'm sysadmin on the server 🙂
If I run ssms as admin then everything works fine... so not sure where the problem is ( lost my hair...)
January 12, 2022 at 3:17 am
Hello,
I am stuck at the same place, if I run SSMS in admin mode, it works. If you found a solution , please share.
thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply