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

TrikebTrikeb Posts: 13
edited August 30, 2007 11:03AM in SQL Toolkit Previous Versions
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...
Jonathan Comeau
Trikeb

Comments

  • 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.
    Software Developer
    Redgate Software
  • 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?
    Jonathan Comeau
    Trikeb
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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?
    Jonathan Comeau
    Trikeb
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    It's not -- internally the Red Gate libraries create structured storage files representing schema in the %TMP% folder.
  • Michelle TMichelle T Posts: 566 Gold 1
    edited August 30, 2007 9:54AM
    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.
    Software Developer
    Redgate Software
  • What would you suggest the problem is then as far as the register command not looking up the right folder?
    Jonathan Comeau
    Trikeb
  • *points at post just above, which was probably posted at the same time you were writing your last reply*
    Software Developer
    Redgate Software
  • Hi Michelle,

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

    Thanks,

    Jonathan
    Jonathan Comeau
    Trikeb
Sign In or Register to comment.