Best answer

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

  • 15 June 2023
  • 4 replies
  • 1365 views

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

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]

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! 🙂

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?

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?

Reply