How to insert a new unique INT if none is given

I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?

And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like.

Thanks.

In Oracle, you would set up a sequence and ask it for the next number. Piece of cake!

I can think of two ways to do it in Sql Server.

The first is the easiest and, to my mind, the best. Create a Threads table with a ThreadId column that is an identity column. Create a trigger on your Messages table. It's simple, it's clean, and it's the right thing to do. :)

The second is to write a trigger that queries the messages table and returns the highest threadid value you find (+ 1). The problem is that, in a multi-user environment, you will have to lock the entire Messages table first to prevent anyone else from running that query until your insert finishes. Otherwise, you will get two different, unrelated messages with the same thread id. It's nasty, prone to error, and actually harder than doing the right thing.

How to increase table size

Okay, I am actually having 2 problems. We have LANdesk and it uses SQL serve
r
2000 and the people in our desktop department are getting a message which
says:
The size of FixedDrives.Caption is too small. Increase its size by at
least 7.
How do you increase the size of this table.
Problem #2
This is a very recent implementation and I notice that the maintenance plan
will not backup the transaction log. It appears to backup the database ok.
But the transaction log never backs up and it is set to backup every 3 hours
.P#1 : SOund like a column rather than a table problem.
P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? Is
there a job for this ?
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
--
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
> server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
> plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
> hours.|||The error you're getting is from LANdesk, not SQL Server. I suggest you
contact their technical support to see what to do. You can't pre-allocate
space for tables in SQL Server, they automatically grow as you insert more
rows into them.
What recovery mode is the database in? If its simple recovery mode, taking a
log backup is a no-op.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
hours.|||ok, I'm by no means a SQL expert so I'm feeling my way through this and yes
you are rights captions is a column in the fixeddrives table. I right
clicked on the table and chose design table. The captions column data type i
s
char and the length is 60. If I increase it by 7 as the error asks and then
click save, I get a warning during the presave process which goes on to say:
Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
I have no clue what to do from there.
As for the transaction log backup problem, a maintenance plan has been
created to backup the transaction log every 3 hours. It fails everytime. Her
e
are the results:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SVRWEBG026' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
Backup can not be performed on database 'LDINV'. This sub task is ignored.
End of maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any help will be greatly appreciated.
"Jens Sü?meyer" wrote:

> P#1 : SOund like a column rather than a table problem.
> P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? I
s
> there a job for this ?
> HTH, Jens SUessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
>
>|||"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:32826F49-2FBF-48CE-9582-3EC1382455D9@.microsoft.com...
> ok, I'm by no means a SQL expert so I'm feeling my way through this and
> yes
> you are rights captions is a column in the fixeddrives table. I right
> clicked on the table and chose design table. The captions column data type
> is
> char and the length is 60. If I increase it by 7 as the error asks and
> then
> click save, I get a warning during the presave process which goes on to
> say:
You should contact LANDesk Tech Support. Going in and changing the
structure of a database for an application could break the application, or
at the very least, waste a lot of time and space with no positive results.
If their customers have encountered this problem before, they might have a
thoroughly tested fix or patch for the front-end software as well as the
database.|||So, if the database is in simple recovery mode you can not backup the
transaction logs? What can you do to tell what mode it is in? For that matte
r
what are the modes?
Again I'm not the SQL guy.
"Paul S Randal [MS]" wrote:

> The error you're getting is from LANdesk, not SQL Server. I suggest you
> contact their technical support to see what to do. You can't pre-allocate
> space for tables in SQL Server, they automatically grow as you insert more
> rows into them.
> What recovery mode is the database in? If its simple recovery mode, taking
a
> log backup is a no-op.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrot
e
> in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> server
> plan
> hours.
>
>

How to increase table size

Okay, I am actually having 2 problems. We have LANdesk and it uses SQL server
2000 and the people in our desktop department are getting a message which
says:
The size of FixedDrives.Caption is too small. Increase its size by at
least 7.
How do you increase the size of this table.
Problem #2
This is a very recent implementation and I notice that the maintenance plan
will not backup the transaction log. It appears to backup the database ok.
But the transaction log never backs up and it is set to backup every 3 hours.P#1 : SOund like a column rather than a table problem.
P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? Is
there a job for this ?
HTH, Jens SUessmeyer.
--
http://www.sqlserver2005.de
--
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
> server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
> plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
> hours.|||The error you're getting is from LANdesk, not SQL Server. I suggest you
contact their technical support to see what to do. You can't pre-allocate
space for tables in SQL Server, they automatically grow as you insert more
rows into them.
What recovery mode is the database in? If its simple recovery mode, taking a
log backup is a no-op.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
hours.|||ok, I'm by no means a SQL expert so I'm feeling my way through this and yes
you are rights captions is a column in the fixeddrives table. I right
clicked on the table and chose design table. The captions column data type is
char and the length is 60. If I increase it by 7 as the error asks and then
click save, I get a warning during the presave process which goes on to say:
Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
I have no clue what to do from there.
As for the transaction log backup problem, a maintenance plan has been
created to backup the transaction log every 3 hours. It fails everytime. Here
are the results:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SVRWEBG026' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
Backup can not be performed on database 'LDINV'. This sub task is ignored.
End of maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any help will be greatly appreciated.
"Jens Sü�meyer" wrote:
> P#1 : SOund like a column rather than a table problem.
> P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? Is
> there a job for this ?
> HTH, Jens SUessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> > Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
> > server
> > 2000 and the people in our desktop department are getting a message which
> > says:
> >
> > The size of FixedDrives.Caption is too small. Increase its size by at
> > least 7.
> >
> > How do you increase the size of this table.
> >
> > Problem #2
> >
> > This is a very recent implementation and I notice that the maintenance
> > plan
> > will not backup the transaction log. It appears to backup the database ok.
> > But the transaction log never backs up and it is set to backup every 3
> > hours.
>
>|||"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:32826F49-2FBF-48CE-9582-3EC1382455D9@.microsoft.com...
> ok, I'm by no means a SQL expert so I'm feeling my way through this and
> yes
> you are rights captions is a column in the fixeddrives table. I right
> clicked on the table and chose design table. The captions column data type
> is
> char and the length is 60. If I increase it by 7 as the error asks and
> then
> click save, I get a warning during the presave process which goes on to
> say:
You should contact LANDesk Tech Support. Going in and changing the
structure of a database for an application could break the application, or
at the very least, waste a lot of time and space with no positive results.
If their customers have encountered this problem before, they might have a
thoroughly tested fix or patch for the front-end software as well as the
database.|||So, if the database is in simple recovery mode you can not backup the
transaction logs? What can you do to tell what mode it is in? For that matter
what are the modes?
Again I'm not the SQL guy.
"Paul S Randal [MS]" wrote:
> The error you're getting is from LANdesk, not SQL Server. I suggest you
> contact their technical support to see what to do. You can't pre-allocate
> space for tables in SQL Server, they automatically grow as you insert more
> rows into them.
> What recovery mode is the database in? If its simple recovery mode, taking a
> log backup is a no-op.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
> in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> > Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
> server
> > 2000 and the people in our desktop department are getting a message which
> > says:
> >
> > The size of FixedDrives.Caption is too small. Increase its size by at
> > least 7.
> >
> > How do you increase the size of this table.
> >
> > Problem #2
> >
> > This is a very recent implementation and I notice that the maintenance
> plan
> > will not backup the transaction log. It appears to backup the database ok.
> > But the transaction log never backs up and it is set to backup every 3
> hours.
>
>

How to increase table size

Okay, I am actually having 2 problems. We have LANdesk and it uses SQL server
2000 and the people in our desktop department are getting a message which
says:
The size of FixedDrives.Caption is too small. Increase its size by at
least 7.
How do you increase the size of this table.
Problem #2
This is a very recent implementation and I notice that the maintenance plan
will not backup the transaction log. It appears to backup the database ok.
But the transaction log never backs up and it is set to backup every 3 hours.
P#1 : SOund like a column rather than a table problem.
P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? Is
there a job for this ?
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
> server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
> plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
> hours.
|||The error you're getting is from LANdesk, not SQL Server. I suggest you
contact their technical support to see what to do. You can't pre-allocate
space for tables in SQL Server, they automatically grow as you insert more
rows into them.
What recovery mode is the database in? If its simple recovery mode, taking a
log backup is a no-op.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> Okay, I am actually having 2 problems. We have LANdesk and it uses SQL
server
> 2000 and the people in our desktop department are getting a message which
> says:
> The size of FixedDrives.Caption is too small. Increase its size by at
> least 7.
> How do you increase the size of this table.
> Problem #2
> This is a very recent implementation and I notice that the maintenance
plan
> will not backup the transaction log. It appears to backup the database ok.
> But the transaction log never backs up and it is set to backup every 3
hours.
|||ok, I'm by no means a SQL expert so I'm feeling my way through this and yes
you are rights captions is a column in the fixeddrives table. I right
clicked on the table and chose design table. The captions column data type is
char and the length is 60. If I increase it by 7 as the error asks and then
click save, I get a warning during the presave process which goes on to say:
Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
I have no clue what to do from there.
As for the transaction log backup problem, a maintenance plan has been
created to backup the transaction log every 3 hours. It fails everytime. Here
are the results:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SVRWEBG026' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
Backup can not be performed on database 'LDINV'. This sub task is ignored.
End of maintenance plan 'DB Maintenance Plan2' on 4/18/2005 1:25:00 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any help will be greatly appreciated.
"Jens Sü?meyer" wrote:

> P#1 : SOund like a column rather than a table problem.
> P#2 Did you choode to backup the TLog ? What does SQL Server Agent say ? Is
> there a job for this ?
> HTH, Jens SUessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
>
>
|||"simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
in message news:32826F49-2FBF-48CE-9582-3EC1382455D9@.microsoft.com...
> ok, I'm by no means a SQL expert so I'm feeling my way through this and
> yes
> you are rights captions is a column in the fixeddrives table. I right
> clicked on the table and chose design table. The captions column data type
> is
> char and the length is 60. If I increase it by 7 as the error asks and
> then
> click save, I get a warning during the presave process which goes on to
> say:
You should contact LANDesk Tech Support. Going in and changing the
structure of a database for an application could break the application, or
at the very least, waste a lot of time and space with no positive results.
If their customers have encountered this problem before, they might have a
thoroughly tested fix or patch for the front-end software as well as the
database.
|||So, if the database is in simple recovery mode you can not backup the
transaction logs? What can you do to tell what mode it is in? For that matter
what are the modes?
Again I'm not the SQL guy.
"Paul S Randal [MS]" wrote:

> The error you're getting is from LANdesk, not SQL Server. I suggest you
> contact their technical support to see what to do. You can't pre-allocate
> space for tables in SQL Server, they automatically grow as you insert more
> rows into them.
> What recovery mode is the database in? If its simple recovery mode, taking a
> log backup is a no-op.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "simpsoro@.webster.edu" <simpsorowebsteredu@.discussions.microsoft.com> wrote
> in message news:F6473B51-EFE8-4F0D-878A-FEC5404D3E5D@.microsoft.com...
> server
> plan
> hours.
>
>
sql

How to incorporate a table field into the email message body nto as an attachmen

Hello everyone,

Please i need your help...

I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though...Just write it in the mail as part of message body...

I use this SQL select statement to retrieve the strTitle and datBorrowed fields

strSQL += @."Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";

Now, I have the following code to write the email

static void SendTest()
{

int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}

And the body of the email is this.....

Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***\n" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***\n" +
"\n" +
"Books Borrowed Yesterday are:\n" +

"\n" +
"\n" +
"Thank you,\n" +
"\n" +
"eLibrarian\n" +
"\n" +
"================================================== ===============\n" +
"\n" +
"This e-mail is automatically generated by the Library system.\n" +
"Please do not reply.");

i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:\n" +

So how should i do this?you want to execute the strSQL. Obtain the result and append it to your body string before calling the Dict.add/GetVal().|||you want to execute the strSQL. Obtain the result and append it to your body string before calling the Dict.add/GetVal().

Im sorry sir...but I'm just a newbie in programming...How should i do it? Could you give me an example? :o|||Please take a look at the link below and download the sample codes:
http://msdn.microsoft.com/vcsharp/downloads/samples/default.aspx

They should help/guide you through your learning/development.sql