 | |
DTS Package Schedule job failed
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: DTS Package Schedule job failed -
01-09-2004
, 01:53 PM
|
it definitely sounds as something is missing in the Oracle config??? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uYzreju1DHA.1704 (AT) tk2msftngp13 (DOT) phx.gbl... Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4%23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, |
#12
| |||
| |||
|
|
Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4%23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, |
#13
| |||
| |||
|
|
Then how come I can run it manually under the SQL Agent account on the server? Kind of weird problem, Thanks Allan, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OJTVMou1DHA.2480 (AT) TK2MSFTNGP10 (DOT) phx.gbl... it definitely sounds as something is missing in the Oracle config??? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uYzreju1DHA.1704 (AT) tk2msftngp13 (DOT) phx.gbl... Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4%23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, |
#14
| |||
| |||
|
|
I am not saying you have anything missing rather it does sound like it. I just had a look on Google and it all points to the drivers and the account of the Agent i.e. Why do I get Driver's SQLAllocHandle on SQL_HANDLE_ENV failed with the Microsoft Oracle ODBC driver? Next Previous When connecting to a DSN using the Microsoft Oracle ODBC driver SQLAllocHandle/SQLAllocEnv fails and SQLGetDiagRec or SQLError returns: IM004:1:0:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed You need to install the NET 8 libraries which may be obtained by installing the Oracle client. The Oracle ODBC driver includes them so this problem does not occur when the Oracle ODBC driver is used. You also need to make sure that the path to the Oracle DLLs included with the Oracle client is included in your system PATH. e.g. if Oracle client is installed in C:\oracle\ora81 then C:\oracle\ora81\bin needs to be in your PATH environment variable. Two common problems related to incorrectly set PATH are: [1] the Oracle client was installed byuser A on the machine where user A is not the administrator. User A's PATH includes the path to the Oracle client libraries but the OOB Server is a service running as local admin and so the Oracle client directory needs to be in the system PATH. [2] the PATH environment contains the correct path but preceded by an old Oracle client install directory which still exists. You can check the PATH environment by going to control panel - System -> Environment. System environment variables available to everyone are in the top pane and user-specific variables in the lower pane. http://tinyurl.com/2yzxj -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uXvKgqu1DHA.1684 (AT) TK2MSFTNGP12 (DOT) phx.gbl... Then how come I can run it manually under the SQL Agent account on the server? Kind of weird problem, Thanks Allan, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OJTVMou1DHA.2480 (AT) TK2MSFTNGP10 (DOT) phx.gbl... it definitely sounds as something is missing in the Oracle config??? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uYzreju1DHA.1704 (AT) tk2msftngp13 (DOT) phx.gbl... Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4%23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, |
#15
| |||
| |||
|
|
-----Original Message----- Thanks Allan. I did check the article and also checked the PATH. it does include the oracle path. The Oracle 8.1.7 client has already been installed on the server. I just have no idea what else could be wrong. I really think it is a bug of the driver but...who should I call, MS or ORACLE? Maybe none of them will say anything. "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:ORbu36u1DHA.4060 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am not saying you have anything missing rather it does sound like it. I just had a look on Google and it all points to the drivers and the account of the Agent i.e. Why do I get Driver's SQLAllocHandle on SQL_HANDLE_ENV failed with the Microsoft Oracle ODBC driver? Next Previous When connecting to a DSN using the Microsoft Oracle ODBC driver SQLAllocHandle/SQLAllocEnv fails and SQLGetDiagRec or SQLError returns: IM004:1:0:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed You need to install the NET 8 libraries which may be obtained by installing the Oracle client. The Oracle ODBC driver includes them so this problem does not occur when the Oracle ODBC driver is used. You also need to make sure that the path to the Oracle DLLs included with the Oracle client is included in your system PATH. e.g. if Oracle client is installed in C:\oracle\ora81 then C:\oracle\ora81\bin needs to be in your PATH environment variable. Two common problems related to incorrectly set PATH are: [1] the Oracle client was installed byuser A on the machine where user A is not the administrator. User A's PATH includes the path to the Oracle client libraries but the OOB Server is a service running as local admin and so the Oracle client directory needs to be in the system PATH. [2] the PATH environment contains the correct path but preceded by an old Oracle client install directory which still exists. You can check the PATH environment by going to control panel - System -> Environment. System environment variables available to everyone are in the top pane and user-specific variables in the lower pane. http://tinyurl.com/2yzxj -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uXvKgqu1DHA.1684 (AT) TK2MSFTNGP12 (DOT) phx.gbl... Then how come I can run it manually under the SQL Agent account on the server? Kind of weird problem, Thanks Allan, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OJTVMou1DHA.2480 (AT) TK2MSFTNGP10 (DOT) phx.gbl... it definitely sounds as something is missing in the Oracle config??? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uYzreju1DHA.1704 (AT) tk2msftngp13 (DOT) phx.gbl... Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4% 23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, . |
#16
| |||
| |||
|
|
This is SQL Proxy account issue. You need to check it. -----Original Message----- Thanks Allan. I did check the article and also checked the PATH. it does include the oracle path. The Oracle 8.1.7 client has already been installed on the server. I just have no idea what else could be wrong. I really think it is a bug of the driver but...who should I call, MS or ORACLE? Maybe none of them will say anything. "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:ORbu36u1DHA.4060 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am not saying you have anything missing rather it does sound like it. I just had a look on Google and it all points to the drivers and the account of the Agent i.e. Why do I get Driver's SQLAllocHandle on SQL_HANDLE_ENV failed with the Microsoft Oracle ODBC driver? Next Previous When connecting to a DSN using the Microsoft Oracle ODBC driver SQLAllocHandle/SQLAllocEnv fails and SQLGetDiagRec or SQLError returns: IM004:1:0:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed You need to install the NET 8 libraries which may be obtained by installing the Oracle client. The Oracle ODBC driver includes them so this problem does not occur when the Oracle ODBC driver is used. You also need to make sure that the path to the Oracle DLLs included with the Oracle client is included in your system PATH. e.g. if Oracle client is installed in C:\oracle\ora81 then C:\oracle\ora81\bin needs to be in your PATH environment variable. Two common problems related to incorrectly set PATH are: [1] the Oracle client was installed byuser A on the machine where user A is not the administrator. User A's PATH includes the path to the Oracle client libraries but the OOB Server is a service running as local admin and so the Oracle client directory needs to be in the system PATH. [2] the PATH environment contains the correct path but preceded by an old Oracle client install directory which still exists. You can check the PATH environment by going to control panel - System -> Environment. System environment variables available to everyone are in the top pane and user-specific variables in the lower pane. http://tinyurl.com/2yzxj -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uXvKgqu1DHA.1684 (AT) TK2MSFTNGP12 (DOT) phx.gbl... Then how come I can run it manually under the SQL Agent account on the server? Kind of weird problem, Thanks Allan, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OJTVMou1DHA.2480 (AT) TK2MSFTNGP10 (DOT) phx.gbl... it definitely sounds as something is missing in the Oracle config??? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:uYzreju1DHA.1704 (AT) tk2msftngp13 (DOT) phx.gbl... Yes, and The setting of "Only the Sysadmin can run the CmdExec and ActiveScript jobs" are checked. Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com wrote in message news:eRx98au1DHA.2156 (AT) TK2MSFTNGP12 (DOT) phx.gbl... The job will execute as the Agent account if the owner of the job is in the sysadmin role. If not it executes as the Agent Proxy Account. Were you at the server when you logged in as the Agent account ? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OIsTXOu1DHA.4064 (AT) tk2msftngp13 (DOT) phx.gbl... Thanks Allan, I am not sure why do I need a DSN because I didn't use it. The Oralce client configuration is fine. I can ran it manually on the server under the SQL Agent account. I think now the question now is what is the different between manually run the package under SQL Agent account and schedule it. Any idea? Thanks, "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com wrote in message news:eihduKt1DHA.3216 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I am coming into this thread late but the 80004005 error is permission denied or "I do not exist" You will need the DSN set up so that the SQL Server Agent service account or the Agent Proxy account can see it. Have a read of this http://support.microsoft.com/?kbid=269074 -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OKPk4% 23s1DHA.2336 (AT) TK2MSFTNGP09 (DOT) phx.gbl... I tried different ways to do it. Saving the package as vbs file or dts file, then manually run it, there is no problem. But if I schedule it, I got the same error message in the output log file. Thanks, "Amsteel" <z2333 (AT) hotmail (DOT) com> wrote in message news:OhFsvyg1DHA.2948 (AT) TK2MSFTNGP09 (DOT) phx.gbl... SQL 2000 SP3 standard on Windows 2000 Adv server. Package is used to transfer data from Oracle. It could run manually but failed when scheduled. Here is the log: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) 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 Error Detail Records: 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. I checked the path enviroment variables and the oralce path is under system variable. Any other idea? Thanks, . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.