DTS works but schedule JOB don't

Question

DTS works but schedule JOB don't

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

AER SSCrazy Points: 2763 More actions · Answer 1

James,

Thank you for your response. Actually, in SQL7 there is no tab for Logging in Package Properties; but you can specify the logs inside the package.

And yes, it will be very interesting if you could describe how you specifying the names of the processes you run inside your package.

Thank you in advance.

Alex

jloesch Ten Centuries Points: 1004 More actions · Answer 2

I guess I better first ask: in SQL 7 DTS Package Designer, is there an option on the Package menu for 'Disconnected Edit'?

James C Loesch

jocke SSC Rookie Points: 38 More actions · Answer 3

I had the same problem with a package in SQL 7.0 using a Interbase ODBC connection.

The packed executed ok when a ran it manually, but when I scheduled it it never finished.

I fixed the problem by uncheck the 'use OLE DB service components' under -'package properties' and 'advanced'.

Maybe this will help you...

/Joakim

AER SSCrazy Points: 2763 More actions · Answer 4

Thank you guys so much for your response.

There is no 'Disconnected Edit' option in SQLServer7, but I guess that 'Use OLE DB Service Components stands for it?!

I unchecked it and the job worked fine for me.

Joakim, thank you so much. This was one the most elegant I've seen before.

Sincerely,

Alex Ruvinsky

P.S. I would greatly appreciate though if anybody could explain why did the job run successfully with this option unchecked if I'm still using OLE DB architecture.

jloesch Ten Centuries Points: 1004 More actions · Answer 5

Disconnected Edit is actually something completely different. Based on the name, I guess it was intended to be used in cases where you wouldn't be connected to a data source. You can use it to change just about anything you want in a DTS package. In the help it is compared to the registry editor, you can do all kinds of stuff, but you better know what you're doing. Its a great tool for *really* messing things up.

The service components option has to do with how your OLE DB objects are created. Checking it basically adds another layer of objects.

James C Loesch

ghernandezsuarez Newbie Points: 9 More actions · Answer 6

CASE #0:

Dont work de Local Package,

SOL:

Used DISCONNECT EDIT, and review All [Connections] STRINGS

CASE #1:

DTS create with EM SQL 2000, do NOT run as JOB with SQL 7.00 SP1,SP2,SP3, but RUN as DTS, is a BUG

SOL:

Recreate the JOBS with EM SQL 7, on the machine with SQL 7.

CASE #2:

SQL 2000 / SQL 7.00

DTS/JOBS create a one machine and export to other RUN, as DTS , but the same JOBS do NOT WORK.

SOL:

RE-CREATE Jobs[ EM->DTS->Package->schedule Packages ], Test Job created,

next copy the NEW -> Execute package:

DTSRun /~Z0x8898C5E5B46B1D5AA840114C9B3E6A52FC0E6C4FB8BD453F46127F08FEA7B9C9DCABDDF80BBFEBABF933D811....

into the Old JOB->Task or rename the job

This command [DTS] are create with the SERVER/USER/PASS of the owner (:-@)

YOU MUST create again, if was any problem with permission o run problem.. sorry

I Hope this Help

stanislavpetkov SSC-Addicted Points: 474 More actions · Answer 7

Try to setup the SQL Server and the SQL Server Agent under the same account. The account should be both member of the local admin group and should be member of the SysAdmin Server Role. Change the job definition in SQL Server Agent /Jobs so that the owner is SA.

Stan

Stanislav Petkov

johnnypenet SSC Rookie Points: 46 More actions · Answer 8

To schedule DTS jobs, I use a small utility , EZ Scheduler (free) from American Systems (www.americansys.com). By using this schedule program, the environment of the SQL server is preserved for the task you schedule. This is very usefull when you work in a mixed network environment (ex Novell) with required drive mappings or when ODBC drivers requires some drive mappings.

It is very simple to use.

jloesch Ten Centuries Points: 1004 More actions · Answer 9

Karen, the account the SQL Server Agent is running under is the one that will be used for any jobs that run. If that account has admin priviledges, then the jobs should run with no problem. The agent that was having problems is probably running under a user account that doesn't have full permissions.

Keep in mind when clearing the check box requiring sysadmin priviledges that doing so could be opening you up to a security risk. Rather than clearing that check box, you might want to make sure your SQL Agent is running under the appropriate account.

I just discovered something, and so am correcting myself here. SQL Server agent jobs have owners, and the owner is checked for execute permissions before the job is executed. It is still executed under the account that SQL Server Agent is running under, but it won't execute if the owner doesn't have execute permissions. You can view and change the owner of a job in the properties dialog for that job. So, the correction is: when the job failed, either the Agent was running under a user account that didn't have execute permissions, or the owner of the job doesn't have execute permissions. You have to check both.

James C Loesch

jloesch Ten Centuries Points: 1004 More actions · Answer 10

The only way that I know to give non-admin users the execute permissions that you want is that check box in the Agent properties, which results in an extended stored procedure being called. You could call the procedure directly, but it results in the same thing. You're essentially giving administrative access to non-admin users. I think a more viable option would be to change the owner of the job to an administrative account.

James C Loesch