Try removing the comments from the SQL code and see if that improves the situation.
There has been a known issue with this in the past, not sure if it's been fixed yet.
@peterz
yes, I sometimes experience that comments with some type of characters inside the comment result in "malformed statements".
The second and or the third comment might cause this.
Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.
However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.
Here is a simplified workspace illustrating this very fact:
Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)
The takeaway form all of this:
It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).
Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.
However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.
Here is a simplified workspace illustrating this very fact:
Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)
The takeaway form all of this:
It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).
That's strange, but thanks for sharing. I suggest you forward this to Safe support, with a link to this thread. Sounds like something they might want to look into.
@peterz
Yes, that does make sense.
It is not peculiar, you have no control over who does what when.
You can either read all the data from one sql creator, for logic dictates that you can do the merging within a single sql script (for instance make the selections through 2 WITH statements and then join them by selecting those 2, or you can use a sql-executor (instead of the reader)triggered by the sql creator I ussualy take a single sample to trigger it (using the Sampler).
@peterz
Yes, that does make sense.
It is not peculiar, you have no control over who does what when.
You can either read all the data from one sql creator, for logic dictates that you can do the merging within a single sql script (for instance make the selections through 2 WITH statements and then join them by selecting those 2, or you can use a sql-executor (instead of the reader)triggered by the sql creator I ussualy take a single sample to trigger it (using the Sampler).
No, that's not correct. The issue I raised has to do specifically with false error/warnings in log file, not with the functioning of the workspace itself. So I still stand by the fact that it's most likely a bug: the warning in log is false.
Also I don't recall seeing any advice against using a SQLCreator and DataReader in the same workspace (that target the same database).
Hello @peterz,
What is occurring here, is when the SQL is sent to the database, FME wraps some additional SQL with the statement. This is done in an attempt to get finalized schema, with the modern data types such as datetime2. Sometimes these additional queries will fail, in your case, it appears the temporary table that FME tries to create fails due to permissions.
I hope that helps explain the behavior you are seeing!
Thanks for your suggestions @gio and @david_r, but the problem still persisted even after removing the comments from the SQLCreator.
However I did manage to narrow it down to a very strange Workbench peculiarity. If you have a combination of SQLCreator and a DataReader in your workspace and both target the same database, this is what causes the false error/warning in the log file.
Here is a simplified workspace illustrating this very fact:
Now, if I replace the DataReader in above example with another SQLCreator, the problem goes away, no more false warnings in log file! (and yes, comments are OK in the SQL statement, it runs fine with them)
The takeaway form all of this:
It appears there's some incompatibility/oddity in using a SQLCreator and DataReader in the same workspace that target the same database, which manifests in false warnings in the log file. The workaround is to use either SQLCreators or DataReaders but not a combination of both (that target the same database).
@peterz I confirmed your findings! It was a tough one to track down. I had narrowed it down to a spot where I was using a FeatureMerger between the DataReader and SQLCreator. I was suspicious of the FeatureMerger, but now I know the true culprit. Thanks!
Hi @peterz,
Have you tried this in a version of 2019 by chance? I noticed the same issue with an SQL creator (only) running in 2019 but I don't recall seeing it previously. I may have to install FME 2018 again to test this.
I am using
Edition: FME Desktop Oracle Edition (floating)
Version: FME(R) 2020.0.0.0 (20200309 - Build 20200 - WIN64)
and also the accompanying FME Server, and I can confirm that this is still an issue. The SQL Creator still generates it's features, and everything keeps working, but there are warnings in the logs:
2021-08-05 14:44:43| 11.8| 0.0|WARN |Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `IF OBJECT_ID('fme_tempAdoDataTypeTable_spatialReader', 'U') IS NOT NULL DROP TABLE fme_tempAdoDataTypeTable_spatialReader; SELECT * INTO fme_tempAdoDataTypeTable_spatialReader FROM ( select a.column1,
a.column2,
los.column3
from Table1 los
join Table2 a on a.ParentColumn=los.ChildColumn ) AS customQuery WHERE 1=0;'. Provider error `(-2147217900) CREATE TABLE permission denied in database 'Cdatabasename redacted]'.'
2021-08-05 14:44:43| 11.8| 0.0|WARN |Microsoft SQL Server Non-Spatial Reader: Getting Custom SQL schema failed. Make sure all columns are named (any ambiguous columns, e.g. GEOM.STBuffer(1), must have an AS clause). If there is an ORDER BY clause, either remove it or add a TOP clause
I redacted the column- and tablenames in the sql statement, but rest assured the sql is perfectly valid and works, returning 165024 rows. There are no function calls or anything like that, it is just selecting three columns from two existing tables.
FME, either desktop or server, or any of the database connections available to us, will never get create table permissions. And it most definitely and assuredly will never even come close to getting a whiff of the drop table permission!
I am not entirely sure what FME is trying to achieve here (there are better ways of getting the result set schema than writing a temporary table - that is something that should never happen), but the resultant warnings in the log files are annoying (especially if you have workspaces analyzing logfiles from Server and all of a sudden start yelling and screaming about warnings - it plays havoc with my dashboards ;-) ).
The weird thing is that it indeed changes when I disable the regular Readers in that workspace that access the same database: the message indeed goes away.
