Carrage Returns, Stored Procedures

[new]

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.

Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lupdate
Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.
I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following.

10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.

10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.

10/4/2003 6:27:08 AM
Backup failed.

[new]

Sorry, I meant to post my Stored Procedure

UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));

"new" <dd*****@inetmicro.com> wrote in message
news:sX*********************@twister.nyc.rr.com...

Question:
What would be the best way to add carrage returns to a record, and would my method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.

Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lupdate
Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.
I would like to create a stored procedure that will append the current Date and Time to each update that is being submitted via a web form. I only want the web form to add text and have the stored procedure append the input text the the existing record so that the data looks like the following.

10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.

10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.

10/4/2003 6:27:08 AM
Backup failed.

[Erland Sommarskog]

new (dd*****@inetmicro.com) writes:

Sorry, I meant to post my Stored Procedure

UPDATE [mitTickets]
SET [mitTickets].details = Now() & Chr(13) + Chr(10) + [@detailstxtbox] +
Chr(13) + Chr(10) + Chr(13) + Chr(10) +
[description],
[mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));

The CRLF are alright, but there are a coupld of other errors:

o There is no Now() in SQL Server. Use
convert(varchar(20), getdate, 121) to get the date.
o & is an operator for bitwise and. You want + for string concatenation.
o [@detailslistbox] will resolve to a column with the name
@detailslistbox. If you want refer to a variable, remove the brackets.
o While legal here, it is best to leave out the table name on the left-
hand side of the SET clause. You can only update the columns of one
table at a time, so the name is redundant here.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Manoj Rajshekar]

Hi,

Use a trigger like the following to do this.

CREATE TRIGGER mitTickets_inserted_time ON mitTickets
FOR insert
AS UPDATE mitTickets
SET lupdate= GETDATE()
WHERE problem in (SELECT problem FROM INSERTED)
That will take care of it.

Regards,
-Manoj