Rip's Domain

Oracle 11g and SSIS error: cannot convert between unicode and non-unicode string data types

Posted in MSSQL, Oracle by rip747 on August 19, 2013

When upgrading our Oracle database to 11g we received the following error when executing previously working SSIS packages on our MSSQL2008R2 server:

Executed as user: <redacted> Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:10:34 PM Error: 2013-08-16 15:10:37.14 Code: 0xC02020F6 Source: <redacted> OLE DB Source [1] Description: Column “<redacted>” cannot convert between unicode and non-unicode string data types. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC004706B Source: <redacted> SSIS.Pipeline Description: “component “OLE DB Source” (1)” failed validation and returned validation status “VS_ISBROKEN”. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC004700C Source: <redacted> SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC0024107 Source: <redacted> Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:10:34 PM Finished: 3:10:37 PM Elapsed: 2.308 seconds. The package execution failed. The step failed.

Turns out the fix was very simple. Basically all we had to do was edit the package in notepad and replace all occurrences of:

validateExternalMetadata=”True”

to:

validateExternalMetadata=”False”

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA with Linked Server

Posted in MSSQL, Oracle, SQL, TechSupport by rip747 on July 18, 2011

most likely this is and issue with the look up for your alias. a quick workaround for is to set the Data Source and Provider Strings to the following:

<FQ hosting name>:<port>/<alias>

oracleserver.mycompany.com:1521/db

thanks to bkgroups<AT>yahoo<DOT>com for the post all the way back in 2007 on sqlmonster.com for the answer.

ORA-04098: trigger is invalid and failed re-validation

Posted in CFWheels, ColdFusion, Oracle, Railo by rip747 on March 27, 2011

when running the cfwheels test suite under oracle you encounter the following with Railo (maybe also with ACF)

Problem: ORA-04098: trigger ‘WHEELSTESTDB.BI_USERS’ is invalid and failed re-validation

Solution: no whitespace can be in the cfquery tag.

This will cause the error:

<cfquery name=”loc.query” datasource=”#application.wheels.dataSourceName#”>
CREATE TRIGGER bi_#loc.i# BEFORE INSERT ON #loc.i# FOR EACH ROW BEGIN SELECT #loc.seq#.nextval INTO :NEW.<cfif loc.i IS “photogalleries”>photogalleryid<cfelseif loc.i IS “photogalleryphotos”>photogalleryphotoid<cfelse>id</cfif> FROM dual; END;
</cfquery>

change the cfquery to this:

<cfquery name=”loc.query” datasource=”#application.wheels.dataSourceName#”>CREATE TRIGGER bi_#loc.i# BEFORE INSERT ON #loc.i# FOR EACH ROW BEGIN SELECT #loc.seq#.nextval INTO :NEW.<cfif loc.i IS “photogalleries”>photogalleryid<cfelseif loc.i IS “photogalleryphotos”>photogalleryphotoid<cfelse>id</cfif> FROM dual; END;</cfquery>

Connecting Railo Express to Oracle XE

Posted in CFWheels, Oracle, Railo by rip747 on October 3, 2010

Follow the instructions to installing Oracle XE and configure a database user.

1) Copy ojdbc14.jar from “C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib” to “C:\railo\lib”

NOTE: for some reason with the jetty version, you need to place the driver in:

<railo-dir>/lib/ext/railo-server/context/lib

2) Start Railo Express

3) Go into Server Administrator and choose to create a new datasource

4) When configuring the database, use the following:

Driver: Other – JDBC Driver
Class: oracle.jdbc.driver.OracleDriver
DSN: jdbc:oracle:thin:@localhost:1521:XE

Tagged with: ,

OraOLEDB.Oracle: ICommandText::Execute returned 0x80040155

Posted in Oracle, TechSupport by rip747 on January 30, 2008

I just ran into this problem today. Everything was working fine with my linked server connection to the Oracle database (if you haven’t been following the saga.. read up on it) and then it just stopped working.

Anywho, I managed to get things working again by deleting the linked server and recreating it. This time though I made sure to the “Microsoft OLE DB Provider for Oracle” provider and made sure that “Allow In Process” was checked under the “Provider Options” from the advice I got here.

After doing that, everything is working once again.

I’m becoming a bit of an expert with linked servers to Oracle.

More Oracle 0x80004005 errors with linked server

Posted in Oracle, TechSupport by rip747 on January 4, 2008

Yeah… Oracle sucks.

For some reason, I was continuing to get the famous 0x80004005 error even after I got everything to work. When the computer started up, I could perform queries to the linked Oracle server. After a little while though, it would start throwing errors at me and for no reason whatsoever. I searched all over and couldn’t find a solution, so I found it on my own. Here’s what I did.

First thing I did was look at my tnsnames.ora file to see what my configuration was, below is what it looked like (hostnames changed to protect the innocent):

FDEV.EXAMPLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdev1.EXAMPLE.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fdev)
)
)

My sqlnet.ora file was also setup correctly with:

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

Now I could ping erpdev1.example.com and if I ran Net Configuration Assistant I would pass the connection test to fdev.example.com. Thing was though I couldn’t perform a tnsping to fdev.example.com for some reason. On a hunch I opened up my hosts file (c:\windows\system32\drivers\etc) and added an entry. I mapped fdev.example.com to the same ip as erpdev1.example.com and saved. Tried again and everything worked!

To sum up, if you run into this weird problem just add an entry into your hosts file to point your service name to the same ip address as your host.

UPDATE:

I ran into this problem again. It seems like many things cause it. This time I received error #7399. The trick into solving this one for me was to open up my SQLNET.ORA file and to comment my NAMES.DEFAULT_DOMAIN section. Seems that this was confusing oracle when accessing the service name.

Now while commenting out the line fixed my problem. You may have to add something to it. Point is to just make sure it is correct. You will know by trying to make a connection through SQLPLUS and if you get an error complaining that it cannot access the service name, you know the problem.

SQL Server 2000: Linked Oracle Server Errors

Posted in Oracle, SQL, TechSupport by rip747 on December 14, 2007

I HATE ORACLE!!! Why does this always happen to me?

Again, I spent the last 4 hours of my day trying to get something as simple as a linked server in MSSQL 2000 to work. What I was doing was setting up a linked server between MSSQL 2000 and Oracle 9i and ran into a lot of problem. Below is ranting and how I finally solved my problem.

The one thing I can’t stand about setting up a linked server in MSSQL with Oracle is that it’s never the same. Sometime you can get away with using the “Microsoft OLE DB Provider for Oracle”, other times you need to use the “Microsoft OLE DB Provider for ODBC Drivers” and still sometimes you need to use the “Oracle Provider for OLE DB”. In summary you have 3 different providers to choose from depending on nothing but the toss of a hat.

Just to prove my point, I already have a linked server setup on another MSSQL 2000 server using the “Microsoft OLE DB Provider for Oracle” provider. On the server I’m setting up, I following the EXACT same configuration and used the same provider and it wouldn’t connect. I’m getting Error 7312 and Error 7399 every time I try to connect and view the tables.

{Error 7399: OLE DB provider ‘MSDAORA’ reported an error. OLEDB error trace [OLE/DB Provider ‘MSDAORA’ IDBInitialize::Initialize returned 0x80004005: ]}

{Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider ‘OraOLEDB.Oracle’. [OLE/DB provider returned message: ORA-00942: table or view does not exist]
OLE DB error trace [OLE/DB Provider ‘OraOLEDB.Oracle’ ICommandPrepare::Prepare returned 0x80040e14]. }

After playing around for 4 hours, I finally found the right combination to connect and run my queries. Here is what I tried. I will say this, that there is no definitive solution for this. You are going to have to try each suggestion and see which one works for you. I should note that I do all of this inside of Enterprise Manager.

First off, pick one of the following providers and create your linked server:
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for ODBC Drivers
Oracle Provider for OLE DB

Now once you have your linked server created, expand the link server and clicked on TABLES to see if you get a list of the tables or if you get the 7399 error. If you get the error, try a different provider until you get the list of tables. When you finally get the listing of tables, remember the value in the “schema” column for that table, it might come into play in the next part.

After you get the list of tables, open up Query Analyzer and try to do a query against the linked server. I have always had to use OPENQUERY to get the query to work. There are two syntaxes that you will have to try:

First try this:

select * from openquery(my_linked_server, ‘select * from table_name’)

If you get the 7321 error try this:

select * from openquery(my_linked_server, ‘select * from schema_value.table_name’)

One of those should work. If anyone else comes up with weird errors or a difference method to connect, leave your suggestion in the comments below.