DTS dropping chars at 255

Question

Post reply

DTS dropping chars at 255

chrismol

SSC-Addicted

Points: 473
More actions
December 4, 2009 at 6:31 pm

#214549

I have a DTS job setup to drop/create tables and download data from MySQL database on a hosted server. One of the fields I am downloading is a text field that frequently has more then 255 chars in it. During the DTS download, all values in this field get cut off at 255 chars. This is a problem because this field contains survey comments and my suers need to see the entire lenght of the field.

Is there a fix for this? I tried converting and casting the field to a varchar(1000) but that did not work. Then I tried populating the first 8 rows of this column with values greater than 255 chars thinking it used the first 8 rows as a sample.

Thanks,

C

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

How do you know the field is being cut off at 255 characters? By looking at it in Query Analyzer? If so, the default for Query Analyzer is to display on 256 characters. You'll need to change the default (Tools, Options, Results, Maximum Characters per Column) to something else (max of 8192). Do note the max... if a column is larger still, then use DATALENGTH to determine the actual length of the content.

I'm not sure but I also believe that DTS defaults to 255 or 8000 on most character based things but I don't know enough about DTS to be able to give you advise there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

chrismol SSC-Addicted Points: 473 More actions · Answer 2

Jeff,

Yes, I was viewing in Query Analyzer. After making that Tools change you suggested, I see that all the chars are actually there, so it's not the DTS job that is cutting off chars. Thanks for pointing that out.

Now my problem is that my users connect to SQL Server 2000 via ODBC with MS Access 2003 and 2007. When they query this same text field and get a result in Access, the text is cut off at 255 chars.

Any idea how to fix this in Access?

Thanks,

C

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

No, sorry. I suspect there's a similar setting but don't know for sure. Might actually have to create a "report" for them which uses a TEXT(xxxx) definition for the field.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

chrismol SSC-Addicted Points: 473 More actions · Answer 4

Ok,thanks. I did setup an Access Report and all the chars came through with fields > 256 chars. However, I had to run the report on the linked table and not on the query. It seems the Access query is the roadblock that does not allow more then 256 chars in a field. There does not seem to be a way (that I know of) to add parameters to a report in Access, unless of course, I'm missing something in Access reports. Any ideas?

This is maddening that Access reports can easily show more then 256 chars but queries cannot. Don't know where to go from here...

C

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I'm not sure either. You might want to post the question on the Access forum of this site to get better visibility for your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions