Error when trying to register database from script file...

Trikeb

Hi,

I'm trying to update a production database from a script generated using a custom VB.NET/SQL Toolkit API application. One method generates the database creation scripts and works just fine.

dataOptions.MappingOptions = dataOptions.MappingOptions Or MappingOptions.MissingFrom2AsInclude

'SetSchemaExecutionBlock
Dim database As Database = New RedGate.SQLCompare.Engine.Database

database.Register(New RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName, "sa", "sa"), schemaOptions)
Dim differences As Differences = database.CompareWith(Nothing, schemaOptions)

Dim difference As RedGate.SQLCompare.Engine.Difference
For Each difference In differences
difference.Selected = True
Next

Dim work As Work = New RedGate.SQLCompare.Engine.Work

work.BuildFromDifferences(differences, schemaOptions, True)
m_SchemaBlock = work.ExecutionBlock

Dim blocksaver As New BlockSaver
blocksaver.FileName = "c:\createdatabase.sql"
blocksaver.EncodingType = EncodingType.UTF8
blocksaver.ExecutionBlock = m_SchemaBlock

blocksaver.SaveToFile()


In the second method used to compare the production database that needs to be updated with the previously generated script, I receive the following error when try to register from the script file.
Error message = "DBInfo is not set and the specified scripts folder does not contain the necessary information either" "Parameter name = dbinfo"

What am I missing to make it work???


dataOptions.MappingOptions = dataOptions.MappingOptions Or MappingOptions.MissingFrom2AsInclude

'source = previously generated script
Dim source As Database = New RedGate.SQLCompare.Engine.Database
'database = production database that needs to be updated
Dim database As Database = New RedGate.SQLCompare.Engine.Database

source.Register("c:\createdatabase.sql", Nothing, Options.Default)
database.Register(New RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName, "sa", "sa"), schemaOptions)

Dim differences As Differences = source.CompareWith(database, schemaOptions)

Dim difference As RedGate.SQLCompare.Engine.Difference
For Each difference In differences
difference.Selected = True
Next

Dim work As Work = New RedGate.SQLCompare.Engine.Work

work.BuildFromDifferences(differences, schemaOptions, True)
m_SchemaBlock = work.ExecutionBlock

Dim blocksaver As New BlockSaver
blocksaver.FileName = "c:\updateproduction.sql"
blocksaver.EncodingType = EncodingType.UTF8
blocksaver.ExecutionBlock = m_SchemaBlock

blocksaver.SaveToFile()

Thanks in advance...

Michelle T

If the script folder has been created by SQL Compare, it ought to have a file in it called SqlCompareDatabaseInfo.xml. This file contains information about the default collation and SQL Server version of the database.

If you don't have a SqlCompareDatabaseInfo.xml file in your script folder, you can pass a ReadFromScriptDatabaseInformation object to Register instead.

http://help.red-gate.com/help/SQLCompar ... mbers.html

You need to create this object, set its fields to appropriate values, and then pass it into source.Register instead of 'Nothing' as the second argument.

Trikeb

I've added the following:

Dim dbinfo As New RedGate.SQLCompare.Engine.ReadFromFolder.ReadFromScriptDatabaseInformation

source.Register("c:\createdatabase.sql", dbinfo, Options.Default)

I get the following error:
Source: mscorlib
Message: The directory name is invalid.

Any ideas?

Brian Donahue

I think that the script folder needs to be a folder rather than a file. The register method will read all .sql files in the specified folder.

Trikeb

Hi Brian,

Here's something very strange! (By the way, I'm running Windows Vista Enterprise with full administrative priviledges + User Account Control set to OFF)

As you suggested, I changed the value of the Path parameter from:
source.Register("c:\createdatabase.sql", dbinfo, Options.Default)
to:
source.Register("c:\", dbinfo, Options.Default)

The result is both strange and variable:

Error: mscorlib -> Access to path "C:\Windows\Temp" is denied
I have specified the script folder to be "C:\" and it attemps to access "C:\Windows\Temp" ???

If I launch my custom installer by explicitly specifying to "Run as administor" I get the following:
Error: mscorlib -> Access to path "C:\Windows\System32\LogFiles\WMI\RtBackup" is denied

How come the register command override tries to lookup a different directory than the one I specified and why would the behavior be different depending on if the application is being executed with explicit administrative rights elevation?

Is this a known problem for Windows Vista?
Would my code work if I was running Windows XP Pro SP2?

Brian Donahue

It's not -- internally the Red Gate libraries create structured storage files representing schema in the %TMP% folder.

Michelle T

The reason it appears to be registering the wrong folder is because it looks in the directory you specify *and* all of its subdirectories - then it fails on one of the subdirectories.

As you only have one file, you should create a new directory, put the file in it, and direct the toolkit to register that folder.

Trikeb

What would you suggest the problem is then as far as the register command not looking up the right folder?

Michelle T

*points at post just above, which was probably posted at the same time you were writing your last reply*

Trikeb

Hi Michelle,

You're right... I saved the script file into another folder without sub-folders and it works.

Thanks,

Jonathan