Batch file that runs sql script getting error Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.'

Question

'You must access this provider through a linked server.'

I'm running this batch file remotely from the database server which is also remote. I'm trying to do an export which shouldn't have a need for the linked server. When I run the query in SSMS it exports fine, but when it's executed from the batch, it gives me the error message below. I think it may be because theyre on 2 different accounts.

Here's my batch code:

SQLCMD -S dbserver -E -d dbname-i \\Network\fldr\rpt\test\script.sql
PAUSE

Here is my SQL script:

SET ANSI_NULLS ON 
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT 'exporting data...'

INSERT INTO OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;
Database=\\Network\fldr\rpt\test\testdata.xls;', 
'SELECT * FROM [Sheet1$]') SELECT * FROM [dbo].view_test ORDER BY [Col4]

Error message in cmd:

Answer 1

I solved the problem. When running the batch file form my work PC, it hits SQL Server using different account -- my Active Directory account credentials, an account which didn't exist in SSMS. I created the account and it worked.

Other steps I took prior to this (that were also needed) are the following:

1) Ran this code

USE [master]
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

2) Edit the registry and add the DWORD "disallowAdHocProcess" with value of "0" at this location: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLADV\Providers\Microsoft.ACE.OLEDB.12.0] "disallowAdHocProcess"=dword:00000000

3) This article: http://support.microsoft.com/kb/814398 and used ProcMon program to find the temp path I was being denied access to.