October 6, 2009 at 2:40 pm
Hi friends,
Have any of you used connection string to connect from SQl server to Oracle database? I was going with the linked server set up, but it got dropped due to some issues in the environment.
I see that connection string has to be mentioned in the application somewhere.. Can connection string be mentioned in the stored procedure in SQl server to access the Tables in Oracle?
Thank you so much
October 6, 2009 at 3:01 pm
You can build out a connection string using OPENROWSET but I would shy away from that before I asked other questions. Does the data that you need required to be up to the moment valid, can you deal with a scheduled copy? Is the data you are likely to want predictable?
Where I'm going with this is using SSIS to take copies of data from Oracle and dropping them on your SQL Server in some schedule to use. If you don't have to have up to the minute data then great, if you do I would first suggest re-evaluateing whether you REALLY do, or is it just nice to have.
CEWII
October 6, 2009 at 4:41 pm
Thanks for your reply... But our team wants to go with connectionstrings and i'm not able to get it work.. Apparently they say I can just use the connectionstring in a sql session and access tables in other databases..
I've this command from connectionstrings to connect to Oracle database..
Data Source=dbname;User Id=myUsername;Password=myPassword;
But when I run it in sql session in SSMS, I get:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
ANy suggestions? Thank you
October 7, 2009 at 8:08 am
That connection string is nothing but text, it isn't any form of a command. You are connected to SQL, you have to use something like OPENROWSET to use that.
I'm back to my original questions, what problem are you trying to solve by querying the data "live", and does it have to be that way.
I've been down this road with developers before, and been in their position.. Does what they want to do make sense? Even if they have decided they want to do it that way is not a guarantee that it will happen, regardless of what they *want* to do.
I am much more leary that I once was about reaching out to another server in a query. I have experienced too many issues to get all warm and fuzzy in these cases..
CEWII
October 8, 2009 at 2:00 am
We loaded the Oracle OLEDB onto the server the packages run from and the developers' machines and set up the TNSnames.ora then used that provider in the dts as it worked much better than the Microsoft one for us.
Better still we've now got rid of the Oracle database for a SQL Server version of the product due to costs.:-D
October 8, 2009 at 2:09 am
lol, weel that certanity fixed the issue, wonder if that would work for anyone else who has oracle problems :Whistling:
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 21, 2009 at 11:24 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply