Here's a situation you won't find every day.

I have a big database project that I am taking over. There is a set of
6 DTS Packages that import 6 tables from the Timberline accounting
software via ODBC every night. A 7th DTS Package calls each one of
these 6 DTS packages and is Scheduled to run every night by the SQL
Server Agent.

The nightly update runs fine. The weird part is that if I try to run
7th Package, or any of the 6 individual packages, by right-clicking on
them and clicking "Execute Package", the package fails!

I could google for tons of results on permission issues when you could
Execute a Package but couldn't get it to run from the SQL Agent, but
this is the other way around.

The packages each delete the contents of a sql table and then use an
ODBC connection to query for live new data to re-populate the table.
The packages fail on the step where they are creating the ODBC link.
The error says something pretty close to "[ODBC Timeberline] Can't find
table 'TableName'".

I'm logged onto the SQL Server itself, and am using Windows
authentication for the SQL Agent. What in the world could be different
between using the SQL Agent to run the 7th "calling" package and
running that 7th package by right-click, Execute Package, that would
cause troubles with the ODBC link?

Thanks in advance,
Toby

Any ideas?

Thanks,
Toby


bierlyt (AT) gmail (DOT) com wrote:

Toby,

How is the DSN connecting to Timberline? Windows or SQL Server
authentication?

Are you logged into the server as you or as the account that is running
the SQL Agent service? That could make a big difference.

How do things work if you copy the DTSRUN command(s) from the job
step(s) and paste it into an xp_cmdshell in Query Analyzer?
EXECUTE master..xp_cmdshell 'DTSRUN <yourStuffHere>'

You could also query master..sysprocesses.
select * from master..sysprocesses where program_name like '%DTS%'
or
select * from master..sysprocesses where program_name like
'%<putJobIDHere>%'

Look at the loginame and nt_username columns while running it manually
versus via the job.

The profiler would also help. Set the ApplicationName filter to %DTS%
and %job%

Norman

get DTS Package Search at http://www.gerasus.com/


bierlyt (AT) gmail (DOT) com wrote:

Thank you for the reply.

I'm not sure what you mean on your first question. There is a
Timberline user & pass that I have to put in. I'm not using Windows or
SQL Server authentication for that.

For the SQL Agent, I am using Windows authentication.

I am logged into the server as me, the SQL Agent runs things as System.
Thank you for the tip on how to check sysprocesses -- that helped me
confirm this.

Whenever the package executes as System, everything works great. When
it executes as me, it give the an error like "[Timberline ODBC]Unable
to open table: TableName. No such table or object." Actually I can
trigger the exact same error by opening the package in design mode and
going to the Transformation tab of the Transform Data Task Properties.

Obviously, there is a permissions issue accessing the Timberline tables
via ODBC from my account. Not sure where to fix this.

I was able to figure out how to use the DTSRUN command from a stored
procedure (which runs the package as System), which I can then call
from Access, which was the entire goal to begin with.

Thank you also for the tip on how to execute DTSRUN from the Query
Analyzer. That helped a lot too.

I'd still like to get figured out how to Execute the package when ran
by me. Otherwise, it's going to be a pain in the butt (or perhaps
impossible) to set up transformations for a new package when I need to.

Thanks,
Toby

Norman Kelm wrote:

I am trying to create some DTS packages to pull from Timberline, could you give me an email with some instructions. Everything I find on the web crashes.

Thanks,

From http://www.developmentnow.com/g/103_...kage-Fails.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/