About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

  • Home
  • Get Help
  • Troubleshooting
  • SQL Server error: Error during execution: (pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'id
Best answer

SQL Server error: Error during execution: (pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'id

  • 15 June 2023
  • 4 replies
  • 1136 views
I.Raz
Userlevel 1

Hello,

I am trying to proof a customer that Zapier can help in connecting SQL Server to other systems. When creating a new transfer, I am asked to enter a query which is not working. The source of the data in an SQL view which does not have a column named 'id’, instead the unique identifier called 'key'.

It seems the query is expecting to get a column named íd’, the error details can be found below. How can I query tables without a column named íd'?

 

Error during execution: (pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'id'. (207) (SQLExecDirectW)") [SQL: SELECT TOP (100) "Customer Number" FROM ****.dbo.**** ORDER BY "Customer Number", ****.dbo.****.id ASC] (Background on this error at: https://sqlalche.me/e/14/f405)

 

icon

Best answer by SamB 16 June 2023, 15:09

View original

4 replies

SamB
Rank
Userlevel 7
Badge +11

Welcome to the Community @I.Raz! 👋

Hmm, it appears it could be the ORDER BY part of the query that’s causing an issue. It seems like it’s trying to reference a column called id which, as you mentioned, doesn’t exist:
a47d76eb21d066e3517144705fe517f6.png

Usually you’ll want to set it to order by the values in a date field of some kind. Like created_date, modified_at etc. 

Can you try changing the ORDER BY section of the query to reference a relevant date field and let us know whether that fixes the error?

I.Raz
Userlevel 1

Hi and thank you  for your asnwer!

Actually, Zapier platofrm added the 'order by id’ part, this was not added by us. I've tried now to add an 'order by' a date field but as you can see below, the id is added automatically so error remains the same:

 

 

SELECT TOP (100) [Customer Number]
      ,[Expiry date]
  FROM [****].[dbo].[****] 
Order by [Expiry Date]
Error during execution: (pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'Expiry Date'. (207) (SQLExecDirectW)") [SQL: SELECT TOP (100) "Customer Number", "Expiry date" FROM ****.dbo.**** ORDER BY "Expiry Date", ****.dbo.****.id ASC] (Background on this error at: https://sqlalche.me/e/14/f405)

 

Any suggestion how we can fix this issue?

SamB
Rank
Userlevel 7
Badge +11

Thanks for getting back to me, @I.Raz!

I’ve been looking into this further and think you’re actually running into a bug that’s been reported for the SQL Server app, specifically when it’s used in a transfer. Sorry for missing that previously!

I don’t have any ETA that I can share on when this will be fixed. But I’ve added you to the list of affected users so we can send you an email notification as soon as it’s sorted. There’s no known workarounds reported for this but if we come across any we’ll be sure to share them here! 🙂

J

I had the same problem with Transfers where my table didn’t have an ‘id’ column. I was able to fix this by creating a view in the SQL database and changing the name of my ID column to ‘id’. Maybe you can change [Customer Number] to an int and use this as your ‘id’
 

CREATE VIEW [dbo].[MailchimpExport] AS

SELECT TOP (100) Convert(int, [Customer Number]) AS id,[Customer Number], [Expiry date] 

FROM [****].[dbo].[****] 

Go

 

then used the view in the query:

SELECT * FROM MailchimpExport ORDER BY [Expiry date]

Reply


Terms & Conditions