none
Script de Alter executa com sucesso mesmo quando não existe base no script

    Pergunta

  • Pessoal,

    Estou com uma situação estranha aqui no banco. Agora estamos com o SSMS na versão 14.0.17199.0 e estamos com o MSSQL 2014 SP2 em nosso ambiente. Anteriormente quando executávamos um alter em uma procedure se tivesse um objeto que não existisse no banco ( como um banco de dados ou um linked server) o SQL me retornava erro, porém agora ele esta executando com sucesso o alter, fazendo com quem a gente não perceba que esta faltando algo e quando o usuario vai executar a procedure ela falha. Alguém sabe se é alguma configuração no sp_configure ou então no SSMS que eu tenho que alterar para voltar a dar erro?

    Carlos Justo

    quinta-feira, 11 de janeiro de 2018 17:51

Respostas

  • Um exemplo simples do codigo seria:

    Alter Procedure [dbo][MinhaProcedure]
    as begin
    select * from [BANCODEDADOS1].[DBO].[TABELA]
    END

    Anteriormente se eu tentasse executar e o banco de dados BANCODEDADOS1 não existisse o SSMS me retornaria com erro e hoje em dia a situação que esta ocorrendo é que executa com sucesso.

    SQL SERVER 2008

    Testei os códigos #1 e #2 em instância com SQL Server 2008 Enterprise, sendo que BANCODEDADOS1 não existe, e não ocorreu nenhum erro durante a compilação de ambos.

    -- código #1
    CREATE PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2
      from [BANCODEDADOS1].[DBO].[TABELA];
    end;
    go
    
    -- código #2
    ALTER PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2, col3
      from [BANCODEDADOS1].[DBO].[TABELA];
    end;
    go
     

    Executei a seguir o código #3 abaixo 

    -- código #3
    EXECUTE dbo.MinhaProcedure;
    go


    e a seguinte mensagem de erro foi exibida:

    Mensagem 208, Nível 16, Estado 1, Procedimento MinhaProcedure, Linha 3
    Nome de objeto 'BANCODEDADOS1.DBO.TABELA' inválido.

    Observa-se que o objeto dito como inexistente é a tabela e não o banco de dados (que é efetivamente o que não existe). O que está conforme o conceito de postergação da resolução de nomes.

    -----

    A seguir testei o caso de linked server, sem que tenha estabelecido o vínculo com outro servidor.

    -- código #4
    DROP PROCEDURE [dbo].[MinhaProcedure]
    go
    
    CREATE PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2
      from PlanaltoCentral.[BANCODEDADOS1].[DBO].[TABELA];
    end;
    go


    Ao executar o código #4 a seguinte mensagem de erro é exibida:
    Mensagem 7202, Nível 11, Estado 2, Procedimento MinhaProcedure, Linha 3
    Não foi possível encontrar o servidor 'PlanaltoCentral' em sys.servers. Verifique se o nome do servidor correto foi especificado. Se necessário, execute o procedimento armazenado sp_addlinkedserver para adicionar o servidor a sys.servers.

    Para mim não houve coerência nesse caso, pois a resolução de nome da tabela deveria ser postergada para o momento da execução, da mesma forma que na execução do código #1. 

    -----

    SQL SERVER 2016

    Em instância com o SQL Server 2016 Express, utilizando SSMS 17.3 (build 14.0.17199.0), repito a execução dos códigos 1 e 2; nenhum erro.

    Ao executar o código #3, a seguinte mensagem é exibida:
    Msg 208, Nível 16, Estado 1, Procedimento MinhaProcedure, Linha 5 [Linha de Início do Lote 16]
    Nome de objeto 'BANCODEDADOS1.DBO.TABELA' inválido.

    Ou seja, o mesmo comportamento nas versões 2008 e 2016.

    Executo então o código #4 e a mensagem de erro exibida é:
    Msg 7202, Nível 11, Estado 2, Procedimento MinhaProcedure, Linha 4 [Linha de Início do Lote 3]
    Não foi possível encontrar o servidor 'PlanaltoCentral' em sys.servers. Verifique se o nome do servidor correto foi especificado. Se necessário, execute o procedimento armazenado sp_addlinkedserver para adicionar o servidor a sys.servers.

    Observa-se o mesmo comportamento que ocorreu quando o código #4 foi executado no SQL Server 2008.

    -----
    OBSERVAÇÕES

    (1) Não efetuei testes com vinculação de servidores definida e ativa. Pode ser que, neste caso, o resultado da execução do código #4 apresente resultados diferentes nas versões 2008/2016, caso a vinculação exista e esteja ativa durante a criação/modificação do procedimento armazenado.

    (2) Não sei se os resultados dos testes anteriores teriam o mesmo resultado na versão 2014 (com SP2). Na dúvida sobre se é algo inerente à versão 2014, sugiro que aplique as atualizações disponíveis para o 2014 SP2; por precaução, somente após o backup completo dos bancos de dados da instância. As atualizações cumulativas mais recentes para o SQL Server 2014 SP2 estão relacionadas no tópico Atualizações para SQL Server.

    (3) Avalie se a causa do comportamento divergente (se realmente existe) está nos provedores utilizados na vinculação.

    (4) Avalie se o usuário que modifica o procedimento armazenado tem acesso, no servidor remoto, aos objetos referenciados.

    (5) Se ainda não o fez, contacte o suporte da Microsoft.


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    sábado, 13 de janeiro de 2018 21:46
    Moderador

Todas as Respostas

  • Eu acho estranho é o comportamento citado em “Anteriormente quando executávamos um alter em uma procedure se tivesse um objeto que não existisse no banco ( como um banco de dados ou um linked server) o SQL me retornava erro”. O usual, inclusive documentado no comando CREATE PROCEDURE, é

    Um procedimento pode referenciar tabelas que ainda não existem. No momento da criação, apenas a verificação de sintaxe é executada. O procedimento não é compilado até ser executado pela primeira vez. Somente durante a compilação todos os objetos referenciados no procedimento são resolvidos. Portanto, um procedimento sintaticamente correto que referencie tabelas que não existem pode ser criado com êxito; entretanto, ele falhará em tempo de execução se as tabelas referenciadas não existirem.

    A documentação cita tabelas; falta pesquisar como é o comportamento com relação a outros objetos (colunas, banco de dados, etc).

    Qual era a versão do SQL Server utilizada anteriormente?

    O artigo Deferred Name Resolution and Compilation pode ser útil para compreender o que está a ocorrer.

    -----
    PS: tinha uma vaga lembrança sobre esse assunto ter sido mencionado anteriormente neste fórum; pesquisando, encontrei o tópico Identificar erro em procedure - Management Studio, de 2014. Sugiro a leitura do mesmo. Aproveito para transcrever um trecho de resposta presente naquele tópico:

    When you create a stored procedure, SQL Server first parses the code to check for syntax errors. If the code passes the parsing stage successfully, SQL Server attempts to resolve the names it contains. The resolution process verifies the existence of object and column names, among other things. If the referenced objects exist, the resolution process will take place fully—that is, it also checks for the existence of the referenced column names.
    If an object name exists but a column within it doesn’t, the resolution process produces an error and the stored procedure is not created. However, if the object doesn’t exist at all, SQL Server creates the stored procedure and defers the resolution process to run time, when the stored procedure is invoked. Of course, if a referenced object or a column is still missing when you execute the stored procedure, the code will fail. This process of postponing name resolution until run time is called deferred name resolution.

    O trecho acima é de autoria de Itzik Ben-Gan e está na página 97 do livro Inside Microsoft SQL Server 2008: T-SQL Programming.

    PS2: Sobre “fazendo com quem a gente não perceba que esta faltando algo e quando o usuario vai executar a procedure ela falha”, avalie a implementação de ambiente de homologação para testar as modificações antes de enviar o código para produção.

    PS3: Pesquisando na web por
            sql server check stored procedure deferred
    encontrei relatos de casos semelhantes e algumas soluções alternativas, mas nenhuma menciona ser 100% confiável.


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    quinta-feira, 11 de janeiro de 2018 21:34
    Moderador
  • Eu acho estranho é o comportamento citado em “Anteriormente quando executávamos um alter em uma procedure se tivesse um objeto que não existisse no banco ( como um banco de dados ou um linked server) o SQL me retornava erro”. O usual, inclusive documentado no comando CREATE PROCEDURE, é

    Um procedimento pode referenciar tabelas que ainda não existem. No momento da criação, apenas a verificação de sintaxe é executada. O procedimento não é compilado até ser executado pela primeira vez. Somente durante a compilação todos os objetos referenciados no procedimento são resolvidos. Portanto, um procedimento sintaticamente correto que referencie tabelas que não existem pode ser criado com êxito; entretanto, ele falhará em tempo de execução se as tabelas referenciadas não existirem.

    A documentação cita tabelas; falta pesquisar como é o comportamento com relação a outros objetos (colunas, banco de dados, etc).

    Qual era a versão do SQL Server utilizada anteriormente?

    O artigo Deferred Name Resolution and Compilation pode ser útil para compreender o que está a ocorrer.

    PS: tinha uma vaga lembrança sobre esse assunto ter sido mencionado anteriomente neste fórum; pesquisando, encontrei o tópico Identificar erro em procedure - Management Studio, de 2014.


    e-mail       José Diz     Belo Horizonte, MG - Brasil


    José Diz,

    Posso estar enganado, mas parece que isso era um bug que surgiu após o SP2 no SQL Server 2014.

    Você se lembra de algo?


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 11 de janeiro de 2018 22:25
    Moderador
  • Justo,

    Você possui algum outro ambiente com versão diferente do SQL Server 2014 que seja possível testar este comportamento?


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 11 de janeiro de 2018 22:26
    Moderador
  • Posso estar enganado, mas parece que isso era um bug que surgiu após o SP2 no SQL Server 2014.

    Pedro, o artigo “Deferred Name Resolution”, anteriormente citado, é da época do SQL Server 2008 R2; ou seja, o comportamento de postergar (deferred) a resolução dos nomes de objetos existe bem antes da versão 2014. É um artigo curto; vale a pena a leitura atenta do mesmo. Nesse artigo fica claro que “table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed”. E também alerta que “when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table”.

    Além disso, esse comportamento de postergar consta na documentação do comando CREATE PROCEDURE.

    O que percebo de diferente neste tópico é a referência a objetos existentes em outro banco de dados, por causa da menção a linked server.


    e-mail       José Diz     Belo Horizonte, MG - Brasil


    quinta-feira, 11 de janeiro de 2018 22:59
    Moderador
  • Ai já ´euma questão de vermos o script de que está falando. De puder poste ele.
    sexta-feira, 12 de janeiro de 2018 12:09
  • Um exemplo simples do codigo seria:

    Alter Procedure [dbo][MinhaProcedure]

    as begin

    select * from [BANCODEDADOS1].[DBO].[TABELA]

    END

    Anteriormente se eu tentasse executar e o banco de dados BANCODEDADOS1 não existisse o SSMS me retornaria com erro e hoje em dia a situação que esta ocorrendo é que executa com sucesso.

    No exemplo que eu sei , foi que tinhamos um linked server

    select * from [Servidor].[BANCODEDADOS1].[DBO].[TABELA]

    E mesmo assim se o banco não existisse no outro servidor apareceria o erro e agora não mais.

    sexta-feira, 12 de janeiro de 2018 18:52
  • Posso estar enganado, mas parece que isso era um bug que surgiu após o SP2 no SQL Server 2014.

    Pedro, o artigo “Deferred Name Resolution”, anteriormente citado, é da época do SQL Server 2008 R2; ou seja, o comportamento de postergar (deferred) a resolução dos nomes de objetos existe bem antes da versão 2014. É um artigo curto; vale a pena a leitura atenta do mesmo. Nesse artigo fica claro que “table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed”. E também alerta que “when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table”.

    Além disso, esse comportamento de postergar consta na documentação do comando CREATE PROCEDURE.

    O que percebo de diferente neste tópico é a referência a objetos existentes em outro banco de dados, por causa da menção a linked server.


    e-mail       José Diz     Belo Horizonte, MG - Brasil


    José,

    É verdade você tem razão eu não me lembrava destas observações.


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:20
    Moderador
  • Justo,

    Mas você esta executando este mesmo select fazendo a chamada do linked server na sua stored procedure?


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 12 de janeiro de 2018 23:22
    Moderador
  • Um exemplo simples do codigo seria:

    Alter Procedure [dbo][MinhaProcedure]
    as begin
    select * from [BANCODEDADOS1].[DBO].[TABELA]
    END

    Anteriormente se eu tentasse executar e o banco de dados BANCODEDADOS1 não existisse o SSMS me retornaria com erro e hoje em dia a situação que esta ocorrendo é que executa com sucesso.

    SQL SERVER 2008

    Testei os códigos #1 e #2 em instância com SQL Server 2008 Enterprise, sendo que BANCODEDADOS1 não existe, e não ocorreu nenhum erro durante a compilação de ambos.

    -- código #1
    CREATE PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2
      from [BANCODEDADOS1].[DBO].[TABELA];
    end;
    go
    
    -- código #2
    ALTER PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2, col3
      from [BANCODEDADOS1].[DBO].[TABELA];
    end;
    go
     

    Executei a seguir o código #3 abaixo 

    -- código #3
    EXECUTE dbo.MinhaProcedure;
    go


    e a seguinte mensagem de erro foi exibida:

    Mensagem 208, Nível 16, Estado 1, Procedimento MinhaProcedure, Linha 3
    Nome de objeto 'BANCODEDADOS1.DBO.TABELA' inválido.

    Observa-se que o objeto dito como inexistente é a tabela e não o banco de dados (que é efetivamente o que não existe). O que está conforme o conceito de postergação da resolução de nomes.

    -----

    A seguir testei o caso de linked server, sem que tenha estabelecido o vínculo com outro servidor.

    -- código #4
    DROP PROCEDURE [dbo].[MinhaProcedure]
    go
    
    CREATE PROCEDURE [dbo].[MinhaProcedure] as
    begin
    SELECT col1, col2
      from PlanaltoCentral.[BANCODEDADOS1].[DBO].[TABELA];
    end;
    go


    Ao executar o código #4 a seguinte mensagem de erro é exibida:
    Mensagem 7202, Nível 11, Estado 2, Procedimento MinhaProcedure, Linha 3
    Não foi possível encontrar o servidor 'PlanaltoCentral' em sys.servers. Verifique se o nome do servidor correto foi especificado. Se necessário, execute o procedimento armazenado sp_addlinkedserver para adicionar o servidor a sys.servers.

    Para mim não houve coerência nesse caso, pois a resolução de nome da tabela deveria ser postergada para o momento da execução, da mesma forma que na execução do código #1. 

    -----

    SQL SERVER 2016

    Em instância com o SQL Server 2016 Express, utilizando SSMS 17.3 (build 14.0.17199.0), repito a execução dos códigos 1 e 2; nenhum erro.

    Ao executar o código #3, a seguinte mensagem é exibida:
    Msg 208, Nível 16, Estado 1, Procedimento MinhaProcedure, Linha 5 [Linha de Início do Lote 16]
    Nome de objeto 'BANCODEDADOS1.DBO.TABELA' inválido.

    Ou seja, o mesmo comportamento nas versões 2008 e 2016.

    Executo então o código #4 e a mensagem de erro exibida é:
    Msg 7202, Nível 11, Estado 2, Procedimento MinhaProcedure, Linha 4 [Linha de Início do Lote 3]
    Não foi possível encontrar o servidor 'PlanaltoCentral' em sys.servers. Verifique se o nome do servidor correto foi especificado. Se necessário, execute o procedimento armazenado sp_addlinkedserver para adicionar o servidor a sys.servers.

    Observa-se o mesmo comportamento que ocorreu quando o código #4 foi executado no SQL Server 2008.

    -----
    OBSERVAÇÕES

    (1) Não efetuei testes com vinculação de servidores definida e ativa. Pode ser que, neste caso, o resultado da execução do código #4 apresente resultados diferentes nas versões 2008/2016, caso a vinculação exista e esteja ativa durante a criação/modificação do procedimento armazenado.

    (2) Não sei se os resultados dos testes anteriores teriam o mesmo resultado na versão 2014 (com SP2). Na dúvida sobre se é algo inerente à versão 2014, sugiro que aplique as atualizações disponíveis para o 2014 SP2; por precaução, somente após o backup completo dos bancos de dados da instância. As atualizações cumulativas mais recentes para o SQL Server 2014 SP2 estão relacionadas no tópico Atualizações para SQL Server.

    (3) Avalie se a causa do comportamento divergente (se realmente existe) está nos provedores utilizados na vinculação.

    (4) Avalie se o usuário que modifica o procedimento armazenado tem acesso, no servidor remoto, aos objetos referenciados.

    (5) Se ainda não o fez, contacte o suporte da Microsoft.


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    sábado, 13 de janeiro de 2018 21:46
    Moderador