varchar(8000) to Access memo truncated at 255?

Question

varchar(8000) to Access memo truncated at 255?

sqltung

Hall of Fame

Points: 3962
More actions
October 5, 2005 at 4:27 pm

#69656

I'm using an Access 2002 front-end with linked SQL Server 2000 tables (using ODBC dsn).
I've got a SQL Server view with a varchar(8000) column - actual data is only as long as 500 chars - and when it's linked into Access, the data seems to be truncated at about 255 chars. If I go into design view for the linked table in Access, of course it warns me that I can't change the design, but shows this field as a memo.
Anyone have an explanation and/or workaround?
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How are you fetching/presenting the data? Check in the option of query analyzer (if you are using it). There's a maximum column size limit in there of 255 that you can change.

sqltung Hall of Fame Points: 3962 More actions · Answer 2

That column is a comma-delimited list created by a user-defined function that is included in a view. This SQL Server view will show the entire column (if you expand the rows in Enterprise Manager). The view is linked to by Access and the column is included in an Access report. The data is set to wrap (and it does), but quits at 255 or so (I haven't actually counted the chars, but looks like 255).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

October 6, 2005 at 8:49 am

#595504

Can we see the code of the view and the udf?

sqltung Hall of Fame Points: 3962 More actions · Answer 4

If you'd like, but it's not really relevant.

I've tested a little and found that the problem is I'm using a UNION query in Access. I'm pulling data from 6 SQL servers.

If I return data from only 1 server (so it's no longer a UNION query) the result is I get the full text field, which has something around 300 chars as the longest returned field. Once I use the UNION keyword, I get only 255 chars for that field.

So now the question has changed to:

Why are my long varchar (>255) SQL Server fields truncated at 255 chars when using a UNION query in Access?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

cast every select of the union to varchar(8000) and you should be fine.

sqltung Hall of Fame Points: 3962 More actions · Answer 6

sqltung

Hall of Fame

Points: 3962

October 6, 2005 at 9:30 am

#595527

Access doesn't have a varchar(8000).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

The idea is to make sure that each selects have the same datatype/length in the union... This is most likely where the problemis coming from.

sqltung Hall of Fame Points: 3962 More actions · Answer 8

I've got an identical database (for dba use) in 6 SQL Server 2000 servers, so there is no difference in data type; so I don't think that's an issue.

Any other guesses (anyone)?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Is it a straight select of the column or there's some manipulation of it?

sqltung Hall of Fame Points: 3962 More actions · Answer 10

sqltung

Hall of Fame

Points: 3962

October 6, 2005 at 12:00 pm

#595596

Straight select.

sqltung Hall of Fame Points: 3962 More actions · Answer 11

When I use the UNION, I'm getting a truncated JobList field (truncated at 255), yet len(JobList) yields the

correct length.

If I create a new query using just one of the SELECTs from the UNION query, it works ok - JobList field is not

truncated.

These are Access queries using linked SQL Server tables (views in SQL Server):

SELECT "prod" AS server,

prod_vMPs.mpnID,

prod_vMPs.mpnPlanID,

prod_vMPs.mpnName,

prod_vMPs.mpnDateCreated,

prod_vMPs.mpnOwner,

prod_vMPs.JobList, len(prod_vMPs.JobList) AS LenJobList,

prod_vMPs.mpnMaxHistoryRows,

prod_vMPs.mpnRemoteHistoryServer,

prod_vMPs.mpnMaxRemoteHistoryRows

FROM prod_vMPs

UNION SELECT "dev" AS server,

dev_vMPs.mpnID,

dev_vMPs.mpnPlanID,

dev_vMPs.mpnName,

dev_vMPs.mpnDateCreated,

dev_vMPs.mpnOwner,

dev_vMPs.JobList, len(dev_vMPs.JobList) AS LenJobList,

dev_vMPs.mpnMaxHistoryRows,

dev_vMPs.mpnRemoteHistoryServer,

dev_vMPs.mpnMaxRemoteHistoryRows

FROM dev_vMPs;

-- for 1 record of the "dev" SELECT, my JobList and LenJobList fields:

JobList: DB Backup Job for DB Maintenance Plan 'Nightly Backup and Weekly DB Checks',

Integrity Checks Job for DB Maintenance Plan 'Nightly Backup and Weekly DB Checks',

Optimizations Job for DB Maintenance Plan 'Nightly Backup and Weekly DB Checks',

Transaction L

LenJobList: 330

SELECT "dev" AS server,