Querying 1C PostgreSQL database from MSSQL via ODBC



The 1C:Enterprise developers forum

#1
People who like this:0Yes/0No
Just came
Rating: 1
Joined: Jul 8, 2015
Company:

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.

 
#2
People who like this:0Yes/0No
Administrator
Rating: 22
Joined: Dec 4, 2017
Company:

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

1C Company support team
 
#3
People who like this:0Yes/0No
Active user
Rating: 4
Joined: Mar 10, 2017
Company: Rufinor

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.

 
Subscribe
Users browsing this topic (guests: 1, registered: 0, hidden: 0)