SQL migration using SSMA, tables need database reference

Ignition
,
1

Hey folks,

We’re in the midst of a SQL migration from MySQL to MSSQL using the SSMA tool.

However, I’ve found that the tables we migrate, when we query from ignition, they need a database reference (ie: select * from database.table), whereas if I build that same table using a transaction group, I can use the ‘standard’ query (ie: select * from table).

Hoping someone can help me understand the mechanism, and possibly help me solve this.

Thanks!

2

Most likely, your migration tool is logging in with different credentials than the gateway, putting unqualified tables in a different schema.

1 Like
3

We’re using the same user credentials between both the gateway and the migration tool. Is that what you mean?

4

Yes, that's what I meant. And there's no overrides anywhere for schema name?

1 Like
5

I think you identified the issue.

The DB name and the name I assigned the DB in the ignition connection are slightly different.

I’ll verify that this is the issue when I get back to the office. That’s a ‘doh’ moment for sure.

6

Turns out that using the SSMA tool, a schema is made named the same as the MySQL database youre migrating from. However, it seems as though the default schema is dbo.

Moving all the tables to the dbo schema fixed the addressing.

1 Like