I suspect this is a user-rights/security issue:
I have a DTS package that needs to run weekly. This DTS package runs fine
when you open it and select "execute". I have done the following to set
this up as an automated task:
1)Go to DTS/Local Packages and right click the package.
2)Select "Schedule" from the pop-up menu.
3)Change settings to "weekly" and set the time to the current time + 5
minutes
4)Go to Management/SQL Server Agent/Jobs and refresh
5)Find the job that you just created, right-click and choose 'properties'
6)Confirm the settings (Owner: <my login name chosen from list> ) and let it
run.
I set up the "Notifications" tab to send me an email on completion which it
does:
========================================
================
JOB RUN: 'PhoneImportFromHR' was run on 2/12/2004 at 2:30:00 PM
DURATION: 0 hours, 0 minutes, 11 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 32
(PhoneImportFromHR). The last step to run was step 1 (PhoneImportFromHR).
========================================
================
Due to the fact that the DTS package runs fine when I run it, but won't run
automatically, I am thinking that this is a security issue (ie. The
automated process does not think I have rights to run this package, but
recognizes me when I try to run it manually). I have tried other login IDs
in the "Owner" box (see step #6 above), but I always get the same result.
Can anyone tell me what I can do to get DTS jobs to run automatically?
Schoo
PS: Windows 2000 Server, running SQL 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided. since you got the email notification,
the job runs and you the notification is working fine. For the job to run
automatically, could you check the job schedule. You could right-click the
job, then choose 'properties'; In the 'Schedule' tab, you will notice the
schedule of how you job runs. If you want to change the schedule, you could
press the 'Edit' button. You could arrange a new schedule of the job, Then
the job will run automatically. You could confirm by right-click the job
and choose the 'View job history' and check the record of how the job runs.
For the 'Notification' button, you could check the 'Email operator', choose
one operator and select when to send the email notification.
Hope this helps. If you still have questions, please feel free to post your
message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for your response... perhaps I was not clear enough on what is
happening. All of the items you mention in regards to changing a schedule,
I am aware of. I am saying that the actual job starts, fails and I get an
email notifying me that the job fails (see original email in this string).
The issue is not how to run the job automatically or how to get it to email
me. The issue is that the DTS package will run fine if I open it up and
execute it, but not if I create a job and try to run it automatically. My
questions is: how can I get the DTS package (that runs manually without
errors) to run without errors automatically.
I hope this is more clear and also that you might shine some light on this
issue for us.
Scott
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Scott,
> Thank you for using the newsgroup and it is my pleasure to help you with
> your issue.
> From the information you provided. since you got the email notification,
> the job runs and you the notification is working fine. For the job to run
> automatically, could you check the job schedule. You could right-click the
> job, then choose 'properties'; In the 'Schedule' tab, you will notice the
> schedule of how you job runs. If you want to change the schedule, you
could
> press the 'Edit' button. You could arrange a new schedule of the job, Then
> the job will run automatically. You could confirm by right-click the job
> and choose the 'View job history' and check the record of how the job
runs.
> For the 'Notification' button, you could check the 'Email operator',
choose
> one operator and select when to send the email notification.
> Hope this helps. If you still have questions, please feel free to post
your
> message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||questions are
1) who is the owner of the job
if owner of job is member of sysadmins fixed serverrole then job will run
dts in job owner's context
if owner of the job is not member of sysadmins then job will attempt to run
in sqlproxyaccount's context(if sql proxy account is configured) if not you
will get a failure
Olu Adedeji
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OEcdtEm8DHA.4044@.tk2msftngp13.phx.gbl...
> Thank you for your response... perhaps I was not clear enough on what is
> happening. All of the items you mention in regards to changing a
schedule,
> I am aware of. I am saying that the actual job starts, fails and I get an
> email notifying me that the job fails (see original email in this string).
> The issue is not how to run the job automatically or how to get it to
email
> me. The issue is that the DTS package will run fine if I open it up and
> execute it, but not if I create a job and try to run it automatically. My
> questions is: how can I get the DTS package (that runs manually without
> errors) to run without errors automatically.
> I hope this is more clear and also that you might shine some light on this
> issue for us.
> Scott
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
run
the
the
> could
Then
> runs.
> choose
> your
rights.
>|||Hi Scott,
Thanks for your update and Olu's reply.
Besides Olu's question, could you run the DTS package in the design windows
of the package? Is it successful or any information? When the mail is send
to you, what is the job history? You could also run the following code in
you Query Analyzer and collect the information of this job.
select * from msdb..sysjobs
select * from msdb..sysjobsteps
select * from msdb..sysjobhistory
select * from msdb..sysnotifications
select * from msdb..sysjobservers
select * from msdb..sysjobschedules
I am waiting on your reply. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott:
How about everything going? Now I would add some more information on this
issue:
There is one good article for your reference:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Also, you could add the error file to capture the detailed error happened
when run the DTS package:
In the DTS package design window, choose from menu, 'Package'->'Properties'
and set the options in the 'Logging Tab'. You could refer to the DTS
Package Properties (Logging Tab) in the SQL Server Books Online.
Hope this helps. If you still have questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Showing posts with label suspect. Show all posts
Showing posts with label suspect. Show all posts
Thursday, March 8, 2012
Monday, February 13, 2012
Auto Restore of Production to Developement Db on same Server
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||
Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||
ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||
thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||
Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||
ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||
thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Subscribe to:
Posts (Atom)