Hi,
I need to do a number of SQL operations in a transaction scope, is it possible the include a SQL_Execute statement in that same transaction? From what I can tell we open a separate connection to SQL with SQL_Connect putting it out of the transaction scope
*This post is locked for comments
You believe right, stored procedure is a procedure that is stored.
But not necessarily a procedure contains more than 1 step, it wouldn't do any harm if you create stored procedure with only 1 sql statement. ;-) Just don't tell SP that it has only 1 statement.
and further you mentioned "a number of SQL operations" in your question.
No option is a last option, actually we make it first or last, as you are more focused on not adding another object in SQL catalogue, and I thought about making things workout easily and perfectly when suggest using SP.
I believe a stored procedure should be a procedure that is stored, and that a procedure contains more than 1 step. If you are going to do only 1 SQL statement then a stored procedure does not make sense. I also try to avoid adding another object to the SQL catalogue, unless I have no other option. Thanks for the suggestion, it would be my last option
Good question.
but
in this case why you bother yourself to use SQL_Execute?
why not calling stored procedure.
Almas, please explain how using an array would mean that I do not need to use transactions, if I have 3 statements in an array and the 3rd one fail, would the first to rollback? Also I need standard Dex operations to be in the same transaction as my SQL_Execute statement, how would I do that?
if you have to do number of SQL operation you can do it by using array so you don't need to care about transaction scope. Read below detail of SQL_Execute.
In certain cases, you may need to execute more than 32K of SQL statements
at one time. You can do this using an array of text fields. Copy the first 32K
of SQL statements into the first element of the text array, the next 32K of
statements into the second element, and so on. Then pass the first element
of the array to the SQL_Execute() function. The array elements will
automatically be assembled and the SQL statements will be executed in the
appropriate order.
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
