Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

selecting column cuts off part of the data

Posted on 2014-03-11
3
Medium Priority
?
579 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
  • 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 34

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

963 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