Can the Job Notifications be configured to use a different transport mechanism?

(SQL Server 2000, SP3a)
Hello all!
In our Production environment, we're moving towards a configuration that will preclude the
use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
Mail, which would leave Net Send as our only available transport.
Is there any way to integrate an additional Notification transport into SQL Server?
Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
make the Notifications leverage something outside the default transports.
Thanks!
John PetersonFor xp_smtp_sendmail, you can just add another step that
sends the email. So if you wanted to notify on failure, just
add a step to the job that you branch to on failure. This
job step can handle the email. You won't have support for
operators but you can write your own - have a table with
whatever email, rules then query this table to find who
should be notified. You can then pass this value to
xp_smtp_sendmail.
-Sue
On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>In our Production environment, we're moving towards a configuration that will preclude the
>use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
>E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
>Mail, which would leave Net Send as our only available transport.
>Is there any way to integrate an additional Notification transport into SQL Server?
>Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
>make the Notifications leverage something outside the default transports.
>Thanks!
>John Peterson
>|||Thanks, Sue!
I had hoped there was a way to integrate the existing Operator aspect, but introduce a new
transport mechanism. It sounds like it won't be that straightforward. :-(
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:85hdqvse38r38os3ul9b6rsvgi600nlt1v@.4ax.com...
> For xp_smtp_sendmail, you can just add another step that
> sends the email. So if you wanted to notify on failure, just
> add a step to the job that you branch to on failure. This
> job step can handle the email. You won't have support for
> operators but you can write your own - have a table with
> whatever email, rules then query this table to find who
> should be notified. You can then pass this value to
> xp_smtp_sendmail.
> -Sue
> On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >In our Production environment, we're moving towards a configuration that will preclude
the
> >use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
> >E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require
SQL
> >Mail, which would leave Net Send as our only available transport.
> >
> >Is there any way to integrate an additional Notification transport into SQL Server?
> >Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
> >make the Notifications leverage something outside the default transports.
> >
> >Thanks!
> >
> >John Peterson
> >
>|||No, it's a bit more of a PITA than that, but at the same time, it gives you
a lot more control. We use this mechanism and we have customized
subjects/bodies, inclusion lists based on various error levels, etc.
A
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks, Sue!
> I had hoped there was a way to integrate the existing Operator aspect, but
introduce a new
> transport mechanism. It sounds like it won't be that straightforward.
:-(|||Thanks Aaron (and Sue)!
Out of curiosity, do you know whether the Notifications is making a SQL call to
xp_sendmail? If so, would it be possible to "swap out" the underlying xp_sendmail .DLL
and replace it with a xp_smtp_sendmail "wrapper" with the same name as xp_sendmail?
I doubt that the Notifications is making a SQL call to "do its business", but I thought
I'd check. :-)
Thanks!
John Peterson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> No, it's a bit more of a PITA than that, but at the same time, it gives you
> a lot more control. We use this mechanism and we have customized
> subjects/bodies, inclusion lists based on various error levels, etc.
> A
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks, Sue!
> >
> > I had hoped there was a way to integrate the existing Operator aspect, but
> introduce a new
> > transport mechanism. It sounds like it won't be that straightforward.
> :-(
>|||Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
Agent does its own MAPI calls.
I'm writing a utility that does xp_smtp_sendmail calls based on your current
alert configuration. The idea is to configure alerts as desired, and then
schedule my .exe every x minutes. My .exe reads off of the alert config
table and reads the eventlog. Based on when each configured alert was fired
the last time, the exe will xp_smtp_sendmail. The current idea is to use
disabled operators (if you enable them, then Agent will complain that it
can't send email).
I'm finishing up this util this week, then I have to wait for it to go on
the web (which might take a few days to a few weeks - I don't have direct
access to the web). Send me an email if you want to give it a try earlier.
I will put it on www.dbmaint.com, free utilities.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks Aaron (and Sue)!
> Out of curiosity, do you know whether the Notifications is making a SQL
call to
> xp_sendmail? If so, would it be possible to "swap out" the underlying
xp_sendmail .DLL
> and replace it with a xp_smtp_sendmail "wrapper" with the same name as
xp_sendmail?
> I doubt that the Notifications is making a SQL call to "do its business",
but I thought
> I'd check. :-)
> Thanks!
> John Peterson
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > No, it's a bit more of a PITA than that, but at the same time, it gives
you
> > a lot more control. We use this mechanism and we have customized
> > subjects/bodies, inclusion lists based on various error levels, etc.
> >
> > A
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks, Sue!
> > >
> > > I had hoped there was a way to integrate the existing Operator aspect,
but
> > introduce a new
> > > transport mechanism. It sounds like it won't be that straightforward.
> > :-(
> >
> >
>|||Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If you don't mind an
early beta tester, I'd be delighted to try it out earlier! :-)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> Agent does its own MAPI calls.
> I'm writing a utility that does xp_smtp_sendmail calls based on your current
> alert configuration. The idea is to configure alerts as desired, and then
> schedule my .exe every x minutes. My .exe reads off of the alert config
> table and reads the eventlog. Based on when each configured alert was fired
> the last time, the exe will xp_smtp_sendmail. The current idea is to use
> disabled operators (if you enable them, then Agent will complain that it
> can't send email).
> I'm finishing up this util this week, then I have to wait for it to go on
> the web (which might take a few days to a few weeks - I don't have direct
> access to the web). Send me an email if you want to give it a try earlier.
> I will put it on www.dbmaint.com, free utilities.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks Aaron (and Sue)!
> >
> > Out of curiosity, do you know whether the Notifications is making a SQL
> call to
> > xp_sendmail? If so, would it be possible to "swap out" the underlying
> xp_sendmail .DLL
> > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> xp_sendmail?
> >
> > I doubt that the Notifications is making a SQL call to "do its business",
> but I thought
> > I'd check. :-)
> >
> > Thanks!
> >
> > John Peterson
> >
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > No, it's a bit more of a PITA than that, but at the same time, it gives
> you
> > > a lot more control. We use this mechanism and we have customized
> > > subjects/bodies, inclusion lists based on various error levels, etc.
> > >
> > > A
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks, Sue!
> > > >
> > > > I had hoped there was a way to integrate the existing Operator aspect,
> but
> > > introduce a new
> > > > transport mechanism. It sounds like it won't be that straightforward.
> > > :-(
> > >
> > >
> >
> >
>|||Great! Send me an email, John (remove the obvious parts), and I can send you
the bits when I'm done with it.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
you don't mind an
> early beta tester, I'd be delighted to try it out earlier! :-)
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> > Agent does its own MAPI calls.
> > I'm writing a utility that does xp_smtp_sendmail calls based on your
current
> > alert configuration. The idea is to configure alerts as desired, and
then
> > schedule my .exe every x minutes. My .exe reads off of the alert config
> > table and reads the eventlog. Based on when each configured alert was
fired
> > the last time, the exe will xp_smtp_sendmail. The current idea is to use
> > disabled operators (if you enable them, then Agent will complain that it
> > can't send email).
> > I'm finishing up this util this week, then I have to wait for it to go
on
> > the web (which might take a few days to a few weeks - I don't have
direct
> > access to the web). Send me an email if you want to give it a try
earlier.
> > I will put it on www.dbmaint.com, free utilities.
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks Aaron (and Sue)!
> > >
> > > Out of curiosity, do you know whether the Notifications is making a
SQL
> > call to
> > > xp_sendmail? If so, would it be possible to "swap out" the underlying
> > xp_sendmail .DLL
> > > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> > xp_sendmail?
> > >
> > > I doubt that the Notifications is making a SQL call to "do its
business",
> > but I thought
> > > I'd check. :-)
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > No, it's a bit more of a PITA than that, but at the same time, it
gives
> > you
> > > > a lot more control. We use this mechanism and we have customized
> > > > subjects/bodies, inclusion lists based on various error levels, etc.
> > > >
> > > > A
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks, Sue!
> > > > >
> > > > > I had hoped there was a way to integrate the existing Operator
aspect,
> > but
> > > > introduce a new
> > > > > transport mechanism. It sounds like it won't be that
straightforward.
> > > > :-(
> > > >
> > > >
> > >
> > >
> >
> >
>|||Good thinkin' Tibor.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> Great! Send me an email, John (remove the obvious parts), and I can send
you
> the bits when I'm done with it.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
> you don't mind an
> > early beta tester, I'd be delighted to try it out earlier! :-)
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
Nowadays,
> > > Agent does its own MAPI calls.
> > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> current
> > > alert configuration. The idea is to configure alerts as desired, and
> then
> > > schedule my .exe every x minutes. My .exe reads off of the alert
config
> > > table and reads the eventlog. Based on when each configured alert was
> fired
> > > the last time, the exe will xp_smtp_sendmail. The current idea is to
use
> > > disabled operators (if you enable them, then Agent will complain that
it
> > > can't send email).
> > > I'm finishing up this util this week, then I have to wait for it to go
> on
> > > the web (which might take a few days to a few weeks - I don't have
> direct
> > > access to the web). Send me an email if you want to give it a try
> earlier.
> > > I will put it on www.dbmaint.com, free utilities.
> > > --
> > > Tibor Karaszi
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks Aaron (and Sue)!
> > > >
> > > > Out of curiosity, do you know whether the Notifications is making a
> SQL
> > > call to
> > > > xp_sendmail? If so, would it be possible to "swap out" the
underlying
> > > xp_sendmail .DLL
> > > > and replace it with a xp_smtp_sendmail "wrapper" with the same name
as
> > > xp_sendmail?
> > > >
> > > > I doubt that the Notifications is making a SQL call to "do its
> business",
> > > but I thought
> > > > I'd check. :-)
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > No, it's a bit more of a PITA than that, but at the same time, it
> gives
> > > you
> > > > > a lot more control. We use this mechanism and we have customized
> > > > > subjects/bodies, inclusion lists based on various error levels,
etc.
> > > > >
> > > > > A
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > Thanks, Sue!
> > > > > >
> > > > > > I had hoped there was a way to integrate the existing Operator
> aspect,
> > > but
> > > > > introduce a new
> > > > > > transport mechanism. It sounds like it won't be that
> straightforward.
> > > > > :-(
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks John. :-)
Perhaps I should say that anyone who like to look at this and expect to
install it to provide some feedback are welcome to drop me an email.
(It'll be a freebie when released, so unless you expect to install and play,
you might as well wait a couple of weeks).
I'll post something here when it is on the web.
--
Tibor Karaszi
"John Sitka" <johnsitka@.REMOVEhotmail.com> wrote in message
news:e3eTqjtoDHA.964@.TK2MSFTNGP10.phx.gbl...
> Good thinkin' Tibor.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> > Great! Send me an email, John (remove the obvious parts), and I can send
> you
> > the bits when I'm done with it.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > > Hello, Tibor! That sounds almost *exactly* what I'd be pining for!
If
> > you don't mind an
> > > early beta tester, I'd be delighted to try it out earlier! :-)
> > >
> > >
> > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
> Nowadays,
> > > > Agent does its own MAPI calls.
> > > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> > current
> > > > alert configuration. The idea is to configure alerts as desired, and
> > then
> > > > schedule my .exe every x minutes. My .exe reads off of the alert
> config
> > > > table and reads the eventlog. Based on when each configured alert
was
> > fired
> > > > the last time, the exe will xp_smtp_sendmail. The current idea is to
> use
> > > > disabled operators (if you enable them, then Agent will complain
that
> it
> > > > can't send email).
> > > > I'm finishing up this util this week, then I have to wait for it to
go
> > on
> > > > the web (which might take a few days to a few weeks - I don't have
> > direct
> > > > access to the web). Send me an email if you want to give it a try
> > earlier.
> > > > I will put it on www.dbmaint.com, free utilities.
> > > > --
> > > > Tibor Karaszi
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks Aaron (and Sue)!
> > > > >
> > > > > Out of curiosity, do you know whether the Notifications is making
a
> > SQL
> > > > call to
> > > > > xp_sendmail? If so, would it be possible to "swap out" the
> underlying
> > > > xp_sendmail .DLL
> > > > > and replace it with a xp_smtp_sendmail "wrapper" with the same
name
> as
> > > > xp_sendmail?
> > > > >
> > > > > I doubt that the Notifications is making a SQL call to "do its
> > business",
> > > > but I thought
> > > > > I'd check. :-)
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > > No, it's a bit more of a PITA than that, but at the same time,
it
> > gives
> > > > you
> > > > > > a lot more control. We use this mechanism and we have
customized
> > > > > > subjects/bodies, inclusion lists based on various error levels,
> etc.
> > > > > >
> > > > > > A
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > > Thanks, Sue!
> > > > > > >
> > > > > > > I had hoped there was a way to integrate the existing Operator
> > aspect,
> > > > but
> > > > > > introduce a new
> > > > > > > transport mechanism. It sounds like it won't be that
> > straightforward.
> > > > > > :-(
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Can SQL2000 notify me if a new job or DTS is created ?

Hi, ya-all. That's basically my question in a nut shell. Can it, and How
do I set it to ?
Thanks.WANNABE wrote:
> Hi, ya-all. That's basically my question in a nut shell. Can it, and How
> do I set it to ?
> Thanks.
>
There's nothing built-in, but you can easily write something yourself to
monitor the sysjobs and sysdtspackages tables in the MSDB database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but you over estimate me!!! Although I would love to give it
a try. I have no idea where to start, could you get me started. Be advised
I will be here posting questions about how to proceed, when I become
stumped.
Does anyone know if this is a feature in SQL2005 '
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E47FC4.7010007@.realsqlguy.com...
> WANNABE wrote:
> There's nothing built-in, but you can easily write something yourself to
> monitor the sysjobs and sysdtspackages tables in the MSDB database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Can SQL2000 notify me if a new job or DTS is created ?

Hi, ya-all. That's basically my question in a nut shell. Can it, and How
do I set it to ?
Thanks.WANNABE wrote:
> Hi, ya-all. That's basically my question in a nut shell. Can it, and How
> do I set it to ?
> Thanks.
>
There's nothing built-in, but you can easily write something yourself to
monitor the sysjobs and sysdtspackages tables in the MSDB database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but you over estimate me!!! Although I would love to give it
a try. I have no idea where to start, could you get me started. Be advised
I will be here posting questions about how to proceed, when I become
stumped.
Does anyone know if this is a feature in SQL2005 '
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E47FC4.7010007@.realsqlguy.com...
> WANNABE wrote:
>> Hi, ya-all. That's basically my question in a nut shell. Can it, and
>> How do I set it to ?
>> Thanks.
> There's nothing built-in, but you can easily write something yourself to
> monitor the sysjobs and sysdtspackages tables in the MSDB database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

can not view DTS Packages and Jobs

I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
RK
Yes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK
|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
RK
"Russell Fields" wrote:

> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>
|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:

can not view DTS Packages and Jobs

I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:

> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, b
ut
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them
as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package,
as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>

can not view DTS Packages and Jobs

I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
> >I have SQL 2k SP4 on Windows 2003
> > Can A sqlserver user, with out having SA role, see the DTS packages, Job
> > and
> > their status ?
> > --
> > RK
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>> Yes, partially, through some workarounds.
>> If you make a SQL Server user a member of the msdb database
>> TargetServersRole, they will be able to see all jobs and their statuses,
>> but
>> will not be able to create or modify jobs. (So this does not work for
>> someone who should be able to create his own jobs. This is an
>> undocumented
>> sideeffect of the role and will not work in SQL Server 2005. But 2005
>> has
>> specific new roles for granting various degrees of access to SQL Agent
>> jobs.)
>> If, instead of storing DTS packages as SQL Server objects, you store them
>> as
>> files on a file share, then anyone who has rights to the file share
>> (read/only if you want that) can examine the contents of the DTS package,
>> as
>> well. (If there is a workaround for examining DTS packages stored on the
>> server, I don't know it.)
>> RLF
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>> >I have SQL 2k SP4 on Windows 2003
>> > Can A sqlserver user, with out having SA role, see the DTS packages,
>> > Job
>> > and
>> > their status ?
>> > --
>> > RK
>>