Hi Steve, In my opinion MS SQL is simpler to admin compared with Oracle. Due to 'Linked Server Oracle Date' I have been forced to move to Oracle. Daniel
________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Remedy Guy Sent: Monday, October 25, 2010 6:15 PM To: [email protected] Subject: Linked Server Oracle Date ** Hi folks, I am trying to have some data from an oracle database available in remedy, which is using SQL server. I have setup a linked server to the oracle DB and using openquery everything works well if I cast the DATE field in oracle to CHAR. The problem is the date field in remedy translates to a character field, which you would expect. IS there a way for me to CAST the date field in oracle to a DATETIME field in SQL which Remedy would recognize as a date field when creating the view? I am not using the MSDAORA provider. Thanks Steve _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ ********************************* This message and any attachments (the "message") are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. Messages are susceptible to alteration. France Telecom Group shall not be liable for the message if altered, changed or falsified. If you are not the intended addressee of this message, please cancel it immediately and inform the sender. ******************************** _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
|
March 25
Create a Linked Server to an Oracle DB from Server 2008 and SQL 2008
We recently needed to create a
linked server from Microsoft SQL
Server 2008 to an Oracle 9.0.1
database on a Windows Server 2008
x64 server. For your reference, here
is an outline of the steps used to
successfully create the linked
server connection. When trying to
establish the linked server in SQL
Management Studio the Oracle OLE
Provider would never seem to work
successfully. Instead, we used ODBC
with a System DSN to create the
connection and this seemed to work
reliably and the overhead of the DSN
and slight performance hit were
acceptable in our case.
Download and Install the Oracle Client 1) Download the Oracle Database 10g Client Release 2 (10.2.0.4) from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204_winx64_vista_win2k8.html. 2) Unzip the archive and run setup.exe in the client folder. 3) When prompted for the installation type, choose Administrator (InstantClient did not work). Configure the Service Name 4) Upon completion of the install the Net Configuration Assistant should automatically launch. If it does not, you can run it from Start à All Programs à Oracle OraClient10g_homeX à Configuration and Migration Tools à Net Configuration Assistant. 5) In the Select naming methods dialog, select the default Local Naming and click Next. 6) When prompted for a service name, enter the desired service name. 7) Choose the protocol, most likely TCP and click Next. 8) Enter the host name or IP address we used the IP address, and enter the port information. When ready, click Next. 9) I recommend that you perform a test, by selecting Yes, perform a test and click Next. If credentials are needed a change Login button will be presented after Next is clicked. 10) When prompted for the net service name just click Next. 11) Keep clicking Next until you get to the configuration complete screen and then click Finish. Create an ODBC System DSN 12) Open the Start menu and navigate to All Programs à Oracle OraClient10g_homeX à Configuration and Migration Tools à Microsoft ODBC Administrator. 13) Select the System DSN tab. 14) Click the Add button. 15) Select the driver that has the name of the Oracle instance in which you installed earlier, this will be something likeOracle in OraClient10g_homeX and click Finish. 16) Enter a Data Source Name for your new system DSN. 17) Select the TNS Service name that you entered in step 6 above. 18) Click the Test Connection button and enter credentials as necessary. Establish the Linked Server 19) Open SQL Management Studio and log into the database engine for the instance you want to establish a linked server. 20) In the Object Explorer, expand the Server Objects Node, then right-click Linked Servers and select New Linked Server 21) On the General tab, create a meaningful name for the linked server in the Linked server prompt. 22) Make sure the radio button for Other data source is selected and in the provider drop-down, select Microsoft OLE DB Provider for OBDC Drivers *NOT* Oracle Provider for OLE DB, I could not get this to work and it would often cause SSMS to freeze just by selecting it. 23) Enter something like Oracle for the Product name. 24) In the Data source box enter the name of the System DSN that you gave in step 16 above. 25) On the left, select the Security link. 26) Configure your security configuration in our case we selected the option for Be made using this security context and entered the credentials so that all connections used the same credential. 27) On the Server Options page, the only change that was made was Collation Compatible was set to True. 28) Click Ok and the new linked
server should be configured
Someone on Windows Live
Trackbacks
The trackback URL for this entry
is:
http://tlingenf.spaces.live.com/blog/cns!B1B09F516B5BAEBF!216.trak
Weblogs that reference this
entry
|
