CODEWITHSUNDEEP
sqldatabaseazureazure-sql-databasedatabase-migration
dweeb_plus_plus
dweeb_plus_plus

Reputation: 83

Azure SQL Data Migration Assistant (DMA) Error - Three or Four Part Names

I'm using the MS Data Migration Assistant tool to move a SQL Server 2016 DB to Azure. I'm getting the following error on 80+ stored procedures:

Queries or references using three- or four-part names not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

All of these stored procedures are using the current database and referencing the current database name. For example, this instruction is causing the error:

DELETE FROM [STDR].[dbo].[report] WHERE [report_id] = @xid

and when I run the command:

SELECT DB_NAME();

I get:

STDR

Could this be an error in the DMA tool? It's preventing me from executing the migration. I'd rather not have to modify all of these procedures. Thanks.

Upvotes: 1

Views: 1602

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15684

It's just the four-part name or three-part name that is not compatible with Azure SQL Database. You can script all your programing objects and then change the three part name format to two-part name format (dbo.[NameOfTheObjet]) on the script using Find and Replace on a text editor like Notepad++, then run that script on your Azure SQL Database to migrate your programming objects.

After that you can use DMA only to migrate the schema and data of your tables.

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

1.Queries or references using three- or four-part names not supported in Azure SQL Database.

It's not the error in the DMA tool. Cross database queries using three or four part names is not supported in Azure SQL Server.

enter image description here

You can read more in the official documentation:Resolving Transact-SQL differences during migration to SQL Database。

2.Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

About this question, I have an idea and I think you can try it. You can specify target Azure Database instance which has the same database name and the same schema objects with your on-premises SQL Server. Otherwise, when your SQL Server 2016 DB is migrated to Azure, the current database is not [STDR] and cause the error.

Reference: Migrate on-premises SQL Server or SQL Server on Azure VMs to Azure SQL Database using the Data Migration Assistant.

Hope this helps.

Upvotes: 1

Related Questions