SQL Server Help & Support
What SQL Server (MSSQL) Triggers and Actions are Supported?
- New or Updated Row - Triggered when a new row is added or modified.
- New Column - Triggered when you add a new column.
- New Row (Custom Query) - Triggered when new rows are returned from a custom query that you provide. Advanced Users Only
- New Table - Triggered when you add a new table.
- New Row - Triggered when you add a new row.
- Update Row - Updates an existing row.
- New Row - Adds a new row.
- Find Row via Custom Query - Finds a row in a table via a custom query you control.
- Find Row - Finds a row in a table via a lookup column.
How to Get Started with SQL Server (MSSQL)
SQL Server (MSSQL) is a bit tricky to get set up properly with Zapier. Our servers run Linux and we have a fickle driver stack to properly communicate with external SQL Service instances. In many instances, it is random whether or not a certain instance will work with Zapier. Here is a short list of compatibility if everything else is set up correctly:
SQL SERVER 2005 - NOT COMPATIBLE
SQL SERVER 2008, 2008 R2 - MAYBE COMPATIBLE
SQL SERVER AZURE - MAYBE COMPATIBLE
SQL SERVER 2012, 2014 - COMPATIBLE
SQL SERVER 2016 - COMPATIBLE
The first thing you can try is adding your SQL Service instance plain. Here is what that looks like:
Most importantly, you'll need to make sure your SQL Service instance is available outside your firewall to the IP address listed here.
If you are able to get your account added without errors (click the "test" button to see) -- congrats! Otherwise, we recommend you do the following, which has been previously tested and confirmed to work:
Set up and install an Amazon RDS SQL Server 2008 R2 instance here: http://aws.amazon.com/rds/sqlserver/
Use the connection detailed supplied when creating the Amazon RDS SQL Server instance on Zapier here: https://zapier.com/app/settings/authorizations/MSSQLAPI
We recommend using an external database like this because it is more secure than directly exposing your production database to the internet.
Common Problems with SQL Server (MSSQL)
Some new rows didn't trigger my Zap#
When using the "New Row" trigger, each poll captures the 50 most recent rows. That means if more than 50 rows are added in a polling interval (5 or 15 minutes depending on your plan), some data will be lost.
If you need to capture more than 50 rows at a time, you'll want to add them to your database in batches—no more than 50 records at a time every 5 or 15 minutes (depending on your plan).
Can't Connect To My Database#
Database connections can be very fickle. Here are a few things you can re-check:
- What version is your SQL Server database?
Zapier only supports the newest version of SQL Server and even then minor driver problems can cause incompatibilities. Zapier has tested SQL Server integration against Amazon AWS SQL Server 2008 R2 and 2010 RDS instances.
I'm getting errors about DateTime conversions#
If you're using a
DateTime column type and receiving errors about converting text into DateTimes, you should try using the
date type or the
Unknown Connection Timeouts#
One known issue that has been reported by a number of our users occurs if you setup SQL Server and install a non-default instance (for example, a named instance). When this is done, the communication port is actually changed but SQL Server uses SQL Server Browser to relay messages to and from port 1433. For one reason or another our integration doesn't work well in this situation. We recommend using the default instance for "the path of least resistance" but if that is not doable you will need to make sure that TCP communication is enabled and you have static port set for the service.
Login failed for user#
The error that commonly looks like
(ProgrammingError) ('42000', " [unixODBC][FreeTDS][SQL Server]Login failed for user 'username'. (18456) (SQLDriverConnect)") None None is a commonly a bug with our password connection string encoding - try switching to a long alphanumeric password - we recommend this tool for generating strong passwords.
Garbled or otherwise incorrectly converted data coming from the Trigger#
As of right now, we only support UTF-8 encoding. You will wish to ensure your DB is set to UTF-8 in order for us to work with the data.
Can't Connect to My Database
Database connections can be very fickle. Here are a few things you can re-check:
What version of MySQL are you using?#
The Zapier MySQL integration doesn't currently support version 8.0+ Your database will need to use an earlier version in order to use it with Zapier.
Is your database IP restricted?#
This is useful if you use a non-AWS host, for example: Rackspace, HostGator, Linode, DigitalOcean, etc. Zapier connections currently come from static IP addresses. Zapier will make connections to your database from the following IP address:
Important: Don't forget to do both things: open up your firewall and add the IPs to your user GRANT permissions.
For example, if you wanted to grant
SELECT access to a user called
zapier on a
your_database.your_table in PostgreSQL, you'll need to use the following command:
GRANT SELECT ON your_database.your_table TO 'zapier'@'18.104.22.168' IDENTIFIED BY 'your_password';
If you plan to insert, make sure to add the insert statement to the grant:
GRANT SELECT, INSERT ON your_database.your_table TO 'zapier'@'22.214.171.124' IDENTIFIED BY 'your_password';
You might try granting all privileges to verify the Zap is working, then narrow down on just the permissions you actually need.
For PostgreSQL, you'll need to configure the server to accept logins from remote IPs (in
pg_hba.conf), and create a user for Zapier to use.
Important: If you are hosting your database on Amazon AWS please follow the steps for using Amazon RDS/EC2 below.
Is your database listening on a public interface?#
Be sure your database isn't bound to only listening to
127.0.0.1 - commonly you want to bind it to
* or remove that restriction completely. Check your
postgresql.conf or similar configuration.
Using Amazon RDS/EC2 or Heroku?#
You need to make sure we can access the database from
If you are using Amazon RDS or EC2 for your database, you need to add
126.96.36.199/32 to your security group.
If you are on the classic platform, navigate to RDS and click "Security Groups" on the left hand navbar. You should see the following:
Select the security group that you are using for your database instance and add the above IP as a CIDR/IP authorization.
Test your connection and if the steps above were completed correctly you should be good to go!
If you are on the VPC platform, please make sure that it is publicly accessible and within a public subnet in addition to whitelisting the above IP.
See Scenarios for Accessing a DB Instance in a VPC for more details on for both VPC and Classic.
Using Google Cloud Platform (GCP) and CloudSQL?#
If hosting MySQL on GCP you need to add the IP (188.8.131.52) to the "Authorised networks" section on the "connections" tab within CloudSQL.
Additional information and instructions here: https://cloud.google.com/sql/docs/mysql/configure-ip#add
Check username, password, SSL certificates#
Check for typos, permissions, and spaces at the end of fields and make sure they are all entered correctly.
Is your database very slow?#
It isn't uncommon to see queries take much too long for Zapier to correctly operate - you should ensure your queries run well under the 30-second limit we impose. Remember, if you are doing
WHERE clauses to add indexes on those columns, please be sure your
id column has an index as well (normally that is a primary key and is automatic).
Database Service De-duplication
The "New Row" trigger sees the most recent 50 items which affects how the maximum amount of rows your Zap can trigger on at one time. See deduplication for more information.
A common problem with database services on Zapier is deduplication. You can read more about deduplicate in general, here.
This applies for all the database services on Zapier like MySQL, MSSQL, Postgres, etc.
Zapier deduplicates off of the primary keys on the table you are triggering new items from. If you have multiple primary keys then a concatenation of all of them is used to deduplicate.
The ramification is if you make a sweeping change to the primary keys on your table, you'll inadvertently Trigger tons of things through your Zap. Sometimes this is caught by Zapier's flood protection feature. Nonetheless, you should turn your off Zap before you make changes and turn it On again after the changes have been made. This allows Zapier to establish a new "baseline".
If your table has no primary keys, Zapier will guess which column to use, usually looking for column names with
id in it.
I Don't Have A Unique "ID" Column#
If you don't have an
id by default, you must create one or return one manually with the query, for example:
SELECT *, CONCAT(user_id, created_at) AS id FROM my_table ORDER BY created_at DESC
Or whatever technique most closely approximates how you want Zapier to consider your rows unique!
Popular Things To Do With SQL Server (MSSQL)
Need More Help? - Our support team is happy to help -- send us a message