cannot schedule package

[OhioSteve]

Here is my situation:

1) I need to periodically move data from an Oracle DB to an SQL Server DB. Each db has its own server, but they are on the same domain.

2)The SQL Server server DOES have the Oracle client on it. However, it is NOT a linked server.

3) I have a laptop on the same domain. My laptop does not have an SQL Server db, but it does have enterprise manager.

4) I used my laptop to make a dts package on the sql server server. It works great!

5) The admin of the sql server server scheduled my package. He told me that he scheduled it under an ID with plenty of permissions. I have every reason to believe him.

6) The scheduled task does NOT run. The error message is "The Oracle client and networking components were not found..."

Why doesn't it run!?

 

[mrdenny]

Connecting to Oracle is very tricky. You need to install the same version of the Oracle client that is on your laptop. The drivers need to be configured the same way that the drivers on your laptop are configured. The tnsnames.ora file will need to have the same entries.

If anything is out of wack, then the DTS package won't run.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mercurymik]

I expect you have setup the database connection on your laptop. When you run a package from your laptop it uses the ODBC or connectivity drivers on your laptop. When you schedule it to run from the server, the server kicks off the job and the connectivity drivers (ODBC) need to be on the server. ergo... the Oracle Client must be on the SQL Server. The following article is for SQL Server 7, but it applies to SQL 2000.

http://support.microsoft.com/default.aspx?scid=kb;en-us;220915

I am having a similar problem, I have created the ODBC's on both my laptop and the SQL Server 2000 (which is running on a Windows Server 2003 box). I can run a DTS package manually that takes data from my SQL server and puts it into a table on the Oracle 9i database from either machine. When I go to schedule the DTS Package, it fails with this Error:
Error: -2147467259 (80004005); Provider Error: 0 (0)

Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Error source: Microsoft OLE DB Provider for ODBC Drivers

Help file:

Help context: 0

Can anyone help with this error?

 

[mrdenny]

Try using the Oracle native drivers instead of the generic Microsoft ODBC drivers. The actual oracle drivers should work better.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)