none
Import bacpac from Azure fails with "The data types datetime and time are incompatible in the add operator" RRS feed

  • Question

  • Tried to import a bacpac from Azure to SQLServer 2012 (using SMSS 2014)

    The original message is

    Could not import package.
    Warning SQL72012: The object [PRIMARY] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [AmsterdamBackup-Production_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [AmsterdamBackup-Production_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Error SQL72014: .Net SqlClient Data Provider: Msg 402, Level 16, State 1, Procedure trg_ins_upd_tbl_redsalarismutaties, Line 7 The data types datetime and time are incompatible in the add operator.
    Error SQL72045: Script execution error.  The executed script:
    CREATE TRIGGER trg_ins_upd_tbl_redsalarismutaties
        ON dbo.tbl_redsalarismutaties
        AFTER INSERT, UPDATE
        AS UPDATE tbl_redsalarismutaties
           SET    gewijzigdopstamp = (CONVERT (DATETIME, CONVERT (DATE, INSERTED.gewijzigdop, 0), 0) + CONVERT (TIME, INSERTED.gewijzigdoptijd, 0))
           FROM   INSERTED
           WHERE  tbl_redsalarismutaties.salarismutatieid = INSERTED.salarismutatieid;

    Table definition

    CREATE TABLE [dbo].[tbl_redsalarismutaties](
    [salarismutatieid] [bigint] IDENTITY(1,1) NOT NULL,
    ......
    [gewijzigdop] [datetime] NOT NULL,
    [gewijzigdoptijd] [datetime] NOT NULL,
    ......
    [gewijzigdopstamp] [datetime] NULL,
     CONSTRAINT [PK_tbl_redsalarismutaties] PRIMARY KEY CLUSTERED 
    (
    [salarismutatieid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    The bacpac files are backups of our production database that is made every night. 

    Any ideas why this happens. 

    regards

    Paul

    Thursday, October 2, 2014 6:26 AM

All replies

  • Do you see this error when creating the same procedure manually or does this only repro when deploying using DacFx?
    Thursday, October 2, 2014 6:36 PM
  • Only when deploying using DacFX
    Friday, October 3, 2014 5:19 AM
  • I am having the same problem. When trying to import bacpac file using Import Date Tier Application option in SQL server 2012,  i get below error on windows 7 64 Bit, desktop machine. But when i import the same file on my laptop (windows 8.1), it works fine.

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Could not import package.
    Warning SQL72012: The object [PRIMARY] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [Demo_eBilling-20150331_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Warning SQL72012: The object [Demo_eBilling-20150331_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
    Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure SearchStatement, Line 28 'concat' is not a recognized built-in function name.
    Error SQL72045: Script execution error.  The executed script:
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    CREATE PROCEDURE dbo.SearchStatement    --exec [SearchStatement] null,null,null,null,null,null,false
    @PatientId int = NULL,
    @LastName VARCHAR(100) = NULL,
    @FirstName VARCHAR(100) = NULL,
    @ProviderId int = NULL,
    @Practice int = NULL,
    @StatmentTemplate int = NULL,
    @Checked bit = 0

    AS

    BEGIN
    SET NOCOUNT ON;
    --IF @Checked = 1
    -- BEGIN
    --Select * from Patient where Patient.PatientTotalBal is not null AND Patient.PatientTotalBal > 0
     --   PRINT 'in if clause';
    --END
    --ELSE
    -- BEGIN
    PRINT 'else clause'

      Select Patient.idPatient as [PatientID], concat(Patient.LastName, ',', Patient.FirstName) as [PatientName], Provider.ShortName as [Provider], practice.ShortName as [Practice], 
      Patient.LastPatPaymentDate as [LastPatPayDate], ISNULL(Patient.LastPatPaymentAmount,0) as [LastPatPayAmount], ISN
     (Microsoft.SqlServer.Dac)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Tuesday, March 31, 2015 10:34 AM
  • Can you please open a Connect bug on this and attach your bacpac to the issue?

    How to file a connect bug: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/160e9004-34b9-496d-981b-5f174da46e4b/steps-to-file-a-connect-bug-for-ssdt?forum=ssdt

    Thanks

    Lonny

    Tuesday, March 31, 2015 9:15 PM
  • Its not possible for me to attach the bacpac with the issue. Is there any temp work around to import the bacpac locally?
    Thursday, April 2, 2015 7:02 AM
  • Is this definitely a SQL Server 2012 instance? The deployment error " 'concat' is not a recognized built-in function name" seems to indicate concat isn't available. It was added in 2012 so would fail against earlier server versions.
    Thursday, April 2, 2015 6:13 PM
    Moderator
  • Yes its SQL Server 2012 instance but on my machine SQL Server 2008 R2 is also installed.  I am trying to import bacpac file from SQL Server 2012 instance by using  "Import Date Tier Application" option. This option is not available in SQL Server 2008.
    Friday, April 3, 2015 7:06 AM
  • Any update??

    Wednesday, April 15, 2015 12:04 PM