I had some DTS packages that are scheduled to run as a job every week. They have been running fine for about 4 months.
One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.
I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so
1. the old package does not run
2. The new package runs fine.
Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.
Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.
Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error
[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied
the UDLs i believe i not working properly. Does anyone know about UDL corruption?
Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?
Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.
Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.
If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.
I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.
-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Friday, March 23, 2012
Help DTS Job programming
Hello:
I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074.
but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.
What can I do?The job simply does not work ...
This means what are you getting any error?
If so review information from DTS package log and SQL error log or use PROFILER to monitor the activity.
That is the only KBA information available if you want to schedule a dts package.
Come with complete details about issue and setup.|||Always make sure you have the same drivers on the server as you do on your local machine if applicable. Don't know if that helps or not|||If you schedule the DTS package it will run in the context of sqlagent privileges and drivers on that server. And also the same fashion even though if you execute the package manually.|||I've seen this as a common problem when people are connecting to the server but running the package from a machine that does have appropriate drivers.
For poster - this may help, these guys are the bet in the biz with DTS - http://www.sqldts.com/default.aspx?222|||someone tolds me that it's a problem with the SQL and Windows NT authentication and security
BTW what is the current authentication set on SQL Server?
I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074.
but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.
What can I do?The job simply does not work ...
This means what are you getting any error?
If so review information from DTS package log and SQL error log or use PROFILER to monitor the activity.
That is the only KBA information available if you want to schedule a dts package.
Come with complete details about issue and setup.|||Always make sure you have the same drivers on the server as you do on your local machine if applicable. Don't know if that helps or not|||If you schedule the DTS package it will run in the context of sqlagent privileges and drivers on that server. And also the same fashion even though if you execute the package manually.|||I've seen this as a common problem when people are connecting to the server but running the package from a machine that does have appropriate drivers.
For poster - this may help, these guys are the bet in the biz with DTS - http://www.sqldts.com/default.aspx?222|||someone tolds me that it's a problem with the SQL and Windows NT authentication and security
BTW what is the current authentication set on SQL Server?
Help Designing an App. to be Run from a Job
Hello,
I'm working on a project that uses SQL Server 2005 and C# 2.0. I need some ideas of the best way to design/implement an application that will be executed from a SQL Server job.
The general idea is: a SQL Server job will call [something] and pass a couple of parameters to [something], then [something] will query a database and gather other information, then [something] will do some processing of data, and finally [something] will output the results for end user consumption.
The problem is that I don't know what [something] should be. Should it be a C# executable (but it can have no UI)? Should it be a web service? Should it be a console application (but, again, it cannot have a UI)? Should the job call a stored procedure, which in turn calls a C# assembly?
Basically, I'm just trying to get some ideas of the best way to design/implement a solution to my situation.
Thanks.
Hi,without further information, I would suggest to use a c# console app called from the job.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Thanks for the input.
What other information could I provide to help choose the best solution?
|||Best thing is to call a Stored Proc from the Sql job
In the Stored proc gather all the parameters that you need and use BCP out to keep that data in a flat file.
There is no need to use Webservice or other C# program as this would cause an additional overhead.
|||There are a number of things your description suggests, and others that it leaves open...
1 - from what you say, it looks like the something does not need access to the resources of the server (as a computer) and only needs access to data inside SQL Server. If this is true, then the use of a SQL Server job to invoke a stored procedure is the way to go (and in SQL Server 2005 you can write your stored procedure in C# if you like).
If, on the other hand, you need access to information outside SQL Server (files, I/O, Active Directory, other PCs, ...) you'll need an app running outside SQL Server (yes - you can invoke an app from inside SQL Server, but why jump through hoops when you don't need to?). If this is the road to take, use Windows' scheduler instead of SQL Server's scheduler.
If you're running inside SQL Server, as one of the other posts suggested, you'll have a Stored Proc to gather parameters, that likely will call another (or more) to do the work. If you're doing stuff outside the database server the app will likely invoke a stored proc to do the 'inside' work - so you're just partitioning where you do what work.
When returning the data to the user - there are a number of options that depend on various factors you've not mentioned as to which is better:
> simply write the data to an output table (with a timestamp on each row if you need to hold multiple day's / run's worth of output) - and the use can have a simple reporting app to read from it.
> If the volume of data is small SQL Server can email the recipient(s) - again, this is probably better if the number of recipients is small. You may also need to consider the security of the data as it travels as email.
> If you need to store the data outside the server (in a dated file, for example) you have to get it out, somehow. If your app is already running outside the server that's a no brainer; if everything is running inside the server I think you'll have to trigger some outside app to pull the data (i.e. I don't think there's a way for a SQL Server job to dump data to the outside world). DO NOT TAKE MY WORD for this - DTS may be invokable as a SQL Server job and may server to store data outside the server, or there may be other capabilities of SQL Server 2005 that I've not encountered yet.
> Worst comes to worst - you have a simple outside app that polls a table in the server for a 'ready' flag and then pulls the data. But that's ugly.
HTH
Griffin
sqlMonday, March 19, 2012
help about sql server job
I created a stored procedure.When i execute the sp in Query Analyzer or in
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO
Help : Performance due to SQL Mail
Dear All,
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert Lie
Hi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegr oups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert Lie
Hi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegr oups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
Help : Performance due to SQL Mail
Dear All,
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert LieHi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert LieHi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
Help : Performance due to SQL Mail
Dear All,
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert LieHi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
I have a sql job that checking incoming email by using xp_findnextmsg,
read the incoming email then reply the sender with query result.
I schedule the job every minute.
My question is:
Does the job will make my sql server perfomance down?
Thanks
Robert LieHi
The job itself, no. But test in your environment as to what affects your own
code you wrote causes on locking and blocking on your tables.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:1121049371.109507.159220@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have a sql job that checking incoming email by using xp_findnextmsg,
> read the incoming email then reply the sender with query result.
> I schedule the job every minute.
> My question is:
> Does the job will make my sql server perfomance down?
> Thanks
> Robert Lie
>
Monday, March 12, 2012
Help ! Dangerous user with SA permissions
Hi guys,
I have been told that only users with SA priviledges could check the
status of a job. For this reason, I had to give SA priviledges to this
user so he could check the results of a job (succesful or not) and do
his work. Now, he makes changes on the database without telling me and
last time we nearly lost one day of work as he changed the db option
to truncate on checkpoint. I don't want to be the one to log on and
verify the results of the job everyday so is there any way that I can
give him limited permissions so he could only check the job and leave
the database alone ? I am pregnant so I am afraid that next time he
does something like that I may hit him on the head with the keyboard
(hormones... :-)Any suggestions would help,
Thanks !!!1) Change the sa password immeditaely.
2) Change the job so that it emails you and the target user every time it
completes and/or fails.|||And give him a written warning if you can't sack him immediately.
dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20040301150956.18347.00000503@.mb-m06.aol.com>...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||2) Or grant him permissions only to execute sp_help_jobhistory.
"DCM Fan" <dcmfan@.aol.comSPNOAM> wrote in message
news:20040301150956.18347.00000503@.mb-m06.aol.com...
> 1) Change the sa password immeditaely.
> 2) Change the job so that it emails you and the target user every time it
> completes and/or fails.|||pramos00@.bellsouth.net (Patricia) wrote in message news:<b50e122b.0403011202.463c7c0e@.posting.google.com>...
> Hi guys,
> I have been told that only users with SA priviledges could check the
> status of a job. For this reason, I had to give SA priviledges to this
> user so he could check the results of a job (succesful or not) and do
> his work. Now, he makes changes on the database without telling me and
> last time we nearly lost one day of work as he changed the db option
> to truncate on checkpoint. I don't want to be the one to log on and
> verify the results of the job everyday so is there any way that I can
> give him limited permissions so he could only check the job and leave
> the database alone ? I am pregnant so I am afraid that next time he
> does something like that I may hit him on the head with the keyboard
> (hormones... :-)Any suggestions would help,
> Thanks !!!
Create a view that points to the job tables in msdb and give the user
SELECT permissions to this view. If the view is create with DBO the
user should be able to access the information without having SA
priviledges.
Something like:
IF OBJECT_ID('vcheckjobstatus') IS NOT NULL
DROP VIEW vcheckjobstatus
GO
CREATE VIEW vcheckjobstatus
AS
SELECT sj.name, sh.run_status, MAX(run_date) as MAXDATE, MAX(run_time)
as MAXTIME
FROM MSDB.DBO.sysjobs sj
INNER JOIN MSDB.DBO.sysjobhistory sh
ON sj.job_id = sh.job_id
GROUP BY sj.name, sh.run_status
GO
GRANT SELECT ON vcheckjobstatus TO user
Randy
www.Database-Security.Info
Wednesday, March 7, 2012
Help - Recovering a deleted job
Any ideas how I can recover a job that has been deleted (?) from SQL
7.0 ? We had one which was used on a regular basis which has now
ceased to exists. I've spoken to the people responsible for the server
and was met by blank faces (as usual). They have no idea when it was
deleted, by whom or when. To make matters worse, they don't think they
can get it off the backup set without a major bit of work to the
server. Suprisingly, none of the users noticed that things had stopped
working.
7.0 ? We had one which was used on a regular basis which has now
ceased to exists. I've spoken to the people responsible for the server
and was met by blank faces (as usual). They have no idea when it was
deleted, by whom or when. To make matters worse, they don't think they
can get it off the backup set without a major bit of work to the
server. Suprisingly, none of the users noticed that things had stopped
working.
Unfortunately, I needed to do some work on this in preparation for my
monthly meeting with my boss on Monday. Whilst I'm not to blame, I'd
like to understand a little more about any options available to us for
recovery as he will undoubtedly ask when he finds out.
I have an old back up on another server, so it's not the end of the
world re-creating it, but I was under the impression it was backed up
properly. Is this normal or have they missed something ?
Are there any ways I can recover this ? What can I get done to the
backup to make sure this is backed up properly ?
Thanks in advance.
Ryan> Are there any ways I can recover this ? What can I get done to the
> backup to make sure this is backed up properly ?
> Thanks in advance.
> Ryan
If you ahve a maintenance plan that covers the system databases then you
should have a copy of the msdb database somewhere - this is the one that you
want.
Using Enterprise manager, restore this backup onto the same server, but
under a database name of "OLDmsdb"
From here, you shoul dbe able to explore sysjobs and sysjobsteps.
It is POSSIBLE to move these entries into the relevant system tables on the
live msdb, but my recommendation would be to extract the data you need from
the old sysjobsteps table, then re-create the new job manually.
Steven
Monday, February 27, 2012
Help - DTS Custom Task to handle Job Failuer
I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party wont generate the source files at that day.
Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then dont run the package so I wont get the page.
Any hints suggestions would be greatly appreciated
J827I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.
I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).
Of course before you do this you need to already have your 50 DTS packages in place.
I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).
Good luck.|||create a table or array with the dates of all non action days (holidays weekends down days).
create a master package that runs all of your sub packages(use the execute package task).
create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @.nonactiveday, return 0
else 1
then pass that value as a global variable to the task
when the 1 hits, run the package
this is a fairly simple solution.
and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.
this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run...|||thanks thanks!|||Are you executing this from the web like in ASP.Net or something?
If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.|||I created a C# Console Application to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.
I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with Class library Type and tried to register / run Regsvr32 appName. I got Could not find entry point for DLLRegisterServer in the DLL / appName.DLL was load but the DLLRegisterServer entry point was not found, the file cant be registered.
Any helps would be highly appreciated!
---------------------
try
{
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
string ServerName = "DEV";
string ServerUserName = null;
string ServerPassword = null;
string PackagePassword = null;
string PackageGUID = null;
string PackageVersionGUID = null;
string PackagName = "DTSTestPackage";
package.LoadFromSQLServer(
ServerName,
ServerUserName,
ServerPassword,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
PackagePassword,
PackageGUID,
PackageVersionGUID,
PackagName,
ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);
package = null;
}
catch(System.Runtime.InteropServices.COMException e)
{
Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
catch(System.Exception e)
{
Console.WriteLine("Exception");
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then dont run the package so I wont get the page.
Any hints suggestions would be greatly appreciated
J827I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.
I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).
Of course before you do this you need to already have your 50 DTS packages in place.
I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).
Good luck.|||create a table or array with the dates of all non action days (holidays weekends down days).
create a master package that runs all of your sub packages(use the execute package task).
create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @.nonactiveday, return 0
else 1
then pass that value as a global variable to the task
when the 1 hits, run the package
this is a fairly simple solution.
and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.
this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run...|||thanks thanks!|||Are you executing this from the web like in ASP.Net or something?
If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.|||I created a C# Console Application to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.
I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with Class library Type and tried to register / run Regsvr32 appName. I got Could not find entry point for DLLRegisterServer in the DLL / appName.DLL was load but the DLLRegisterServer entry point was not found, the file cant be registered.
Any helps would be highly appreciated!
---------------------
try
{
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
string ServerName = "DEV";
string ServerUserName = null;
string ServerPassword = null;
string PackagePassword = null;
string PackageGUID = null;
string PackageVersionGUID = null;
string PackagName = "DTSTestPackage";
package.LoadFromSQLServer(
ServerName,
ServerUserName,
ServerPassword,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
PackagePassword,
PackageGUID,
PackageVersionGUID,
PackagName,
ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);
package = null;
}
catch(System.Runtime.InteropServices.COMException e)
{
Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
catch(System.Exception e)
{
Console.WriteLine("Exception");
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
Subscribe to:
Posts (Atom)