FieldMapping Not Valid For Matching Mappings..

lc6529

I am about to go nuts here because I cannot figure out why this statement should not work!

I get "FieldMapping Not Valid For Matching Mapping" on the line

oTBLMapping.MatchingMappings.Add(oTBLMapping.FieldMappings(aFields(I).ToString.Trim.ToUpper))

___________________

Sub SyncDatabases(ByVal sServerName As String)
        '
        ' Redgate Declarations
        '
        Dim dSourceDatabase As New RedGate.SQLCompare.Engine.Database
        Dim dTargetDatabase As New RedGate.SQLCompare.Engine.Database
        Dim oTBLMap As New RedGate.SQLDataCompare.Engine.TableMappings
        Dim oTBLMapping As RedGate.SQLDataCompare.Engine.TableMapping
        Dim oRedGateSession As New RedGate.SQLDataCompare.Engine.ComparisonSession
        '
        ' VB.Net SQL Declarations
        '
        Dim oProvider As New SqlProvider
        Dim myConnString As String = "User Id=lvadmin;Password=lvadmin;Initial Catalog=Labelsync;Server=" & sServerName
        Dim mySelectQuery As String = "SELECT SourceDB,TargetDB,TBLToCompare,Fields,ComparisonType FROM Tables_To_Compare Where (Active=1) Order By ComparisonOrder"
        Dim myConnection As New System.Data.SqlClient.SqlConnection(myConnString)
        Dim myCommand As New System.Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
        Dim myReader As System.Data.SqlClient.SqlDataReader
        '
        ' Misc Declarations
        '
        Dim I As Integer
        Dim aFields As String()
        '
        ' Connection and statements To Setup DBReader
        '
        ' We will read a table that has the following fields
        '
        ' SourceDB - The 'master' database
        ' TargetDB - The 'Target' database
        ' tblToCompare - The Database Table To Compare
        ' Fields - Fields To Map, Comma Delimited  IE:  Top Level Assy Number, Plant Code, MFG_ID1
        ' ComparisonType - VT,TT
        '
        Try
            myConnection.Open()
            myReader = myCommand.ExecuteReader()
            While myReader.Read
                '
                ' All Databases will use lvadmin for userid and password
                '
                dSourceDatabase.RegisterForDataCompare(New ConnectionProperties(sServerName, myReader("SourceDB").ToString, "lvadmin", "lvadmin"))
                dTargetDatabase.RegisterForDataCompare(New ConnectionProperties(sServerName, myReader("TargetDB").ToString, "lvadmin", "lvadmin"))
                '
                ' Setup Our Mappings
                '
                ' VT Means Compare a VIEW to a TABLE
                ' TT Means Compare a TABLE to a TABLE
                '
                Select Case myReader("ComparisonType").ToString.Trim.ToUpper
                    Case "VT" : oTBLMapping = oTBLMap.Join(dSourceDatabase.Views("[dbo].[" + myReader("tblToCompare").ToString.Trim + "]"), dTargetDatabase.Tables("[dbo].[" + myReader("tblToCompare").ToString.Trim + "]"))
                    Case "TT" : oTBLMapping = oTBLMap.Join(dSourceDatabase.Tables("[dbo].[" + myReader("tblToCompare").ToString.Trim + "]"), dTargetDatabase.Tables("[dbo].[" + myReader("tblToCompare").ToString.Trim + "]"))
                End Select
                '
                ' Retrieve Our Field Names
                ' 
                aFields = Split(myReader("Fields").ToString, ",")
                '
                ' Clear Out Old Comparisons
                '
                oTBLMapping.MatchingMappings.Clear()
                '
                ' Setup Our Comparison Fields Based On The Field Names Now Present In The Array
                '
                For I = LBound(aFields) To UBound(aFields)
                    oTBLMapping.MatchingMappings.Add(oTBLMapping.FieldMappings(aFields(I).ToString.Trim.ToUpper))                Next
                '
                ' Refresh Our Mappings
                '
                oTBLMapping.RefreshMappingStatus()
                '
                ' Setup our comparison session
                ' 
                oRedGateSession.CompareDatabases(dSourceDatabase, dTargetDatabase, oTBLMap)
                '
                Dim oBlock As ExecutionBlock = oProvider.GetMigrationSQL(oRedGateSession, True)
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            myReader.Close()
            myConnection.Close()
        End Try

    End Sub

richardjm

OK just had a look and this error will occur in a few circumstances.

1) The fieldmapping you're using isn't in both databases (i.e. not Success)
2) The type of the field is one of the following...
image
ntext
nvarchar(max)
text
varbinary(max)
varchar(max)
variant
xml

Hope this helps

PS I've also seen you noticed the fully qualified accessor from your other query

lc6529

Ok Richard, I did indeed find that I had a column name missing. I should have caught that but that's what I get for trying to quit the Mt Dew Code Red habit.

However, I now get a NEW error:

Mappings Only Supports Mapping Objects
Parameter Name:Value

This occurs on the same line and occurs after (hopefully) setting the mapping up for the first column. I verified that the column name I am trying to map is in both tables and is of the same type (nvarchar(50))

lc6529

>
This occurs on the same line and occurs after (hopefully) setting the mapping up for the first column
>

What I mean to say is the code goes through the first column name (which is an nvarchar(12) ) WITHOUT an error and then goes to the SECOND column name which is where the error occurs.

richardjm

Sounds like the argument is null - i.e. it couldn't find the FieldMapping in the FieldMappings collection at all for the indexer you supplied to it.

lc6529

But that does not make sense. I have verified by hand that the field is indeed there. In fact, I even opened the table to modify and COPIED the field name and pasted it to INSURE the name was correct and I still get that error message.

I then opened SQL Data Compare and setup my project to insure it would work. The data transferred without an issue so the names of the tables match.

However when I try to do this programmatically I get the error.

One more interesting caveat. I found three columns that do not give an error so I ran the project and allowed it to map those three columns only.

The next error message is:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[MySecretServerName]" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[MySecretServerName]".


What I have done is setup an access database as a linked server and then created a SQL table with views in it that reference the linked server.
Your redgate Data Compare will use these views and thus allow me to sync an access table with a SQL table.

richardjm

The only way data compare is going to throw that error on the MatchingMappings.Add is if the argument isn't a FieldMapping or it's null. I've not tried the linked server trick with access and oledb so I've no idea if that could be causing the problems however as you have the project working in the UI it should work in the Toolkit also.