Best answer

Committing after executing SQL Server stroed proc from Zapier

  • 17 June 2020
  • 5 replies
  • 724 views

Userlevel 2

I am trying to get my Zap to execute a stored procedure on a remote SQL Server. The stored procedure stores a new row in a table using a list of parameters.

Everything works fine - except that I can’t get to commit the transaction.

I can execute the SP using the “Find Row via Custom Query in SQL Server” function in my Zap, but it doesn’t commit. It connects, and works, since I get a return value that has the correct new row ID, but the transaction is never committed. 

The query in my zap looks like this:

BEGIN TRAN

EXEC MyStoredProc Param1, Param2……

COMMIT TRAN

but it never commits.

Is this perhaps because the whole Zap function is a read-only transaction? - wouldn’t I get an error message then?

Is there any other way of achieving what I’m trying to do?

icon

Best answer by satya 21 June 2020, 20:49

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +11

Hi @BrightSun!

To be fully transparent, my SQL knowledge is somewhat limited. However, I found some internal documentation that may apply here:

What is the behavior in Zapier? What is the error?

Customer was trying to use the Find Row via Custom Query to run a Stored Procedure which creates (via INSERT) data in their database. The Stored Procedure contained an INSERT followed by a SELECT (to return data to the Zap) and was running without error, but the data created by the INSERT wasn't actually showing up in the database.

What in the app causes this to happen? How can it be fixed?

Adding an explicit COMMIT immediately after the INSERT in the Stored Procedure appears to have resolved this.

Perhaps when we issue a query (request), the AUTOCOMMIT in that session is disabled, which is why adding an explicit COMMIT works (and is considered best practice).

With that in mind, it may help to try adding a COMMIT statement in the query. Does this sound worth a try?

Userlevel 2

The stored procedure is already doing that unfortunately. So, in this case that does not make it work.

Userlevel 1

Using commit in Store procedure and explicitly in zapier query doesn’t work. Is it even possible to insert data from stored procedure?

 

 

@BrightSun Do you find it possible to commit your changes?

Userlevel 1

@BrightSun  Hope you found a fix.

Here is how I solved this issue. By default the connection is executing query by setting implicit transaction mode to ON. I turned off implicit transactions and removed transaction in zapier query window.

Userlevel 2

That’s great, thank you very much, that was precisely the problem.