| Author |
Topic |
|
TomK
Starting Member
12 Posts |
 Posted - 2005-07-22 : 10:53:08
|
| Hi, I'm trying to run my DTS by using sp_OACreate. Basically everything works except the datapump to an Oracle db. I make a connection using ODBC. When I however, manually run the DTS there is no problem.Does anyone know what the issue might be?thanks,Tom |
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-22 : 11:40:57
|
| I had a similar issue, but mine involved copying files from a PC to the MSSQL server inside a DTS package. manually things ran smoothly, but as a job it failed time after time. turned out that manually I was running the DTS as administrator, but as a job MSSQL didn't have enough access rights. maybe your problem is something like this |
 |
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-22 : 12:11:47
|
| Thanks for the suggestion but I'm running the DTS from sa account.I've now tried using an OLEDB connection as well, but that did not work either. |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-24 : 05:53:56
|
| Ok, I've tested the DTS in a scheduled task and that does not not work either. It may be a permission issue then but I have no clue how to procede. Anyone? |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-25 : 03:17:25
|
| is the ODBC connection a system DSN or a user DSN? |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-25 : 04:04:16
|
quote:
Originally posted by thebruins
is the ODBC connection a system DSN or a user DSN?
Neither, I've specified the connection in my DTS as a simple ODBC connection. Next to that I've also tried an OLEDB for ORACLE connection, but that didn't work either. If that makes any sense? |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-25 : 05:01:10
|
| have you enabled logging for the package and checked what the log says? |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-25 : 05:06:08
|
| you could also try creating a system DSN on the mssqlserver (config panel -> ODBC), and then use that DSN inside the package |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-25 : 05:47:49
|
| Hi, This is what I get from job history:DTSStep_DTSDataPumpTask_3, Error = -2147467259 (80004005) Error string: ORA-12154: TNS:could not resolve service name Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 12154 (2F7A) Error string: Error source: Help file: Help context: 0 DTSRu... Process Exit Code 1. The step failed. |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-25 : 07:10:29
|
| "could not resolve service name"http://www.cryer.co.uk/brian/oracle/ORA12154.htmhttp://www.experts-exchange.com/Databases/Oracle/Q_20902239.htmlis tnsnames.ora (somewhere in the oracle client folder) readable for everyone? |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-26 : 05:03:27
|
quote:
Originally posted by thebruins
"could not resolve service name"http://www.cryer.co.uk/brian/oracle/ORA12154.htmhttp://www.experts-exchange.com/Databases/Oracle/Q_20902239.htmlis tnsnames.ora (somewhere in the oracle client folder) readable for everyone?
thanks for providing those links. We now think I may have something to do with the account under which our jobs run. It probably is not included in our Oracle user group and can't therefore see the Oracle environments.More in a few hours. |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-27 : 04:39:40
|
| Right, we've added the Oracle environment to the account that we use for running jobs. We did this by running a script that normally adds the environment to a user when he/she logs in.This did not work however. Does anyone know how to configure an account for Oracle that is only used as a service? |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-27 : 04:47:47
|
| I have never worked with Oracle, but 'oracle environment' = a bunch of environment variables? if so, maybe you could add the environment to the system variables so all users can use them? |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-27 : 05:05:27
|
quote:
Originally posted by thebruins
I have never worked with Oracle, but 'oracle environment' = a bunch of environment variables? if so, maybe you could add the environment to the system variables so all users can use them?
Unfortunately our sa is on holiday (Murphy's law), that's why it's taking so long for these (probably) easy issues to deal with. I'll update this thread when I know more. |
|
|
|
lizak
Starting Member
12 Posts |
Posted - 2005-07-27 : 10:32:48
|
| I thought the account used to run the DTS was only used for authority purposes. It sounds like you are expecting it to also have properties set in a login script. I don't think it works that way but I've never tried it.We've got an Oracle box in house that I know eventually I'll have to use so I'm curious to see how this problem ends up. |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-27 : 16:54:20
|
| Adding the Oracle role to the sqlserver account made no difference. Tomorrow we'll add a Oracle client on the server; that should fix most problems. |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-07-30 : 12:12:46
|
| Right, adding the oracle client appears not to be the optimal solution. Normally we have a kixstart app that adds a network allias to the oracle drive. Unfortunately this doesn't work due to the fact that when calling the sqlservice it runs under it's own environment, without mappings etc. We are now trying to fix the issue with unc mappings. |
|
|
|
TomK
Starting Member
12 Posts |
Posted - 2005-08-04 : 15:41:57
|
quote:
Originally posted by TomK
Right, adding the oracle client appears not to be the optimal solution. Normally we have a kixstart app that adds a network allias to the oracle drive. Unfortunately this doesn't work due to the fact that when calling the sqlservice it runs under it's own environment, without mappings etc. We are now trying to fix the issue with unc mappings.
Ok, found the problem and solved it! First we had to add .world to the SID we tried connecting to. Then we needed to alter listener.ora installed locally on the sqlserver as the registry used that listener as default. In the listener.ora was an error (wrong server name). After fixing that everything works.thanks the Bruins for helping along. |
|
|
|
|
Problems with ODBC when run DTS with sp_OACreate