Forum

Unable to update external data source

Nov 23, 2019 01:31 AM

Just came

Points:: 8
Joined:: Apr 2, 2012

Hi,

I'm having an issue while trying to upd ate a record in external data source.
1C platform generates wrong TSQL code and fails with syntax error.

Could you please let me know if there a way to avoid the error by changing anything on my side?

Connection string for the external data source:
DRIVER={SQL Server};SERVER= < server name >;Trusted_Connection=yes;DATABASE=DBA;

TSQL query generated by 1C:Platform (obviously it is wrong):

Code
UPD ATE "Meta"."TableName" T1 SE T databaseName = 'a', databaseServerRoleNonProd = 's', databaseServerRoleProd = 's', isActiveNonProd = 1, isActiveProd = 1 WHERE T1.id = 1

Proper query:

Code
UPDATE T1 SE T databaseName = 'a', databaseServerRoleNonProd = 's', databaseServerRoleProd = 's', isActiveNonProd = 1, isActiveProd = 1 FR OM "Meta"."TableName" T1 WH ERE T1.id = 1

Code

Platform: 1C:Enterprise 8.3, training version (8.3.15.1489)
Configuration: Configuration
Mode: File (without compression)
Application: Thin client
Localization: Infobase: English (United States), Session: English
Interface mode: Taxi

Errors:
--------------------------------------------------------------------------------
11/22/2019 2:23:45 PM
External database error:
error executing query
, reason:
ODBC error. SQLSTATE: 42000
Error number: 102
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'T1'.

Profile

Alexey Bochkov

Nov 23, 2019 01:35 AM

Just came

Points:: 8
Joined:: Apr 2, 2012

One more error while trying to ins ert records:

Code
INS ERT IN TO "Meta"."table" (id,databaseName,databaseServerRoleNonProd,databaseServerRoleProd,isActiveNonProd,isActiveProd) VALUES(23,'a','a','a',TRUE,TRUE)

Code
ODBC error. SQLSTATE: 42S22 Error number: 207 Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'TRUE'.

`TRUE` is not a valid value for SQL Server. It should be 1/0 values.

Profile

Alexey Bochkov

Nov 23, 2019 01:50 AM

Just came

Points:: 8
Joined:: Apr 2, 2012

Same issues with different driver:

Code
ODBC error. SQLSTATE: 42000 Error number: 102 Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'T1'.

Profile

Alexey Bochkov

Nov 23, 2019 02:18 AM

Just came

Points:: 8
Joined:: Apr 2, 2012

Same issue with 8.3.16.1063 (which is not available to download here yet):

Code

Platform: 1C:Enterprise 8.3, training version (8.3.16.1063)
Configuration: Configuration
Mode: File (without compression)
Application: Thin client
Localization: Infobase: English (United States), Session: English
Interface mode: Taxi

Errors:
--------------------------------------------------------------------------------
11/22/2019 3:16:51 PM
External database error:
error executing query
, reason:
ODBC error. SQLSTATE: 42000
Error number: 102
Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'T1'.

Profile

Aleksandr Biryukov	#5 Nov 25, 2019 05:47 PM
Administrator Points:: 0 Joined:: Oct 3, 2019	Hi Alexey! Can you send me your 1C code? And, if possible, SQL dump? Aleksandr.
	Profile

Alexey Bochkov	#6 Nov 26, 2019 02:07 AM
Just came Points:: 8 Joined:: Apr 2, 2012	Aleksandr, Here it is. You need SQL 2017 to restore the sql database.
	Profile

Aleksandr Biryukov	#7 Nov 26, 2019 08:22 AM
Administrator Points:: 0 Joined:: Oct 3, 2019	Alexey, thanks! I`ll reply in a few days
	Profile

Aleksandr Biryukov	#8 Nov 29, 2019 05:15 PM
Administrator Points:: 0 Joined:: Oct 3, 2019	Alexey, unfortunately, I was not able to simulate the error that occurs to you. Can you do it on version 8.3.14?
	Profile

Aleksandr Biryukov	#9 Nov 29, 2019 05:24 PM
Administrator Points:: 0 Joined:: Oct 3, 2019	By the way, there is another way to update external data: you can create a stored procedure on SQL Server, and this procedure will update the necessary data.
	Profile

Alexey Bochkov	#10 Dec 03, 2019 11:01 PM
Just came Points:: 8 Joined:: Apr 2, 2012	Aleksandr, Thanks for your reply. Is there anything I can do to help you track down this issue? Maybe enable and collect tech log? With what configuration? Since you've released an ability to update an external data source, I've tried it on different platforms and computers but every time had these errors. Was hope 8.3.16 is finally stable.. but I don't discount an option that I'm doing something wrong.
	Profile

Alexey Bochkov

#11

Dec 03, 2019 11:02 PM

Just came

Points:: 8
Joined:: Apr 2, 2012

Quote
By the way, there is another way to update external data:

I'm not that desperate... this will defeat the whole purpose of having simple 1C UI on top of external data source and make code unnecessary more complex.

Profile

Alexey Bochkov	#12 Dec 04, 2019 09:58 PM
Just came Points:: 8 Joined:: Apr 2, 2012	Aleksandr, I turned out that it was my fault indeed - seems that `DBMS type` parameter is mandatory. It was set in Designer.. Anyway, it works now. Thank a lot!
	Profile

Aleksandr Biryukov	#13 Dec 05, 2019 12:17 PM
Administrator Points:: 0 Joined:: Oct 3, 2019	Hi Alexey, Glad to hear it! It`s fine!
	Profile

Aleksandr Biryukov

#14

Dec 05, 2019 12:20 PM

Administrator

Points:: 0
Joined:: Oct 3, 2019

Quote
I'm not that desperate... this will defeat the whole purpose of having simple 1C UI on top of external data source and make code unnecessary more complex.

I agree with you that this will complicate the work. But such a solution will not depend on possible 1C bugs :-)

Profile