Column content is truncated when I view but there when I process

Question

Column content is truncated when I view but there when I process

allenb-717661

SSC Journeyman

Points: 98
More actions
November 14, 2014 at 11:16 am

#301126

I'm using 2008 R2 and I have a column with a test field. I know that there are 497 records in the field because when I use a translator it processes all 497 records. When I view the field and copy then paste into a text editor I only see 6 records. I have tried increase the size of the field with no affect.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

allenb-717661 (11/14/2014)
I'm using 2008 R2 and I have a column with a test field. I know that there are 497 records in the field because when I use a translator it processes all 497 records. When I view the field and copy then paste into a text editor I only see 6 records. I have tried increase the size of the field with no affect.

Quick question, can you share some more information on the process, data source etc., without further information there is no way one can provide any assistance.

😎

Lowell SSC Guru Points: 323518 More actions · Answer 2

could it be because the SSMS settings is defaulting to a 256char display limit for a column length, in order to conserve resources?

on both the Results to Grid and Results to Text tab, increase the size to 8000;

SSMS wil ignore any value larger than 8000, if you need data larger than that for copy /paste, you need to send the results to file,a nd opent he file from there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

allenb-717661 SSC Journeyman Points: 98 More actions · Answer 3

these are medical records that I'm receiving from providers using a process called PHINMS. The files come in at all hours and are inserted into a SQL database. The columns include recordId and time received. The coumn that I'm having trouble with is a payloadText content which has text. When I look at the coumn in SQL and copy the content into a text editor only 6 records show up. But when I run some parsing translator on the file there are 497 records processed. So I know that all the records are there but I don't see them in SQL. I think it may have to do with the size of the field and have tried changing with out sucess. I did not have this problem in 2005.

allenb-717661 SSC Journeyman Points: 98 More actions · Answer 4

Thanks SSChampion tried that and no change. How do I send it to a file?

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

allenb-717661 (11/14/2014)
Thanks SSChampion tried that and no change. How do I send it to a file?

Menu:

Tools, Options, Query Results, SQL Server.

There's an option in the very first drop down that lets you put the results out to a file.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

There are also buttons on the SQL Editor toolbar in SSMS that allow you to write the results to text, grid or file. I think the chances are probably good that it's something in the SSMS options like Lowell showed above.

If you're in the US, be careful with writing medical data to a file on your computer - you don't want to get hit with a HIPPA violation. I don't know the medical data laws of other countries.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

allenb-717661 SSC Journeyman Points: 98 More actions · Answer 7

Thanks, I'm well aware of HIPPA deal with it everyday. I figured out how to download to a file. Still working on being able to see the data when viewing SQL.

thanks