sp_MSdropconstraints is not available on Azure SQL Database

[aurelienhayet]

Hi,

When configuring Wolverine with the UseSqlServerPersistenceAndTransport and AutoProvision options, and then connecting to an Azure SQL Database, EXEC sp_MSdropconstraints commands are failing because of the unavailability of the sp_MSdropconstraints stored procedure.

Perhaps you can find alternative commands, or allow to opt-out the sp_MSdropconstraints commands ?

Aurélien

PS : Great great work by the way, congrats ;) !

[jeremydmiller]

Meh, that's in Weasel, not Wolverine. For the moment, you'll have to turn off AutoProvision and export the SQL through the command line. dotnet run -- db-dump [file path] and apply it manually. Might have to tweak the scripts to remove that call

[aurelienhayet]

Thanks Jeremy for your advice, that's what I did in order to unlock myself. I apologize for the wrong location of the issue. I hesitated but as AutoProvision() was part of the wolverine project and was the root cause of this issue, I opted for it. But you're right, it would be more relevant in Weasel.

[Rookian]

Same happens with IMessageStore.Admin.MigrateAsync.

[Rookian]

What am I doing wrong when I run 'dotnet run -- db-dump [path] -i`, when only an empty sql file is generated?

Code:

var builder = WebApplication.CreateBuilder(args);
builder.Host.ApplyOaktonExtensions();
builder.Host.UseWolverine((context, opts) => 
{
    var connectionString = context.Configuration.GetConnectionString("sqlserver")!;
    opts.UseSqlServerPersistenceAndTransport(connectionString, schema: "wolverine",
        transportSchema: "ppstransport");
    opts.AutoBuildMessageStorageOnStartup = false;
});
var app = builder.Build();
return await app.RunOaktonCommands(args);

[jnkb]

For those who have the same problem: As a workaround, I created the stored procedure sp_MSdropconstraints via SQL Server Management Studio with the following code. After that, the app service starts without errors and the Wolverine tables are created.

(taken from https://pawjershauge.blogspot.com/2012/05/spmsdropconstraints-transact-sql.html)

 --  This will be called merge at the subscriber side, check for dbo permission  
create procedure sp_MSdropconstraints  
    @table sysname,  
    @owner sysname = null  
as  
    declare @const_name nvarchar(258)  
    declare @objid int  
    declare @retcode int  
    declare @qualified_tablename   nvarchar(517)  
    declare @quoted_tablename   nvarchar(270)  
    declare @quoted_ownername   nvarchar(270)  

    --   
    --  Check for subscribing permission  
     --   
    exec @retcode=sys.sp_MSreplcheck_subscribe  
    if @retcode<>0 or @@ERROR<>0 return (1)  

    select @quoted_tablename = quotename(@table)  

    if @owner is not NULL  
    begin  
        set @quoted_ownername = QUOTENAME(@owner)  
        set @qualified_tablename= @quoted_ownername + '.' + @quoted_tablename  
    end  
    else  
        set @qualified_tablename= @quoted_tablename  

    set @objid = object_id(@qualified_tablename)  
    if @objid is null  
    begin  
        if @owner is null  
        begin  
            select @objid = object_id from sys.objects   
                where name=@quoted_tablename  
        end  
        else  
        begin  
            select @objid = object_id from sys.objects   
                where name=@quoted_tablename and schema_name(schema_id)=@quoted_ownername  
        end  
    end  
    if @objid is NULL  
        return (1)  

    select @const_name = QUOTENAME(object_name(object_id)) from   
        sys.foreign_keys where parent_object_id = @objid  

    while @const_name is not null  
    begin  
        exec ('alter table ' + @qualified_tablename +  
            ' drop constraint ' + @const_name)  
        if @@ERROR <> 0  
            return (1)  
        set @const_name = NULL  
        select @const_name = QUOTENAME(object_name(object_id)) from   
            sys.foreign_keys where parent_object_id = @objid  
    end  

    return (0)