Short Scripts – Novembro 2018

Muito boa tarde, já estamos no mês de novembro, e este é mais um post da sessão Short Scripts.

Tudo bem? Já esta se preparando para as festividades de final de ano?

Eu particularmente ainda não, na verdade a grana esta curta então tenho que esperar um pouquinho….

Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 35 posts publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a alguns meses no final do último post desta sessão, publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.

O post de hoje

Normalmente compartilho os principais scripts armazenados nos últimos meses, no post de hoje vou fazer a mesma forma que o anterior, compartilhar scripts relacionados a um tema específico, sendo o tema de hoje a propriedade Identity(), desta maneira, você vai se deparar com alguns códigos que estejam envolvidos com este tema e que também se vinculem com outros, dentre os quais destaco:

• Ativando e Desativando a propriedade Identity;
• Capturando o último valor Identity;
• Comando DBCC CheckIdenty();
• Função Identity;
• Ident_Current;
• Realizando insert com a propriedade Identity;
• Refazer numeração sequencial;
• Scope_Identity;
• Select Into;
• Sequência Numérica;
• Set Identity_Insert On / Off;
• Tabela de sistema sys.indexes;
• Tabela de sistema sys.objects;
• Tabela de sistema sys.identity_columns;
• Tabela de sistema sys.index_column; e
• Variáveis de sistema @@Identity.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Novembro 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Ativando e Desativando a propriedade Identity() —

SET IDENTITY_INSERT NomedaTabela On
Go

SET IDENTITY_INSERT NomeDaTabela Off
Go

— Short Script 2  – Comando DBCC CheckIdent(), redefinindo a sequência Identity() —
Declare @Identity Int

— Obtendo o último valor Identity() —
Set @Identity=(Select Ident_Current(‘NomedaTabela’))

— Aplicando e redefinindo a sequência Identity através do último valor obtido —
DBCC CheckIdent(‘NomedaTabela‘,Reseed,@Identity)
Go

— Short Script 3  – Identificando se a chave primária possui a propriedade Identity aplicada —
Select O.Object_Id,
O.Name,
Case IC.is_identity
When 0 Then ‘Identity desabilitado’
When 1 Then ‘Identity habilitado’
End As ‘Identity’
From sys.objects O Inner Join sys.identity_columns IC
On O.object_id = IC.object_id
Where IC.is_identity=1

— Short Script 4  – Obtendo o último valor Identity de cada tabela —
SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],
sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types
on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 5  – Resentando o valor Identity atualmente em uso — 
DBCC CHECKIDENT (‘NomedaTabela’, RESEED, 0)
Go

— Short Script 6 – Identificando o valor Identity atualmente em uso —
DBCC CHECKIDENT (‘NomedaTabela’, NORESEED)
Go

— Short Script 7 – Utilizando a função Identity() em conjunto com o comando Insert —

— Exemplo 1 —
Create Table Valores
(Codigo Int)

Insert Into Valores Values(1)
Go 100

Select Identity(Int, 2,2) As Linha, Codigo Into Registros from Valores
Go

— Exemplo 2 —
Select identity(int, 1,1) As Seq, name from sys.sysdatabases
Go

Muito bem, missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

---

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/09/18/short-scripts-setembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post a ser publicado somente em 2019, mais especificamente no mês de fevereiro.

Um forte abraço, lhe desejo um ótimo final de ano.

Até mais.

Conhecendo a Stored Procedure não documentada MSunmarkreplinfo

Galera, bom dia.

Tudo bem?

A dica de hoje tem o objetivo de apresentar uma das diversas Stored Procedures existentes no SQL Server, mas que não possuem documentação oficial por parte da Microsoft, estou se referindo a MSunmarkreplinfo, que recentemente acabei conhecendo e utilizando.

Para apresentar mais sobre esta Stored Procedure, vou contar um pouco do que aconteceu e como consegui encontrar esta relíquia.

Nos últimos dias um dos meus clientes solicitou a configuração de uma estrutura de Replicação Transacional para suas bases de dados, até ai sem problemas, todo processo de configuração do Publisher e Subscriber realizado normalmente, especificando os artigos que seriam replicados entre os servidores, horário de replicação, filtros, entre outros detalhes.

Pois bem, durante alguns dias o processo de replicação funcionou sem problemas, mas no último final de semana, um dos programadores da empresa tinha a necessidade de aplicar algumas atualizações ao ambiente, dentre os elementos que seriam atualizados uma das bases de dados que estava envolvida na replicação seria envolvida nesta atualização.

Dentre os procedimentos padrões definidos pelo desenvolvedor para aplicar as atualizações consistia em realização do backup e posteriormente fazer um detach do banco de dados, foi justamente neste ponto que os problemas começaram a aparecer!!!

Não sei exatamente por qual motivo mas somente realizando o detach do banco de dados as atualizações na aplicação e banco eram aplicadas corretamente e o ambiente voltava a funcionar. Em conversa com o programar informei que poderíamos manter a estrutura de replicação e no processo de detach realizar alguns procedimentos específicos para os bancos envolvidos no processo de replica. Mas como santo de casa não faz milagre, o cara ficou louco, e disse que desta forma não poderia mais trabalhar, então acabou sobrando pra mim, você já podem imaginar o que aconteceu. Tive que remover todo processo de replicação para este banco de dados, ai que vem o pior, durante o processo de remoção da estrutura de replicação o servidor sem qualquer motivo específico travou.

Com certeza vocês podem imaginar alguns dos principais problemas durante um processo de reconfiguração de um ambiente o servidor travar, mas para minha felicidade ou infelicidade o banco de dados, não sofreu maiores problemas, mas o SQL Server não conseguiu remover o status das tabelas envolvidas na replicação, como sendo artigos replicados, desta forma, todo e qualquer processo de alteração na estrutura ou até mesmo exclusão das tabelas não poderiam ser realizados.

Nesta momento, eu consegui entender que o SQL Server ainda estava trabalhando com a replicação mesmo que toda estrutura tivesse sido removida, o que naquele momento poderia dizer que exista então um processo de replicação fantasma, conhecida como Ghost Replication, algo muito comum de se acontecer quando realizamos alterações nas configurações de uma replicação transacional e ocorrem falhas no ambiente.

Mesmo assim, sabendo da possível existência da Ghost Replication, o programador, tentou excluir a tabela ou até mesmo realizar algumas alterações em sua estrutura, foi neste momento que ao realizar por exemplo um simples Drop Table ou Alter Table, o SQL Server apresentava a seguinte mensagem:

Unable to drop a table even after removing replication. Getting the following errors, when try to drop or alter the table:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table ‘t1’ because it is published for replication.
Server: Msg 4931, Level 16, State 1, Line 1
Cannot add columns to table ‘t1’ because it is being published for merge replication.

Básicamente a mensagem informava que era impossível realizar a exclusão ou alterações na tabela pois a mesma estava envolvida em um processo de replicação.

Ao entrar em contato comigo e alertar sobre esta situação, o programador destacou a impossibilidade de manter o ambiente em funcionamento sem poder aplicar as novas atualizações.

Foi justamente com base nesta situação e sabendo que o status das tabelas envolvidas na replicação não haviam sido alterados, comecei a realizar algumas tentativas de alteração direta nas tabelas e visões de catálogo de sistema existentes no SQL Server 2005, trabalhando diretamente com a Sys.Objetcs e Sys.SysObjects.

Dentre as diversas tentativas, tentei alterar as colunas ReplInfo e Is_Published existentes nas respectivas tabelas e visões de catálogo, mas para minha raiva não consegui realizar a alteração. O SQL Server retornava uma mensagem informando que existia dependência entre estes objetos de sistemas o que impossibilitava esta alteração.

Lógicamente neste instante o que me restava era tentar de alguma forma buscar mais informações, através do Books On-Line, como também, utilizando a própria Internet. Em ambos as alternativas praticamente encontrei os mesmos exemplos, procedimentos, dicas e sugestões. Mas como a esperança é a última que morre comecei a buscar mais informações nos Fóruns MSDN e TechNet nos Estados Unidos e foi justamente neste locais que encontrei informações e exemplos de como utilizar MSunmarkreplinfo.

Não encontrei muita coisa sobre esta Stored Procedure, mas o pouco que encontrei foi o suficiente para conseguir resolver o meu problema, e justamente esta solução que vou compartilhar com vocês, conforme o exemplo apresentado a seguir:

 — Exemplo: Utilizando a MSunmarkreplinfo para remover o Status de tabela envolvida em replicação —

SET NOCOUNT ON
DECLARE @tablename NVARCHAR(128), @RC INT

DECLARE curTable CURSOR FOR
SELECT [name] AS tbl FROM sys.tables

OPEN curTable
FETCH NEXT FROM curTable INTO @tablename

 WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename
FETCH NEXT FROM curTable INTO @tablename
END

 CLOSE curTable
DEALLOCATE curTable
GO

Com o código apresentado anteriormente consegui alterar de uma forma forçada as colunas ReplInfo e Is_Published na tabela de catálogo de sistema Sys.Objects e utilizada também pela Sys.SysObjects, desta forma, o programador já poderia realizar todas as alterações desejadas, mecher na estrutura das tabelas, como também, executar qualquer outro tipo de procedimento em seu ambiente.

Sendo assim, tudo voltou ao normal, mas o processo de replicação não pode mais ser configurado a este ambiente, fazendo-se necessário adotar outros processos de disponibilidade dos dados entre os servidores da empresa.

Espero que esta dica possa ter ilustrado um pouco de como é possível utilizar Stored Procedures não documentadas em algumas situações, vale ressaltar que qualquer procedimento desconhecido deve ser analisado e realizado em ambientes de teste.

Agradeço a sua visita, nos encontramos em breve.

Até mais.