CODEWITHSUNDEEP
c#sql-servervb.netsql-agent
Eric
Eric

Reputation: 145

Trying to execute a SQL Agent Job from code

I have a large script that moves data from one database to another. It contains a transaction from within it does what it must do. The script is currently executed manually.

I was trying to get it working by sending the script to the server but then when the connection was broken, it stopped the script.

So as per suggestions on this site and others I created a SQL Agent Job with the script code in the step.

The file that had the original script is now only having this code:

EXEC msdb.dbo.sp_start_job @job_name = 'script_3'

This is send to the sql server with this code:

  Public Sub PerformNonQuery(source As String, commandTimeOut As Integer)
  Dim cnnUse As IDbConnection = Nothing
  Try
    Dim queryCommandSQLClient As New SqlCommand

    Select Case CheckConnection()
      Case "SQLCLIENT"
      cnnUse = OpenSQLClient()
      With queryCommandSQLClient
        .CommandTimeout = commandTimeOut
        .CommandType = CommandType.StoredProcedure
        .CommandText = source
        .Connection = CType(cnnUse, SqlConnection)
        .ExecuteNonQuery()
      End With

      queryCommandSQLClient.Dispose()
    End Select
  Catch ex As Exception
    Throw
  Finally
    If Not IsNothing(cnnUse) Then
      cnnUse.Close()
    End If
  End Try
End Sub

this function is called with:

When I run this command I check with this if the transaction from the job is running (when it runs it takes up to 15-20 minutes):

select * from sys.dm_tran_active_transactions where name like 't_TAPBeheer_Copy_To_AP'

The result is always nothing.

Is it not firing the job? I never see it in the Job Activity monitor.

I know the job is good, because if I run it in management studio, it works.

What am I doing wrong here? How can I figure out why the job is not executed?

Upvotes: 0

Views: 85

Answers (3)

Eric
Eric

Reputation: 145

First of all, thank you to those who tried to help.

I managed to get it working.

What I needed to do:

  • Give the user the webapplication uses to connect to the database access to the MSDB database via the user-mapping in sql management studio.
  • Set the role membership for this user and database: I checked SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole.
  • Created a JOB in SQL Agent that runs the script that moves the data in a transaction. No scheduling settings. Job owner is the user the application uses.
  • Created a Stored Procedure that runs the msdb.dbo.sp_start_job procedure
  • Call from my code this stored procedure.
  • My webapplication checkes every second, by javascript timer, if the transaction is still running.

Why it went wrong.... ME!

One of my mistakes was executing the SP with owner = ''. removing that fixed it.

It is now running! yay!

Upvotes: 1

Xavier J
Xavier J

Reputation: 4728

Rather than relying on the SQL Agent to perform the work, I would have encapsulated the processing into a stored procedure, and then investigated the use of Hangfire in a console application or web application to take care of the scheduling. This assumes that your schedule could be expressed in regular intervals per a Unix/Linux style crontab expression. You'd also have to make precautions such that Hangfire doesn't try to execute the same job more than once simultaneously.

Anyhow, if you're not required to use SQL Agent, then Hangfire may give you far much more flexibility and ability to derive the information you're looking for.

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9316

maybe not the answer but too long for comment

There are several possible issues and the missing await for async call was already mentioned in comments.

CommandType

When you set CommandType = CommandType.StoredProcedure it means that the CommandText property contains the name of the procedure only (docs. If proc has paramenters then in this case they should be defined as Parameters property objects (see docs and SO answer with example).

What you have is a plain-text ad-hoc query thus your SqlCommand should be of type CommandType.Text

sp_start_job

Job-management system procs don't fail with exceptions, they return 0 on success and 1 on failure (docs). You should check return code - it may be non-zero and no job gets started because of somewhat error.

EXEC @res = msdb.dbo.sp_start_job ...
IF @res <> 0
    -- throw or select something

Also, if the job ever gets started, then job history (see on job activity monitor) should be populated with some info per run.

ConnectionString

Also double-check your connection string - it is possible that you see zero activity on server because you're connecting to whatever sever but not the expected one. Perhaps there would be a connection timeout or something like that if you awaited for the command correctly.

Please test your code with proposed alterations and update your question if you obtain any new details, error messages and so on.

Upvotes: 1

Related Questions