"Oracle Not Available" error from SQL

We have a DTS package utilizing an ODBC connection to and Oracle database to run a few updates. I can TNSPING the Oracle instance but get the error Ora-01034 ORACLE not available when trying to run the package.
Any ideas?DTS packages can run as scheduled tasks, on the server itself, or from a workstation. They may or may not have the same NT Credentials. My guess would be that you're running the task from two different machines (a workstation and a server), where one has Oracle connectivity and the other does not.

-PatP|||Pat, Thanks for the reply.

This is all running from our SQL server and is scheduled from there. When i am looking at the DTS package, specifically the connection i can not even close it without getting the error.|||Have you rebooted NT since you installed the Oracle drivers on your server?

-PatP|||We have been utilizing the Microsoft ODBC for Oracle drivers.|||Have you tried running xp_cmdshell stored procedure from sql server with something like 'tnsping oracle_server'?

exec master..xp_cmdshell 'tnsping oracle_server_name'

what is the result? it could help to determine the source of problem

btw we have been using microsoft ole db provider for oracle for two years in dts packages and it's absolutely problemless. but only for selects. mojza

"Bulk copy insert failed"

I'm copying data from tables in one server to tables in another via DTS.

This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.

When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.

Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.

But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?

"..Transform requires script Text and Language.." error?

Hi,
I'm trying setup a package to replicate a database table into a another
database's table.
I thought a Data Driven Query Task would work. I got as far as making the
two connections and setting up the Data Driven Query's source SELECT (with
stored procedure I created in the source database) and destination table
under the Bindings tab.
However, when I run the task I get the error below
"ActiveX Scripting Transform requires script Text and Language and at least
one Phase function to be specified."
When I preview the data in under the task's Source tab, I see data AND the
destination table gets created but not data gets transfered. Can someone
please help on what this error means?
Also, I eventually want to update this destination table with several other
subsequent queries from other databases in order to conslidate related data
into one large reporting table but I have to get the initial population of
data to work first. This will be refreshed daily
Does this approach/package task seem to be applicable?
Thanks, Dave.
Dave,
you'd be best off posting in the separate DTS discussion group.
Rgds,
Paul Ibison
|||Open up your package, save it as a bas file and post it here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||I have the exact same problem as this guy. Could you please help?
thanks
marlene
url:http://www.ureader.com/msg/1156295.aspx