notes field truncated

[nyzja]

Hello! I have a SQL select script to get the ID and description (along with a few more fields). If I run the script in Query view of the table in SQL Server Enterprise Manager, I get the description fine. But if I run the same script in SQL Query Analyzer, the description field gets truncated.

I have to run this in a DTS package to export to a .txt file. But my resulting text file also gives me a truncated description.

My SQL looks like this:

SELECT ID, CONVERT(nvarchar(2000), REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(2000), DESCRIPTION), CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '')) AS DESCRIPTION
FROM table1


Please advise. Thanks!

 

[druer]

If I recall correctly Query Analyzer only shows the first 255 (I think) of textual datatypes. Is that about what you are seeing, or are you seeing a truncation at like 10 or something variable?

 

[gmmastros]

How big is your data?

Select Max(DataLength(Description)) From Table1

You are converting to nvarChar(2000) so if the datalength is greater than 2000, your query is truncating it.

Also, QA truncates your data (when it is displayed, not in the database). To view more data...

Click Tools -> Options
Results tab

change 'Maximum Characters per column'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nyzja]

That would be about right. So Query Analyzer shows only the first 255 characters?

What about in the DTS? I had the destination of my script to go to a text file. And in the text file, it's also truncated to about the same amount of characters even though I had defined the destination column to be quotable 2000 characters.

Thanks!

 

[nyzja]

My max datalength is only 290 characters. I also tried to convert to nvarChar(4000), after all the replace, but it still truncates to the same spot... about 255 characters...

 

[druer]

Try changing the output character max in query analyzer and run your query again.

If you see them all then there is obviously something limiting them in DTS (I'm no expert in DTS and perhaps George will say "yes there is a limit just like in QA). However, if QA still shows a limit it could be that there is a "funny" character embedded in the text causing it to be truncated.

 

[nyzja]

Yeah, I was able to see the whole description in QA when I changed the output character to 300. So it really comes down to the DTS...