Skip to main content
Hi I've right clicked on a table in the sqlcreator and chosen 'Script as select' and done a very trivial where clause. the problem is the columns are all varchar200 and not dates .. what have I missed?

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?

Annotation 2020-08-16 224421


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


Reply