Carrage Returns, Stored Procedures

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, lastupdate
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));
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. and have
it also update the field [lastupdate]
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.
Thank You,
DaveAssuming it is a VARCHAR(8000),
CREATE PROCEDURE dbo.addDetails
(
@.mitTicketID INT,
@.detail VARCHAR(255)
)
AS
BEGIN
DECLARE @.append VARCHAR(512)
SET @.append = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ CONVERT(CHAR(10), GETDATE(), 101) + ' '
+ STUFF(RIGHT(CONVERT(VARCHAR(255), GETDATE(), 109), 14), 9, 4, ' ')
+ CHAR(13) + CHAR(10) + @.detail
UPDATE mitTickets
SET
details = details + @.append,
lastUpdate = GETDATE()
WHERE ID = @.mitTicketID
END
GO
You can change all that formatting to this, if you're happy with MMM DD
YYYY HH:MMAM format:
CONVERT(VARCHAR(26), GETDATE())
I don't know what you mean by overhead and wasted space; if you want a
carriage return in the column, there isn't a more efficient way than putting
a carriage return in the column. Not sure what kind of magic you might be
expecting there?
In any case, I think this should be redesigned. Why do you want to store
all that detail in a huge column? That column almost looks like a table.
Why don't you store detail rows in a relational table... it will make the
handling of adding rows much easier, it will make reporting easier, it will
certainly make *removal* of older data easier, it will avoid the problems
you'll have when you have to make it TEXT because all of your data reaches
8000 characters at some point, and it will completely eliminate the
redundancy of having a lastupdate column.
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.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, lastupdate
> 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));
>
> 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. and
have
> it also update the field [lastupdate]
> 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.
>
> Thank You,
> Dave
>|||Hi
I am not sure why you wish to add this to the database as a single field.
What would happend if you wanted to query the data e.g all activity on a
certain day?
It would be better to add the carriage returned on output, either when
selecting the data or probably more preferably through the user interface.
You can use the getdate() function to get the current system date and time.
John
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.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, lastupdate
> 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));
>
> 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. and
have
> it also update the field [lastupdate]
> 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.
>
> Thank You,
> Dave
>

Carrage Returns, Stored Procedures

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.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" <dduryea@.inetmicro.com> wrote in message
news:sXWfb.50789$nU6.8336240@.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.|||new (dduryea@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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

Care and Feeding of Databases

I've been wrestling with some questions about databases.
1. I allow my databases to grow 10% and I find that I will have tons of
excess space according to EM database properties. I try to shrink but
it doesn't. I try a full databack up and transaction backup, but the
shrink still doens't help. (I understand that it may be bad form to
allow the database to automaticly grow, however I wish to learn of
whats going on here. Changing the option to prevent growth wont teach
me anything) ;)
2. Because of the above situation, I've found a brute force way: Create
a smaller database (the original size) and use Import/Export to copy
all the data and objects. However, many times this process will fail
with vague errors, (when I check for details about the error, it just
restates it had an error, nothing I could sink my teeth into).

Anyone have links or info that speaks to these situations, or "Do's and
Dont's" about database maintance?
TIA1. It's not really clear (to me) what the exact problem is. Do you mean
that you 'successfully' shrank the database, but the file sizes didn't
change? If so, how did you shink it - with Enterprise Manager, or with
DBCC SHRINKDATABASE/SHRINKFILE (see Books Online)? Did you get any
error messages? How big is the database, and what size are you trying
to shrink it to?

2. No idea - what exactly are you doing, and what are the errors?

You might find these links useful:

http://support.microsoft.com/defaul...kb;en-us;315512
http://support.microsoft.com/defaul...kb;en-us;272318
http://support.microsoft.com/defaul...kb;en-us;324432
http://www.sql-server-performance.c...se_settings.asp

Finally, have you installed the latest servicepack? There are a number
of fixes documented in the Knowledge Base related to shrinking
databases.

Simon|||Thanks Simon,
As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
think the original size that I created was 2,048MB. I try to shrink it,
no change in size. I then learned that I have to backup the
transactions so that they can be flagged as clearable (not sure the
exact terms). So I did a backup via EM, shrink still wont work (via
EM). I dont get errors, i just dont get the files to shrink in size. I
will say, that I have shrunk the databases in the past succesfully, it
just seems hit or miss.
I know point 2 is very vague, I'll try to recreate and post follow ups.
running with SP4.
Thanks for the links
Rob|||Simon,
The third article you listed is esentially what I've been doing...
Thanks!
Rob|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1119367627.676850.223550@.g44g2000cwa.googlegr oups.com...
> Thanks Simon,
> As an example: Databae DS_V5_TARGET is 23,328MB with 16,086 MB free. I
> think the original size that I created was 2,048MB. I try to shrink it,
> no change in size. I then learned that I have to backup the
> transactions so that they can be flagged as clearable (not sure the
> exact terms). So I did a backup via EM, shrink still wont work (via
> EM). I dont get errors, i just dont get the files to shrink in size. I
> will say, that I have shrunk the databases in the past succesfully, it
> just seems hit or miss.

First question:
Why are you letting it grow in the first place. You're better off
keeping it one size. (i.e. not growing and shrinking it.)

Second question: Where is most of the space, in the DB file or the log file?
If it's the log file most likely the "virtual log" is at the end of the
physical log file. (I thought SQL 2000 "fixed" this issue but I haven't
really looked into it.)

Suggestion:
Don't let DB grow by 10% if you do insist on using autogrowth. Use a fixed
amount. Otherwise each time it grows it'll grow by a larger amount each
time.

> I know point 2 is very vague, I'll try to recreate and post follow ups.
> running with SP4.
> Thanks for the links
> Rob

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter
>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>