Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Varchar(max) to add to a field? Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 3:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:56 AM
Points: 279, Visits: 382
Merry Holidays everyone, I've created a basic table which includes a text field. I then created a SP which basically read the text field and then added to the same field, the problem is I get an error that says I can't use TEXT or Varchar(Max), is there a way round this?

Table

CREATE TABLE KLN
(RecId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Dte DATETIME,
machineid VARCHAR(50),
Userid VARCHAR(50),
memo text)



Stored Procedure
CREATE  PROCEDURE [dbo].[Save_kln]
@sDte as datetime,
@smachineid as varchar(50),
@sUserid as varchar(50),
@smemo as text

AS
DECLARE @sRESULTS varchar(MAX)
if exists (select * from [kln] where [dte] = @sdte and Userid = @Suserid)
begin
set @sRESULTS = (select memo from [kln] where [dte] = @sdte and Userid = @Suserid)
update [kln] set memo = @sRESULTS + @smemo where [dte] = @sdte and Userid = @Suserid
end
else
begin
INSERT INTO [kln] (dte,machineid,userid,memo) VALUES (@sdte,@smachineid,@suserid,@smemo)
end

Post #1400544
Posted Thursday, December 27, 2012 4:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
You shouldn't be using TEXT in the first place, it's an old SQL 2000 data type, included only for backward compatibility. Use Varchar(max)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1400546
Posted Thursday, December 27, 2012 4:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:56 AM
Points: 279, Visits: 382
GilaMonster (12/27/2012)
You shouldn't be using TEXT in the first place, it's an old SQL 2000 data type, included only for backward compatibility. Use Varchar(max)



Varchar(Max) isn't allowed in my situation either
Post #1400547
Posted Thursday, December 27, 2012 4:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Post your code with the varchar(max) used and post the exact error message


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1400549
Posted Thursday, December 27, 2012 4:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:56 AM
Points: 279, Visits: 382
turns out what you suggested did work after all, I'd like to thank you for your help
Post #1400554
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse