Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Skorch
Constraint Violating Yak Guru
300 Posts |
 Posted - 2011-01-13 : 12:57:21
|
| I have a DTS package which connects to Oracle via OLE DB and transfers data to several tables from my SQL Server database. There are two threads in the DTS running concurrently to speed up the execution. When running the DTS manually, it works just fine and completes in about 5 minutes. However when scheduled through a job, the job starts and hangs indefinitely on the first step. This happens both in QA and prod environments, but does not occur in dev. In all cases the job is created from an account with sysadmin privs. Deleting and recreating the job does not resolve the problem, and the issue is there regardless of whether the job runs through a schedule or executed manually. Any ideas on what could be causing this?Some days you're the dog, and some days you're the fire hydrant. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-13 : 13:02:43
|
| are three servers having same configuration?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-01-13 : 13:03:34
|
| Yes, I believe so.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-01-13 : 13:23:10
|
| Actually turns out dev and QA run SQL 2000 SP3, while prod runs SP4. That still doesn't explain why the job succeeds in dev but not QA or prod though..Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 14:28:19
|
| What is the 1st step?Are you sure the connection string to Oracle is the same on all 3 servers? |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-01-13 : 14:50:19
|
| The first step is to truncate data in the first table in Oracle. Connection strings are not the same throughout the environments as they connect to the dev, QA and prod Oracle databases, respectively. However I don't think the Oracle connection is the issue here, as the DTS runs fine when executed manually (not through a job)Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 14:59:47
|
| Is the Oracle client installed on the server?When you run it manually, are you logged into the server or are you doing it from a workstation?Of course the connection string matters. It is 100% critical. Can you manually connect to Oracle using the connection string that this DTS uses?When you say that the first step truncates a table in Oracle, is this the first step in the job, or the 1st step in the DTS?Are you logging the output from the DTS? if so, can you post it? |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-01-13 : 15:51:45
|
| Yes, Oracle client is installed on the server and I can connect to Oracle using the credentials the DTS uses.When run manually, the DTS ran successfully both from the server directly and a workstation.The first steps which truncate the Oracle tables (I say steps because both are executed as they are run in parallel) are the first steps in the DTS. The job which executes this DTS only contains one step, which is to execute the DTS. It was created by scheduling the DTS.The DTS is logged, and the logs for the times when it attempted to run through the job only show the first two steps (parallel ones) with a Start Time and no End Time. Both have and Elapsed Time and Error Code of 0.Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
|
|
|
|
|
DTS job hanging