SQLException on CompareDatabase

mpb747

I get the following exception on the session.CompareDatabases sub:

System.Data.SqlClient.SqlException was unhandled
Message="The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=15
LineNumber=1
Number=1033
Procedure=""
Server="bsserver1"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at _83._1()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Here is my Code:

Private Sub PushData()
'create an intersection of the tables
Dim mappings As New TableMappings

mappings.CreateMappings(db2.Tables, db1.Tables)

Dim i As Int16
Dim sX As String = ""
For i = 0 To mappings.Count - 1
If Not mappings.Item(i).Obj2 Is Nothing Then
sX = UCase(mappings.Item(i).Obj2.Name.ToString)
If htTables.ContainsKey(sX) Then
sX = htTables.Item(sX).ToString
If sX.Length > 1 Then
mappings.Item(i).Include = True
Select Case Mid$(sX, 1, 1)
Case "1"
mappings.Item(i).Where.Clause1 = sWHdatLast
mappings.Item(i).Where.Clause2 = sWHdat120
Case "2"
mappings.Item(i).Where.Clause1 = sWHdtLast
mappings.Item(i).Where.Clause2 = sWHdt120
Case "3"
mappings.Item(i).Where.Clause1 = sWHdatLast
mappings.Item(i).Where.Clause2 = sWHChart
Case "4"
mappings.Item(i).Where.Clause1 = sWHdtLast
mappings.Item(i).Where.Clause2 = sWHChart
Case "6"
mappings.Item(i).Where.Clause1 = sWHdatLast
mappings.Item(i).Where.Clause2 = sWHNote
Case "7"
mappings.Item(i).Where.Clause1 = sWHdatLast
mappings.Item(i).Where.Clause2 = sWHAssess
End Select
Else : mappings.Item(i).Include = False
End If
Else : mappings.Item(i).Include = False
End If
End If
Next

'compare the databases
Dim session As New ComparisonSession
Try
session.CompareDatabases(db2, db1, mappings, SessionSettings.IncludeDifferentRecords Or SessionSettings.IncludeIdenticalRecords Or SessionSettings.IncludeRecordsInOne)

Catch ex As Exception
MsgBox(ex.Message)
End Try

'now get the ExecutionBlock containing the SQL

Dim provider As New SqlProvider
Dim block As ExecutionBlock
Dim x As Long
Try

block = provider.GetMigrationSQL(session, True)

x = block.BatchCount
sReport = CStr(x) + " Push Batches to execute" + vbCrLf

Dim executor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor

executor.ExecuteBlock(block, sSVNetwork, sDB)
Catch ex As Exception
sReport += "PUSH ERROR - " + ex.Message + vbCrLf
numErrs += 1
Finally
block = provider.Block
If (TypeOf block Is ExecutionBlock) Then
block.Dispose() 'dispose of the objects to delete temporary files
End If
End Try

'dispose of the objects to delete temporary files
session.Dispose()
End Sub

This only started after I added the two newest Where clauses (Case 6 and 7).

Any help would be greatly appreciated.

Thanks,

Matt Barnes
Barnestorm, Inc.

mpb747

That was strange - I removed the parantheses from my where clause and it works now. Thanks,
Matt Barnes
Barnestorm, Inc.

richardjm

Glad it works now.

By the way, how are you finding the new API? We spent a lot of time restructuring it because of the tight integration with SQL Compare and the introduction of mappings. As these two changes were going to affect the API extensively we took the opportunity to do a little house keeping.

mpb747

So far I've been very impressed. We wrote our data sync software using version 3.x of datacompare and compare last time, and I've just completed the rewrite using version 5. All of our customers (Home Health care nurses mainly) store their data on tablets with an instance of MSDE, then come back to the office to sync their data up with the server, and they tend to want a very quick process so they can send over their assessments, as well as get the new patients. The old version we had would take around 3 minutes on a good day, and up to 15 minutes on a bad day, depending on network activity and amount of data. With the new version, the process is down to less than a minute. I think the main reason is the ability to specify where clauses on the table mappings to limit the amount of data compared. Thanks for the great upgrade!

-Matt Barnes
Barnestorm, Inc.