Forum

Querying 1C PostgreSQL database from MSSQL via ODBC

Ged Last	#1 Dec 20, 2018 05:34 AM
Just came Points:: 0 Joined:: Jul 8, 2015	Hello, I have succesfully connected a 1c Postgre SQL database with MSSQL 2012 via ODBC driver, yet when am executing a specific query, I get an error, not sure about the reasons why it shows up. ODBC driver - Postgre SQL Unicode version 11.00.00.00 PSQLODBC35W.DLL 2018-11-17 1C platform version 8.3.5 1517. PostgrePro 9.4, 1C version of course. The query attempted: sel ect * from openquery([TEST2], 'select * fr om baze_testine.public._Document260') Results: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'TEST2' returned data that does not match expected data length for column '[MSDASQL]._number'. The (maximum) expected data length is 14, while the returned data length is 22. The document number is actually 11 symbols long in the documents. Visibly there are no longer numbers. 22 seems like a doubled value. Same error message shows up when trying to query other documents. Would welcome some advice-insight on how to approach this situation/to solve it. Thank you in advance.
	Profile

Vladimir Gurov	#2 Dec 21, 2018 08:08 AM
Interested Points:: 0 Joined:: Dec 4, 2017	Dear Ged Last, We do not provide any technical support on services that directly access 1C:Enterprise infobase's data instead of the native 1C:Enterprise tools. If your task is to simplify access to the data (for instance, in order to generate analytical reports that you intend to build via third-party tools relying upon SQL requests to the data), you can consider using Data Accelerator in conjunction with the database backup utility. Both tools are parts of the forthcoming 1C:Enterprise platform 8.3.14. The database backup utility saves a copy of selected metadata objects to a remote DBMS and can redirect data requests to the DBMS. Data Accelerator is an in memory DBMS optimized for generating analytical reports. Best regards, Vladimir Gurov
	Profile

Alex Ogourok	#3 Jan 31, 2019 09:19 AM
Active user Points:: 0 Joined:: Mar 10, 2017	Hello Ged Last, Different SQL dialects have different data types storage specification. Document number in 1c with the length of 11 characters may be stored differently in MSSQL and PostgreSQL. You need to find specification of data types of MSSQL and PostgreSQL and compare them to each other. If you need to integrate PostgreSQL and MSSQL you better do it via native 1c Enterpise tools.
	Profile