What are the challenges you face when working across database platforms? Take the survey

Generate data using SQL Agent job

Hi all
I currently have a .sqlgen project which I would like to run automatically via a SQL Agent job.
I can get a Powershell script to run correctly populating the data from a dev database to a local database in Pwershell itself.
However, once I try to run it in a SQL job, I keep getting "Access to the path is denied" error in the Agent logs.

I am using a CmdExec type job with the following example command:
Powershell.exe -ExecutionPolicy Bypass -file "D:\Users\robinw\HA.ps1"

Also using a proxy that uses my own credentials to access the resources available.
When running the job, I can access the contents of the folder which contains the programs and files needed.

It seems to be a problem revolving firing the SQLDataGenerator.exe file. The proxy can see the file and should be able to execute by virtue of the proxy imitating my own security profile. But always comes up with the path denied error, even when explicitly navigating to the folder in question.

Anyone have any ideas whether running sql data gen files is possible in SQL Agent?

Thanks in advance.



  • Options
    Alex BAlex B Posts: 1,146 Diamond 4
    Hi @robinw,

    Are you targeting the executable that is in the SQL Data Generator installation directory or the one in the SQL Change Automation directory?  I believe it will be the one in the former location and you will need to use the latter one in the SQL Change Automation directory for licensing reasons.

    The default installation directory is SQLDataGenerator.exe in "C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SDG".  Please try using this executable and see if it works!

    Kind regards,
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.