April 12, 2006 at 3:40 pm
I am pretty confused on this. The current Machine is a 64 bit Server running SQL Enterprise SP4
1) DTS runs at 32 bit? I figure this because I can see all the 32 bit drivers
Also the current DTS that I am extracting data from Oracle works like a charm when I run it manually
2) Agent (jobs) runs at 64 bit? I figure this because the job is having an issue initiating the odbc that Oracle uses saying it can not open a connection. So is there a work around to make the agent work at a 32 bit ability or a work around to get the job to work.
Also before I upgraded to 64 bit SQL 2000 the job worked on the 32 bit machine. this was just a conversion to a new machine
April 12, 2006 at 3:47 pm
It is a permission issue. What account does SQL Agent (the service) run under?
April 12, 2006 at 4:04 pm
Domain\Administrators
I am the administrator
April 12, 2006 at 4:09 pm
Also if it was a permission issue is it throwing me a bogus error?
Executed as user: PPP\Administrator. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 6413 (190D) Error string: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open. 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.
April 12, 2006 at 4:16 pm
It says the connection is not open, this can be a permission issue an yes I have seen strange errors. When I was asking the account SQL Agent is running I mean when you right click on SQL Server Aget and select Properties an the goto the tab about login or authentication (sorry no server running here at home at the moment so I don't have a frame of reference in front of me), will have Local System checked or have a user account an password entered. This is the account it is executing under when th job runs. If not you the tes with an admin account to see if the issue is the same if works you know it is a permissions issue with regards to the Orace driver.
April 12, 2006 at 4:27 pm
Under Agent properties it is running as (This Account) User ppp\Administrator PWD XXXXXX
PPP\Administrator is a group. I belong in that group. It has the same set up as the old server that it worked on.
I think it has to do with the driver but I do not know how to fix it. I have tried 10g but same result. Oracle support is no help either.
April 12, 2006 at 4:42 pm
I'll let you know if I find anything else. But I have seen this before and it is a pain I know. Will pose to some friends at work tomorrow.
April 12, 2006 at 4:44 pm
That would be huge. thanks if you live in MO I will buy you lunch
April 12, 2006 at 5:51 pm
So I went on with what you were saying about security and installed on another machine. Go pretty much the same error.
Found a site that said permissions are messed up. So I went through and looked at the Agent ppp\Administrator, server ppp\Administrator, added permissions to odbc.ini in registry for ppp\Administrator, oracle folder permissions for ppp\Administrator
Tried all this and a reboot hoping but none of it made a difference. If you can I would really appreciate the help
April 12, 2006 at 9:45 pm
Have you tested a similar package to a 32 bit machine, just wonderng if there is an issue with converting the information from the 64 bit machine to the 32 bit source
April 13, 2006 at 5:39 am
It sounds like you are running SQL Server 2000 Enterperise Edition with SP4. In SQL Server 2000, Microsoft only created an Itaninum version (IA-64) and not an x64 version (Intel/AMD). Interestly enough both Enterprise Manager and DTS were never ported to the IA-64 platform. For this reason I run both EM and DTS from another machine. For DTS I used a dedicated low-end 32-bit server.
April 13, 2006 at 6:46 am
I am confused. Are you running 2005 or 2000. Are you using DTS or SSIS? Is the SQL Server 64 Bit or Oracle?
What I know, I am running 2005 64bit and using SSIS to move data from an Oracle 64bit server. This does work however, I am not using Agent to schedule the job. The SQL Server has to have the Oracle client installed. If you are running an SSIS package through Visual Studio (a 32-bit app) from your SQL server (your term serv'd into the box), the pack will fail and con not connect to Oracle 64bit. VS will try to connect using the 32bit driver and not the 64bit. MS knows this is an issue.
Give me some more info and I will try to help.
April 13, 2006 at 7:42 am
I am confused. Are you running 2005 or 2000. Are you using DTS or SSIS? Is the SQL Server 64 Bit or Oracle?
--I have the option of using either one. It was working on the 32 bit SQL 2000 before I moved to SQL 2000 AMD Xeon 64 bit SP4 running in WOW64. I have a smaller 64 bit AMD Xeon SQL 2k5 server running that would probably be better to use since it is at the plant where the data is. I just did not know how to connect using SSIS. So with that information I am sure that will confuse the matter. This is just replicating data so if you can just tell me what the connection should be in 2005 that would be great. I will run with that. Also on the 2005 would I still need to install the oracle client?
September 7, 2006 at 4:23 pm
JKSQL,
Did you ever find a solution to this issue ?? I am having the same problem. We are running 32-bit sql 2000 on a 64 bit server. If I use the Oracle OLEDB driver I get a ora-12154, and if I use the MS ole db for Oracle, I get ora-06413. I am very comfortable with Oracle and believe that it is a SQL issue, but have no idea how to resolve it.
September 7, 2006 at 4:33 pm
We had similar issue on SQL Server 2005 x64 and opened a call with Microsoft. We installed the x64 version of the Oracle driver from Oracle and it worked.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply