TalendDate in MssqlInput

Anonymous — Thu, 18 Oct 2018 01:32:29 GMT

I am trying to pass a variable in a MSSqlInput component to retrieve the current UTC time.

I am using the talend function TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate())

However when i place this into the MSSQL component and attempt to declare it as a variable i am getting some error. I have tried many variations, but still can not get it to work.

tMSSqlInput

Query:

"
DECLARE @DtNow DATETIME = TalendDate.formatDateInUTC(\"yyyy-MM-dd HH:mm:ss\", TalendDate.getCurrentDate() )
"

I am getting the below error

[statistics] connected
Exception in component tMSSqlInput_2 (Load_StatusCredits_CC)
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
[FATAL]: wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC - tMSSqlInput_2 Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.tMSSqlInput_2Process(Load_StatusCredits_CC.java:916)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.runJobInTOS(Load_StatusCredits_CC.java:1614)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.main(Load_StatusCredits_CC.java:1248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.tMSSqlInput_2Process(Load_StatusCredits_CC.java:916)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.runJobInTOS(Load_StatusCredits_CC.java:1614)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.main(Load_StatusCredits_CC.java:1248)
[statistics] disconnected

Re: TalendDate in MssqlInput

Anonymous — Thu, 18 Oct 2018 07:58:31 GMT

Hi,

Since you are using tMSSQLInput component, you will have to write a SQL query to extract the UTC time. Could you please try the below query?

SELECT GETUTCDATE()

https://docs.microsoft.com/en-us/sql/t-sql/functions/getutcdate-transact-sql?view=sql-server-2017

You are currently trying to run the declare part in MSSQLInput component. If your intention is to run a stored proc, could you please try tMSSQLSP component?

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: TalendDate in MssqlInput

cterenzi — Thu, 18 Oct 2018 14:42:26 GMT

Everything inside the quotes in your query is sent to the database as-is. What you want is to drop in the current timestamp at runtime. To do this, alter your query like this:

"
DECLARE @DtNow DATETIME = '" + TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate() ) + 
"'

SELECT @DtNow;"

When the job runs, the current date will be added to the query string and passed to the database.

Re: TalendDate in MssqlInput

Anonymous — Fri, 19 Oct 2018 06:47:33 GMT

Thanks guys,

I was able to get it running using the below code;

DECLARE @DtNow DATETIME = '"+TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate())+ "'

topic Re: TalendDate in MssqlInput in Talend Studio

TalendDate in MssqlInput

Re: TalendDate in MssqlInput

Re: TalendDate in MssqlInput

Re: TalendDate in MssqlInput