SQLCompare Errors

BJ

Let me start off by saying I inherited this current process, so I am at a total disadvantage. What I do know:
we are creating a new db, then using syncronize to make them the same before replicating to the new db.
Assembly Version = 1.0.1.18
using commandline utility, not the GUI
all sp's, views, fn's are accounted for in the log before it gets the null reference exception
my cmd string is (i have replaced names in the string)

utils\SQLCompare.exe /out:utils\SQLCompare.log /force /server1:Server1Name /database1DB1name /username1:DB1UserName /password1:DB1UserPW /server2:Server2Name /database2:Server2DBName /username2:Server2UserName /password2:Server2UserPW /synchronize /options:IncludeDependencies,IgnoreTriggers

We have recently upgraded to SQLServer 2005, and now the synchronize process is no longer working. We are getting a null reference exception. This does not happen in 2000, with the same .bak file.

Unexpected error: Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
System.NullReferenceException
at RedGate.SQLCompare.CommandLine.CommandProcessor.GetComparisonExecutionBlock()
at RedGate.SQLCompare.CommandLine.CommandProcessor.ᐄ()
at RedGate.SQLCompare.CommandLine.CommandProcessor.Run()
at RedGate.SQLCompare.CommandLine.Startup.ᐁ(String[] )

I tried to d/l the latest version for the 14 day trial, but when i run the same command, i get the error:

Error: Exception of type '_3' was thrown.

At this point I am more than willing to look into ANY suggestions or ideas. As I mentioned earlier, I inherited this, and am still trying to learn it, since it was working earlier, I didn't need to mess with it.

Thank you,

bj

Brian Donahue

Thanks for your post.

I'm curious to know what happens if you run SQL Compare (the user-interface version) to compare the two databases? Are any exceptions thrown by the UI program that are more meaningful?

BJ

Thank you for the quick reply. I followed your advise, and tried to synchronize through the UI. I recieved this error.

The following error message was returned from the SQL Server:

[8102] Cannot update identity column 'RecordHistoryID'.

The following SQL command caused the error:

We have many identity columns, why would this error just now be showing? this table and stored procedure hasn't been changed since Jan '06? Does SQLServer 2005 have more stringent checks than 2000 in this area? Or is this something I can correct through SQLCompare?

thank you,

bj

Brian Donahue

It may be helpful to know what update is being attempted on the identity column -- if it's a schema update, the column may need to be dropped and the table rebuilt and maybe this is not happening?

BJ

I have corrected that, it was a few deprecated sp's.

now, i have gotten to the point where the sync will work with the gui, but i am still on the same null reference error on the command line. any thoughts on the difference?

note: the ui is a much later version. I am guessing herein lies the problem. However, when i try to run the cmd line of the new version i get the aformentioned error(post 1)

bj

Brian Donahue

Hi BJ,

I have to say, our error message is completely useless. In order to find the problem, we'd either need 1. a copy of the databases in question to reproduce the problem, or 2. a crash dump of the application (would require that you know how to use a debugger).

BJ

Brian Donahue wrote:

Hi BJ,

I have to say, our error message is completely useless. In order to find the problem, we'd either need 1. a copy of the databases in question to reproduce the problem, or 2. a crash dump of the application (would require that you know how to use a debugger).

I can't send our db for several reasons, but I did narrow the problem down to a specific area. Functions. I created a new db with no data, 2 tables, and 1 function. I get the same error on this db. Here is the function I used.

CREATE   FUNCTION [dbo].[fnStrParser](@strIN VARCHAR(4000))


RETURNS  @UStrTable TABLE (strval varchar(100))
AS
BEGIN

DECLARE   @i INT , @pos INT, @len INT
SELECT @i = 1, @pos=1, @len = LEN(@strIN)

WHILE (@pos > 0)
BEGIN
	SELECT @pos = CHARINDEX(',', @strIN, @i) 
	IF @pos > 0
		INSERT @UStrTable VALUES (SUBSTRING(@strIN, @i, @pos-@i))	
	ELSE
		INSERT @UStrTable VALUES (SUBSTRING(@strIN, @i, @len))
	SET @i= @pos+1

END
RETURN  
END
GO

I can also send a script of the db if you prefer.

Brian Donahue

Can you please go into the folder where SQLCOMPARE.exe is and right-click on RedGate.SQLCompare.Engine.dll, go to properties, and get the version number? Anything less that version 4 of this library won't work with SQL Server 2005 in a lot of cases.

Thanks!

BJ

ok..i posted that information in my first post. If you have any ideas on the 14 day trial, I am more than happy to try them. I installed the trial on my computer, then tried to move the files to the server where the cmd line is actually run from.

BJ wrote:

Let me start off by saying I inherited this current process, so I am at a total disadvantage. What I do know:
we are creating a new db, then using syncronize to make them the same before replicating to the new db.
Assembly Version = 1.0.1.18
using commandline utility, not the GUI

BJ wrote:

I tried to d/l the latest version for the 14 day trial, but when i run the same command, i get the error:

Error: Exception of type '_3' was thrown.

Brian Donahue

Thanks, but the version of the dll is probably different than the executable.

BJ

Brian Donahue wrote:

Thanks, but the version of the dll is probably different than the executable.

sure was! sorry about that.
3.1.9.12

Brian Donahue

Thanks! I just noticed that you pointed out the UI version works, but command-line doesn't. So maybe it's not just because you use SQL Server 2005. Do you use the same options in the UI version as the command-prompt, and are you also synchronizing with the UI version successfully?

BJ

yes, the UI syncronizes correctly on sql 2k5. the same options are used.

good news though. while waiting for your reply, I a version 5 SQLBundle on the server, and everything worked great. I believe I am passed the problems I was having. If the testing over the next week goes correctly, we will be upgrading our old to the new.

thank you very much for your help. I am hoping it was just the old version, as it seems now!

bj