Powershell Commandlets crash on Invoke-DatabaseBuild

When running Invoke-DatabaseBuild, I'm getting the below error. This machine is fresh installed using SQL Toolbelt which contained SQL Change Automation Powershell 3.3. Please advise how I should proceed - it looks like from forum activity SQL Source Control was having a similar error back in April and it required a patch to that product. Wondering if it's a similar issue.

Console output as follows:

An error
occurred while attempting to verify your deployment scripts: An unexpected error was encountered while querying the
database system tables (for Comment). You may have insufficient permissions, or the database may be in an inconsistent
state.  You are recommended to use SQL Compare with dbo permissions.  You can check the database is in a consistent
state by running DBCC CHECKDB.
WARNING:  Original error message: Cannot load the Backup Reader dll (see inner exception)!
WARNING:  Query being executed: SQL query:
WARNING:  SELECT sm.object_id AS id,
WARNING:  definition AS text,
WARNING:  1 AS colid,
WARNING:  (CASE WHEN OBJECTPROPERTY(so.object_id, N'IsReplProc')<>0 OR so.type = 'P' THEN 1 ELSE 0 END) AS number,
WARNING:  CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit)  AS Encrypted, --
WARNING:  CONVERT(bit, CASE WHEN so.type = 'D' AND so.parent_object_id != 0 THEN 1 ELSE 0 END) AS IsDefault,
WARNING:  ISNULL(sm.uses_quoted_identifier, 0) AS QuotedIdentifier,
WARNING:  NULL AS ctext
WARNING:  FROM sys.sql_modules sm WITH (NOLOCK)
WARNING:  LEFT JOIN sys.objects so WITH (NOLOCK) ON so.object_id = sm.object_id
WARNING:  WHERE (so.type in ('P', 'V', 'TR', 'TA', 'IF', 'FN', 'TF', 'FS', 'FT')
WARNING:  OR (so.type = 'D' AND so.parent_object_id != 0)
WARNING:  OR sm.object_id IN (SELECT object_id FROM sys.triggers WITH (NOLOCK) WHERE parent_class=0))
WARNING:  UNION ALL
WARNING:  SELECT d.object_id AS id,
WARNING:  d.definition AS text,
WARNING:  parent_column_id AS colid,
WARNING:  0 AS number,
WARNING:  CAST(0 AS BIT) AS Encrypted, -- Not encryptable https://msdn.microsoft.com/en-us/library/ms173758.aspx
WARNING:  CAST(1 AS BIT) AS IsDefault,
WARNING:  CAST(0 AS BIT) AS QuotedIdentifier,
WARNING:  NULL AS ctext
WARNING:  FROM sys.default_constraints d WITH (NOLOCK)
WARNING:  UNION ALL
WARNING:  SELECT
WARNING:  p.object_id AS id,
WARNING:  p.definition AS text,
WARNING:  CAST(1 as int) AS colid,
WARNING:  procedure_number AS number,
WARNING:  CAST(CASE WHEN p.definition IS NULL THEN 1 ELSE 0 END AS bit) AS Encrypted, --

WARNING:  CAST(0 as bit) AS IsDefault,
An error has occurred that was not properly handled. Additional information is shown below. The Windows PowerShell process will exit.
WARNING:  m.uses_quoted_identifier AS QuotedIdentifier,
WARNING:  NULL AS ctext
WARNING:  FROM sys.numbered_procedures p WITH (NOLOCK)
WARNING:  LEFT JOIN sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id
WARNING:  ORDER BY id, colid, number

Unhandled Exception: System.IO.FileLoadException: Could not load file or assembly 'System.Data.SQLite, Version=1.0.110.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
   at RedGate.BackupReader.MTFReader.SysTableStoreBase.Close()
   at RedGate.BackupReader.MTFReader.SysTableStoreBase.Finalize()

Thanks,
Scott
Tagged:

Answers

  • Hi Scott,

    Can you confirm the exact version number of the installed SCA powershell?

    It looks like you have encrypted objects. Do you have sysadm permissions?

    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • scottw_cmscottw_cm Posts: 4 New member
    Hi Tianjiao,

    The full version is 3.3.19206.9626 for SqlChangeAutomation.dll - let me know if you were looking for something different.

    This is a local install of SQL Server, and the user account has the sysadmin role in SQL.

    Regards,
    Scott
  • Hi Scott,

    As we discussed via email, this is caused by different versions of the dll being loaded in the PowerShell from the version needed.

    As a temp workaround, please copy the System.Data.SQLite.dll from the SC.exe install directory (C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\Modules\SqlChangeAutomation\SC) to the main SCA install directory (C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\Modules\SqlChangeAutomation) 

    We've logged this issue as SCA-2632 and please check release note for update regarding this bug.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Just want to give a quick update that the fix has been released in 4.0.19259. Please upgrade the let us know if there is any issue.

    Thanks!
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.