Migrating MySQL procedure to SQL Server-CodePudding

So I'm migrating data from a MySQL (MariaDB) to a SQL Server 2019, and all tables migrated fine, but the stored procedures didn't, and I would like to know what causes this error, since I've never used SQL Server only MySQL.

My ERROR log form SQL Server Migration Assistant for MySQL

Starting Phase #1
Loading to database new procedure SwMetrics.testreportingdebug.AddTestCaseRequirement ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.AddTestResultRequirement ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.BeginTestResults ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.BeginTestRun ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.BeginTestRunWithTestJob ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateJiraLink ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTarget ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestCase ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestJob ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestResultsJiraLink ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestSuite ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestSuiteCaseLink ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestSuiteCollection ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateTestSuiteCollectionLink ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.CreateVersion ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.EndTestJob ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.EndTestResults ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.EndTestRun ...
  ... sql execution failed
Loading to database new function SwMetrics.testreportingdebug.IsTemporaryName ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.SetStatusName ...
  ... sql execution failed
Loading to database new procedure SwMetrics.testreportingdebug.SetTestJobFinished ...
  ... sql execution failed
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.AddTestCaseRequirement
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.AddTestResultRequirement
 Errors: Incorrect syntax near '`'.
Incorrect syntax near '`'.
Incorrect syntax near '`'.
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Synchronization error: Incorrect syntax near '`'.
Incorrect syntax near '`'.
Incorrect syntax near '`'.
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. On: SwMetrics.testreportingdebug.BeginTestResults
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.BeginTestRun
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.BeginTestRunWithTestJob
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateJiraLink
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTarget
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestCase
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestJob
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestResultsJiraLink
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestSuite
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestSuiteCaseLink
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestSuiteCollection
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateTestSuiteCollectionLink
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateVersion
 Errors: Incorrect syntax near '`'.
Synchronization error: Incorrect syntax near '`'. On: SwMetrics.testreportingdebug.EndTestJob
 Errors: Incorrect syntax near '`'.
Synchronization error: Incorrect syntax near '`'. On: SwMetrics.testreportingdebug.EndTestResults
 Errors: Incorrect syntax near '`'.
Synchronization error: Incorrect syntax near '`'. On: SwMetrics.testreportingdebug.EndTestRun
 Errors: Invalid column name '__'.
Invalid column name '__'.
Synchronization error: Invalid column name '__'.
Invalid column name '__'. On: SwMetrics.testreportingdebug.IsTemporaryName
 Errors: DEFAULT or NULL are not allowed as explicit identity values.
Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.SetStatusName
 Errors: Incorrect syntax near '`'.
Synchronization error: Incorrect syntax near '`'. On: SwMetrics.testreportingdebug.SetTestJobFinished
Synchronization operation is complete.

Error 1

An example of one of the failing MySQL stored procedures BeginTestRun

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)
BEGIN
  INSERT INTO TestRuns (`id`,`StartTime`, `TestJobId`)
  VALUES (NULL,StartTime,TestJobId);
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

The above one had this error:

Errors: DEFAULT or NULL are not allowed as explicit identity values.

What should I write instead of NULL? and why isn't this allowed?

Error 2

Another one EndTestRun

CREATE DEFINER=`root`@`localhost` PROCEDURE `EndTestRun`(TestRunId INT, EndTime DATETIME)
BEGIN
  UPDATE TestRuns
  SET `EndTime` = EndTime
  WHERE ID = TestRunId;
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

Had this error

Synchronization error: Incorrect syntax near '`'. On: SwMetrics.testreportingdebug.EndTestRun Errors: Invalid column name '__'.

I don't really see where __ should be?

Error 3

And another different one is CreateVersion

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `CreateVersion`(varName varchar(100), Version varchar(100), TargetId INT)
BEGIN

  INSERT INTO Versions(`id`,`Name`,`Version`,`TargetId`)
  VALUES (NULL,varName,Version,TargetId);
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

With the error

Synchronization error: DEFAULT or NULL are not allowed as explicit identity values. On: SwMetrics.testreportingdebug.CreateVersion Errors: Incorrect syntax near '`'.

Should ` just be removed, why isn't it allowed?

CodePudding user response：

Errors: DEFAULT or NULL are not allowed as explicit identity values.

I imagine that the id column in this table is an identity, which means the following code should work.

CREATE PROCEDURE CreateVersion (varName varchar(100), Version varchar(100), TargetId INT)
BEGIN

  INSERT INTO Versions(Name,Version,TargetId)
  VALUES (varName,Version,TargetId);
  SELECT SCOPE_IDENTITY() AS LastInsertId;
END

CodePudding user response：

First SQL Server does not accepts accents (which are specific to MySQL) to delimits SQL identifiers (name of table, column, procedure... and so on). You must use SQL ISO standard double quote or specific SQL Server delimiter (square brackets) or nothing if your SQL identifier conforms to the standard. So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun` ...

Must be rewrite as :

CREATE DEFINER="root`@`localhost" PROCEDURE "BeginTestRun" ...

Or :

CREATE DEFINER=[root`@`localhost] PROCEDURE [BeginTestRun] ...

Second DEFINER is aspecific MySQL terms/object that ISO SQL Standard does not know, nor Microsoft SQL Server. The equivalent in the standard ISO SQL is the owner of the objects that is automatically associated to the SQL user that execute the CREATE statement. So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun` ...

Must be rewrite as :

CREATE PROCEDURE "BeginTestRun" ...

Third, every variables must be prefixed with a "@" (arobas / at sign). So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)

Must be rewrite :

CREATE PROCEDURE [BeginTestRun] (@StartTime DATETIME, @TestJobId INT)

Fourth, DATETIME is an obsolete datatype and it would be preferable to use DATETIME2. So

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)

Must be rewrite as :

CREATE PROCEDURE [BeginTestRun] (@StartTime DATETIME2, @TestJobId INT)

Fith, LAST_INSERT_ID() does not exists in SQL Server and must be replaced by SCOPE_IDENTITY(). So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)
BEGIN
  INSERT INTO TestRuns (`id`,`StartTime`, `TestJobId`)
  VALUES (NULL,StartTime,TestJobId);
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

Must be rewrite as :

CREATE PROCEDURE [BeginTestRun] (@StartTime DATETIME2, @TestJobId INT)
BEGIN
  INSERT INTO TestRuns (id, StartTime, TestJobId)
  VALUES (NULL, @StartTime, @TestJobId);
  SELECT SCOPE_IDENTITY() AS LastInsertId;
END

Sixth, because SQL Server procedures are created by default in a debug mode, you must prefer to add the SET NOCOUNT ON statement to avoid untimely messages during operation. So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)
BEGIN
  INSERT INTO TestRuns (`id`,`StartTime`, `TestJobId`)
  VALUES (NULL,StartTime,TestJobId);
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

Must be rewrite as :

CREATE PROCEDURE [BeginTestRun] (@StartTime DATETIME2, @TestJobId INT)
BEGIN
SET NOCOUNT ON;
  INSERT INTO TestRuns (id, StartTime, TestJobId)
  VALUES (NULL, @StartTime, @TestJobId);
  SELECT SCOPE_IDENTITY() AS LastInsertId;
END

Seventh, when your table as an IDENTITY column, by default you cannot INSERT any value inside. So, if your column id in the table TestRuns is an IDENTITY, you should not specify this column in the list of insert target columns. So :

CREATE DEFINER=`root`@`localhost` PROCEDURE `BeginTestRun`(StartTime DATETIME, TestJobId INT)
BEGIN
  INSERT INTO TestRuns (`id`,`StartTime`, `TestJobId`)
  VALUES (NULL,StartTime,TestJobId);
  SELECT LAST_INSERT_ID() AS LastInsertId;
END

Must be rewrite as :

CREATE PROCEDURE [BeginTestRun] (@StartTime DATETIME2, @TestJobId INT)
BEGIN
SET NOCOUNT ON;
  INSERT INTO TestRuns (StartTime, TestJobId)
  VALUES (@StartTime, @TestJobId);
  SELECT SCOPE_IDENTITY() AS LastInsertId;
END

Of course, the best way is to learn Transact SQL !