A Pergunta :
Tanto quanto eu posso dizer, sp_executesqladiciona instruções ao início do script SQL dinâmico enviado. Porém, um rastreamento do SQL Profiler não captura as instruções extras e nem o DBCC OUTPUTBUFFER. Então:
- Existe alguma maneira de ver as instruções extras adicionadas aos lotes SQL dinâmicos enviados por
sp_executesql? - Alguém pode confirmar definitivamente que minhas conclusões sobre as declarações extras estão corretas/incorretas?
Fundo
Eu tenho um banco de dados onde alguns objetos (views, sinônimos, SPs) são reescritos com base nos dados de uma Scripttabela. Se o banco de dados for movido para outro servidor, um procedimento armazenado percorre as linhas da Scripttabela, substitui certos valores de chave no script SQL fornecido por aqueles definidos para o novo contexto do servidor e executa o script.
Tudo estava funcionando bem até que fiz alguns ajustes para adicionar suporte para permissões de script por meio desse mesmo mecanismo. O banco de dados integra-se ao produto de um fornecedor e, em cada ambiente, o banco de dados do fornecedor pode ter um usuário diferente que deve receber permissão para uma exibição específica em meu banco de dados para fins de relatório. Portanto, tenho que consultar esse usuário (no banco de dados do fornecedor), usar esse nome para criar o usuário em meu banco de dados, se ele não existir e, finalmente, conceder SELECTpermissão. Isso exigia scripts mais demorados e fazer dynamic-sql dentro do dynamic-sql, então eu queria passar o @Debugparâmetro do meu script externo para poder ver o script extra que estava sendo gerado e confirmar sua correção antes de tentar executá-lo.
Além de alterar quais tipos de objeto podem ser incluídos no script e tornar o DROPscript opcional, a única alteração material que fiz para acomodar o @Debugparâmetro foi alterar isto:
EXEC (@CreateSQL);
para isso:
EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
Então me deparei com um problema: o único procedimento armazenado em minha Scripttabela não podia mais ser criado, embora o DROPanterior ainda funcionasse bem. O resultado que obtive foi este:
Msg 156, Nível 15, Estado 1, Linha 1
Sintaxe incorreta perto da palavra-chave 'PROCEDIMENTO'.
Isso foi muito confuso, mas depois de discutir com ele por algum tempo, finalmente descobri o problema: sp_executesqlvincula parâmetros ao SQL dinâmico adicionando secretamente uma DECLAREinstrução ao topo antes de executar. Como CREATE PROCEDUREdeve ser a única instrução no lote, mas agora há uma instrução extra antes da CREATE PROCEDURElinha, ela gera um erro. Ele diz Line 1- o que me enganou ainda mais - mas isso é obviamente ajustado pelo mecanismo para que as pessoas não fiquem confusas sobre os números de linha de seu próprio script ao lidar com erros.
A solução para o problema foi detectar com qual tipo de objeto estava sendo trabalhado e NÃO passar o @Debugparâmetro para que o script que não deve ter outras instruções funcione bem. Uma mudança rápida fez o trabalho:
IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN
EXEC sp_executesql @CreateSQL;
END
ELSE BEGIN
EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
END;
Eu também poderia ter aninhado meu SQL dinâmico um nível mais profundo, para criar o procedimento dentro do SQL dinâmico (novamente, dentro do script na tabela), mas essa foi uma solução menos ideal no meu caso.
Suspeito que o uso OUTPUTde variáveis com sp_executesqltambém adicionaria uma ou mais instruções ao final do script para permitir que o mecanismo as capture, provavelmente em uma SELECTinstrução que é silenciosamente engolida.
Antes do sp_executesql, você deve ver uma instrução sp_prepare no Profiler que forneceria mais informações: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx
Às vezes, isso pode ser difícil de encontrar, mas ficou um pouco mais fácil com eventos estendidos em 2008R2 - especificamente rastreamento de causalidade.
http://msdn.microsoft.com/en-us/library/bb630284.aspx
Descobri que o servidor não coloca o conteúdo do script injetado no lote, mas no plano de execução em cache. Então, você só pode ver isso acontecendo no profiler se o SQL dinâmico executado não estiver no cache. Isso me confundiu originalmente porque só comecei a rastrear depois de executar meu teste uma vez, então a instrução já estava no cache. Finalmente encontrei esse evento quando troquei os servidores de banco de dados e tive o rastreamento em execução na primeira execução.
Aqui está o lote que executei:
E aqui está o que eu encontrei na
SQL Profiler 10.0.1600.22corrida contraMicrosoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64):Então eu estava certo que há texto sendo injetado, mas errado em alguns pontos:
Não é uma
DECLAREdeclaração normal. É outra coisa. O que é isso... fique ligado.Quando há variáveis OUTPUT, ele é tratado mais como um procedimento armazenado com uma
OUTPUTvariável. Não há nenhum conteúdo ocultoSELECTou outro adicionado ao final, como os drivers do provedor de banco de dados geralmente fazem aos lotes enviados para coletar valores de retorno ou contagens de linhas.Não há nenhum ajuste de número de linha acontecendo. O script extra é adicionado ao início sem quebras de linha.
Se você tentar enviar o texto completo em cache diretamente, receberá um erro:
Portanto, parece que o termo correto é uma "consulta parametrizada". Embora o termo normalmente se refira à codificação front-end, aparentemente existe um tipo interno de SQL Server que é usado em SQL armazenado em cache em planos de execução.