June 20, 2003 at 4:08 pm
I am trying to get a DTS package to run on a schedule.
I used the following from the Books On Line:
How to schedule a DTS package using the Schedule Package option (Enterprise Manager)
To schedule a DTS package using the Schedule Package option
In the SQL Server Enterprise Manager console tree, expand Data Transformation Services, and then click either Local Packages or Meta Data Services Packages.
In the details pane, right-click the Data Transformation Services (DTS) package you want to schedule, and then click Schedule Package.
In the Edit Recurring Job Schedule dialog box, complete the required information.
Note The scheduled package will be executed by SQL Server Agent using the permissions specified during server registration. If Windows Authentication was used, then SQL Server Agent will attempt to load the package using its own security, which may not be sufficient to load the package. For more information see Handling Package Security in DTS.
All the package does is start then hangs. It does not fail, it does not finish, no errors, no messages in the logs or Event Viewer.
Help!
ks
Edited by - kschorz on 06/20/2003 4:11:00 PM
Edited by - kschorz on 06/21/2003 12:55:27 PM
Kevin S.
June 20, 2003 at 4:18 pm
If htere is any interactive section, like a dialog box, it will hang. Run it interactively and be sure there is no dialog that appears.
Steve Jones
June 20, 2003 at 4:24 pm
If htere is any interactive section, like a dialog box, it will hang. Run it interactively and be sure there is no dialog that appears.
Thank you for the reply Steve.
We have already eliminated any interactive dialogs. Now (the plot thickens) The DTS package worked fine until we applied SQL Server 2000 SP3 to guard against the SQL Slammer virus. Now no go! We have tried this same package on a fresh install of SQL Server 2000 without any patches and it worked fine.
Any ideas?
Thank you!
Kevin S.
Edited by - kschorz on 06/21/2003 12:55:09 PM
Kevin S.
June 20, 2003 at 4:25 pm
quote:
If htere is any interactive section, like a dialog box, it will hang. Run it interactively and be sure there is no dialog that appears.
Thank you for the reply Steve.
We have already eliminated any interactive dialogs. Now (the plot thickens) the DTS package worked fine until we applied SQL Server 2000 SP3 to guard against the SQL Slammer virus. Now no go! We have tried this same package on a fresh install of SQL Server 2000 without any patches and it worked fine.
Any ideas?
Thank you!
Kevin S.
Kevin S.
Kevin S.
June 21, 2003 at 8:46 pm
what is the package trying to accomplish?
I run into similar problem when trying to call a oracle script using sql+ from DTS package. I forgot 'exit' command on the script.
June 23, 2003 at 12:39 am
Read the Service Pack 3 Readme.htm. I also had a problem with SP3 because it disables ad-hock queries. I used OPENQUERY.
Hope that helps,
Jani.
June 23, 2003 at 2:31 am
There is a problem with DTS and SP3 if you are accessing a table with a large no. of columns. There is an earlier thread covering SP3 issues.
Andy
June 23, 2003 at 6:37 am
You should also check to make sure the SQL Agent is logging in with the appropriate permissions. I've also seen this problem if you have problems with the version of MDAC that you are running.
June 23, 2003 at 10:17 am
Thank you all so much for the replies.
I have checked a few things: I have the DisallowAdhocAccess registry option explicitly set to 0 (zero) per the SP3 Readme 5.1.7.
The DTS package just runs a Visual Basic ActiveX Script. I can post it here but it is a little long.
I also have several DTS Jobs that are executed by the SQL Server Agent that are working fine and they were created/owned by the same user.
I am looking at some other things but I am really stumped this time. As I stated earlier, this DTS package executed fine until we applied SP3.
Any other ideas?
Thank you.
Kevin S.
Edited by - kschorz on 06/23/2003 10:37:12 AM
Kevin S.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply