Solved

SQL Executor errors when using an insert into statement

  • 15 October 2019
  • 9 replies
  • 32 views
dbryantgeo
Rank
Badge +6

I have the following SQL being used by a SQL Executor;

 

FME_SQL_DELIMITER ;

DROP TABLE dbo.[PREMISE_CONTRACT_ROLLBACK];

SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT];

 

It runs successfully using the "Run" option when I'm accessing the transformer itself. However, if I just add a Creator > SQL Executor > Logger and run the workbench, the following error occurs;

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `SELECT * INTO #fme_tempAdoDataTypeTable_spatialReader FROM ( SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT] ) AS customQuery WHERE 1=0'. Provider error `(-2147217900) Incorrect syntax near the keyword 'INTO'.'

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

It looks like FME is wrapping the statement in its own statement and causing the syntax issue, but I don't appear to be able to get around this.

My only thoughts right now are to add a reader and a writer, but the process is already long enough without reading unnecessary data into memory.

icon

Best answer by erik_jan 16 October 2019, 16:01

View original

9 replies

erik_jan
Rank
Userlevel 2
Badge +12

Maybe not the issue, but I think the last ; should not be there.

; is used to separate two SQL statements and none is following the last ;

david_r
Rank
Userlevel 4

Maybe try using a different SQL delimiter, that has worked for me previously:

FME_SQL_DELIMITER #
DROP TABLE dbo.[PREMISE_CONTRACT_ROLLBACK]
#
SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT]
dbryantgeo
Rank
Badge +6

Thank you @david_r and @erik_jan. I have attempted both your proposals but unfortunately neither has worked - I'm still getting a malformed SQL error message.

david_r
Rank
Userlevel 4

Thank you @david_r and @erik_jan. I have attempted both your proposals but unfortunately neither has worked - I'm still getting a malformed SQL error message.

Which version of FME are you running? The method I posted below worked fine in FME 2017.

dbryantgeo
Rank
Badge +6

Which version of FME are you running? The method I posted below worked fine in FME 2017.

Hi David, we're running 2019.1 - this is something I absolutely should've specified sorry

david_r
Rank
Userlevel 4

Hi David, we're running 2019.1 - this is something I absolutely should've specified sorry

It can well be that something has changed since FME 2017, in your place I would contact Safe support about it. Perhaps also link them back to this thread.

erik_jan
Rank
Userlevel 2
Badge +12

In Oracle the Insert SQL statement is:

Insert into dbo.[PREMISE_CONTRACT_ROLLBACK] Select * FROM dbo.[PREMISE_CONTRACT].

From the documentation it seems the same in SQL Server SQL:

INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table
dbryantgeo
Rank
Badge +6

In Oracle the Insert SQL statement is:

Insert into dbo.[PREMISE_CONTRACT_ROLLBACK] Select * FROM dbo.[PREMISE_CONTRACT].

From the documentation it seems the same in SQL Server SQL:

INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table

How strange! At least this worked

steveatsafe
Rank
Badge +11

In my testing... the table was still created... and it seems the messages (similar to yours) were just warnings. Odd. I'll see if I can get this cleared up. Thanks for sharing.

SteveatSafe

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.