Hi @ldonofrio ,
could you please provide sample workspace and data to understand better your issue?
Hi @ldonofrio ,
could you please provide sample workspace and data to understand better your issue?
Thanks,
The problem shows up when I try to use dates in the where clause of a sqlcreator to limit results (commented out below) ,
I added an inspector and I saw that (when the mouse hovers over the BSEG_ST_DT column) that it is of type " fme_varchar(200)" however the sql DB has it as a date so I assume that there is a setting that I haven't chosen perhaps on the connection??
any way with the line uncommitted I get the error below:
Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `SELECT top(100) *
FROM GIS_B2B.Ivw_VIC_MIRN_BILL_HISTORY_V]
where >BSEG_ST_DT] < 2019-12-31 00:00:00 and 0BSEG_END_DT] > 2019-01-01 00:00:00'. Provider error `(-2147217900) Incorrect syntax near '00'.'
A fatal error has occurred. Check the logfile above for details
The below feature caused the translation to be terminated
SQLCreator_<Rejected> (TeeFactory): SQLCreator_<Rejected>: Termination Message: 'SQLCreator output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''
SQLCreator_<Rejected>: Termination Message: 'SQLCreator output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''
A fatal error has occurred. Check the logfile above for details
Kind Regards
Hi @ldonofrio ,
could you please provide sample workspace and data to understand better your issue?
in case you can't read the SQL in the query I've copied it here.
Ta
FME_SQL_DELIMITER ;
SELECT top(100) *
FROM GIS_B2B._vw_VIC_MIRN_BILL_HISTORY_V]
--where hBSEG_ST_DT] < $(parDec) and BSEG_END_DT] > $(parJan);
Hi @ldonofrio ,
could you please provide sample workspace and data to understand better your issue?
Hi @ldonofrio ,
I have no SQL Server DBMS to reproduce yor issue, so I try to give you some general tips based on your feedback:
- did you try the Microsoft SQL Reader instead of the SQLCreator?
- your workspace log says that SQL complains about date format, maybe the date format expected by SQL Server provider is different from the date format built in your query; try, for example, to enclose the sting in single quotes.
By the way, did you try to execute the query directly with an SQL Server Cient tool?
Hope that helps!
Hi @ldonofrio ,
I have no SQL Server DBMS to reproduce yor issue, so I try to give you some general tips based on your feedback:
- did you try the Microsoft SQL Reader instead of the SQLCreator?
- your workspace log says that SQL complains about date format, maybe the date format expected by SQL Server provider is different from the date format built in your query; try, for example, to enclose the sting in single quotes.
By the way, did you try to execute the query directly with an SQL Server Cient tool?
Hope that helps!
hi, I changed the 'FME_SQL_DELIMITER' and cast a sql variable and used single quotes for my fme parameter 'parJan' and it worked.
Thanks for you reply