Problems with free text feild
-
Hello every one,
I'm having problem here concatenating 3 varchar(2000) free text fields into filed type text.
the problem is that I'm not getting all the data that I should ,its just not adding the last filed which I dont know why??
any help will be really appreciated
Thanks
Alaa
-
We need table DDL to answer this one.
One thing to check out is the make sure that no column is of unicode datatype (nchar, nvarchar) and not even some constant concatenation you may be adding to this. That would cause you to be limited at 4000 characters.
-
I dont have any unicode datatype but I do have constant concatenation and I dont see why I shouldn't have any if I'm dumping my 3 fileds + the constant concatenation into feild type text?
Thanks
Alaa
-
Can you post the code so we can look at it. We'll explain as we go along.
Short story : varcharCol1 + UnicodeStaticString + varcharCol2 ... = Unicode = 4000 chars max.
-
Hi,
my code is
-
- VARIABLES
-- Source Table Variables
Declare
@day_notes varchar(2500),
@nite_notes varchar(2500),
@general_notes varchar(2500),
@info varchar(8000),
@bfr_tag varchar(2000),
@aft_tag varchar(2000),
@spc_tag varchar(200),
@string varchar(8000)
-----------------------------------------------------------------------------------------------------------
-- STATIC VARIABLES
Set @bfr_tag = '{\rtf1\ansi\deff0{\fonttbl{\f0\froman Tms Rmn;}{\f1\fswiss Arial;}}{\colortbl\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue127;\red0\green127\blue127;\red0\green127\blue0;\red127\green0\blue127;\red127\green0\blue0;\red127\green127\blue0;\red127\green127\blue127;\red192\green192\blue192;}{\info{\creatim\yr1996\mo9\dy6\hr10\min22\sec23}{\version1}{\vern262367}}\paperw12240\paperh15840\margl360\margr3300\margt360\margb0\deftab720\pard\ql\li0\fi0\ri2992{\f1\fs20\cf5\up0\dn0 \loch\af1 '
Set @spc_tag=' }{\par}\pard\ql\tx2880{\f0\fs20\cf0\up0\dn0 \loch\af0 '
Set @aft_tag =' }{\f0\fs20\cf0\up0\dn0 \loch\af0 }}'
-----------------------------------------------------------------------------------------------------------
Set NoCount On
Declare MainCursor Cursor For
SELECT GENERAL_NOTES,DAY_NOTES, NITE_NOTES
FROM dbo.Mytable
Open MainCursor
Fetch Next From MainCursor Into @general_notes,@day_notes,@nite_notes
While @@Fetch_Status = 0
Begin
Set @string =null
if (isnull(@day_notes,'') not like '' OR isnull(@general_notes,'') not like'' OR isnull(@nite_notes,'') not like '')
BEGIN
IF ISNULL(@GENERAL_NOTES,'') NOT LIKE ''
Select @GENERAL_NOTES =@general_notes
ELSE
Select @GENERAL_NOTES =' '
IF ISNULL(@DAY_NOTES,'') NOT LIKE ''
Select @DAY_NOTES = @DAY_NOTES
ELSE
Select @DAY_NOTES =' '
IF ISNULL(@NITE_NOTES,'') NOT LIKE ''
Select @NITE_NOTES = @NITE_NOTES
ELSE
Select @NITE_NOTES =' '
END
Select @info-2 = 'GENERAL NOTES: '+ @general_notes + @spc_tag+@day_notes+@spc_tag + @nite_notes
insert into general_info (geninfo_id,info)
select @geninfo_id,@bfr_tag + 'GENERAL NOTES: '+ @general_notes + @spc_tag+@day_notes+@spc_tag + @nite_notes
Fetch Next From MainCursor Into @general_notes,@day_notes,@nite_notes
End
Close MainCursor
Deallocate MainCursor
the @bfr_tag ,@aft_tag and @spc_tag are special strings used to enable my text in an rtf format as the front end required
hope that will help ..
Thanks,
Alaa
-
Can you supply the table DDL please?
TIA.
-
First, you have a major bug in the code you posted... you don't create a value for @GenInfo_ID variable which means that you are populating the GenInfo_ID column of the General_Info table with NULLs. Either that, or you didn't post all the code
Second, you never use some of the variables... of partcular concern is the @Aft_Tag variable.
Third, this type of formatting should really be done in the GUI... not in SQL.
Fourth, and this is going to sound a bit mean... but why in the heck are you using a
cursor 
for this? 
The following code is the set-based version of your code...
--===== Supress the autodisplay of rowcounts
SET NOCOUNT ON
--===== Declare local variables
DECLARE @Bfr_Tag VARCHAR(1000)
DECLARE @Aft_Tag VARCHAR(100)
DECLARE @Spc_Tag VARCHAR(100)
--===== Preset the static variables
SET @Bfr_Tag = '{\rtf1\ansi\deff0{\fonttbl{\f0\froman Tms Rmn;}{\f1\fswiss Arial;}}{\colortbl\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue127;\red0\green127\blue127;\red0\green127\blue0;\red127\green0\blue127;\red127\green0\blue0;\red127\green127\blue0;\red127\green127\blue127;\red192\green192\blue192;}{\info{\creatim\yr1996\mo9\dy6\hr10\min22\sec23}{\version1}{\vern262367}}\paperw12240\paperh15840\margl360\margr3300\margt360\margb0\deftab720\pard\ql\li0\fi0\ri2992{\f1\fs20\cf5\up0\dn0 \loch\af1 '
SET @Spc_Tag = ' }{\par}\pard\ql\tx2880{\f0\fs20\cf0\up0\dn0 \loch\af0 '
SET @Aft_Tag = ' }{\f0\fs20\cf0\up0\dn0 \loch\af0 }}'
--===== Do the concatenation and insert to the table all in one step.
-- You must have a good understanding of NULLs to understand how it works.
INSERT INTO General_Info (GenInfo_ID,Info)
SELECT GenInfo_ID = ????????, --YOU NEED TO REPLACE THE QUESTION MARKS WITH SOMETHING!!!!
Info = @Bfr_Tag
+ 'GENERAL NOTES: '
+ ISNULL(General_Notes,' ') + @Spc_Tag
+ ISNULL(Day_Notes,' ') + @Spc_Tag
+ ISNULL(Nite_Notes,' ')
FROM dbo.Mytable
WHERE General_Notes > '' --Faster than IS NOT NULL and checks for blanks, too
OR Day_Notes > '' --Faster than IS NOT NULL and checks for blanks, too
OR Nite_Notes > '' --Faster than IS NOT NULL and checks for blanks, too
...and you STILL need to figure out what to do about the GenInfo_ID ... stongly suggest you look into what an IDENTITY column is.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff for your reply,
I have a value for the @geninfo_id but I didn't post it ,actually I thad to take off some of the codes to just simplify it .and I have to use cursors becuase i'm dealing with data conversion between two databases and that is required from me as a developer to do it in SQL too..
Thanks again
-
Gonna save Jeff some typing.
You still don't need a cursor for that conversion. Use temp tables and batch convert the data before final import.
-
Remi is spot on both in statement and method suggested... you still don't need a cursor to do that. Do like remi recommended...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks everyone for the suggestions ..I will give it closer lock but if I worked on avoiding the cursors would it help solving the problem ??
Thanks
Alaa
-
Not likely, but it would potentially avoid many others!
-
Based on the original code you posted, you shouldn't have a problem...
The @bfr_tag holds 634 characters.
@general_notes holds a max of 2500 characters.
@day_notes holds a max of 2500 characters.
@nite_notes holds a max of 2500 characters.
@spc_tag holds 56 characters 2 times for a total of 102 characters.
@aft_tag holds 38 characters.
Thats a total of 7640 characters... well within the 8000 character limit for concatenation.
Two things could be happening... either your output is being truncated by the default limits of character width for a given column (can be changed to 8060), or the code you posted doesn't have everything that's being added.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply