MS 255 Char limit (1 Viewer)

[FoFa]

Way around this?

DECLARE @sVar Char(500)

SET @sVar space(500)

SET @sVar = STUFF(@sVar,250,16,'ABCDEFGHIJKLMNOP')
PRINT @sVar
Shows a bunch a spaces with the ABC... in 250 through 266
INSERT INTO Table (My500Col) values(@sVar)

The data in the My500Col is truncated at 255 (I.E. shows ABCDEF and then is empty)

So what is up with that?

Breaking into two variables (each 250) and doing a:
INSERT INTO Table (My500Col) values(@sVar1 + @sVar2)
Does the same thing.


Also once the actual data is in (two columns of char(250) each) and you try to pull it out as one field:
SELECT My250Col1 + My250Col2
It truncates at the same 255 point.

So what IS up with that?

 

[SQL_Hell]

Hi there

Is this 255 char truncation happpening when using QA or EM?

I think QA and EM have a 255 char limit on viewable fields, maybe this wont be the case in an application.

 

[pdx_man]

Tools ... Options ... Results ... Maximum Characters per Column

The default is set to 255. I changed mine to 8000 when I came across this issue. No problems since.

 

[FoFa]

Hi there

Is this 255 char truncation happpening when using QA or EM?

I think QA and EM have a 255 char limit on viewable fields, maybe this wont be the case in an application.

It is in a stored procedure, but I was using QA to analyze what was going on.

 

[FoFa]

Tools ... Options ... Results ... Maximum Characters per Column

The default is set to 255. I changed mine to 8000 when I came across this issue. No problems since.

And this option is where/what?
Since I was running a stored procedure, it must be one of "those" SET options also, huh?

 

[pdx_man]

In Query Analyzer.

 

[SQL_Hell]

Tools ... Options ... Results ... Maximum Characters per Column

The default is set to 255. I changed mine to 8000 when I came across this issue. No problems since.

Excellent, I never knew that was there. That helps me with an issue I am having too, thanks

 

[FoFa]

In Query Analyzer.

That's nice, but how does that stop the truncation issue when a stored procedure is run from a job? (I.E. not in QA)

 

[pdx_man]

It won't truncate from a call from a job to a SP. It is when you are looking at the results through QA. If you want to check it, do a LEN(FieldName). The data is there.

 

[FoFa]

Well maybe it is DTS then. Putting it to a fixed length file, using a SQL select in the transformation package, detination is fixed length positional file of 500 byte rows. Truncates at position 255 (or 256, but you get the drift).

So I changed it to use two variables of 250 char each, breaking it up. Using the TWO varibables, and creating a positional file, it works a champ. BUT now they want me to PUSH it to the Oraclce table directly. Guess what, in my select using COL1 + COL2, it truncates at 255 again.

BTW I set up my QA to 4000 string length, so should not be an issue now even in QA, right?

 

[pdx_man]

It won't be an issue in QA with that setting as long as the field size for COL1 + COL2 doesn't exceed 4000.

Is this the full scope of your issue? Don't mean to sound b!tchy, but first you have TSQL which appears you are running in QA, then, you ask about Stored Procedures only to be followed up by the latest post that your are doing a DTS Transformation and lastly a new curve of pushing the data to Oracle.

As far as DTS goes, check each of the transformations and check the mapping of the field. Are you mapping to Unicode or ANSI? Is this a straight, generic transformation or are you utilizing any code.

I would suggest creating a new package from scratch without anything fancy.

As far as the Oracle element, how are you PUSHing the data? Replication?

 

[FoFa]

Well my first post was the basic code, which I had working, until the format pushed the actual data past the 255 limit. It was a stored procedure extracting data into a 500 varchar variable, and inserting it into a 500 varchar column. We noticed the data in the column was being truncated. The table is used by DTS procedure to push the data to Oracle, but the data was truncated in the SQL table.
I have since recreated the stored procedure and DTS job and it seems to function fine now (don't ask me).