Functional database connection in Insights can't access tables created after the connection was made.

1353
6
Jump to solution
03-09-2018 03:41 AM
TobiSellekaerts
New Contributor III

We have just gotten started with our newly installed Insights on Enterprise ArcGIS 10.5.1.  We are running Portal 10.5.1, and have a hosting server also with 10.5.1.

We are bringing in data successfully from SQL Server using the Database connection.  We created a connection to our database (let's call it Connection A) and added data table X (a geodatabase feature class) to Insights.  The next day, there are new records in Layer X which we can see in Insights.  All good so far.

The next week, we create a new table (it happens to be a geodatabase feature class) in the Database; let's call this one table Y.  Using Connection A, we can see table Y.  We can add it to our Insights workbook.  However, after adding it we get the dreaded 'Something went wrong with this database connection. Check to see if the connection properties need to be updated.'

Although I don't think anything is wrong with Connection A (since it's working with table X), I follow the instructions (Update a database connection—Insights reference | ArcGIS ) and specify a different login username for the database (just to give it something different - we have a few connections we can use). I stop and then start the Relational Database service on the hosting server.  Table X still works and still updates.  Table Y still gives us the error message.

We create yet another new table in the database and try to add it to Insights, but get the same error message.  At this point, we theorize that any new tables created (after Connection A was created) will be unusable.

Next I create a new connection - Connection B - in Insights to the same database. I give it a different connection name, but all other parameters are identical.  Using this new connection, Table Y works fine.  So we have a workaround for now.

As I understand from Troubleshoot a database connection—Insights reference | ArcGIS , we cannot delete any previous connections as data from those connections are in use by other workbooks.  So our workaround means every time we create a new table in the database, we will have to make a new connection in Insights.

Finally to the question: how do I get an existing database connection in Insights to successfully use a new table added to that database after the initial connection was made? Could this reflect a problem with our SQL Server databases?

0 Kudos
1 Solution

Accepted Solutions
JonEmch
Esri Regular Contributor

Thank you for the follow up Toby, please keep me posted.

Keep on keeping on!

View solution in original post

0 Kudos
6 Replies
WillHughes2
New Contributor III

What version is Insights? I'd recommend updating to v2.2.1 if you have not done so already. 

0 Kudos
TobiSellekaerts
New Contributor III

We are running 2.2.1 (that's what was installed at the beginning). 

Interestingly I noticed this week that when adding a table (not a feature class) to SQL server after creating the database connection, the table was immediately visible in insights.  However, actually adding the table to insights caused a 'something went wrong with this data source' error.  Only after creating yet another new database connection in Insights was I able to add the table successfully to insights.

0 Kudos
WillHughes2
New Contributor III

Is the connection using the schema owner? What version is your SQL Server database? Ensure you are using the correct JDBC driver, and check for required database privileges. Required database privileges—Insights reference | ArcGIS Also have you checked the logs? The Insights error codes range is 111000 - 111999.

https://servername.domain.com:6443/arcgis/admin/logs/query 

0 Kudos
JonEmch
Esri Regular Contributor

Hey Tobi,

   I wanted to follow up on my colleagues question. Were you able to solve this issue? To highlight Will's point it does indeed sound like there may be an issue with the permissions given to that Insights user.

Keep on keeping on!
0 Kudos
TobiSellekaerts
New Contributor III

No, but it's OBE now as we've upgraded!  I'll confirm we're not having the problems any longer when I get back into the office.

JonEmch
Esri Regular Contributor

Thank you for the follow up Toby, please keep me posted.

Keep on keeping on!
0 Kudos