About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Thursday, May 21, 2009

Link Server limited return row size from Informix

So I ran into the following error while trying to run a query.

"Maximum output rowsize (32767) exceeded"

The problem with this, was that I was using a SQL SERVER link server, called ETS, to an Informix database. The problem was that I could not limit the size on the "Notes", so basically I ended up using an exec and open query just to accomplish this task.



declare @Query varchar(max)

set @Query = N'select

wrkhdr.region_no,

notif.atten,

notif.dba,

notif.owner,

notif.addr1,

notif.addr2,

notif.city,

notif.state,

notif.country,

notif.postal_code,

notif.notif_id,

notif.notif_date,

notif.start_date,

notif.end_date,

wrkhdr.create_user,

wrkhdr.rid,

wrkhdr.license_no,

notif.license_status,

rtrim(CAST(notes.note as nvarchar(250)))

FROM

notification notif

join workq_hdr wrkhdr ON wrkhdr.workq_id = notif.workq_id

left join document_header ON document_header.filing_id = wrkhdr.filing_id

left join document_header_notes ON document_header.recno = document_header_notes.document_header_notes_id

left join notes ON document_header_notes.notes = notes.note_id

where notif.notif_id = ' + convert(varchar(30),@Parameter1)

set @Query = N'select * from openquery(ets2, ''' + REPLACE(@Query, '''', '''''') + ''')'

exec (@Query)