FieldMapping Not Valid For Matching Mappings..

lc6529lc6529 Posts: 30
edited April 20, 2007 3:16AM in SQL Toolkit Previous Versions
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



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
            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
                ' 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
                ' Setup our comparison session
                oRedGateSession.CompareDatabases(dSourceDatabase, dTargetDatabase, oTBLMap)
                Dim oBlock As ExecutionBlock = oProvider.GetMigrationSQL(oRedGateSession, True)
            End While
        Catch ex As Exception
        End Try

    End Sub


  • 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...

    Hope this helps

    PS I've also seen you noticed the fully qualified accessor from your other query :)
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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))
  • >
    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.
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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.
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.