Has anyone ever successfully scheduled a DTS package to
connect and extract data from an Interbase/Firebird
database?

My DTS package will run under SEM but fails when
scheduled. Most of the time this would be linked to
permissions in some way but I have very carefully setup a
test environment and used a login with sysadmin/local
administrator/domain rights. I have checked that SQL
Server Agent + Proxy + SEM all use the same login.

I get the following error:

Step Error Source: Microsoft OLE DB Provider for ODBC
Drivers
Step Error Description:[XTG Systems][InterBase6 ODBC
Driver], unavailable database
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Any input appreciated.

My setup is:

MS SQL Server 2000 with SP3
MDAC 2.8 RTM
Interbase v6 /Firebird 1.03 db Server
Firebird ODBC Driver
Connection via ODBC - tried System DSN and File DSN

On Fri, 23 Apr 2004 07:37:41 -0700, "Neil" wrote:

G'day Neil,

It is most likely because of your connection type. If you don't specify
a TCP/IP connection, then you are probably connecting via named pipes.
Change to using TCP/IP and you should be OK.

e.g. Database=servername:c:\databases\yourdatabase.gdb

cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi

Ross,

thanks - you were right. When I specified a TCP/IP
connection it executed successfully when scheduled.

What surprised me is that SQL Enterprise Manager
successfully ran the package when I hadn't specified the
type of connection. Presumably it 'assumed' a TCP/IP
connection whereas SQL Server Agent assumes a named pipe.

Thanks again.

Neil

On Mon, 26 Apr 2004 01:41:36 -0700, Neil wrote:

No, what the deal with named pipes is that you need an authenticated
security context that permits the creation of named pipes. When you are
using SQL EM, you are obviously logged in to Windows and must have
permission to open a named pipe to the server that Interbase is sitting
on. When you run your scheduled task, it is probably running as SYSTEM,
and has no permission to open a named pipe to the Interbase server.

This is always a fun trap

cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi