How Do I Add 1 to a Field?

I have a Table: Thread

There are 2 relevant fields: ThreadID and Counter (integer).

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

I know how to do this as 2 stored procedures (one to get the current value of the counter and a second procedure to write the new value).

There must be a better way to do it as one procedure though.

Any suggestions?

Thanks,

Chris

You can do this in a number of ways. get the number and add the 1 at the application layer. or even as simple as

SELECT counter+1

FROM Thread

WHERE Threadid = @.Threadid

|||

The example you give will return a number 1 larger, but I also need to have that new number written into the data.

Can I do that with 1 stored procedure?

Chris

|||

Chris Messineo:

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

Thats what you wanted right?

|||

I'm sorry if my question is confusing.

I need to get the new value, but I also need to set that value in the db. I believe your solution will just get me the new value.

Does that make sense?

Chris

|||

do an UPDATE then. You can write a proc that will do the update and return the value. you can either return using an OUTPUT parameter or a SELECT statement.

How could I do that (Query)

Hello all,

I'm using SS 2000

Based on the following query, how could I get only one row for each
different field "F1"? I don't want to use temp table.

----
SELECT ?
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
----

One of the possible answer could be:

F1 F2 F3
-- --- ----
B 3 11 /* One row for "B" */
A 4 10 /* One row for "A" */

TIA

YannickYour question is not clear. Please tell us the logic you want to follow. How
do you define the value of F2 and F3?

Shervin

"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:N%%cb.13673$yD1.1527468@.news20.bellglobal.com ...
> Hello all,
> I'm using SS 2000
> Based on the following query, how could I get only one row for each
> different field "F1"? I don't want to use temp table.
> ----
> SELECT ?
> FROM (
> SELECT 'A' AS F1, 1 AS F2, 10 AS F3
> UNION ALL
> SELECT 'B', 2, 12
> UNION ALL
> SELECT 'B', 3, 11
> UNION ALL
> SELECT 'A', 4, 10) T
> ----
> One of the possible answer could be:
> F1 F2 F3
> -- --- ----
> B 3 11 /* One row for "B" */
> A 4 10 /* One row for "A" */
> TIA
> Yannick|||Do you mean taht this query represents some data in a table?

If so, and assuming that (f1,f2) is unique:

SELECT T.*
FROM
(SELECT f1, MAX(f2) AS f2
FROM T
GROUP BY f1) AS X
JOIN T
ON T.f1 = X.f1 AND T.f2 = X.f2

--
David Portas
----
Please reply only to the newsgroup
--|||Yannick Turgeon (nobody@.nowhere.com) writes:
> I'm using SS 2000
> Based on the following query, how could I get only one row for each
> different field "F1"? I don't want to use temp table.
> ----
> SELECT ?
> FROM (
> SELECT 'A' AS F1, 1 AS F2, 10 AS F3
> UNION ALL
> SELECT 'B', 2, 12
> UNION ALL
> SELECT 'B', 3, 11
> UNION ALL
> SELECT 'A', 4, 10) T
> ----

This could do it:

SELECT F1, MIN(F2), MIN(F3)
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
GROUP BY F1

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In my real situation, the SELECT ... UNION clause is replaced by a SELECT sub
query which return rows without unique key. Two rows could be exactly
the same. It does not matter which row is returned for a F1 but I want one
and only one row for each F1, and F2 and F3 must be from the same row.
This exclude "SELECT F1, MIN(F2), MIN(F3) ..."

I hope is clearer. Thanks for your help.

Yannick

Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a crit*:

> Your question is not clear. Please tell us the logic you want to follow. How
> do you define the value of F2 and F3?
> Shervin|||Davis,

I haven't been clear enough. T is a subquery which return rows without
unique key. Two rows could be exactly the same.

Thanks for your time.

Yannick

Le Fri, 26 Sep 2003 22:22:33 +0100, David Portas a crit*:

> Do you mean taht this query represents some data in a table?
> If so, and assuming that (f1,f2) is unique:
> SELECT T.*
> FROM
> (SELECT f1, MAX(f2) AS f2
> FROM T
> GROUP BY f1) AS X
> JOIN T
> ON T.f1 = X.f1 AND T.f2 = X.f2
> --
> David Portas
> ----
> Please reply only to the newsgroup|||> I haven't been clear enough. T is a subquery

Then post the actual subquery, the DDL for the base tables and some sample
data as INSERT statements. Without that it's difficult to give a full
answer.

--
David Portas
----
Please reply only to the newsgroup
--|||OK, assuming F2 and F3 are integer values less than 10,000,000,000 this qury
returns what you want. SF2 and SF3 columns of the result set are string
fields, you can convert them back to integer values if you want.

select f1,
left(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF2,
right(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF3
from YourSubQuery
group by f1

Shervin

"Yannick Turgeon" <nobody@.nowhere.com> wrote in message
news:pan.2003.09.27.14.26.06.827858@.nowhere.com...
> In my real situation, the SELECT ... UNION clause is replaced by a SELECT
sub
> query which return rows without unique key. Two rows could be exactly
> the same. It does not matter which row is returned for a F1 but I want one
> and only one row for each F1, and F2 and F3 must be from the same row.
> This exclude "SELECT F1, MIN(F2), MIN(F3) ..."
> I hope is clearer. Thanks for your help.
> Yannick
>
> Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a crit :
> > Your question is not clear. Please tell us the logic you want to follow.
How
> > do you define the value of F2 and F3?
> > Shervin

How Change field value triggered by date?

In a table i had field called Status and it is of type integer and another two field From and To ... they are of type date .....
what i want to do is to change the value of status field when the current date become equal to the From field and also make another change when the current date become equal to the To field ...

So How can i do This on SQL server 2005?

You need to run a query to update the rows, something like this

UPDATE mytable
SET Status = CASE
WHEN datediff(d, From, getdate()) = 0 THEN 1
WHEN datediff(d, To, getdate()) = 0 THEN 2
END

You could create a calculated column based on the same expression if you want status to always be up to date without running the query first. Otherwise schedule a job to run that query every night so that the status column is always correct.

|||

Thank You for your reply and it is helpful,

But what if the From and To fields in a child table to the original table that have the field status?

e.x. Master Table have the following fields:

PersonID, Name, Status

Details Table has the following fields:

ID,PersonID (as foreign key to the master table), From, To

How the query will look like?!

|||

UPDATE mastertable
SET Status = CASE
WHEN datediff(d, detailtable.From, getdate()) = 0 THEN 1
WHEN datediff(d, detailtable.To, getdate()) = 0 THEN 2
END
FROM mastertable
INNER JOIN detailtable ON mastertable.PersonID = detailtable.PersonID

How can we call Function into Select qry ?

Hi ,
I need to call Function in Select Qry and some of the field of that Qry is
Input to
Function
like select Col1,col2, (select dbo.fn(Col1))Col3 from X
Any help Please ?
KrisKris wrote:
> Hi ,
> I need to call Function in Select Qry and some of the field of that
> Qry is Input to
> Function
> like select Col1,col2, (select dbo.fn(Col1))Col3 from X
> Any help Please ?
> Kris
Select
Col1, Col2, dbo.fn(Col1), Col3
From
X
Is that what you mean? If not, maybe what you're talking about is the
APPLY operator in SQL 2005 which allows you to add columns to a result
set based on the results of a function:
Select Col1, Col2
CROSS APPLY dbo.MyFunc(Col1)
David Gugick - SQL Server MVP
Quest Software|||David Thanks for your reply.
What I understand is, We can not use function into Select Qry, as function
suppose to return value for each records. I have to use Cursor.
Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
read Value into @.Variable
select dbo.fn(@.Variable)
When I used Function in Select Qry. It is not returning value for all record
s,
select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
something like
Col1, Col2 ,Col3
A B AB
X Y AB
Is there any other way i can call fuction in select Qry becoz cursor is slow
.
Thx-Kris
"David Gugick" wrote:

> Kris wrote:
> Select
> Col1, Col2, dbo.fn(Col1), Col3
> From
> X
> Is that what you mean? If not, maybe what you're talking about is the
> APPLY operator in SQL 2005 which allows you to add columns to a result
> set based on the results of a function:
> Select Col1, Col2
> CROSS APPLY dbo.MyFunc(Col1)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Kris (Kris@.discussions.microsoft.com) writes:
> David Thanks for your reply.
> What I understand is, We can not use function into Select Qry, as function
> suppose to return value for each records. I have to use Cursor.
> Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
> read Value into @.Variable
> select dbo.fn(@.Variable)
> When I used Function in Select Qry. It is not returning value for all
> records,
> select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
> something like
> Col1, Col2 ,Col3
> A B AB
> X Y AB
> Is there any other way i can call fuction in select Qry becoz cursor is
> slow.
It's not fully clear what sort of function you have, but it seems like
you have a scalar function. In such case you can do:
SELECT col1, col2, dbo.fn(col1 + col2) AS Col3 FROM X
If you don't get back all rows as expected, you may have some other
problem with your actual query.
If you have a table-valued function, it depends on which version of
SQL whether you can do this in query or not.
It is difficult to assist when you don't give accurate information of
what you are doing. Could you include the actual code you are using?
Also, please specify which version of SQL Server you are using.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can the Profiler textdata field be enlarged?

When running Profiler to save a trace to a table the field 'TextData' gets
trunciated. The datatype for that field is as shown:

Column name | Data Type | Length | Allow Null

TextData ntext 16 yes

I can not find a way to set the trace up to create a tablewith TextData of
length greater than 16. This really causes a problem when trying to capture
long running queries for tuning as the query itself maybe truncated. Is
there a way around this?"Robert" <stop.spam@.boeing.com> wrote in message
news:HtAJF7.7yM@.news.boeing.com...
> When running Profiler to save a trace to a table the field 'TextData' gets
> trunciated. The datatype for that field is as shown:
> Column name | Data Type | Length | Allow Null
> TextData ntext 16 yes
>
> I can not find a way to set the trace up to create a tablewith TextData of
> length greater than 16. This really causes a problem when trying to
capture
> long running queries for tuning as the query itself maybe truncated. Is
> there a way around this?
>
ntext can store up to 1GB of Unicode text, so you should be able to see the
full query text. If not, perhaps you can clarify how you are viewing the
data? Using Query Analyzer, or another tool? QA displays only 256 characters
by default, so you may need to check your settings (Tools - Options -
Results).

By default, MSSQL stores a pointer to ntext data in the table, not the data
itself, which is where the 16 bytes comes from - that is the size of the
pointer only, not the data.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4033b59f$1_3@.news.bluewin.ch...
> "Robert" <stop.spam@.boeing.com> wrote in message
> news:HtAJF7.7yM@.news.boeing.com...
> > When running Profiler to save a trace to a table the field 'TextData'
gets
> > trunciated. The datatype for that field is as shown:
> > Column name | Data Type | Length | Allow Null
> > TextData ntext 16 yes
> > I can not find a way to set the trace up to create a tablewith TextData
of
> > length greater than 16. This really causes a problem when trying to
> capture
> > long running queries for tuning as the query itself maybe truncated. Is
> > there a way around this?
> ntext can store up to 1GB of Unicode text, so you should be able to see
the
> full query text. If not, perhaps you can clarify how you are viewing the
> data? Using Query Analyzer, or another tool? QA displays only 256
characters
> by default, so you may need to check your settings (Tools - Options -
> Results).
> By default, MSSQL stores a pointer to ntext data in the table, not the
data
> itself, which is where the 16 bytes comes from - that is the size of the
> pointer only, not the data.
> Simon

Thanx Simon

You nailed it! I am not worthy.

How can I verify a GUID number ?

Dear SQL,

I need to SELECT something from a database that has a UNIQUEIDENTIFIER (GUID) field,

If the number is wrong (has some other than A-Z 0-9) than theASP page just freaks out and gets "error converting from a character string to uniqueidentifier"

How can I check that the GUID is OK before I SELECT ?
this is the number format:
{7A9B5F81-4936-4A31-B4E2-9168AAB75A0}

I tried to cast this "error" number with no successs:
"WHERE Deceased_ID = cast('"& "---4936-4A31-B4E2-9168AAB75A0" &"' as uniqueidentifier)"

Thanks in advance, Yovav.?? The string you're trying to cast is not a valid GUID, whats with the concats and the "--"? If a GUID is in the database then it will always be valid.|||I know it's not valid - that's the idea...

I'm getting a GUID as a parameter (using GET) and I want to avoid getting an error
if the user will change the GUID I'm sending to something illegal like "--+..."

The problem is that if I'm trying to SELECT some stuff WHERE someGUID = '" & givenGUID & "'
it will get a nasty error on the ASP page (error converting from a character string to uniqueidentifier)

I also tried to cast it into varchar(38) - which is working - BUT not if the GUID contains some illegal characters...

I guess I will have to write a function to validate GUID number - I could not find a function like that anywhere :-(|||Solved|||but GUID is a datatype so it will error if you try to put an invalid item into it, can't you rely on that?|||Solved

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:

I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:

i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim

How can I track what canges a field?

I'm guessing there is no easy way to do this. I inherated a database and
many codebases that hit that database. One of the programs is inacuratly
adjusting the stock field and none of them log they're activity. Outside of
a bunch of programming (which will be done eventually) is there any way I
can track what changed the value and when? The only thing I can think of is
running a trace with SQL Profiler but I think I have to capture all activity
for the database, not just the spisific table or record, or field. The
database has way to much activity to trace for more than a few min. and we
may go a day or so before having a problem.Perhaps you can create a trigger that writes to a log table each time the
column is updated. You can record when, by whom and sometime the
application.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bishop" <nospam@.nospam.com> wrote in message
news:%23KZCFKeWIHA.5164@.TK2MSFTNGP03.phx.gbl...
I'm guessing there is no easy way to do this. I inherated a database and
many codebases that hit that database. One of the programs is inacuratly
adjusting the stock field and none of them log they're activity. Outside of
a bunch of programming (which will be done eventually) is there any way I
can track what changed the value and when? The only thing I can think of is
running a trace with SQL Profiler but I think I have to capture all activity
for the database, not just the spisific table or record, or field. The
database has way to much activity to trace for more than a few min. and we
may go a day or so before having a problem.|||Wow, triggers are cool, that's exactly what I needed. Thanks!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e1VvXOeWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Perhaps you can create a trigger that writes to a log table each time the
> column is updated. You can record when, by whom and sometime the
> application.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:%23KZCFKeWIHA.5164@.TK2MSFTNGP03.phx.gbl...
> I'm guessing there is no easy way to do this. I inherated a database and
> many codebases that hit that database. One of the programs is inacuratly
> adjusting the stock field and none of them log they're activity. Outside
> of
> a bunch of programming (which will be done eventually) is there any way I
> can track what changed the value and when? The only thing I can think of
> is
> running a trace with SQL Profiler but I think I have to capture all
> activity
> for the database, not just the spisific table or record, or field. The
> database has way to much activity to trace for more than a few min. and we
> may go a day or so before having a problem.
>

How can I track what canges a field?

I'm guessing there is no easy way to do this. I inherated a database and
many codebases that hit that database. One of the programs is inacuratly
adjusting the stock field and none of them log they're activity. Outside of
a bunch of programming (which will be done eventually) is there any way I
can track what changed the value and when? The only thing I can think of is
running a trace with SQL Profiler but I think I have to capture all activity
for the database, not just the spisific table or record, or field. The
database has way to much activity to trace for more than a few min. and we
may go a day or so before having a problem.
Perhaps you can create a trigger that writes to a log table each time the
column is updated. You can record when, by whom and sometime the
application.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bishop" <nospam@.nospam.com> wrote in message
news:%23KZCFKeWIHA.5164@.TK2MSFTNGP03.phx.gbl...
I'm guessing there is no easy way to do this. I inherated a database and
many codebases that hit that database. One of the programs is inacuratly
adjusting the stock field and none of them log they're activity. Outside of
a bunch of programming (which will be done eventually) is there any way I
can track what changed the value and when? The only thing I can think of is
running a trace with SQL Profiler but I think I have to capture all activity
for the database, not just the spisific table or record, or field. The
database has way to much activity to trace for more than a few min. and we
may go a day or so before having a problem.
|||Wow, triggers are cool, that's exactly what I needed. Thanks!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e1VvXOeWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Perhaps you can create a trigger that writes to a log table each time the
> column is updated. You can record when, by whom and sometime the
> application.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:%23KZCFKeWIHA.5164@.TK2MSFTNGP03.phx.gbl...
> I'm guessing there is no easy way to do this. I inherated a database and
> many codebases that hit that database. One of the programs is inacuratly
> adjusting the stock field and none of them log they're activity. Outside
> of
> a bunch of programming (which will be done eventually) is there any way I
> can track what changed the value and when? The only thing I can think of
> is
> running a trace with SQL Profiler but I think I have to capture all
> activity
> for the database, not just the spisific table or record, or field. The
> database has way to much activity to trace for more than a few min. and we
> may go a day or so before having a problem.
>