Making MSSQL as the Persistence Provider

[tyadav]

Hi,

I am trying to make MSSQL Server 2014 as my data persistence provider as per the instructions provided at http://support.ptc.com/cs/help/thingworx_hc/thingworx_6.5_hc/index.jspx?id=ConfiguringaPersistenceProviderEntity&action=…  but unfortunately, I am not able to do so. I receive an error message(please see attached pic). How can I solve this error?

Also when the instructions say import the PersistenceProvide package what exactly the instructions mean by that?

[posipova]

Have you looked into have an MSSQL  as your model&data provider? Then you could deploy the MSSQL-based version of ThingWorx. I believe the link you tried to show in the helpcenter talks about that scenario.

Now, you may also connect MSSQL as your data provider, but the route would be described here Steps to connecting to your Relational Database and some more detail in here Re: Using a third party Jar file for Database connection

[tyadav]

Hi,

I followed the steps in provided in the article Steps to connecting to your Relational Database using the JDBC extension. I was able to create the template but after configuring the thing created on the template, I am not able to test the service as mentioned in the guide.

The jdbc driver class name:  com.driverclassname.jdbc.Driver (default, as the above article, says that it is automatically created)

Connection String used: jdbc:sqlserver://localhost:<port number> ;databaseName=<databasename>;integratedSecurity=true;

and the connectionValidationString = select 1 (as mentioned in the above mentioned guide)

Is there some formatting or parameter error which is causing such an error?

The error which I am getting is Error loading tables. Please check your configuration.

Also the error in application logs is :2017-12-02 00:24:15.260+0100 [L: ERROR] [O: c.t.t.Thing] [I: TestDB] [U: Administrator] [S: ] [T: http-nio-8080-exec-1] ERROR starting: JDBC Driver Class Not Found: com.driverclassname.jdbc.Driver

[cmorfin]

Hi Tushar

just wondering are you really usign com.driverclassname.jdbc.Driver for the driver classname or is that a way to represent it ?

I think you should be using com.microsoft.sqlserver.jdbc.SQLServerDriver for MS SQL Server.

Kind regards

Christophe

[tyadav]

Hi Christophe,

Unfortunately, yes I was using com.driverclassname.jdbc.Driver for the driver classname (since in the article it said that the field is populated automatically and hence, I didn't change it.)

Also, after trying your suggestion and checking whether it is working correctly or not, by creating a SQL query service (as mentioned in the article), I am getting 'Tables not found' message in the Tables/Columns tab (which I am guessing is correct, since it is a new database connection and there are no tables created there apart from the ones created during twschema installation). Is this correct?

Also, after doing the above-mentioned steps, I am still getting the following error message while creating Mssql as the persistence provider: save failed, status: Internal Server Error message: Import Failed: Unable to create PersistenceProvider: Unable to connect to the PersistenceProvider: Mssql.

Thanks!

[cmorfin · Accepted solution]

Hi Tushar

I would like to step back to better understand what you are doing and if you actually need to do it.

My understanding is that you have downloaded the ThingWorx-Platform-Mssql-8-x-x media and installed it.

Is that correct ?

If that is the case the installation has already configured the ThingWorxPersistenceProvider to point to MSSqlServer.

You should be able to see it under DATA STORAGE > Persistence Providers, Advanced button tick Show System Objects.

So if you want to use one MS SQLServer there is nothing else for you to do.

You will only need to create a new persistence provider if you want to connect ThingWorx to a second different SQL Server.

Is that what you are trying to achieve ? (In which case you can probably get clues form the Configuration page of the ThingworxPersistenceProvider already created)

or are you using only one MS SQLServer ? (in which case you have no need to create a Persistence Provider.)

Thank you

Kind rgeards

Christophe

[tyadav]

Hi Christophe,

Thank you very much for clarifying the situation.I got confused as the name for the MSsql persitence provider is also ThingWorxPersistenceProvider  and I thought that it is the same ThingWorxPersistenceProvider (limited capacity )which we get in the education version and I would have to add the new persistence provider as it was not mentioned also in the installation guide that TW will make MsSQL as the default provider without any modifications ( that is actually a really good thing but it would be nice it was mentioned somewhere or maybe I didn't find it). I ended up literally following the word to word installation for the new persistence provider which was causing the conflict during installation.

Thanks again for clarifying.

Tushar