Yet another "Jet drivers on 64 bit" question........
Had a scenario where a query using OPENROWSET wouldn't work on the newly built 64-bit server. And of course, a lot of the answers here (thanks to @Pavel) pointed me in the right direction
And despite downloading and installing Microsoft Access Database Engine 2010 Redistributable, I still had an error.
EDIT : I should add this is the error I was getting
This post, and subsequently this post got me going in the right direction - essentially the account running the query needs permissions to the folder that is the temp directory of the account running the SQL Server service.
I was running this on my local instance, and normally the directory for a normal user is C:\Users\user.name\AppData\Local\Temp, but my SQL instance had been configured to run as Network Service - where is this account's temp directory? I managed to workaround the issue by changing the service account to a domain user.
This then got me thinking, on the live server, SQL is running under LocalSystem - again where would I find this account's temp directory.
So finally, the question.... where is the 'user' temp directory for
and does it differ across Windows Server 2008, Server 2008R2, Windows 7, etc....
I believe the path would be under: C:\Windows\system32\config\systemprofile\AppData\Local\Temp
I checked an Windows XP and Windows 7 and that is the directory. I don't believe it has changed but have not confirmed it.
EDIT Test done so far on Windows 7 machine. Setup PowerShell script to execute 2 commands...
According to [this Stackoverflow post] the location does differ from pre Win7/2008 and post Win7/2008.
Windows XP (and I would think Windows 2003) has an entry under "Documents and Settings" Windows 7/2008/2008R2 uses C:\AppData or C:\Windows\Temp