Alexey Bochkov
Nov 23, 2019 01:31 AM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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'.
Alexey Bochkov
Nov 23, 2019 01:35 AM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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.
Edited: Alexey Bochkov
- Nov 23, 2019 01:40 AM
Alexey Bochkov
Nov 23, 2019 01:50 AM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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'.
Alexey Bochkov
Nov 23, 2019 02:18 AM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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'.
Aleksandr Biryukov
Nov 25, 2019 05:47 PM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
Hi Alexey! Can you send me your 1C code? And, if possible, SQL dump? Aleksandr.
Alexey Bochkov
Nov 26, 2019 02:07 AM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
Aleksandr, Here it is. You need SQL 2017 to restore the sql database.
Aleksandr Biryukov
Nov 26, 2019 08:22 AM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
Alexey, thanks! I`ll reply in a few days
Aleksandr Biryukov
Nov 29, 2019 05:15 PM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
Alexey, unfortunately, I was not able to simulate the error that occurs to you. Can you do it on version 8.3.14?
Aleksandr Biryukov
Nov 29, 2019 05:24 PM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
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.
Alexey Bochkov
Dec 03, 2019 11:01 PM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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.
Edited: Alexey Bochkov
- Dec 03, 2019 11:05 PM
Alexey Bochkov
Dec 03, 2019 11:02 PM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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.
Edited: Alexey Bochkov
- Dec 03, 2019 11:03 PM
Alexey Bochkov
Dec 04, 2019 09:58 PM
Just came
Rating:
1
Joined: Apr 2, 2012
Company: BizzSimple.com
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!
Edited: Alexey Bochkov
- Dec 04, 2019 09:58 PM
Aleksandr Biryukov
Dec 05, 2019 12:17 PM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
Hi Alexey, Glad to hear it! It`s fine!
Aleksandr Biryukov
Dec 05, 2019 12:20 PM
Administrator
Rating:
23
Joined: Oct 3, 2019
Company:
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
Subscribe