Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

selecting column cuts off part of the data

Posted on 2014-03-11
3
Medium Priority
?
567 Views
Last Modified: 2014-03-11
I have a stored procedure and in it, I create a temp table, then dump some data into it. When I select from that temp table, one of my fields is getting cut off, even though my column definition is a lot more than the number of chars in the field. here's the code in question:

create table #tblResults (profilePicture varchar( 100 ), userDetails nvarchar(4000), offerText varchar( 20 ), goods varchar( 500 ), services varchar( 500 ), 
                         borrow varchar( 500 ), teach varchar( 500 ) );  

insert into #tblResults 
SELECT 
     userDetails = '<a href="/profile.asp?id=' + cast( u1.userID as varchar ) + '">' + u1.displayName + '</a>:&nbsp;' + cast( u2.aboutMe as varchar )
FROM #tblMatchedUsers u1 inner join tblUsers u2 on u1.userID = u2.userID 

Open in new window


the field "aboutMe" is the field that's getting cut off. In the table tblUsers, it is defined as a text field, and I'm assuming that's where the problem lies, even though the data in the record i'm testing is about 30 chars long.

Is there a way I can cast the whole thing as a text? when I tried that, I got errors.

Any help is greatly appreciated!

Cheers
Josh
0
Comment
Question by:Big Monty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39920760
When n is not specified when using the CAST and CONVERT functions, the default length is 30. ( see first line after Remarks )

Looks like in your SELECT line you are CAST( as varchar), but not defining the length, so the default of 30 kicks in.
0
 
LVL 33

Author Closing Comment

by:Big Monty
ID: 39920772
did not know that was the default, thx!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39920856
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question