Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

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?

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?

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

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?

@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.

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

Terms & Conditions