# Database used by system.db.\* queries

**URL:** <https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229>\
**Category:** Ignition\
**Created:** [February 5, 2024, 2:37pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229 "2024-02-05T14:37:06Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 5, 2024, 2:37pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/1 "2024-02-05T14:37:06Z")

</div>

We had an issue a few weeks ago after archiving data from a few of our MSSQL databases. The "archived" databases had tables with the same name as the "active" databases, and some of our **system.db.** \* queries began to fail if they did not have the database and schema specified within the inline query. For example, this query would fail:

> SELECT [column] FROM [table]

But this query would succeed

> SELECT [column] FROM [database].[dbo].[table]

The python statements in Ignition had the correct database specified in the statement (e.g. **system.db.runScalarPrepQuery(query,args,DB)** where DB is the correct database for the active data), but the default database for the Ignition project is a different database.

I suspect that if the queries ran against the default project database, SQL wouldn't know which database (i.e. "Active" or "Archived") to use.

Here is my question: Is it possible that the **system.db.** \* queries will use the default database specified in the project rather than the database specified in the **system.db.** \* statement itself?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 5, 2024, 8:08pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/2 "2024-02-05T20:08:50Z")

</div>

Yes, if they are running within the project itself and the db connection is _not specified_ within the function.

> [@Uploading a file to a table](https://forum.inductiveautomation.com/t/uploading-a-file-to-a-table/45448/11):
>
> I’ve gotten in the habit of always specifying the db connection when using scripting, even if the default is specified in the project properties. It avoids confusion as to that connection you’re using It works regardless of the scope where the script is run. (Designer / Client / Gateway)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 8:10pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/3 "2024-02-05T20:10:49Z")

</div>

> [@rbachman](#):
>
> use the default database specified in the project rather than the database specified in the **system.db.** \* statement itself?

No.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 5, 2024, 8:21pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/4 "2024-02-05T20:21:59Z")

</div>

All said, if you are specifying the db connection, then the connection does not appear to be pointing to the correct database.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 8:29pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/5 "2024-02-05T20:29:38Z")

</div>

> [@JordanCClark](#):
>
> not appear to be pointing to the correct database.

Or the database has unexpected schema search/fallback rules that are confusing the issue.

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 5, 2024, 8:30pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/6 "2024-02-05T20:30:42Z")

</div>

I should clarify: we have had multiple programmers review the code, and we are confident that the correct database was specified in the **system.db.** \* statement. However, when the query was run, it would return an error until we added **[database].[dbo].[table]** in the **FROM** statement.

If the issues is _not_ that the script is using the default database from the project, is there another explanation as to why the table was not able to be located based on the Ignition database connection specified in the parameters?

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [February 5, 2024, 8:40pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/7 "2024-02-05T20:40:17Z")

</div>

Double check the database connection configuration for that DB in the gateway. Confirm that it is pointing at the correct DB.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 8:40pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/8 "2024-02-05T20:40:25Z")

</div>

Look at the database connection's extra parameters. Check for schema overrides. Check for the schema defaults in that user's account settings on the DB side.

Have you tried using just `[dbo].[table]` ?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 8:44pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/9 "2024-02-05T20:44:33Z")

</div>

> [@rbachman](#):
>
> runScalarPrepQuery

Have you tried using `system.db.runPrepQuery()` followed by `ds.getValueAt(0,0)` to emulate `runScalarPrepQuery` ? To eliminate the possibility of a bug in just that function?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 8:46pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/10 "2024-02-05T20:46:29Z")

</div>

Are you trying to pass any arguments using keywords? That isn't supported everywhere (though I understand that IA plans to fix them all).

{ You haven't shown your actual script in this topic yet. }

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 5, 2024, 10:03pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/11 "2024-02-05T22:03:31Z")

</div>

Here is one example of code where we had an issue. Again this issue started as soon as we had a new database called **ARCHIVED JNAP Broadcasts** that had the same tables as the **JNAP Broadcasts** database.

```auto
DB = 'JNAPBroadcasts'

def nextCSNQuery(currentCSN):
	# Set the variables
	query = '''SELECT TOP (1) ProcessSequence 
				FROM LoadDetail					
				WHERE ProcessSequence > ? and assigned <> 99
				ORDER BY ProcessSequence asc'''
	args = [currentCSN]
	newCSN = system.db.runScalarPrepQuery(query,args,DB)

```

In order to fix this, the code now reads as follows:

```auto
DB = 'JNAPBroadcasts'

def nextCSNQuery(currentCSN):
	# Set the variables
	query = '''SELECT TOP (1) ProcessSequence 
				FROM [JNAP Broadcasts].[dbo].[LoadDetail]
				WHERE ProcessSequence > ? and assigned <> 99
				ORDER BY ProcessSequence asc'''
	args = [currentCSN]
	newCSN = system.db.runScalarPrepQuery(query,args,DB)

```

The Database Connection in the Gateway looks like this:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/f/5f804b2e80c19c2b069e5aa2e86a79cf4e14068b.png)

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 5, 2024, 10:04pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/12 "2024-02-05T22:04:50Z")

</div>

> [@pturmel](#):
>
> Have you tried using `system.db.runPrepQuery()` followed by `ds.getValueAt(0,0)` to emulate `runScalarPrepQuery` ? To eliminate the possibility of a bug in just that function?

Also, I should mention that it wasn't just for **runScalarPrepQuery** ; we had this same issue for **runPrepQuery** and **runPrepUpdate** as well.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 10:06pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/13 "2024-02-05T22:06:51Z")

</div>

Consider logging your arguments just before each call, to see if anything is smashing that global variable.

I am otherwise out of ideas. Except, maybe, fire Microsoft?

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 5, 2024, 10:09pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/14 "2024-02-05T22:09:28Z")

</div>

> [@pturmel](#):
>
> Consider logging your arguments just before each call, to see if anything is smashing that global variable.

I'm almost certain this isn't the issue because the Global Variable was used by other scripts that were working as long as the database and schema were referenced before the table name.

> [@pturmel](#):
>
> Have you tried using just `[dbo].[table]` ?

I do wonder if this could somehow be part of the issue. We didn't try it with just the schema and table reference. Our team stumbled across the solution when they tried to run it in the console and received some sort of odd error that led them to adding the database and schema to the FROM statements.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 10:11pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/15 "2024-02-05T22:11:58Z")

</div>

> [@rbachman](#):
>
> working as long as the database and schema were referenced before the table name

That just means that your cluster is set up for cross-database access. I think a broken `DB` variable is a very likely cause.

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [February 5, 2024, 10:13pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/16 "2024-02-05T22:13:58Z")

</div>

Could spaces in a database name cause issues? We run the rule of no spaces/underscores only for DB/table/column names so I'm not sure.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 5, 2024, 10:26pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/17 "2024-02-05T22:26:08Z")

</div>

No idea. Might need to be URL-encoded in a DB extra parameter.

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 6, 2024, 2:27pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/18 "2024-02-06T14:27:54Z")

</div>

Thanks for all of the feedback. For now, we've specified internally that all queries need to have the full database, schema, and table names for our queries, and everything seems to be working well. I just wanted to make sure that there wasn't something I was missing.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 6, 2024, 2:42pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/19 "2024-02-06T14:42:03Z")

</div>

I really think you should log the `DB` global variable just before your `system.db.` calls.

---

<div class="post-metadata">

**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\
**Post date:** [February 6, 2024, 3:08pm UTC](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229/20 "2024-02-06T15:08:45Z")

</div>

> [@pturmel](#):
>
> I really think you should log the `DB` global variable just before your `system.db.` calls.

It's definitely not a bad idea, but here is an example of the exact code that was failing for another query. We literally set the database variable 3 lines before we use it in this case. I'm quite confident in this specific case of the database variable, and it is correct for the table we were trying to query.

```auto
		database = 'JNAPBroadcasts'
		query = "SELECT LoadID, PartNumber, VINNumber, PlantCode FROM LoadDetail WHERE ProcessSequence = ?"
		args = [seqNum]
		pydata = system.db.runPrepQuery(query, args, database)

```

Here is the fix we had to implement to get it to work.

```auto
		database = 'JNAPBroadcasts'
		query = "SELECT LoadID, PartNumber, VINNumber, PlantCode FROM [JNAP Broadcasts].[dbo].[LoadDetail] WHERE ProcessSequence = ?"
		args = [seqNum]
		pydata = system.db.runPrepQuery(query, args, database)

```

[Next page](https://forum.inductiveautomation.com/t/database-used-by-system-db-queries/85229.md?page=2)