I am successfully creating a CSV file using the Jet 4.0 driver on my development machine but the process fails on the customer's machine.
First I create a schema.ini file in the target folder as follows:
Then, I run the following command against a SQL Server database:
INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0', 'Text;Database=C:\', [tmpExportFile#csv]) ( [col1], [col2] ) SELECT [mytable].[col1], [mytable].[col2] FROM [mytable]
On my development machine it executes correctly. However, on the customer's machine the following error occurs:
OLE DB provider 'Microsoft.Jet.OleDB.4.0' does not contain table 'tmpExportFile#csv'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OleDB.4.0', TableName='tmpExportFile#csv'].
Changing the output folder to "NewFolder" causes a different error
INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0', 'Text;Database=C:\NewFolder', [tmpExportFile#csv]) ( [col1], [col2] ) SELECT [mytable].[col1], [mytable].[col2] FROM [mytable]
db error: OLE DB provider 'Microsoft.Jet.OleDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OleDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
[OLE/DB provider returned message: 'C:\NewFolder\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
In both cases the the folders do exist and there is no existing tmpExportFile.csv file.
Any ideas ???