mysticmind
commented
3 months ago It would be better to have our own version of sp_MSdropconstraints. I have made few edits/tweaks to the stored procedure code and have outlined it as below:
create procedure weasel_dropconstraints
@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)
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) I will discuss with @jeremydmiller and get this going.
Hi!
We are using Wolverine with SQL Server Message Persistence with an Azure SQL database.
When Wolverine creates the required tables, we are receiving a
SQLExceptionbecausesp_MSdropconstraintsis called when dropping and creating the missing tables and this stored procedure is not available in Azure SQL.I figured out that this comes from Weasel, where I found the following line:
https://github.com/JasperFx/weasel/blob/d5c3ad1e254b55882ca0ebf62b79a221febe30db/src/Weasel.SqlServer/Tables/Table.cs#L67
I couldn't find much documentation for
sp_MSdropconstraints, except this source code. It seems like this stored procedure is part of the Merge Replication Feature which is only available in SQL Server (but not in Azure SQL).Would it be possible to replace the
sp_MSdropconstraintswith native SQL code for dropping the foreign key constraints? Thesp_MSdropconstraintsbasically does exactly that according to the linked source code.I would also be happy to provide a PR with that change, but I would need some help regarding the release and bringing the version to Wolverine.