Hmm.
Consider interrogating your database connection via the designer query browser to obtain the connection properties and environment. Perhaps also query information_schema.tables.
1 Like
I checked it just now with the database connection in the Designer and it looks good.
I have discovered that the queries would only fail if they were the second inline query within the function to run, and the first query utilized a different database connection from the second inline query. I even found one example where the variable used for the first system.db.* call was different from the second system.db.* function, so I don't think the issue can be that the second variable wasn't set correctly.
I have opened a ticket with IA to see if they can help us dig deeper. We've got everything working now, but my main concern is that, as we continue to archive data, this may show up again somewhere else and cause further issues.
Is there any chance your queries are causing triggers to run that are causing connection property side effects?
Consider adding a statement to the data source configuration that explicitly sets the current database every time a connection is taken from the pool.
We opened a ticket with IA, but we have had trouble recreating the issue. For now, I think we're going to chalk it up to something on our side, and I'll add to this thread and open a new ticket with IA if we see it happen again.
Do you have access to MSSQL management studio? Can you see if there is a default schema applied to this user?
I could be wrong, but this feels like an MSSQL/configuration problem, not an Ignition problem. Could you see what happens if you try to execute the 2 different queries from the command line using sqlcmd? That might give us a helpful piece of information.
1 Like
The error is definitely coming from SQL, and I am able to recreate it in SSMS as follows:
- The exact table name must exist in two different databases.
- I run the query without the database or schema by utilizing a connection to any database on the server that does not contain either of the tables.
As an example, we have a table named RFV that exists in a database named Production Data and ARCHIVED Production Data. If I run the following query while utilizing a connection to either of those database, it succeeds.
SELECT TOP (100) * FROM [RFV]
However, if I try to run the query against a different database on the server that does not contain the table (e.g. Production Schedule), I receive the following error.
However, if I add the full database and schema to the FROM statement it works fine.
SELECT TOP (100) * FROM [Production Data].[dbo].[RFV]
The confusing part was that we appear to have been specifying the correct database connection in the SQL query (e.g. Production Data), so we were surprised to receive an error in Ignition.
Do any queries using this database connection have a USE database statement, especially if they are USE SOME_OTHER_DATABASE?
We had some queries that had this and saw behavior similar to what you're describing. An IA support rep strongly recommended we stop using this pattern in support ticket #100338.
Interestingly, the queries that were misbehaving were not the ones that had USE ..., but this pattern had some impact on the database connection that caused the bad behavior.
1 Like
Thanks for the the feedback on this one. I haven't personally run into this, but found lots of threads online about different default schemas and various things that SQL does to try and automatically figure out what tables to use etc. Definitely annoying!
As far as I know, we do not ever use the USE [database] statement within Ignition. I think we may have a few stored procedures that use the statement, and Ignition may call the stored procedures, but I don't believe we have ever used the statement in an Ignition inline query or Named Query. We were expecting that the database parameter in the system.db.* queries would automatically force the query to use the database specified in the database connection.
Only for the first query to run in the connection. Ignition doesn't open new connections for every query--it opens multiple and maintains a pool of connections. It then draws from the pool as needed. If a query/procedure changes the current database for a connection, it'll stay that way for the next query that draws that connection from the pool.
That's the basis of the recommendation I made here (three days ago):
Make sure the correct database is selected every time a query pulls a connection from the pool.
1 Like
I think we're doing this, but I want to make sure that I'm understanding what you are saying. The image below is an example of some of the code that was failing. The first query specified a specific database connection (i.e. Production_Data) and completed without an error.
The second query specified a different database connection (i.e. JNAPBroadcasts) and failed. If the database connection we had specified in the parameters for the second query was used (i.e. JNAPBroadcasts), it should have completed without an error; however, if the connection specified by the first query (that completed without an error) had been used (i.e. Production_Data), we would expect the second query to fail.
By "triggers" are you referring to triggers in Ignition or SQL Server triggers?
Each query you are running in that script is pulling a "fresh" connection from the connection pool. If the prior use of that connection changed the database in use before it ended, your query will fail.
And I mean SQL Server triggers. Or any other procedure or script that could change the current database for the connection, screwing up that connection when it returns to the pool.
Keep in the mind that the pool is gateway global. ANY query that causes
USE [some_other_database]
to happen will poison that connection in the pool.
1 Like
FWIW, you can check the current db for an MS SQL Server connection with:
SELECT DB_NAME() As DB;
Put that in the designer Query Browser. Execute it every couple seconds for a few minutes. If it ever shows anything other than the correct name, some query in Ignition is running something that changes it.
3 Likes
I don't see this happening anywhere in our code where it was failing. In the example in my previous post, we reference the JNAPBroadcasts connection for one query prior to the code I posted, but that connection utilizes the same SQL database as the query that ultimately failed.
Here is a rundown of the database connections used in order and where the failure occurred.
- Production_Schedule connection pulling data from the Production Data database: Succeeded (full [database].[dbo].[table] was specified)
- JNAPBroadcasts connection pulling data from the JNAP Broadcasts database: Succeeded (only the [table] was specified)
- Production_Data connection pulling data from the Production Data database: Succeeded (only the [table] was specified)
- JNAPBroadcasts connection pulling data from the JNAP Broadcasts database: Failed (only the [table] was specified)
I can confirm that we did not use any SQL Triggers.
We do not ever use the USE [DATABASE] statement.
However, I think it's entirely possible that we have other queries that call a database connection and then specify a database in the FROM statement of the query that is not the default database used in the connection such as what I listed in #1 above, and it sounds like this might be able to explain what we experienced.
1 Like
I caught it! I turned on Auto Refresh and watched for a minute, and the DB that came up finally changed after a while from the expected JNAP Broadcasts to Production Schedule. I'm certain now that this is our root cause.
Thanks @pturmel!!!
2 Likes
Now, go edit your DB connection, and go to the bottom of your advanced settings. Put:
USE JNAPBroadcasts;
(or whatever its real name is) into the connection initialization commands.
This will paper over the bug until you can audit all of your queries.