Hi -
We are running on a new SQL2000 server in which we have just gone live
with. We had switched over from the 6.5 box, in which we had to
transfer the data from the AS400's with DataMirror(yuck). We have DTS
packages created that pull data from the AS400 boxes and then populate
tables with this data. We are running into problems due to the fact
that in the SQL Server agent we have jobs that are scheduled to run,
whether that be weekly, monthly or daily, and are coming back as
successful, but they are not populating the tables. When you manually
run them, they work. We have gone through all of the 2000 or so
packages that have been created (several times), and checked for rouge
truncate statement, but we have found none. Could anyone give his or
her opinions as to what could be the possible problem? Could it
possibly be a networking issue?

Thanks in advance,
Brandon

P.s. I'm just a intern, looking for a career in d.b.a. someday…

In addition to Andrew's reply which would be my first port of call I have
seen Agent report successful on DTS packages that clearly cannot be valid (I
have deliberately messed up the package), even logging within the package
returns good.

When you say it works fine manually, are you sat at the server ? or your PC
?
Try logging in as the Agent account and running it on the server itself.
This will highlight any permissions issues

http://support.microsoft.com/?kbid=269074

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Yeah, sorry when i say manually, i mean that when you run them on the
server itself, the package runs successfully. We have made a aspx page
that gives back the row counts, and thats how we check to see if the
tables are being populated. We are using SQL2000 as a bucket for the
data. One question i have is why would it be a permissions issue, if
they run succesfully say, a week or two, and then randomly not work?
That is whats confusing me.

Thanks in advance,
Brandon





"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Have you checked other than the asp page to make sure the rows are not
really there? Sounds like someone may have messed with the system since it
last worked that you didn't know about.

--

Andrew J. Kelly
SQL Server MVP


"Brandon McNamara" <Bmcnama1 (AT) oswego (DOT) edu> wrote

Guys,
Yeah we go into query analyzer and run a select count(*) from the
database that shows no row counts, and sure enough there are no row
counts. As far as filtering do you mean using WHERE, and HAVING
statements?
No we dont use those statements within the tables. In the views that we
create for the users, yes we use statements like those. But other than
that the tables are just buckets for the data from the AS400's

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Well you might want to take a look at this KB. If you run it manually and
it works OK but not when it's scheduled it really sounds like permissions.

http://www.support.microsoft.com/?id=269074 How to Run a DTS Package as a
Scheduled Job


--

Andrew J. Kelly
SQL Server MVP


"Brandon McNamara" <anonymous (AT) devdex (DOT) com> wrote