April 14, 2004 at 12:50 pm
I wrote a DTS package with ActiveXScript Task using VBscript to run Ms Access report to snapshot viewer. It worked when I click Execute button. When I send to Scheduling, it hadn't done anything. It's running forever until I cancel the job.
Here is my Code. Can you tell me any wrong or how can make it run for scheduling. Thanks.
Function RunUnder18DeletionReport ()
Dim objAccess
dim strSql
strSql = "SET CONCAT_NULL_YIELDS_NULL OFF " & _
"SELECT Minors.CourtYear, Minors.DistrictCode, Minors.CourtCode, Minors.DistrictCode + Minors.CourtCode CourtCde, " & _
"JurorNumber, TownCode, LastName + ' ' + FirstName + ' ' + MI + ' ' + Suffix Name, DOB, Address1, Address2, " & _
"City, State, Zip1, DisqualStatus, DistrictDescription, CourtDescr " & _
"FROM (Minors INNER JOIN Districts ON (Minors.DistrictCode = Districts.DistrictCode) AND (Minors.CourtYear = Districts.CourtYear)) " & _
"INNER JOIN Court ON (Minors.CourtCode = Court.CourtCode) AND (Minors.DistrictCode = Court.DistrictCode) AND (Minors.CourtYear =Court.CourtYear) " & _
"Where DisqualStatus = 'X';"
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Jamis\Admin\Reports1.mdb"
objAccess.Run "PassthroughQuery", "qryDeleteUnder18", strSql, "ODBC;DSN=SqlServer;UID=Jamis;PWD=Watcher;Trusted_Connection=Yes"
objAccess.DoCmd.OutputTo 3, "rptDeleteUnder18", "Snapshot Format", "C:\Jamis\Admin\Reports\rptDeleteUnder18.snp", False
objAccess.CloseCurrentDatabase
objAccess.Quit
RunUnder18DeletionReport = DTSTaskExecResult_Success
End Function
April 14, 2004 at 1:41 pm
It sounds like a permission issue. Since you are using Trusted_Connection, make sure that the logged in user has the appropriate permissions to access the Data Source. You may also wish to include objaccess.DoCmd.SetWarnings(0) to turn off system messages and modal dialog boxes from appearing on the server during unattended execution. Hope this helps.
April 15, 2004 at 8:38 am
in addition to the permissions problem, if you are being prompted, the the job will appear to "hang". sometimes when you run something like this through a job the system will attempt to prompt you. since it is unattended no one responds.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
April 15, 2004 at 9:25 am
I think permission problem. I unclick box "Only user with SysAdmin ...." and I set to Proxy Account to my ID. I delete the job and create a new job. I still hang. When I look in the PackageLog table, I see the operator is NT Authority/System. Is it mean that this user run the job schedule and It doesn't have SysAdmin Previlige. How can I set it up to SysAdmin PRevilege? When I go to security and Login I don't see this user ID. Any Help? Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply