64 vs 32

  • 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

  • It is a permission issue. What account does SQL Agent (the service) run under?

  • Domain\Administrators

    I am the administrator

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

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

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

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

  • That would be huge. thanks if you live in MO I will buy you lunch

  • 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

    http://www.orafaq.com/forum/t/38079/0/

  • 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

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

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

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

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

  • 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