A inicialização da assinatura gera o erro "A opção "INLINE=ON" não é válida para esta função" que está incorreta

Consulte este artigo A única solução que funcionou para mim foi descartar e recriar a UDF após a atualização para o SQL 2019.

A coluna inline_Type de Sys.sql_modules será 1 após a atualização para o SQL 2019. Depois de descartar e recriar a UDF, Inline_type será 0 e a inicialização da replicação funcionará bem.

Encontrei esse problema ao migrar uma publicação que inclui algumas UDFs escalares do SQL 2016 para o SQL 2019. Alterar as configurações do banco de dados do editor não resolveu o problema. E como os assinantes incluem versões mais antigas do SQL Server, adicionar "WITH INLINE=OFF" não é uma opção na definição da função. Portanto, minha solução é forçar o udf a não ser inlineável adicionando o seguinte código na definição da função:

--Junk code to force is_inlineable to 0 
DECLARE @dummy DATETIME; SELECT @dummy = GETDATE()

Se você fizer uma seleção em sys.sql_modules, is_inlineable será exibido como 0 e os scripts de instantâneo serão gerados com êxito.

Como acompanhamento disso, a atualização para o SQL Server 2019 CU9 remove essa solução alternativa.
Qualquer função do formato: Criar função como tabela de retorno selecione 1 x

É criado com inline_type = 1 e, em seguida, a replicação gera o erro acima.

Tivemos que reverter CU9 devido a esse recurso.

Desculpas pelo atraso na postagem dessas descobertas, passei mais de um mês indo e voltando com o suporte da Microsoft sobre isso (em um ponto eles me disseram que a replicação não foi projetada para replicar funções e procedimentos armazenados e apenas não replicá-los como uma correção ?!!), eles finalmente admitiram que é realmente um bug, depois que eu enviei a eles um monte de evidências de experimentos do meu lado. O bug é, na verdade, com o processo de restauração/atualização do banco de dados no SQL Engine e não com a própria replicação transacional como apareceu pela primeira vez.

Causa/Investigação

A causa raiz disso é que quando um banco de dados é restaurado de uma versão anterior do SQL (2008R2 no meu caso) para uma nova instância do SQL 2019, por padrão, parece sinalizar as funções com inline_type = 1 em sys.sqlmodules (isso se manifesta como "Marcado para inlining" nas propriedades da função na GUI). Há um sinalizador nessa tabela chamado inlinable que foi um pouco complicado ao investigar inicialmente, pois parece que a replicação está usando inline_type , inlinable permanece verdadeiro mesmo após a solução alternativa e não impede a replicação.

Cheguei a esta conclusão através dos seguintes passos:

• Restaurar banco de dados A partir de 2008R2 e definir a compatibilidade para 150: mais de 100 funções foram restauradas com inline_type = 1 e inlinable = 1. Assim, quando a replicação é iniciada, ela eventualmente falha nessas funções com ...

16203 – A opção “INLINE=ON” não é válida para esta função.

Essa opção é aplicada automaticamente por replicação devido ao sinalizador inline_type.

OU

Uma opção inválida foi especificada para a instrução "CREATE/ALTER FUNCTION". Sintaxe incorreta perto da palavra-chave 'com'.

O motivo do 2º erro é porque está declarando automaticamente INLINE = ON no código replicado, fazendo com que a sintaxe seja inválida quando a função contém um CTE.

• Clone o banco de dados restaurado: mesmo resultado acima
• Defina o nível de compatibilidade do banco de dados para 140/130/120/110 (alterações pré-inlining em 2019): mesmo resultado acima.
• Restaurando e atualizando o banco de dados no assinante de replicação primeiro: Mesmo resultado acima.
• Definir manualmente INLINE = OFF: não corrige o problema e também é relatado como sintaxe inválida em algumas das funções.
• ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = Off: Mesmo resultado acima.
• Criando a assinatura como Pull em vez de Push: Mesmo resultado acima.
• Eliminando uma das funções e recriando: A função recriada é bem-sucedida e falha na próxima.
• Recriando o banco de dados inteiramente a partir de scripts gerados: Funciona sem erros na replicação.

Gambiarra

A única solução viável para isso é recriar todas as funções após a restauração/atualização para que seus sinalizadores sejam definidos corretamente. Nota: nem todas as funções são sinalizadas incorretamente, algumas legitimamente devem ser sinalizadas com inline_type = 1, a solução alternativa irá configurá-las corretamente para que não faça mal se elas forem capturadas no script abaixo.

Eu fiz isso por meio de um PowerShell rápido e sujo (abaixo), alguns dos artigos da Microsoft de problemas semelhantes sugerem que sp_refreshsqlmodule isso não funcionou para mim em primeira instância, mas não consigo ver nenhuma razão lógica para não, então vale a pena tente em primeira instância em vez disso.

#Recreate all function to fix inlining issue
#BACKUP DATABASE FIRST! Small Potential for dropped functions to not recreate!
$SQLServer = "YOURSERVER\Instance"
$SQLDatabase = "YourDatabase"
$Constring = "server='$SQLServer';database='$SQLDatabase';trusted_connection=true;"
$GetFunctionsSQL = "SELECT m.definition,o.name  FROM sys.sql_modules m INNER JOIN 
sys.objects o ON m.object_id = o.object_id WHERE m.inline_type = 1 AND o.type IN ('FN','IF','TF')"
$con = New-Object "System.Data.SqlClient.SqlConnection"
$con.ConnectionString = $Constring
$cmd = New-Object "System.Data.SqlClient.SqlCommand"
$cmd.Connection = $con
$cmd.CommandText = $GetFunctionsSQL
$sda = New-Object "System.Data.SqlClient.SqlDataAdapter"
$sda.SelectCommand = $cmd
$dt = New-Object System.Data.Datatable
Try {
   $sda.Fill($dt)    >> $null
}
catch {
   Write-Error "$_"
}
foreach ($r in $dt.Rows) {
   $nme = $r["name"]
   $def = $r["definition"]
   Write-Host "Processing $nme"
   $con = New-Object "System.Data.SqlClient.SqlConnection"
   $con.ConnectionString = $Constring
   $cmd = New-Object "System.Data.SqlClient.SqlCommand"
   $cmd.Connection = $con
   $cmd.CommandText = "drop function $nme"
   Try {
      $cmd.Connection.Open()
      $res = $cmd.ExecuteNonQuery()
   }
   catch {
      Write-Error "$_"
   }
   if ($res -eq "-1") {
      Write-Host "Reacreating..."
      $cmd.Connection.Close()
      $cmd.Connection = $con
      $cmd.CommandText = "$def"
      Try {
         $cmd.Connection.Open()
         $res = $cmd.ExecuteNonQuery()
      }
      catch {
         Write-Error "$_"
      }
      $cmd.Connection.Close()
   }
}

Resolução Final

A causa raiz terá que ser corrigida no código de restauração do mecanismo/banco de dados SQL para realmente resolver esse cenário exato. A Microsoft confirmou que a correção para isso será implementada na atualização do SQL Server 2019 CU6.

Finalmente, podemos gerar uma solicitação de Hotfix para esse problema que está chegando na CU6. Isso pode ser lançado em julho ou agosto de 2020.

Não tenho certeza se concordo com isso sendo chamado de Hotfix quando você tem que esperar 3 meses por isso!

Espero que isso ajude outra pessoa e também sirva de lição de que, quando você entrar em contato com o suporte da Microsoft, esteja preparado para fazer muito de sua própria investigação sem aviso prévio e enviá-la para eles, isso realmente ajudou a acelerar o processo.