Tuesday, January 23, 2007

ORA-12154 using C# with Microsoft Visual Studio : Not what you think!

Ok, this one goes in the infuriating category, because I quite literally spent days on it.  Everybody who's tried to interface with Oracle should know the ORA-12154 error, TNS:could not resolve the connect identifier specified.  Basically it means failed to connect to the database because the driver can't figure out what you want to connect to.  Normally that's a problem with TNSNAMES.ORA or something similar.  (A bad username/password would be a different error.)

Well, I started getting this in a Solution I'd been working in.  Couldn't understand why.  I was simply creating a new console application that linked into an object framework of my own creation that I was using in plenty of other places.  I kept assuming it had something to do with my connection string, since I can never remember whether it's "User Id" or "UID" and all that nonsense.

Where it gets weird is that I'd create a new solution, create a test project...and it worked fine.   So I know it's not my machine's ability to connect to the database.  That is, not a TNSNAMES.ORA problem or anything like that.

So then I'd take my working project, link it into my solution, and it continues to work.  Ok, cool.  Create a new console application in my solution, and basically mirror everything that's in the working project, including dragging and dropping the App.config file (which contains the connection string) from good project to new project.

And it fails.

This went on for days.  I tried every combination I could imagine.  I checked the versions on all my DLLs.  I diff'd every source file in the two directories.  They were literally the exact same.  Just a single line invoking a common library they both shared, for pete's sake.  So it's not even like the error is in the common code, because it works for one but not the other.

At last, just now, I found it. Ready for the clear and obvious solution that makes all the sense in the world now that you see it?

There was a parenthesis in the directory name where my solution was stored.  Apparently at some point when I wasn't paying attention I'd nested a reference to Foo and VS had created a directory called c:\Foo (2) where it was putting my solution.

I close the solution, rename the directory c:\Bar, and reopen the solution. Rebuild it (you have to rebuild it when you do that), and everything works fine.  Rename it again and put the parenthesis back, and back comes the ORA-12154 error.

So, there you go.  The problem was not in anything I wrote, it was in where it lived on the disk.  Naturally when I created a project outside the solution and then linked it back in, it did not live in the same directory (even though it appeared to now be part of the solution).  And when I created a new project, supposedly right next to the working one, I was really creating it inside of the bad directory.  Maybe I'm the only person in the history of Visual Studio to have a parenthesis in a directory name, but I didn't even put it there, the software did it automatically and I never really paid it much attention.  But when you have a parenthesis in the directory where your solution is stored, it will manifest itself as a "TNS:cannot resolve connect identifier" error. 

Have I mentioned how much I hate this technology recently?

 

 

Technorati tags: , , , , ,

16 comments:

tanu said...

You have saved me hours and hours of debugging. Thankuuu soooo much.

Anonymous said...

I just wasted 2 days before finding this blog entry. Any ideas why you can't have parentheses?

Duane said...

I don't know for a fact why you can't have them but I can't say I'm completely surprised. It's common when dealing with things like file and pathnames to start with a simple rule like "Only alphanumeric allowed" and then modify the rule accordingly as you find exceptions, rather than starting out with a perfect format. I used to work in email all day long, and let me tell you, the variations on what constitutes a good email address are amazing.

Not saying it's an excuse, just saying that I've seen similar bugs before.

ripnetuk said...

Thank you - saved me hours of work :) george

enma said...

Thank you sooooo much too..you helped..

Anonymous said...

My sincerest gratitude for figuring that out. Really, that's the last thing i could have thought about. I hope they fix that ridiculous bug.

Sonny said...

This problem was so frautrating that I almost considered using oledb.net ,good thing I stumbled upon your solution. It just saved me from using odp.net. Thanks a lot.

kishor d gupta said...

u r great,

after done 10 web site

u give me he correct solution .

thanks

Anonymous said...

Thank you so much for sharing this!! i have the same error and took me a whole day to figure until i read your post!

Cheers

Anonymous said...

Tanx for Solution !

Richard Dingwall said...

I don't have a lot of experience with Oracle beyond their super-flaky client tools but nonsense like this really casts doubt on the quality of their server product...

quillbreaker said...

I hit this problem as well. I was using Visual Studio 2008's test suite and it was putting the compiled code for a test iteration in "c:\program files (x86)\microsoft visual studio 9.0\Common7\IDE\".

Yup.

You know what, Oracle can have Java. They deserve it.

manoj said...

it was really frustating when 1 solution works and other created same way, does not works.. it helped me a lot and saved my time too... thanks

Pradeep said...

Thanks a Lot, it took us 2 days debugging this issue & trying to find out solutions all over the internet. Finally landed up on your page to get the right solution.
Hats off to you for having shared this knowledge.

ImTheNightmare said...

thank you very very very much!!! this solution works so fine!!! i can't believe that the error was a parenthesis!!

Aberk said...

Thank you for posting this!