March 6, 2002 at 8:07 am
I have set up a new test server and loaded the databases via backup/restore from our production server. I am encountering a problem with a particular job that will not run. The only difference between the servers is the production server is Win NT 4.0 SP6 and the test server is Windows 2000 Server SP2. The SQL Server on both servers is SQL 7.0 SP3.
The job runs a DTS package (#1), which in turn runs a VB program. The VB program basically sweeps thru a set of subdirectories looking for files to process (this is the ETL front end of a data warehouse). Once a set of files is found, the VB program runs another DTS package (#2)and sleeps until a file is written out (which is written by another VB program called from DTS #2). For some reason, that end-of-job file is never written out. This process runs fine if you run the DTS package (#1) manually. It only hangs if you run from a job.
We use a service account for our SQL related services. If I change SQLAgent to run under the System account, it runs fine. But with our service account, it hangs. The service account has permissions to run these VB programs (and everything else on the server as Administrator).
Any ideas? Thanks in advance...
March 6, 2002 at 9:22 am
Does the service account have disk permissions too? Try using RunAs (if you're on Win2K) to test the app separately.
Andy
March 6, 2002 at 9:32 am
With RUNAS it works fine. The VB prog executes and writes the end-of-job file as it should.
March 6, 2002 at 11:17 am
When it runs the pacakge, does the VB program load and run the package or call sp_start_job?
Steve Jones
March 6, 2002 at 7:40 pm
Also try logging in as SQLAgentExec to make sure it is not some problem with it's permissions as it has control of the DTS package.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 2:53 am
Steve:
the DTS package #2 is executed from the VB program with:
Ret = Shell("DTSRun /S " & SERVER & " /N " & PACKAGE & " /E")
March 7, 2002 at 3:07 am
Antares686:
I cannot log on as SQLAgentCmdExec - is there a password?
SQLAgent (and also MSSQLServer, MSSQLServerOLAPService) are all running under our svcSQLDW service account. svcSQLDW is in the Administrator and OLAPAdministrator groups and has
full permissions on the VB programs, as well as the subdirectories which the files are being read and written.
I ran across this on the web:
http://www.microsoft.com/sql/using/tips/administration/May14.asp
But this does not seem to be the case here. Nothing (that I can tell) is using SQLAgentCmdExec.
March 7, 2002 at 5:24 am
Ok first see right click on SQL Server Agent in EM and choose properties then see which startup account both are on. If they are both using system then you are running under Local System account and may or may not be an issue but I cannot get in thru that. If it is "This Account:" then you will see SQLAgentCmdExec in the box but I do no know the default password. When I setup my server I change it in Users and corrected it with SQL Server Agent and made sure the Services listing was using it as well. The only way I know how to get the password is change it yourself. But if like you said it shows your user svcSQLDW then login as that account and verify everything is working in the user context as that is where it will occurr.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 7:29 am
Yeah, everything is using our svcSQLDW service account. This account is in the Admin group, so should have permission to do everything. When signed on as svcSQLDW (and other Admin accounts), I can manually run the VB prog and it runs fine. Everything works except running as a job in EM.
I've even taken the flipside approach: I've
set up SQLAgentCmdExec as the service account, set it with Admin permissions, file permissions, set it as the job owner, etc.
Same thing happens.
It's $#!+ like this that makes me long for my Sybase/Perl/Unix days.... 😐
March 7, 2002 at 8:04 am
Ok let's turn on the package logging or specify and Error File (which is close to the same) output for the DTS package to see if they will hint to us why maybe. If you will open your DTS packages and right click the package and view it's properties and set up either. For logging do here are the step from BOL:
How to Enable Package Logging (Enterprise Manager)
To enable package logging
Open the Data Transformation Services (DTS) package for which you want to create a log.
On the Package menu, click Properties to display the DTS Package Properties dialog box.
Do one of the following:
Save package logs to Microsoft® SQL Server™ by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.
Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
How To View Package Logs (Enterprise Manager)
To view package logs
In SQL Server Enterprise Manager, expand Data Transformation Services.
Do one of the following:
Right-click Local Packages (if the Data Transformation Services (DTS) package log was saved to Microsoft® SQL Server™) and then click Package Logs.
Right-click Meta Data Services Packages (if the package log was saved to SQL Server 2000 Meta Data Services), and then click Package Logs.
Click Local Packages or Meta Data Services Packages, and in the details pane, right-click a package and click Package Logs
or
Error file
Specify the name of the file where package and step status and error information will be written. This file will contain a list of the steps not executed, in addition to the steps that were executed and their result. The file can be on a local drive or on a mapped drive. If the file does not exist at package run time, the file will be created. The file does not have a default extension assigned to it; you must put the extension on the file name. The most common extension is .txt.
This will only help though if the problem is in DTS somewhere, if it is in your VB apps you have to figure out how to deal with those.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 9:26 am
I'm running SQL 7 & OLAP Services. There is no logging tab. I tried enabling the error handling - Package Properties / General / Error Handling / Error file: <filename> - but no errors are written out. Nothing. When I run the main package manually, the error logs do get generated but I do not have the problem when I run the package manually, only as a job, so that doesn't tell me anything.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply