topic Re: SQL Executor syntax for update statement using @Value for a string in ArcGIS Data Interoperability Ques.

SQL Executor syntax for update statement using @Value for a string

MichaelKohler — Mon, 03 Jun 2024 20:58:07 GMT

We have a FME workspace where I need to update rows in the table that meet a where condition. The condition is a string value. When I set up the sql statement it is like this.

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = @Value(PARCEL_NUMBER)

and when the executor is run, the below error is returned for each feature.

Query failed, possibly due to a malformed statement. Query Text UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = 342073103690008 Provider error `(-2147217913) Error converting data type varchar to numeric.'

The PARCEL_NUMBER and the PARCELID fields are TEXT in both instances. This is what was used in python.

UPDATE ADDRESSES_TEST SET internalonly = 1 WHERE PARCELID = '{}'

where the PARCEL_NUMBER is quoted. Also, internalonly is a smallint in both databases

Any leads would be greatly appreciated.

Re: SQL Executor syntax for update statement using @Value for a string

BruceHarold — Mon, 03 Jun 2024 21:05:55 GMT

Michael the single quotes around PARCELID make it a literal and not an identifier. Just remove them.

Re: SQL Executor syntax for update statement using @Value for a string

MichaelKohler — Mon, 03 Jun 2024 21:23:46 GMT

Thanks for the quick reply.

Both statements with, and without the single quote return the same error.

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = @Value(PARCEL_NUMBER)

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = 340750300650005'. Provider error `(-2147217913) Error converting data type varchar to numeric.'

and

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = @Value(PARCEL_NUMBER)

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = 342073108650005'. Provider error `(-2147217913) Error converting data type nvarchar to numeric.'

The parcel number is a text string and should have quotes. This works with python and in SQL.

update ADDRESSES_TEST set internalonly = 1 where PARCELID = '342073103690008'

if i take the quotes away from the PARCELID and run in SQL, the same conversion error is given

Error converting data type nvarchar to numeric.

Very much appreciate your help.

Re: SQL Executor syntax for update statement using @Value for a string

MarceloMarques — Mon, 03 Jun 2024 21:44:19 GMT

use SQL Server function CAST or CONVERT
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Re: SQL Executor syntax for update statement using @Value for a string

MichaelKohler — Mon, 03 Jun 2024 22:00:51 GMT

Thank you. That did the trick

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = CAST(@Value(PARCEL_NUMBER) As nvarchar)

Re: SQL Executor syntax for update statement using @Value for a string

MarceloMarques — Mon, 03 Jun 2024 22:05:39 GMT

Glad to help.