Have you played with ODBC settings like use of underscores and ANSI settings on the SQL server? I recall having to use underscores to resolve some ODBC related issues where forms contained special characters. And having to set ANSI ON on the SQL server, for some other similar type of problems. Changing ANSI configuration may require SQL restart.
Joe _____ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Tuesday, July 02, 2013 2:19 PM To: arslist@ARSLIST.ORG Subject: Re: Linked Server Puzzle Have you tried creating a local SQL view of the remote table using the ODBC driver (either to the Oracle view or to the T table directly) and then creating a ARS view form on that? Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Tuesday, July 02, 2013 1:14 PM To: arslist@ARSLIST.ORG Subject: Re: Linked Server Puzzle ** Thanks Joe....It's not case sensitive.... I've tried it about every which way I can try. It's an old SHR:People table on a legacy box that's going away. We're trying to come up with a way to keep the new CTM:People form in sync with the old (50,000 + entries to maintain on both systems). I was hoping this would be a little bit easier then what it is turning out to be! *sigh* On Tue, Jul 2, 2013 at 2:03 PM, Joe D'Souza <jdso...@shyle.net> wrote: ** Just my two cents but maybe the name is case sensitive? And the ODBC driver might me converting to all caps or all small and breaking things? If the above is a possible reason, try altering the source table and its columns if that is possible to have its name in all caps or all small. Joe _____ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Monday, July 01, 2013 2:15 PM To: arslist@ARSLIST.ORG Subject: Linked Server Puzzle ** First off..... ARS 7.6.04 patch 1 Windows Server 2008 R2 Standard ITSM 7.6.04 MS SQL 2008 R2 Windwos Server 2008 R2 Standard Linking to an Oracle 10g db I am attempting to create a view form of the legacy server at my current project. It's a NON unicode database. My current db is Unicode. I've installed Oracle Client on the production db server. I've tried 2 methods to connect 1. I created an ODBC connection to the Oracle server. I get a successfful connection. I then access the current SQL server and create a linked server using the ODBC connection I had created. All tables show up, however when I attempt to select a table, I get an error that the table (any of them) contains no columns or the current user does not have permissions on that object. This is strange as I am using the db owners login to access the data. Additionally, if I go ahead and attempt to create the view form at this point, Remedy will pull back all of the fields on the form, but if I try to save the form, I receive the ARError message 552: "The SQL database operation failed. : Invalid object name "\[2 task name]\[table name]. (SQL Server 208) 2. Instead of using the ODBC drivers, I instead tried to create the Linked Server using the "Oracle Provider for OLE DB" provider. I set it up with the Product name of "Oracle" and the Data Source name is the appropriate 2 task name listed in the TNSNAMES.ORA file that I loaded on the server when I installed the Oracle Client. I am using the Oracle user that owns the db to connect. When I attempt to create it, I get the error "The Linked server has been created but failed a connection test. Do you want to keep the linked server." Additionally, it tells me that "An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)" and "Cannot create an instance of OLE DB Provider "OraOLEDB.Oracle for linked server "[2 task name]". (Microsoft SQL Server, Error: 7302). Additionally, in the SQL Server log, I am seeing the error "The OLD DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider." I was going through the kbase and google, and I came across references to making sure that Named Pipes and TCP are enabled (they are) and that the db user (ARAdmin) has local admin privelages on the db server (it does). I'm more than a bit stumped. I don't currently have a lot of DBa support, so I'm hoping someone in ARSlist land may have run into this and knows what direction to point me. Thanks much in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
