Solved

selecting column cuts off part of the data

Posted on 2014-03-11
3
550 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

630 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