Re: Update string too long?
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 02/15/05
- Next message: Louis Davidson: "Re: Tips for Tuning ??"
- Previous message: Louis Davidson: "Re: procedure tuning"
- In reply to: David Gugick: "Re: Update string too long?"
- Next in thread: Sue: "Re: Update string too long?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Feb 2005 22:32:48 -0500
> And we won't question to reason for this routine as you asked...
>
That is going to be hard, but since you promised :)
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Blog - http://spaces.msn.com/members/drsql/ Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "David Gugick" <davidg-nospam@imceda.com> wrote in message news:%23bABxpwEFHA.2572@tk2msftngp13.phx.gbl... > Sue wrote: >> Hello. >> >> I need to take information from several columns, delimit the values >> with commas, and put the results in another column. I am using joins >> etc to filter only the rows to update. When I do something like: >> >> UPDATE dbo.MSP_TASKS >> SET dbo.MSP_TASKS.EXT_EDIT_REF_DATA = '1, ' + >> CAST(dbo.MSP_TASKS.TASK_ACT_WORK AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_WORK AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_REM_WORK AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_DUR AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_ACT_DUR AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_REM_DUR AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_START_DATE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_FINISH_DATE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_CONSTRAINT_TYPE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_CONSTRAINT_DATE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_ACT_START AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_ACT_FINISH AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_STOP_DATE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_RESUME_DATE AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_PCT_COMP AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_COST AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_FIXED_COST AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_ACT_COST AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_LEVELING_DELAY AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_PCT_WORK_COMP AS VARCHAR(30)) + ', ' + >> CAST(dbo.MSP_TASKS.TASK_FIXED_COST_ACCRUAL AS VARCHAR(30)) >> FROM dbo.MSP_PROJECTS INNER JOIN >> dbo.MSP_TASKS >> ON dbo.MSP_PROJECTS.PROJ_ID = dbo.MSP_TASKS.PROJ_ID >> >> WIth this script, I am getting NULL in the >> dbo.MSP_TASKS.EXT_EDIT_REF_DATA column. I am using the CAST >> statement because it wasn't allowing me to join the values otherwise. >> I did discover that if i do not use all the columns(fields?) It does >> the update fine. I have tried reducing the VARCHAR values, but it >> isn't enough. Another point is that from code (PHP for this part I >> believe), the update works just fine. I am just trying to run this >> once for now, but would like to make this a stored procedure for >> future programming needs. >> >> Sorry if this question is so simple that I should have been able to >> find the solution elsewhere. Also if I didnt use the terminology >> correctly. I am only trying to get this resolved, not to be judged >> for what I don't know yet. This board/newsgroup/discussion group is a >> bit intimidating. > > We are all sweet. > > If any of those columns have a NULL value when concatenating, you'll get > NULL in the final column. > > Select 'n' + 'u' + 'l' + null -- This returns NULL > > To prevent this add an ISNULL() function around each column and set the > second parameter to '' (quote-quote). > > BTW, what is the data type and length for the > dbo.MSP_TASKS.EXT_EDIT_REF_DATA column you are inserting into? Can it > support the full length of the inserted data? > > And we won't question to reason for this routine as you asked... > > -- > David Gugick > Imceda Software > www.imceda.com
- Next message: Louis Davidson: "Re: Tips for Tuning ??"
- Previous message: Louis Davidson: "Re: procedure tuning"
- In reply to: David Gugick: "Re: Update string too long?"
- Next in thread: Sue: "Re: Update string too long?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
