Hi everyone,

I'm trying to schedule a DTS package to import data from Timberline into an
SQL Server database.

Here's the setup:
i) The DTS package is set up as a local package on the machine running SQL
Server. Timberline runs on a different server and there is an ODBC
connection between the two servers. We want to copy two tables from Timberline
to the SQL database twice a day, for use on our Intranet (running ColdFusion).

ii) When I run the package in Enterprise Manager by right-clicking on it then
selecting "Execute Package", it runs fine. I have administrator privileges on
the SQL server, but not on the network. I have access to the folders on the
Timberline server. I am the DTS package owner.

ii) When I schedule the job it fails. The SQLServerAgent service and the
MSSQLServer service on the server both run as a network account which I
suspect is *not* a domain administrator, but I'm not sure. The DTC
Coordinator service runs under LocalSystem. The owner of the job is SA.

From what I've read, the problem I'm having is most likely permissions
related. Does anybody know what kind of permissions SA and/or the
SQLServerAgent service need to have on the SQL server machine, on the network
and/or on the Timberline server in order for the DTS package to execute
automatically? Or would changing the ownership of the DTS package or of the
job help?

I really need to get this working as a matter of urgency so any suggestions
would be greatly appreciated. Thanks in advance -
Sherlyn Koo
koo (AT) shfa (DOT) nsw.gov.au

What exactly is the error ?

The SQL Server Agent account needs to be able to access the other server and
if that uses Windows authentication then the account also needs the correct
privileges. The Account also needs to be able to see the DSN and use it
then you have created.

Easy test is to log in as the SQL Server Agent account and try to run the
package.

--

----------------------------
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



"Sherlyn Koo" <koo (AT) shfa (DOT) nsw.gov.au> wrote

Hi Allan,

Thanks for your feedback.

I'm not sure if it's salient, but I did neglect to mention it earlier
- we're using SQL7.0 with SP4 on Windows 2000. The Timberline DSN is
of type "Timberline Data" and points to the folder on the Timberline
server which holds the data files. I can execute the job manually in
Enterprise Manager regardless of whether the DSN uses a mapped drive
or UNC.

This is the error message from the SQL Server Agent Job History:

--begin error message--
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147217843 (80040E4D) Error
string: [Timberline][ODBC Driver][DLL]No such database. Error
source: Microsoft OLE DB Provider for ODBC Drivers Help file:
Help context: 0 Error Detail Records: Error:
-2147217843 (80040E4D); Provider Error: 1203 (4B3) Error string:
[Timberline][ODBC Driver][DLL]No such database. Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
--end error message--

So then I guess, according to Microsoft KB article 269074, I need to
"grant the SQLAgentCmdExec account proper login and database
permission rights to the SQL Server". Is this the same account under
which the SQLServerAgent service runs? Currently SQLServerAgent runs
under a domain account but I'm not sure what kind of rights this
account actually has across the domain - I'll have to find out from
IT. It is defined as a system administrator for SQL Server though.
Or is there a way to ensure that the job is always run from my
account?

Sorry if I'm asking stupid and/or obvious questions, I've kind of been
thrown in the deep end with this DTS stuff, and of course it's
super-urgent. Thanks for your help...

Sherlyn Koo
koo (AT) shfa (DOT) nsw.gov.au



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

[Timberline][ODBC Driver][DLL]No such database

Would suggest permissions for the account are not there. SQL Server Agent
will fire the job. If the job owner is not a sysadmin and the job step type
is a CmdExec then the proxy account will be the account doing the work.

--
--

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

"Sherlyn Koo" <koo (AT) shfa (DOT) nsw.gov.au> wrote

Hi Allan,

Thanks again for your feedback. As it turns out, the plot has
thickened.

As stated before, I could create and run the DTS package to import the
Timberline data perfectly while logged in as myself, but it then
wouldn't run when scheduled. I managed to get access to the SQL
Server machine as the NT account under which SQLServerAgent runs.
Using this account I had no problems creating, scheduling and running
another DTS package to access Timberline. However for some reason the
SQLServerAgent account could only see a few of the hundreds of
Timberline tables, none of which are the ones we are actually
interested in. So it's a bit strange but I guess it's some kind of
Timberline permissions issue. We'll be chasing this up with our
Timberline support people and hopefully they'll be able to resolve it
for us soon...

Regards,
Sherlyn Koo
koo (AT) shfa (DOT) nsw.gov.au

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

It certainly sounds that way. You have proved connectivity and you can see
a number of databases. The rest sounds like specific permissions issues
with the other DBs. Thanks for geting back to us.

--
--

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

"Sherlyn Koo" <koo (AT) shfa (DOT) nsw.gov.au> wrote

Timberline also uses a mapped drive. If a user is not logged in and the mapped drive is unavable, then the database will be unavailable.
Timberline (Pervasive) datafiles are mapped for the ODBC and is usually the T Drive.


What I do is use SQL Client tools and DTS Run package name and make sure that all file locations are same etc... (using UNC Paths or mapped drives)

Hope this helps.


From http://www.developmentnow.com/g/103_...Timberline.htm

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