Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshan
Ive used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
--
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating overnight data vs Live - suggestions needed.

We have an MIS system which has approx 100 reports. Each of these
reports can take up to several minutes to run due to the complexity of
the queries (hundreds of lines each in most cases). Each report can be
run by many users, so in effect we have a slow system.

I want to seperate the complex part of the queries into a process that
is generated each night. Then the reports will only have to query
pre-formatted data with minimal parameters as the hard part will have
been completed for the users when they are not in. Ideally we will
generate (stored procedure possibly) a set of data for each report and
hold this on the server. We can then query with simpler parameters
such as by date and get the data back quite quickly.

The whole process of how we obtain the data is very complex. There are
various views which gather data from the back office system. These are
very complex and when queries are run against them including other
tables to bring in more data, it gets nicely complicated.

The only problem is that the users want to have access to LIVE data
from the back office system, specifically the Sales team who want to
access this remotely. My method only allows for data from the night
before, so is there an option available to me which will allow me to
do this ? The queries can't be improved on an awful lot, so they will
take as long as they take. The idea of running them once is the only
way I can see to improve the performance in any significant way.

True I could just let them carry on as they are and let them suffer
with the performance on live data, but I'd like to do something to
improve the situation for them.

Any advice would be appreciated.

Thanks

RyanYep, that definitely makes a difference. Taken me a while to get back
to this, but we're looking at a solution along these lines.

By taking a copy of the data from the view into a table and then
replicating this onto another database (for generating) and then
running stored procedures (from each complex query) against this,
we've been able to reduce the time from 5 minutes for a test generate
query to about 3 seconds. We've timed the other parts and can probably
generate all of our data in less than 5 minutes for 100 complex
queries that would normally take hours.

Once this data is generated we'll simply swap it for the current data
which should be pretty quick. Our report now loads in less than a
second as opposed to 5 mins or so.

This means we can probably update the data once an hour and push it
onto the query database which will be more than quick enough for the
users. This has the knock on effect of being able to produce the
reports in seconds instead of minutes.

Erland, thanks for the advice on this it should make quite a
difference.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93DEF27F53F61Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > The only problem is that the users want to have access to LIVE data
> > from the back office system, specifically the Sales team who want to
> > access this remotely. My method only allows for data from the night
> > before, so is there an option available to me which will allow me to
> > do this ? The queries can't be improved on an awful lot, so they will
> > take as long as they take. The idea of running them once is the only
> > way I can see to improve the performance in any significant way.
> If users want current data, you cannot pre-compute it for them. You
> know, have the cake and eat it.
> What you could to do is to set up a report server which you replicate
> to, so that you take the load of the back-office system.
> Yet another alternative, is to have three databases:
> A - the source-system.
> B - the view system that the users use.-
> C - a computation system.
> You keep the computation database updated by means of log shipping
> (this is better than replication in this case). One you have applied
> a log, you pre-compute all data. When this step is done, you flush the
> pre-computed tables on B, and insert the data from C. Now you go and
> pick up a new log from A. A more fancy variant is to have a seamless
> switch between B and C which you flip constantly. I believe there are
> such things.|||Ryan (ryanofford@.hotmail.com) writes:
> By taking a copy of the data from the view into a table and then
> replicating this onto another database (for generating) and then
> running stored procedures (from each complex query) against this,
> we've been able to reduce the time from 5 minutes for a test generate
> query to about 3 seconds. We've timed the other parts and can probably
> generate all of our data in less than 5 minutes for 100 complex
> queries that would normally take hours.

300 seconds down to three! That's not bad!

> Erland, thanks for the advice on this it should make quite a
> difference.

I'm only glad to have helped! And thanks for reporing back!

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

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

Generate SQLscript for db?

Due to the large size db, I need to generate a SQL script and keep it in
VSS. The script should contain all create objctes statemnts. Using
'Generate SQL Script' in the Enterprise Manager by right clicking the db
is not helping. What I am missing here?
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***> Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping.
Why not? If you don't say what it does wrong, it is hard for us to help you.
Here's a general
article on the subject: http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message news:eclCR3rkFHA.1948@.TK2MSFTNGP12.p
hx.gbl...
> Due to the large size db, I need to generate a SQL script and keep it in
> VSS. The script should contain all create objctes statemnts. Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping. What I am missing here?
> Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Could you describe why that isn't working?
Visual Studio.NET also has the Generate Script from the Server Explorer.
Clint Hill
H3O Software
http://www.h3osoftware.com
Test Test wrote:
> Due to the large size db, I need to generate a SQL script and keep it in
> VSS. The script should contain all create objctes statemnts. Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping. What I am missing here?
> Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||The script got created but it was without create objects statements.
This is what I did - in the 'Generate SQL Script' window, I clicked on
'Formatting' tab, I noticed 'Generate create and drop objects' boxes are
checked by default (which I do want). Then, I clicked on 'Options' tab,
and checked the script db (and the ones I needed) and hit ok, it did
generate a script but without create objects. I tried many times but
with same results. It has logins, dbs settings and create db statements
only. where all other create objects (tables, views, stored procs) went
(even though they were seleceted)?
*** Sent via Developersdex http://www.examnotes.net ***|||Did you add the objects you want to include to the right list box in the lef
t-most tab?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message news:utOLieskFHA.1148@.TK2MSFTNGP12.p
hx.gbl...
> The script got created but it was without create objects statements.
> This is what I did - in the 'Generate SQL Script' window, I clicked on
> 'Formatting' tab, I noticed 'Generate create and drop objects' boxes are
> checked by default (which I do want). Then, I clicked on 'Options' tab,
> and checked the script db (and the ones I needed) and hit ok, it did
> generate a script but without create objects. I tried many times but
> with same results. It has logins, dbs settings and create db statements
> only. where all other create objects (tables, views, stored procs) went
> (even though they were seleceted)?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Got it. Thanks, Tobor!
You have to hit 'Show All' in the 'General' tab to get the objects
selection. By defualt, it was all greyed (thats why I was ignoring it).
My bad.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***