Analytics v11.0.5 : issue in Microsoft SQL Server ( OLEDB) datasource

[nsaxena]

Hello all, we recently moved from 10.2 FP8 to V11.0.5 . One of the sql server connetion (OLEDB) is not working in cognos11 analytics.

I did saw that in v.11 onwards OLEDB connection is not supported by cognos. so we need alternate.

I read that it could be due to missing drivers etc, but can you please suggest where to check and how to fix this.

[Karti]

Have you installed the necessary drivers on to your Front End servers? I know that when we do our instances, we have to install the SQL client in order to get the necessary drivers to talk to SQL on a different machine.

[AnalyticsWithJay]

On the Cognos servers, try connecting to your database using another connectivity tool other than Cognos. This will identify whether the issue is related to Cognos or your environment.

If the client is installed, you should find an entry in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\

Have a read of this page if you haven't already:
http://www-01.ibm.com/support/docview.wss?uid=swg21988989

Excerpt...
Microsoft SQL Server via OLEDB or Native Client 2005 and 2018
Older versions of the Microsoft SQL Server client libraries are no longer supported ( https://msdn.microsoft.com/en-us/library/cc280510.aspx). Applications that must continue accessing SQL Server via OLEDB can use Native client connections that will include Provider=SQLNCLI11. This corresponds to the current SQL Server Native client version 11 that is supported with SQL Server 2016, 2014, and 2012. Alternatively, connections that use the Microsoft ODBC drive for SQL Server could be used.

[nsaxena]

If the client is installed, you should find an entry in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\

I cannot find any entry in above path.I checked my old environments where SQL OLEDB is working there too there is no entry in above path of regedit. Though i could find below settings (refer to attached image).
What does it mean? : is sql native client installed? will that mean drivers already there? Please suggest.

Path where i could find entries :
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC DRIVERS>> CONTAINS 'SQL SERVER' entry
HKEY_LOCAL_MACHINE\SOFTWARE\SQL SERVER>> CONTAINS SQL SERVER entry

[nsaxena]

Have you installed the necessary drivers on to your Front End servers? I know that when we do our instances, we have to install the SQL client in order to get the necessary drivers to talk to SQL on a different machine.

Can anyone suggest me list of drivers(for SQL OLEDB )so that i can find and make sure they are available in  cognos analytics/drivers location.

Cognos environment : v.11.0.5.

[nsaxena]

any help appreciated.

[zakatak]

I'm confused by what you want.

I did saw that in v.11 onwards OLEDB connection is not supported by cognos. so we need alternate.

Followed up by...

Can anyone suggest me list of drivers(for SQL OLEDB )so that i can find and make sure they are available in  cognos analytics/drivers location.

Which seem to counter each other. To me, it reads as "Please help me do this thing that isn't supported." which seems like a bad idea. Why not follow IBM's instructions for connecting to SQL Server?

[TomCognos]

Just download the latest sql sever drivers. "ODBC Drive 13for SQL Server". Had to do the same for here going from 10.2.2 to 11.03 (then 4 now 5 soon 6). All is working not an issue. You dont need to get an ODBC that reads OLEDB. Just as long as there is an odbc that connects cognos to sql server.

[MFGF]

My advice is to grab the SQL Server JDBC driver and use this instead of OLEDB. You could use the SQL Server Native Client, but (just as with ODBC and OLEDB) this is limiting you to 32-bit CQE access. If you use the JDBC connection you can utilise 64-bit DQM access and as a bonus you don't need anything apart from the SAL Server JDBC driver on your Cognos server(s).

Cheers!

MF.

[the6campbells]

Cognos Analytics requires that your machine have current versions of the Microsoft Native Client.

Refer to the Microsoft pages with respect to them phasing out the older versions of Native Client
http://msdn.microsoft.com/en-us/library/cc280510.aspx


See also  http://www-01.ibm.com/support/docview.wss?uid=swg21988989

[nsaxena]

Thank you all for lovely suggestions. Surely will explore and update you on status. Thanks!