How to execute a sql query instead of a stored procedure in a process step?

Hi All,

I have created a process in which one of the step executes a stored procedure for updating a table column value based on an if else condition. this works fine for me. but now I want to execute a sql query instead of a stored procedure for the same operation. I am not aware on how to achieve this?

For this I have selected Process Task = SQLComponent - Execute SQL 

Parameters - SOLStmt = Values = " my sql query goes here"

The process is being terminated with an error which I am not able to identify.

Could any one please suggest me what is the correct method for achieving this.

we are  using version 7..1.3.

Thanks in Advance.!

Regards,

T.Sudhir

Parents
  • I am not able to figure that out. So was looking for the steps for executing a inline SQL query rather than a procedure in a process step.

  • What do you mean, you are not able to find the error message? It would be easier for the community to help, having an error message.

    In addition, I suggest you take a look at some of the default jobs using the Execute SQL task to get some real-life samples about how it can be used. You can identify those process steps easily in Designer. 

  • here is the error message Markus

    ErrorMessages = (2019-09-05 11:15:21.940) [810023] Error during execution of statement:  if exists(select 1 from CCCHROverride ho where ho.CCC_RequestID = '324983274' and GETUTCDATE() > DATEADD(hh,6,XDateInserted) begin update CCCHROverride set CCC_ProcessingStatus='Failed',CCC_Message='Request timed out' where CCC_RequestID='12345' end
    [810143] Database error 156: Incorrect syntax near the keyword 'begin'.
    [System.Data.SqlClient.SqlException] Incorrect syntax near the keyword 'begin'.
       at VI.JobService.ProcessorThread._ExecuteJob(Job job, JobResult result)
       at VI.JobService.JobComponents.SQLComponent.Task_Execute()
       at VI.Base.SyncActions.Do[T](Func`1 function)
       at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
       at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
       ---- Start of Inner Exception ----
       at VI.DB.DataAccess.ReadWriteDbSession.<SqlExecuteNonQueryAsync>d__13.MoveNext()
       ---- Start of Inner Exception ----
       at VI.DB.DataAccess.ReadWriteDbSession.<SqlExecuteNonQueryAsync>d__13.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.ReadWriteDbSession.<IgnoreBrokenConnectionAsync>d__42`1.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.ReadWriteDbSession.<_SqlExecuteNonQueryAsync>d__14.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.ReadOnlyDbSession.<ExecuteAndLogAsync>d__92`1.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.ReadOnlyDbSession.<ExecuteAndLogAsync>d__93`1.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.SafeDbCommand.<ExecuteNonQueryAsync>d__16.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.SafeDbCommand.<_CheckedAsync>d__40`1.MoveNext()
       ---- Start of Inner Exception ----
       at VI.DB.DataAccess.SafeDbCommand.<_CheckedAsync>d__40`1.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at VI.DB.DataAccess.PhysicalMsSqlConnection._AsyncSqlCommand.<ExecuteNonQueryAsync>d__31.MoveNext()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
       at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
       at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
       at System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

  • [810143] Database error 156: Incorrect syntax near the keyword 'begin'.

    Mean, your SQL statement is not valid. How do I know? I pasted it in SQL Server Management Studio.

    You are missing a right parenthesis before the begin keyword.

Reply Children
No Data