• State Suggested Answer
  • Replies 6 replies
  • Answers 2 answers
  • Subscribers 95 subscribers
  • Views 5162 views
  • Users 0 members are here
Options
Related

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

tsudhir08
over 5 years ago

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 Reply Children
  • 0 over 5 years ago in reply to Markus Weiss-Ehlers

    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.

  • 0 over 5 years ago in reply to tsudhir08

    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. 

  • 0 over 5 years ago in reply to Markus Weiss-Ehlers

    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)

  • 0 over 5 years ago in reply to tsudhir08
    tsudhir08 said:
    [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.