locked
Task Scheduler for Script using SQL Server Express Edition RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have am trying to run a Task using the Task Scheduler which runs a script using a database protected with a username and password. When the Task is run the SQL Server opens but displays the Login Details page which stops the script from running. Is there anywhere on the Task Scheduler or script I would need to add the username and password to get this to run successfully?

    Thanks in advance for any assistance it is greatly appreciated.

    Jonny

    Friday, October 19, 2018 11:12 AM

All replies

  • User475983607 posted

    Usually a scheduled task accesses reusable code not a web page  which is what I assume is the current programming approach.  Can you provide a high level overview of what you're trying to do?  Can you provide code that reproduces the issue?

    Friday, October 19, 2018 12:22 PM
  • User-1901014284 posted

    Hi mgebhard,

    No problem at all, please see below a copy of the code within my script.

    Updatetable.sql Script code

    USE [CRM]

    UPDATE
    Customer_Contracts
    SET
    Status = 2
    WHERE
    Status = 1
    AND
    Contract_End_Date < GETDATE()

    In regards to the Task Scheduler I have no code but have set up using the Create Basic Task Wizard and populated all relevant fields and ensured that the Edit Actions tab I have selected the above script from with my C drive. I have also tried adding the username and password under the "Add arguments" section of the same page but this has not worked also. 

    Friday, October 19, 2018 12:50 PM
  • User475983607 posted

    Hi mgebhard,

    No problem at all, please see below a copy of the code within my script.

    Updatetable.sql Script code

    USE [CRM]

    UPDATE
    Customer_Contracts
    SET
    Status = 2
    WHERE
    Status = 1
    AND
    Contract_End_Date < GETDATE()

    In regards to the Task Scheduler I have no code but have set up using the Create Basic Task Wizard and populated all relevant fields and ensured that the Edit Actions tab I have selected the above script from with my C drive. I have also tried adding the username and password under the "Add arguments" section of the same page but this has not worked also. 

    Still not clear.   I think you are trying to execute SQL script in a file?  If so, the approach will not work.

    For Windows Task Scheduler I usually build a Console Application that uses Entity Framework or ADO.NET and a standard connection string like normal.  Then simply call the console app's exe from the Windows Scheduled task. 

    IMHO, SQL Agent is a better approach for executing SQL.

    https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-2017

    Friday, October 19, 2018 12:59 PM
  • User-1901014284 posted

    Thank you mgebhard,

    Unfortunately I am using SQL Server Express so am unable to use the SQL Agent which is why I am using the task scheduler. I will look at the link provided and feedback how I get on.

    Thank you for your assistance again it is greatly appreciated.

    Jonny

    Friday, October 19, 2018 1:46 PM
  • User753101303 posted

    You mean you try to run a SQL Script with SqlCmd ? SqlCmd is really launched ?

    If using Windows authentication you could configure the task to run under a Windows allowed account and use the -E option.

    If using SQL Server authentication it should be -U MyId -P MyPassword.

    Rather than "doesn't work", it's best to tell what happens so that we can better understand which EXACT problem you are facing currently (for example could it be that SqlCmd not even starts ?)

    Friday, October 19, 2018 2:05 PM