Connect SQL Server to Informix DB
The connection thru informix db driver is successfully connecting. this is what I am trying to do is: - Client having Infomix DB server and they have provided the connection string. - The connection with their server using SetNet32 is being done successfully. - We have created the System DSN for Informix DB and tested its working fine. - Now I am trying to connect it thru sql server 2005. For that I have created the Linked Server. - When I try to shoot the query to their db and then I am getting the followin error:: OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message "Unspecified error". OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message "[Informix][Informix ODBC Driver][Informix]Database not found or no system permission.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "realplus_sst". The provider supports the interface, but returns a failure code when it is used.
GUNJAN SHAH wrote: Don't know the details 'cause I don't do Windows stuff much, check the manuals, but IB there's a script that needs to be run to configure the IDS server to support OLEDB connections by creating some tables in sysmaster and populating them. Art S. Kagel Oninit > The connection thru informix db driver is successfully connecting. this is > what I am trying to do is: > > - Client having Infomix DB server and they have provided the connection > string. > - The connection with their server using SetNet32 is being done successfully. > - We have created the System DSN for Informix DB and tested its working fine. > - Now I am trying to connect it thru sql server 2005. For that I have created > the Linked Server. > - When I try to shoot the query to their db and then I am getting the followin > error:: > > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "Unspecified error". > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "[Informix][Informix ODBC Driver][Informix]Database not found or no system > permission.". > Msg 7311, Level 16, State 2, Line 1 > Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider > "MSDASQL" for linked server "realplus_sst". The provider supports the > interface, but returns a failure code when it is used. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > ================================================================================ =========== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > ================================================================================ =========== > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========
Art, You are correct there is a sql coledbp.sql in the INFORMIXDIR/etc that must be run. Bruce Simms Data Base Services TALX Corporation 2330 Ball Drive St. Louis, MO 63146 Phone (314) 214-7703 FAX (314) 983-3238 bsimms@talx.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art S. Kagel (Oninit LLC) Sent: Thursday, February 07, 2008 2:43 PM To: ids@iiug.org Subject: Re: Connect SQL Server to Informix DB [11232] GUNJAN SHAH wrote: Don't know the details 'cause I don't do Windows stuff much, check the manuals, but IB there's a script that needs to be run to configure the IDS server to support OLEDB connections by creating some tables in sysmaster and populating them. Art S. Kagel Oninit > The connection thru informix db driver is successfully connecting. this is > what I am trying to do is: > > - Client having Infomix DB server and they have provided the connection > string. > - The connection with their server using SetNet32 is being done successfully. > - We have created the System DSN for Informix DB and tested its working fine. > - Now I am trying to connect it thru sql server 2005. For that I have created > the Linked Server. > - When I try to shoot the query to their db and then I am getting the followin > error:: > > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "Unspecified error". > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "[Informix][Informix ODBC Driver][Informix]Database not found or no system > permission.". > Msg 7311, Level 16, State 2, Line 1 > Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider > "MSDASQL" for linked server "realplus_sst". The provider supports the > interface, but returns a failure code when it is used. > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > ======================================================================== =================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > ======================================================================== =================== > > ======================================================================== =================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ======================================================================== =================== ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
No, when you are attempting to connect using ODBC, you dont need to run the coledbp.sql file. Make sure that, when you want to connect using ODBC, the provider name is specified as "Microsoft OLEDB Provider for ODBC Driver" and the "Data Source" Text Box has the system DSN name that you created. But when you are trying to connect using IBM Informix OLEDB Provider you must run the coledbp.sql file in the sysmaster database using informix ID. Regards, Prem "Bruce Simms" <BSimms@talx.com> Sent by: ids-bounces@iiug.org 08/02/2008 02:30 Please respond to ids@iiug.org To ids@iiug.org cc Subject RE: Connect SQL Server to Informix DB [11233] Art, You are correct there is a sql coledbp.sql in the INFORMIXDIR/etc that must be run. Bruce Simms Data Base Services TALX Corporation 2330 Ball Drive St. Louis, MO 63146 Phone (314) 214-7703 FAX (314) 983-3238 bsimms@talx.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art S. Kagel (Oninit LLC) Sent: Thursday, February 07, 2008 2:43 PM To: ids@iiug.org Subject: Re: Connect SQL Server to Informix DB [11232] GUNJAN SHAH wrote: Don't know the details 'cause I don't do Windows stuff much, check the manuals, but IB there's a script that needs to be run to configure the IDS server to support OLEDB connections by creating some tables in sysmaster and populating them. Art S. Kagel Oninit > The connection thru informix db driver is successfully connecting. this is > what I am trying to do is: > > - Client having Infomix DB server and they have provided the connection > string. > - The connection with their server using SetNet32 is being done successfully. > - We have created the System DSN for Informix DB and tested its working fine. > - Now I am trying to connect it thru sql server 2005. For that I have created > the Linked Server. > - When I try to shoot the query to their db and then I am getting the followin > error:: > > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "Unspecified error". > OLE DB provider "MSDASQL" for linked server "realplus_sst" returned message > "[Informix][Informix ODBC Driver][Informix]Database not found or no system > permission.". > Msg 7311, Level 16, State 2, Line 1 > Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider > "MSDASQL" for linked server "realplus_sst". The provider supports the > interface, but returns a failure code when it is used. > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > ======================================================================== =================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > ======================================================================== =================== > > ======================================================================== =================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ======================================================================== =================== ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!