0

I have requirement of automating the process of running macros to fill sql tables. I am using a sql server job scheduling agent for this. Here is my macros batch file:

"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\stiwari\Documents\access_table.accdb" /x upload

These commands work when executed as a batch file from command prompt and the data moves to SQL db. They also work when executed in windows task scheduler. However this syntax doesn't work when using a cmd job task in SQL server. I have even enabled proxy credentials still it doesn't work. I get this error all the time:

The step didn't generate any output. Process Exit Code-1073741502. The step failed.

What can be the reason?

Sahil Tiwari
  • 159
  • 2
  • 12
  • Is MS Access installed on the server where you are running the job ? Does the accdb file also exist on the server? – Tim Mylott Oct 19 '18 at 17:28
  • No the db is in a different server while the access file is in my PC. Do I need to move access/file to the same server? – Sahil Tiwari Oct 19 '18 at 17:38
  • Yes, understand adding that command into a SQL Server Agent Job means those commands are executed on the server where that job is located. Which means whatever resources those commands need will need to be loaded there and or accessible. – Tim Mylott Oct 19 '18 at 17:48
  • 1
    Also, this looks to be using MS Access. Just moving or coping the MSACCESS.exe won't work. You'll probably need to install ms access on the server. – Tim Mylott Oct 19 '18 at 17:51
  • 1
    You need to log onto the server and install the Microsoft Access application. When that is completed, updated the first part of your command above in your SQL Agent Job to reflect the location of where MSACCESS.EXE was installed on the server. Next, move or copy the access_table.accdb file onto the server or to a network location accessible by the server and service account and update the second part of your command to that location. I don't know what /x upload is doing... running a macro? The account running the job on the server has to have permissions to do whatever that is doing. – Tim Mylott Oct 19 '18 at 18:21
  • This all seems a little backwards to me. I know what you're ultimately trying to accomplish https://stackoverflow.com/questions/52782237/i-am-getting-index-was-outside-the-bounds-of-the-array-while-migrating-data-fr and you have settled on attempting to push this data through access prior to SQL server. How did odbc drives work out that was suggested here on your other question https://stackoverflow.com/questions/52879836/how-can-i-use-ssis-to-copy-data-from-1000-tables-in-a-crm-to-access – Tim Mylott Oct 19 '18 at 18:28
  • I didn't use SSIS. I settled with a scheduler job because my main task was automation. Yes,so I had to build a macro that is running vb code for connecting with ODBC driver for SQL to move data from Access to SQL therefore you see /x upload where upload is my macro running that vb code. – Sahil Tiwari Oct 19 '18 at 18:36
  • I was able to get the CRM data in SQL through linked server objects but it wasn't able to retrieve the schema. When I wrote a SQL procedure to take in data from sage and map it to particular database, I encountered a lot of migration problems.I then tried moving data to access and then to SQL which worked successfully. – Sahil Tiwari Oct 19 '18 at 18:42
  • Ok, my prior comment should spell out what you need to do if trying to get this to run via SQL Agent Job, hopefully that ends up working out. – Tim Mylott Oct 19 '18 at 20:01
  • @TimMylott, can I specify a PATH variable in the Environment variables which would have the msaccess.exe filepath and then move/copy the msaccess.exe and access db where my SQL server lies? – Sahil Tiwari Oct 22 '18 at 15:00
  • That won't really gain you anything and that won't work as you're talking about copying the msaccess.exe onto the server. Attempting to run this command via a SQL Server Agent Job will require MS Access to be installed on the server where that job is executed from. No way around that. – Tim Mylott Oct 22 '18 at 15:16

0 Answers0