msdeploy.exe : Error: .Net SqlClient Data Provider: Invalid object name 'Blobs'

Question

I'm trying to install Sitecore Commerce v9.2. I had a few issues that I solved earlier. However, right now I'm getting a database error.

When I check the database I can see two new Commerce databases installed (SitecoreCommerce9_Global and SitecoreCommerce9_SharedEnvironements). I'm using SQL Server 2017 which has all the Sitecore XP 9.2 databases.

msdeploy.exe : Error: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 2 Invalid object name 'Blobs'.
At C:\Program Files\WindowsPowerShell\Modules\SitecoreInstallFramework\2.1.0\Public\Tasks\Invoke-CommandTask.ps1:31 char:13
+             & $Path $Arguments | Out-Default
+             ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (Error: .Net Sql...t name 'Blobs'.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

Error: Script execution error.  The executed script:
BEGIN TRANSACTION;
CREATE TABLE #Blobs (
    BlobId  UNIQUEIDENTIFIER NOT NULL,
    [Index] INT              NOT NULL,
    Data    IMAGE            NOT NULL,
    Created DATETIME         NOT NULL
);
CREATE TABLE #Items (
    ID         UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    Name       NVARCHAR (256)   COLLATE database_default NOT NULL,
    TemplateID UNIQUEIDENTIFIER NOT NULL,
    MasterID   UNIQUEIDENTIFIER NOT NULL,
    ParentID   UNIQUEIDENTIFIER NOT NULL,
    Created    DATETIME         NOT NULL,
    Updated    DATETIME         NOT NULL
);
CREATE TABLE #SharedFields (
    ItemId  UNIQUEIDENTIFIER NOT NULL,
    FieldId UNIQUEIDENTIFIER NOT NULL,
    Value   NVARCHAR (MAX)   COLLATE database_default NOT NULL,
    Created DATETIME         NOT NULL,
    Updated DATETIME         NOT NULL,
    PRIMARY KEY CLUSTERED ([ItemID], [FieldID])
);
CREATE TABLE #VersionedFields (
    ItemId   UNIQUEIDENTIFIER NOT NULL,
    FieldId  UNIQUEIDENTIFIER NOT NULL,
    Language NVARCHAR (
Info: An error occurred while the batch was being executed.
Info: Updating database: Faulted.
Info: Updating database (Failed)
Info: Deploying package to database: Faulted.
Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 2 Invalid object name 'Blobs'.
Error SQL72045: Script execution error.  The executed script:
BEGIN TRANSACTION;
CREATE TABLE #B
lobs (
    BlobId  UNIQUEIDENTIFIER NOT NULL,
    [Index] INT              NOT NULL,
    Data    IMAGE            NOT NULL,
    Created DATETIME         NOT NULL
);
CREATE TABLE #Items (
    ID         UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    Name       NVARCHAR (256)   COLLATE database_default NOT NULL,
    TemplateID UNIQUEIDENTIFIER NOT NULL,
    MasterID   UNIQUEIDENTIFIER NOT NULL,
    ParentID   UNIQUEIDENTIFIER NOT NULL,
    Created    DATETIME         NOT NULL,
    Updated    DATETIME         NOT NULL
);
CREATE TABLE #SharedFields (
    ItemId  UNIQUEIDENTIFIER NOT NULL,
    FieldId UNIQUEIDENTIFIER NOT NULL,
    Value   NVARCHAR (MAX)   COLLATE database_default NOT NULL,
    Created DATETIME         NOT NULL,
    Updated DATETIME         NOT NULL,
    PRIMARY KEY CLUSTERED ([ItemID], [FieldID])
);
CREATE TABLE #VersionedFields (
    ItemId   UNIQUEIDENTIFIER NOT NULL,
    FieldId  UNIQUEIDENTIFIER NOT NULL,
    Language NVARCHAR (
  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_METHOD.
Error: Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 2 Invalid object name 'Blobs'.
Error SQL72045: Script execution error.  The executed script:
BEGIN TRANSACTION;
CREATE TABLE #Blobs (

 BlobId  UNIQUEIDENTIFIER NOT NULL,
    [Index] INT              NOT NULL,
    Data    IMAGE            NOT NULL,
    Created DATETIME         NOT NULL
);
CREATE TABLE #Items (
    ID         UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    Name       NVARCHAR (256)   COLLATE database_default NOT NULL,
    TemplateID UNIQUEIDENTIFIER NOT NULL,
    MasterID   UNIQUEIDENTIFIER NOT NULL,
    ParentID   UNIQUEIDENTIFIER NOT NULL,
    Created    DATETIME         NOT NULL,
    Updated    DATETIME         NOT NULL
);
CREATE TABLE #SharedFields (
    ItemId  UNIQUEIDENTIFIER NOT NULL,
    FieldId UNIQUEIDENTIFIER NOT NULL,
    Value   NVARCHAR (MAX)   COLLATE database_default NOT NULL,
    Created DATETIME         NOT NULL,
    Updated DATETIME         NOT NULL,
    PRIMARY KEY CLUSTERED ([ItemID], [FieldID])
);
CREATE TABLE #VersionedFields (
    ItemId   UNIQUEIDENTIFIER NOT NULL,
    FieldId  UNIQUEIDENTIFIER NOT NULL,
    Language NVARCHAR (
Error count: 1.
Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a non-zero exit code - (-1)
At C:\deploy\Deploy-Sitecore-Commerce.ps1:93 char:1
+ Install-SitecoreConfiguration @params -Verbose *>&1 | Tee-Object "$PS ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration

[TIME] 00:10:52
Invoke-CommandTask : Command C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a non-zero exit code - (-1)
At C:\Program Files\WindowsPowerShell\Modules\SitecoreInstallFramework\2.1.0\Public\Tasks\Invoke-WebDeployTask.ps1:36 char:2
+     Invoke-CommandTask -Path $Path -Arguments $msdeployArgs -TaskName ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-CommandTask

Answer 1

I was facing same issue and found that it tries to update the Blobs and other tables in XP instance i.e. YourSitecoreXP_Master DB

So if the Sitecore prefix in the deploy script is wrong it tries to create a new database and update the Blobs table which is not available and hence fails.

So please check $SiteNamePrefix in deploy script, should match to the XP prefix. e.g.- if your Sitecore XP site name is storefront920.local so the database will be created as storefront920_Master. So in this case $SiteNamePrefix will have to be storefront920

Hope this helps

Answer 2

It's likely you have other databases on the instance that have users and were created before attempting to change the containment level.

Run the following and then try again:

    Use master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Answer 3

Make sure you check the XC-Install.log file for more detailed information.

In my case, I made a mistake on the SitecoreDbServer parameter in Deploy-Sitecore-Commerce.ps1.