Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Five Stars

Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Has anyone encountered invalid column name errors using the MSSQL component recently? I'm running into this error in my job:

 

Exception in component tMSSqlInput_1
java.sql.SQLException: Invalid column name 'Balance'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)

Here's my current query:

 

"SELECT
SiteId,
Name,
Comments,
Contact,
ContactPhone,
InstallDate,
Address,
Email,
LogoPath,
TimeZone,
Logo,
Factor,
CustomerId,
Balance,
date_update
FROM
sites (nolock)"

I've tried using Talend Open Studio v7.1.1 and v7.2.1 and both are throwing the same error. From what I can tell, "Balance" or "date_update" are not reserved names in Java or MSSQL.

 

Community Manager

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Can you show us your table schema?

Five Stars

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Thanks for the quick response! I've included details about the MSSQL database table and the Talend schema below:

 

Screen Shot 2019-11-21 at 8.50.42 PM.pngDatabase SchemaScreen Shot 2019-11-21 at 8.51.12 PM.pngTalend Schema

Community Manager

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Sorry, I meant a schema DDL dump. The column is clearly there, but it could be defined in your DDL in a weird way

Five Stars

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Gotcha. DDL below but nothing about the date_update column seems unusual.

 

CREATE TABLE sites (

SiteId int IDENTITY(1,1) NOT NULL,

Name nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Comments nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Contact nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

ContactPhone nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

InstallDate datetime NULL,

Address nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Email nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

LogoPath nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

TimeZone int NULL,

Logo image NULL,

Factor int NULL,

CustomerId int NULL,

Balance int NULL,

date_update datetime NULL,

CONSTRAINT Sites_PK PRIMARY KEY (SiteId)

)

 

In the meantime, I've tried using a CTE and/or aliasing the date_update column to a different string but I'm still running into the same error.

Community Manager

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Does this still happen if you remove the (nolock) hint?

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog