"Invalid Column Name 'MISSING'"- Error while executing block

Hello,

We are facing a very weird error while updating a database getting records inserted into the target database. The error we get is " Invalid Column Name 'MISSING'". And the data copy into the target database table fails.

We are using SQL DataCompare 5.2 within our .net environment.
This message is not consistent, so we are not able to trace the error.

We are reading the entire block of the SQL script into various batches so that if a batch (data copy into a table) fails then we continue with the next batch in the block.
Also, we are getting this error while inserting a table having large binary data column which has datatype as ntext

The script with error we are getting is

[b]EXEC(N'DECLARE @pv binary(16)
'+N'SELECT @pv=TEXTPTR([ReportXml]) FROM [dbo].[cfgReport] WHERE [ReportName]=N''Collateral List by Status/Type'' AND [ReportClassName]=N''rptRetailCollateral'' AND [UserID]=Missing AND [ReportDescription]=Missing AND [SchemaName]=Missing AND [PackageType]=Missing AND [ReportModule]=Missing AND [ReportType]=Missing AND [ReportTypeSub1]=Missing AND [DefaultQuery]=Missing AND [DefaultQueryLink]=Missing AND [DefaultQueryColumn]=Missing AND [DefaultQueryOperator]=Missing AND [QueryRequired]=Missing AND [OrderBy]=Missing AND [MasterSuretyID]=Missing AND [DeleteFlag]=Missing AND (ReportType=''Standard'' or ReportType=''Backup'')[/b]


I do have the error screen with me but i don't have any means to attach with this thread else would have done that.

Please suggest.

Thanks,
-Awaneesh

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Awaneesh,

    If this is happening inside some Toolkit .NET code and not in the commercial (UI) release of the program, we may need to see the code and databases to determine the cause.

    It looks like, somehow, the data in the ResultStore is being cast as System.Reflection.Missing.Value instead of SQL Null. That's just a guess on my part, though.
  • Hello Brian,

    Thanks for your reply.

    We got this error while using the Red-Gate toolkit bundle as well as the SQL Datacomapre 5.2. commercial release both. Intially we got the error in the .net code then we tried with the commercial relase & the got the same error. I have the screen shot for the error but i don't have any means to attach the image for the error over here.

    As i said that this error occurs only during the update of a DB having a table with 'ntext' datatype. And that to be the fauilre occurs only for insert script not for the update script.

    Intially when there is no data into the table then the insert works fine but if we have some data into it & try to synchronize for adding the new data & updating the old data in the target database then this error flashese.

    Please let me know some email id so that i can send you the error screen for your reference.

    Also the .Net code snippet & the table script in which we are getting error as below:


    Code Snippet
    1. First we are excluding the entire table mapping 
    
    tablemappings.CreateMappings(dbCompareSource.Tables, dbCompareDestination.Tables)
    
                    For counter = 0 To tablemappings.Count - 1
    
                        tablemappings(counter).Include = False
    
                    Next
    
     
    
    2. Calling a function with the where condition and comparison columns and include that table in the table mapping
    
     
    
    The function has the follwing code
    
     
    
    For Each mapping In mappings
    
                    If (mapping.Obj1.Name = tablename) Then
    
                        If (ComparisionColumns <> "") Then
    
                            mapping.MatchingMappings.Clear()
    
                            strarr = ComparisionColumns.Split(",")
    
                            For Each columnnames As String In strarr
    
                                mapping.MatchingMappings.Add(mapping.FieldMappings(columnnames))
    
                            Next
    
                            mapping.RefreshMappingStatus()
    
                            If CheckTargetRecord(tablename, Batchid) > 0 Then
    
                                If Not (mapping.IndexMappings.PrimaryKey Is Nothing) Then
    
                                    For counter = 0 To mapping.IndexMappings.PrimaryKey.Obj1.Fields.Count - 1
    
                                        mapping.FieldMappings(mapping.IndexMappings.PrimaryKey.Obj1.Fields(counter).Name).Include = False
    
                                    Next
    
                                End If
    
     
    
                                For counter = 0 To mapping.Obj1.Fields.Count - 1
    
                                    If (mapping.Obj1.Fields(counter).Identity = True) Then
    
                                        mapping.FieldMappings(mapping.Obj1.Fields(counter).Name).Include = False
    
                                    End If
    
                                Next
    
                            End If
    
                            If Trim(condition) <> "" Then
    
                                mapping.Where = New WhereClause(condition)
    
                                If blDataAppend = False Then
    
                                    DeleteRecords(tablename, condition, Batchid)
    
                                End If
    
                            End If
    
                            mapping.Include = True
    
                            End If
    
                            Exit For
    
                End If
    
     
    
    Next
    
     
    
     
    
     
    
    3. Then finally we are comparing the two databases with the new mappings and generating the script. The script generated has the MISSING value.
    
     
    
    DataSession.CompareDatabases(dbCompareSource, dbCompareDestination, tablemappings, SessionSettings.IncludeRecordsInOne + SessionSettings.IncludeDifferentRecords)
    
    block = DataProvider.GetMigrationSQL(DataSession, True)
    


    Script of the Table
    CREATE TABLE [dbo].[cfgReport](
    
          [ReportID] [int] IDENTITY(1,1) NOT NULL,
    
          [UserID] [int] NULL,
    
          [ReportName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportClassName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportDescription] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [SchemaName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_cfgReport_DefaultQueryColumn]  DEFAULT (1),
    
          [PackageType] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportModule] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportTypeSub1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [ReportXml] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [DefaultQuery] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [DefaultQueryLink] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_cfgReport_DefaultQueryLink]  DEFAULT (N'all'),
    
          [DefaultQueryColumn] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [DefaultQueryOperator] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_cfgReport_DefaultQueryOperator]  DEFAULT (N'Equal'),
    
          [QueryRequired] [bit] NOT NULL CONSTRAINT [DF_cfgReport_QueryRequired]  DEFAULT (1),
    
          [OrderBy] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    
          [MasterSuretyID] [int] NULL CONSTRAINT [DF_cfgReport_MasterSuretyID]  DEFAULT (0),
    
          [DeleteFlag] [bit] NULL CONSTRAINT [DF_cfgReport_DeleteFlag]  DEFAULT (0),
    
     CONSTRAINT [PK_cfgReport] PRIMARY KEY CLUSTERED 
    
    (
    
          [ReportID] ASC
    
    ) ON [PRIMARY]
    
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    






    Thanks,
    -Awaneesh
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    Thanks a lot for the scripts. I believe that we understand what makes this happen now, and I think we will need to patch to software to prevent it. On the surface, it looks like an insert containing a row with an ntext field that is set to an empty string has this effect.

    Thanks a lot for pointing this out to us. We shall endevor to fix this in a future release.
Sign In or Register to comment.