Get along with image sql server linked servers from PostgreSQL

Question

When obtaining an image grabde with sql server linked servers from PostgreSQL, I get the following error: OLE DB provider 'MSDASQL' for linked server 'bd_acceso_ruisegip' returned data that does not match expected data length for column '[MSDASQL] . fot_imagen '. The data length (maximum) expected is 255 and the data returned is 38471.

Answer 1

I found a similar issue when replicating some Forum data from PostgreSQL to MSSQL, using the PostgreSQL 64-bit driver and a Linked Server (.

When I coded like this:...

select * into Post from OpenQuery(PostgreSQL_Test1, 'select * From public.post')

... the MSSQL table defaulted to a column size of nvarchar(4000).

My fix: First, run it once with a small limit on the number of rows copied:

select * into Post from OpenQuery(PostgreSQL_Test1, 'select * From public.post limit 10')

Next, right-click on the local Post table. Choose "Script table as drop and create" In the create script, replace the size of the offending column with VARCHAR(MAX)

Next, create the table. Then use:

Insert Post select * from OpenQuery(PostgreSQL_Test1, 'select * From public.post')

Hope that helps. Your mileage may vary.

Answer 2

Don't know if you were dealing with a bytea column but I was having the same problem. Found the answer in the configuring of the postrgres ODBC system dsn. Under the Options/Datasource-page 2 there is a option for bytea as LO. Clicked that and now it works like a champ.