We are on 11.0.9 and 11.0.13. We use MS SQL Server.

Awhile ago, i worked on a way to allow reporting on SQL data that is not in a FM package. Now I cannot remember how I did it. But remember that it was some option where i entered the SQL Server, then the Schema, then found the SQL Tables (maybe entered a prefix table name to narrow down the list of tables) and then selected the columns from that table. Also had an option to rename the columns. What is this feature called and how do i get back to it? Maybe it was a data set but i have googled data sets and nothing so far has discussed how to pull in SQL data directly. 

Thanks

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

WOrking with it a bit more and think it was a Data Server Connection. In trying to get that to work I am getting an error  "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" when trying to connect with the following connection string or about 50 different variations of the following. Below is exactly what is used in the jdbe data source connection string.

jdbc:sqlserver://XXXXXXXXXX.XXXXX.local:1433;DATABASE=BankXX;LOGINTIMEOUT=0;sslProtocol=TLSv1.2;DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

I am able to connect to SQL database tables directly from Reporting or adding them as sources in Data Modules without problems.  It seems your issue is related to your Cognos installation settings or could also be SQL security.

------------------------------
Mauro Santos Otero
------------------------------

Original Message:
Sent: Wed February 03, 2021 05:41 PM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

@Mauro Santos Otero How have you used SQL tables directly in Reporting (other than Custom SQL queries). And how did you add them as source in Data Modules?​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Wed February 03, 2021 07:44 PM
From: Mauro Santos Otero
Subject: Reporting on SQL Data that is not in a Framework Manager Package

I am able to connect to SQL database tables directly from Reporting or adding them as sources in Data Modules without problems.  It seems your issue is related to your Cognos installation settings or could also be SQL security.

------------------------------
Mauro Santos Otero
------------------------------


Original Message:
Sent: Wed February 03, 2021 05:41 PM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

We have only ever been able to get the SQL connection working using "sqljdbc42.jar" in the drivers folder. We use SQL server 2016 and CA 11.1.3 & 11.1.6

For us the connection string setting did not need all the parameters

JDBC URL:      jdbc:sqlserver://xx.xx.xx.x;databaseName=dbnamehere
Driver class name:  com.microsoft.sqlserver.jdbc.SQLServerDriver

What we found was that it was critical to define the SQL database name for authentication.


------------------------------
Jonathan berry
------------------------------

Original Message:
Sent: Wed February 03, 2021 05:41 PM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

@Jonathan berry - can you show me your exact JDBC URL? The one you show has "Driver class name", which i do not think is correct. Did you specifically try  mssql-jdbc-7.0.0.jre8  ​in the Driver folder and not get it to work? And what do you mean "define the SQL database name for authentication"? Thanks for your help.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Thu February 04, 2021 04:37 AM
From: Jonathan berry
Subject: Reporting on SQL Data that is not in a Framework Manager Package

We have only ever been able to get the SQL connection working using "sqljdbc42.jar" in the drivers folder. We use SQL server 2016 and CA 11.1.3 & 11.1.6

For us the connection string setting did not need all the parameters

JDBC URL:      jdbc:sqlserver://xx.xx.xx.x;databaseName=dbnamehere
Driver class name:  com.microsoft.sqlserver.jdbc.SQLServerDriver

What we found was that it was critical to define the SQL database name for authentication.


------------------------------
Jonathan berry
------------------------------


Original Message:
Sent: Wed February 03, 2021 05:41 PM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

There is no need to be trying to define a driver class name as part of the URL.

A connection automatically has a default property which correspond to the class name used by a vendor in their driver.

When CA needs the JRA to locate the driver, it will pass that name to the JRE.

Most of the time, you would never have a reason to change the default driver class name which CA provides.

In terms of the SQL Server driver you mentioned, while it is not ancient, that is circa mid-summer 2018.

You can see which versions of vendors drivers have been verified at https://www.ibm.com/support/pages/node/1106607#11.1.7fp2r

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Wed February 03, 2021 05:41 PM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

mssql-jdbc-7.0.0.jre8 is in the Drivers folder. I assume that is what is needed for DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver  when using JDBC. Again, I am trying to setup a Connection in the Data Server Connection and am using the exact same connection string (minus the ^password and ^userid) that is used in the Data Source Connections for JDBC. In Data Source Connections it TEST = Success. BUt in Data Server Connection the URL is TEST = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Here's the jdbc string I use in a data server connection. You can keep some of your params but I'd lose the driver name and everything after that since driver is defined automatically for you in the next field down. Once you test successfully and load metadata, your new server connection will be a choice in any data module as a source.

jdbc:sqlserver://XX.XX.XX.XX\XXSERVERNAMEXX:XXXX

------------------------------
Victoria Ratkos
------------------------------

@Victoria Ratkos - Where is the "driver automatically defined for you in the next field down"? Could you show me your "Edit Microsoft SQL Server connection"
​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Thu February 04, 2021 08:43 AM
From: Victoria Ratkos
Subject: Reporting on SQL Data that is not in a Framework Manager Package

Here's the jdbc string I use in a data server connection. You can keep some of your params but I'd lose the driver name and everything after that since driver is defined automatically for you in the next field down. Once you test successfully and load metadata, your new server connection will be a choice in any data module as a source.

jdbc:sqlserver://XX.XX.XX.XX\XXSERVERNAMEXX:XXXX

------------------------------
Victoria Ratkos
------------------------------

Sure thing Brenda, let's see if this image comes through:
I glanced at other server connections (most of mine are mysql, not ms sql) and they all have that driver class name field below and I never edit it manually. Not sure why it's not listed on yours? Though I'm on 11.1.4

------------------------------
Victoria Ratkos
------------------------------

Original Message:
Sent: Thu February 04, 2021 08:58 AM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

@Victoria Ratkos - Where is the "driver automatically defined for you in the next field down"? Could you show me your "Edit Microsoft SQL Server connection"

​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------


Original Message:
Sent: Thu February 04, 2021 08:43 AM
From: Victoria Ratkos
Subject: Reporting on SQL Data that is not in a Framework Manager Package

Here's the jdbc string I use in a data server connection. You can keep some of your params but I'd lose the driver name and everything after that since driver is defined automatically for you in the next field down. Once you test successfully and load metadata, your new server connection will be a choice in any data module as a source.

jdbc:sqlserver://XX.XX.XX.XX\XXSERVERNAMEXX:XXXX

------------------------------
Victoria Ratkos

The older Cognos Admin pages always showed the driver class name.

Some of the initial CA 11.x releases did not in the "new" admin page to define connections.

If someone is not familiar with Java/JDBC, couple of tips

(a) Do not assume that the name of a JAR file will confirm what version of the driver you are using

(b) Some vendors will document steps to get that information without making an actual connection to a source.

If you have made a connection, the test response in both Admin interfaces will tell you the names and versions of the driver and server.

Those details are from the vendors code. In some cases, the same driver (i.e. MySQL, IBM JCC, SQL Server, ...) can be used to point to various products (i.e. SQL Server,  Azure SQL or Azure SQL DW). In the test response, you may also see a "sub type" which indicates that CA had determined that you are pointing at perhaps Azure SQL DW.

Don't leave multiple versions of a vendors driver in <cognos>\drivers. When Java is requested to dynamically load a class, it will search use the class name it is given (i.e. your driver class name), and will pick "first one found".

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Thu February 04, 2021 09:06 AM
From: Victoria Ratkos
Subject: Reporting on SQL Data that is not in a Framework Manager Package

Sure thing Brenda, let's see if this image comes through:
I glanced at other server connections (most of mine are mysql, not ms sql) and they all have that driver class name field below and I never edit it manually. Not sure why it's not listed on yours? Though I'm on 11.1.4

------------------------------
Victoria Ratkos
------------------------------


Original Message:
Sent: Thu February 04, 2021 08:58 AM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

@Victoria Ratkos - Where is the "driver automatically defined for you in the next field down"? Could you show me your "Edit Microsoft SQL Server connection"

​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS


Original Message:
Sent: Thu February 04, 2021 08:43 AM
From: Victoria Ratkos
Subject: Reporting on SQL Data that is not in a Framework Manager Package

Here's the jdbc string I use in a data server connection. You can keep some of your params but I'd lose the driver name and everything after that since driver is defined automatically for you in the next field down. Once you test successfully and load metadata, your new server connection will be a choice in any data module as a source.

jdbc:sqlserver://XX.XX.XX.XX\XXSERVERNAMEXX:XXXX

------------------------------
Victoria Ratkos

Well ... got it to work

I was using the connection string from data source
jdbc:sqlserver://XXXXXXXXXX.XXXXX.local:1433;DATABASE=BankXX;LOGINTIMEOUT=0;sslProtocol=TLSv1.2;DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

and was getting the error
 = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

Seems the "fix" is to remove the parameter DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

Still have an outstanding question to @Mauro Santos Otero​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

@Brenda Grossnickle ​ Yes. once the connection is setup and schemas loaded I can access SQL tables select them for adding them into the data module. I cannot help you with the connection side because that's done by the Admin.  Once that is solved, you should be able to add DW tables to a data module without issues.  I recall that the Admin had to load the schema (after setting the connection) for making the fables visible. 




------------------------------
Mauro Santos Otero
------------------------------

Original Message:
Sent: Thu February 04, 2021 09:51 AM
From: brenda grossnickle
Subject: Reporting on SQL Data that is not in a Framework Manager Package

Well ... got it to work

I was using the connection string from data source
jdbc:sqlserver://XXXXXXXXXX.XXXXX.local:1433;DATABASE=BankXX;LOGINTIMEOUT=0;sslProtocol=TLSv1.2;DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

and was getting the error
 = "XQE-DAT-0036 Invalid connection string, missing information: ;DRIVER_NAME=" 

Seems the "fix" is to remove the parameter DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

Still have an outstanding question to @Mauro Santos Otero​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------