Controling xp_sendmail

Hi,
I'd like the email be sent only when the query has results. When it doesn't
(null or 0), the email should not be sent:
EXEC master..xp_sendmail @.recipients = 'mike',
@.message = 'Message text',
@.query = '--',
@.subject = 'SQL Mail test to attach query results',
@.dbuse = 'pubs'
Howto?
TIA
MikeMike,
In this situation, you'll have to run the query twice. Once to see if you
get results, and then once in xp_sendmail.
Of course, you could pump the results from the test into a different table,
and then query that from xp_sendmail. But I'd only consider doing this if
your query really can't be run twice.
Rob
"Mike_B" wrote:

> Hi,
> I'd like the email be sent only when the query has results. When it doesn'
t
> (null or 0), the email should not be sent:
>
> EXEC master..xp_sendmail @.recipients = 'mike',
> @.message = 'Message text',
> @.query = '--',
> @.subject = 'SQL Mail test to attach query results',
> @.dbuse = 'pubs'
>
> Howto?
> TIA
> Mike
>
>

Controling Replation agent actions

Hello there
I some questions:
1. Iw'd like to run some store procedures before i'm starging the
replication every day. Dose it enouth to add the task before the replication
task?
2. If the replication faild i would like to make sure that none of the
changes will be made.
3. IF an error occur can i nevegat it to the type of error:
if the error is data error(validation of foreign key) or connection error.
So i can give it order to run 5 minutes afterword?
4. Can i output the errors to outside files in order that other programs
that use sql server (Access) can give a message to the user?
Roy,
adding another step would be fine, or another job which runs sp_start_job
after your code had finished.
Trapping errors is really well catered fro in SQL 2005 but not so easy in
SQL 2000. You'd be better testing your changes in code rather than
attempting your changes eg look for a PK value before trying the FK insert.
If the PK doesn't exist, then logging this to a table and taking appropriate
actions eg making your own error message available to Access.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||1) I would make these procs the first job step of your replication job
2) Double click on the job step, select advanced and have it run a job or
script on failure.
3) This is a little tricky - the error should be in the msrepl_errors table
in the distribution database where the agent is run. You can query it there,
but the error may not be there depending on the error message. What you
would need to do is restart the agent on failure, but this time use the
verbose agent profile. The complete error message will now be in the
msrepl_Errors table. Errors are also logged in text files and dumped in
%WindDir%\system32 and will have an err extension. You can poll for them
using FileSystemWatcher.
4) You might want to poll msrepl_errors as discussed above and write to an
access database. Or you could fire something using the Replication Alert
Agent Failure.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
> Hello there
> I some questions:
> 1. Iw'd like to run some store procedures before i'm starging the
> replication every day. Dose it enouth to add the task before the
> replication
> task?
> 2. If the replication faild i would like to make sure that none of the
> changes will be made.
> 3. IF an error occur can i nevegat it to the type of error:
> if the error is data error(validation of foreign key) or connection error.
> So i can give it order to run 5 minutes afterword?
> 4. Can i output the errors to outside files in order that other programs
> that use sql server (Access) can give a message to the user?
>
>
|||Whell Hilary
I've made an error in the replication and no MSRepl_Errors table were
created
After i got the error when i run the agent on the enterprise manager i
opened the query anlyser with the distribution database and i counldn't see
the table
When will the table appear?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYG7KczAGHA.272@.TK2MSFTNGP09.phx.gbl...
> 1) I would make these procs the first job step of your replication job
> 2) Double click on the job step, select advanced and have it run a job or
> script on failure.
> 3) This is a little tricky - the error should be in the msrepl_errors
> table in the distribution database where the agent is run. You can query
> it there, but the error may not be there depending on the error message.
> What you would need to do is restart the agent on failure, but this time
> use the verbose agent profile. The complete error message will now be in
> the msrepl_Errors table. Errors are also logged in text files and dumped
> in %WindDir%\system32 and will have an err extension. You can poll for
> them using FileSystemWatcher.
> 4) You might want to poll msrepl_errors as discussed above and write to an
> access database. Or you could fire something using the Replication Alert
> Agent Failure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ePub0LyAGHA.2656@.tk2msftngp13.phx.gbl...
>

Controling Names of Agents

Hello there
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>

Controling language of returned schedule description

Hi!
When I GetScheduleProperties, I would like to control the language of the
returned schedule description. Any thoughts on how I might accomplish this?
The language of this schedule description changes when I use Report
Manager, so there must be a way to accomplish this.
Thanks in advance for any enlightenment,
BobHi Bob
Did you get anywhere on this one ?
Anybody else manaded to do it ?
"bobhug" wrote:
> Hi!
> When I GetScheduleProperties, I would like to control the language of the
> returned schedule description. Any thoughts on how I might accomplish this?
> The language of this schedule description changes when I use Report
> Manager, so there must be a way to accomplish this.
> Thanks in advance for any enlightenment,
> Bob
>