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

awaneesh.pandey

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

Brian Donahue

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.

awaneesh.pandey

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 Donahue

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.