topic Re: [resolved] MSSQL Query works in SQL Builder but not in Job in Talend Studio

[resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Sat, 16 Nov 2024 12:51:59 GMT

I have a query

SELECT 	JOB_ID,
		PHYSICAL_DATASET,
		LOGICAL_DATASET,
		FACT_BREAK,
		ENTERPRISE_ID,
		BREAK_ID,
		SOURCE_TIMESTAMP,
		DATAFEED_BREAK,
		ETL_TRANSACTION,
		TARGET_STATE,
		Dataset_Type,
		Target_Dimension,
		Target_Category,
		--Dimension_Context,
		Context_ID,
		Reference_Composite,
		Dimension_ID,
		Control_Key,
		Placeholder_Flag,
		Unknown_Flag,
		Orphan_Flag,
		--SOURCE_COLUMN,
		--SOURCE_VALUE,
		CONTEXT_KEY_01,
		CONTEXT_KEY_02,
		CONTEXT_KEY_03,
		CONTEXT_KEY_04,
		CONTEXT_KEY_05,
		CONTEXT_KEY_06,
		CONTEXT_KEY_07,
		CONTEXT_KEY_08,
		CONTEXT_KEY_09,
		CONTEXT_KEY_10
FROM ( 
SELECT  JOB_ID,
		PHYSICAL_DATASET,
		LOGICAL_DATASET,
		FACT_BREAK,
		ENTERPRISE_ID,
		BREAK_ID,
		SOURCE_TIMESTAMP,
		DATAFEED_BREAK,
		ETL_TRANSACTION,
		TARGET_

To see the whole post, download it here

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Wed, 15 Jun 2011 09:01:02 GMT

Hi David
I think the problem caused by "--SOURCE_COLUMN", is it a column name or just comment? Remove it should be ok.
Best regards
Shong

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Wed, 15 Jun 2011 09:17:52 GMT

Hi Shong,
--Dimension_Context, --SOURCE_COLUMN, & --SOURCE_VALUE, are all actual colunms i just commented out in the sql code. I have now removed the them from the sql query altogether. However I'm still getting the same error:
Here is the error when i Execute the job:

Starting job xxxxxxtest at 01:11 15/06/2011.
Exception in component tMSSqlInput_1
java.sql.SQLException: Incorrect syntax near ','.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
	at poc_star.xxxxxxtest_0_1.xxxxxxtest.tMSSqlInput_1Process(xxxx

To see the whole post, download it here
OriginalPost.pdf

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Wed, 15 Jun 2011 09:29:16 GMT

Also is there a way to see the Actual SQL Query that is being sent to the backend? I tried to use tJavaRow on the Main output but it would always fail before it even got the tJavaRow
The code i used in tJavaRow

System.out.println((String)globalMap.get("tMSSqlInput_1_QUERY"));

-David

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Wed, 15 Jun 2011 09:40:06 GMT

Hi Shong,
I missed a commented out line from the SQL statement, once i removed it, it worked! Thx for the help on that.
However, can you reply to my other two questions:
1) are SQL Comments not allowed?
2) how can i view the actual SQL query that is generated by Talend.
Thx,
David

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Wed, 15 Jun 2011 15:18:07 GMT

David,
Comments are not allowed because of the restriction on multi-line strings in Java. If you take a look at your generated code, you will see that the query gets placed in a single line. This means the '--' will comment out everything after it, causing your syntax error.
Pulling the query from the globalMap is the correct way to see what exactly Talend will send to the DB.

Re: [resolved] MSSQL Query works in SQL Builder but not in Job

Anonymous — Fri, 17 Jun 2011 02:49:44 GMT

Thanks John! Understood.
-David