Search code examples
sqlazure-devopsyamlsql-scripts

Azure DevOps SQL Task: Column names in each table must be unique. Column name 'currency' in table 'test' is specified more than once

I am trying to execute the following SQL script into the database. When I deploy it the first time, it succeeds without errors. When I deploy it the second time, it is giving the error.

I am not using an inline script in this case. I am making use of the task SqlAzureDacpacDeployment@1

IF NOT EXISTS (SELECT 1  FROM SYS.COLUMNS WHERE  
  OBJECT_ID = OBJECT_ID(N'[dbo].[test]') AND name = 'currency')
BEGIN
  ALTER TABLE [dbo].[salesorder] ADD currency varchar(10)
END

I tried also

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'currency' 
              AND object_id = OBJECT_ID('test'))
    BEGIN
        ALTER TABLE test
        ADD currency varchar(10)
    END
ELSE
  BEGIN
    PRINT 'Currency kolom bestaat reeds.'
  END

What might be the solution for this in this case?

Solution

  • I could reproduce the issue running the same query in the Query editor hub of my SQL DB, if the column currency alreay existed in [dbo].[salesorder]. Image

    You may try the SQL script in the sample pipeline below, while we need to enable system.debug to check the output in the pipeline debug logs.

    variables:
  ARMSvcCnnName: ARMSvcCnnWIFAutoSub1
  AzureSQLServerName: xxxxsqlserverxxx
  AzureSQLDBName: xxxsqldbxxx
  system.debug: true

pool:
  vmImage: 'windows-latest'

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Run SQL query'
  inputs:
    azureSubscription: '$(ARMSvcCnnName)'
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(AzureSQLServerName).database.windows.net'
    DatabaseName: '$(AzureSQLDBName)'
    deployType: 'InlineSqlTask'
    SqlInline: |
      -- Check if the currency column exists in [dbo].[test]
      IF NOT EXISTS (
          SELECT 1 
          FROM SYS.COLUMNS 
          WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[test]') 
          AND name = 'currency'
      )
      BEGIN
          -- Check if the currency column exists in [dbo].[salesorder]
          IF NOT EXISTS (
              SELECT 1 
              FROM SYS.COLUMNS 
              WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[salesorder]') 
              AND name = 'currency'
          )
          BEGIN
              ALTER TABLE [dbo].[salesorder] ADD currency varchar(10);
              PRINT 'Currency column added to [dbo].[salesorder].';
          END
          ELSE
          BEGIN
              PRINT 'Currency kolom bestaat reeds in [dbo].[salesorder].';
          END
      END
    IpDetectionMethod: 'AutoDetect'
    DeleteFirewallRule: false

    Image