| Author |
Topic |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 13:06:26
|
| What does the job do? Have you tried dropping and re-creating the job? |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 14:42:15
|
| I haven't tried dropping and recreating.The job pulls in records from another database into stagging tables.Next it runs sql to compare the records from the stagging tables agains the existing records and updates accordingly. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 15:35:54
|
| Could it be that the data it pulled from the other database didn't have a lot of changes or new rows? Or that it wasn't updated at all since the last job execution? |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 15:51:10
|
| I dropped the job, and recreated the job.I also added insert statements to the package that write to a log table.I get no entries in the log table and the job still says it executed correctly in 0-1 seconds. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 15:55:09
|
| The first step in the job is to log that it's starting the job and the second step in the job is to truncate the stagging tables and it's not doing either. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 16:07:09
|
| Do you have any BEGIN TRANSACTION constructs in there? If there's a ROLLBACK in there anywhere it will undo everything without throwing an error (unless you specifically RAISERROR along with a ROLLBACK) Even then the error may not fail the job unless you set the job step to do so.Have you tried running the SQL in Query Analyzer? Is this a DTS job by any chance? |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 16:49:32
|
| It's a DTS job the job executes dtsrun /~Z[PackageID].The DTS package has use transactions checked off.Run all the queries in the dts except transformations in Query Analyzer and it runs fine.Run the DTS package manually and it runs fine. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 17:08:20
|
| There might be a permissions issue, especially if any of the SQL Server services run on a domain account. Ask your network admin if any rights or group memberships were changed for those logins. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 18:12:56
|
| I have access to the machine via shared drive.I can execute command line statements.Is there an easy way to determine if I have the right permissions to run dtsrun.exeI tried running dtsrun via command line but I keep getting "Invalid hex text for encrypted parameter" |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-24 : 19:44:18
|
| My alternate for now was to write a vbs script that creates a DTS.Package object and schedule it using the windows task scheduler.I think from now on I will have a dedicated account that all it does is execute dts packages and scheduled jobs.Edited by - ValterBorges on 10/25/2002 09:13:12 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-25 : 19:35:50
|
| Valter, remember that when this is run as a scheduled job in SQL, it is running under the permissions of the SQL Agent, and not under your own permissions. I find this is often the culprit when a job can be run manually but fails when scheduled. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-26 : 00:46:58
|
| The sql agent is using my nt account.The job was running fine for 2 months and then one day it just stopped. I'm sa on the sql server and last I know I had admin rights on the box. The box in question is leased from another company and they take care of the network administration.I've been trying to get in contact with the admin but no luck. Anyways vbs and windows scheduler solution is working great. I actually prefer this because if the box goes down the vbs will fail and notify me and I can have one central scheduler for all the sql servers and all the task. |
|
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2002-10-27 : 03:15:35
|
| Yes what a intresting discussion ."Heaven's light is our guide"Sanjeevshrestha |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-10-27 : 04:24:06
|
quote:
I tried running dtsrun via command line but I keep getting "Invalid hex text for encrypted parameter"
Looks like somehing has changed invalidating the DTSRUN commandline parameters. Instead of switching to VBS and Windows scheduling, I would just right click on the DTS package, select 'Schedule Package' and create a new job and get rid of the old one. This should fix it. You could even edit your job step and specify the required arguments. DTSRUN is documented in SQL Server Books Online--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
|
Baby You're Much To Fast