AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • Início
  • system&network
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • Início
  • system&network
    • Recentes
    • Highest score
    • tags
  • Ubuntu
    • Recentes
    • Highest score
    • tags
  • Unix
    • Recentes
    • tags
  • DBA
    • Recentes
    • tags
  • Computer
    • Recentes
    • tags
  • Coding
    • Recentes
    • tags
Início / dba / Perguntas / 183851
Accepted
Martin Surasky
Martin Surasky
Asked: 2017-08-19 08:26:59 +0800 CST2017-08-19 08:26:59 +0800 CST 2017-08-19 08:26:59 +0800 CST

Impedir que o código T-SQL que não é compatível interrompa o script

  • 772

Aqui está um desafio interessante que não consegui resolver... Há uma boa consulta T-SQL que eu, Jonathan Kehayias, criei há algum tempo para encontrar problemas de conversões implícitas nas consultas que eu amo. O problema é que essa consulta não funciona em bancos de dados com níveis de compatibilidade antigos (80 e anteriores). Suponho que isso ocorre porque as funções com valor de tabela (TVFs) foram introduzidas no SQL Server 2005.

O problema é que, se eu usar esse script para validar conversões implícitas em todos os meus bancos de dados assim:

declare @sql    nvarchar(4000)
set @sql =
'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''?'' AND compatibility_level >= 90)
BEGIN   
    USE ['+'?'+'] ;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET QUOTED_IDENTIFIER ON
    DECLARE @dbname SYSNAME 
    SET @dbname = QUOTENAME(DB_NAME())

    BEGIN TRY
        RAISERROR(''?'', 0, 42) WITH NOWAIT;
        WITH XMLNAMESPACES 
            (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'') 
        INSERT INTO DMTAdmin.dbo.BestPractices_ImplicitConversions
        SELECT 
            GETDATE(),
            @dbname,
            stmt.value(''(@StatementText)[1]'', ''varchar(max)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)''), 
            ic.DATA_TYPE AS ConvertFrom, 
            ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
            t.value(''(@DataType)[1]'', ''varchar(128)'') AS ConvertTo, 
            t.value(''(@Length)[1]'', ''int'') AS ConvertToLength, 
            query_plan 
        FROM sys.dm_exec_cached_plans AS cp 
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
        CROSS APPLY query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple'') AS batch(stmt) 
        CROSS APPLY stmt.nodes(''.//Convert[@Implicit="1"]'') AS n(t) 
        JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
            ON QUOTENAME(ic.TABLE_SCHEMA) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)'') 
            AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)'') 
            AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)'') 
        WHERE t.exist(''ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]'') = 1
    END TRY
    BEGIN CATCH
    END CATCH;
END
'

exec sp_msforeachdb @sql

... o script vai bombar porque 9 dos meus bancos de dados bizzilion estão no nível de compatibilidade 80! Você pode facilmente tentar isso criando um banco de dados em uma caixa com um nível de compatibilidade antigo e executando este script, você verá que ele falhará.

Como você pode ver, meu script está usando um TRY/CATCH, mas isso não ajudou porque o problema é na verdade um erro de compilação (e não um erro de tempo de execução).

Também tentei com um IF logo no início desse T-SQL dinâmico (como você pode ver), mas, novamente, os pontos de decisão não impedirão que o código seja compilado para esses bancos de dados e falhe.

Nada do que tentei até agora me ajuda a evitar esse erro e, como resultado, o trabalho que tenho relata como "falha" mesmo quando estou bem com esses bancos de dados específicos sendo "ignorados" se eu pudesse.

Algum de vocês tem uma idéia de como eu poderia implementar isso para que eu pudesse pesquisar em todos, exceto nos bancos de dados de nível de compatibilidade antigos?

sql-server t-sql
  • 2 2 respostas
  • 107 Views

2 respostas

  • Voted
  1. Best Answer
    markp-fuso
    2017-08-19T09:19:57+08:002017-08-19T09:19:57+08:00

    Em vez de fazer a sysdatabases.comptlevelverificação dentro de seu SQL dinâmico, puxe o sysdatabases.cmptlevelcheck-out para o lote de nível superior e use-o para determinar em quais bancos de dados construir/executar consultas dinâmicas. Como alternativa, use o cmptlevelpara personalizar a consulta principal conforme necessário.

    Se você cria uma grande consulta SQL para atingir todos os bancos de dados desejados ou usa um cursor/loop para criar/executar uma consulta dinâmica para cada banco de dados desejado, é com você.

    Um esboço rápido de pseudocódigo de uma solução de cursor/loop:

    declare @dbname varchar(30), @cmptlevel int, @sql varchar(max)
    declare dbcur cursor for select name, cmptlevel from sysdatabases
    open dbcur
    fetch dbcur into @dbname, @cmptlevel
    while @@sqlstatus = 0
    begin
        select @sql='... common query code ... add '@dbname..' prefix to tables as needed ... '+
                    case when @cmptlevel <  90 then '... cmptlevel< 90 specific code ... add '@dbname..' prefix to tables as needed ...'
                         when @cmptlevel >= 90 then '... cmptlevel>=90 specific code ... add '@dbname..' prefix to tables as needed ...'
                    end + 
                    '... common query code ... add '@dbname..' prefix to tables as needed ... '
        exec sp_excecutesql @sql
        fetch dbcur into @dbname, @cmptlevel
    end
    close dbcur
    
    • 4
  2. David Browne - Microsoft
    2017-08-19T09:47:05+08:002017-08-19T09:47:05+08:00

    Você não precisa executar a consulta no contexto de cada banco de dados para ler seu catálogo. Você pode usar um nome de três partes como mydb.sys.columns. Isso também deve permitir que você inclua os 80 bancos de dados compatíveis.

    Então isso simplifica para:

    declare @sql    nvarchar(max)
    set @sql =
    N' 
        SET QUOTED_IDENTIFIER ON;
        DECLARE @dbname SYSNAME = ''[?]'';
    
        WITH XMLNAMESPACES 
            (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'') 
        INSERT INTO DMTAdmin.dbo.BestPractices_ImplicitConversions
        SELECT 
            GETDATE(),
            @dbname,
            stmt.value(''(@StatementText)[1]'', ''varchar(max)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)''), 
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)''), 
            ic.DATA_TYPE AS ConvertFrom, 
            ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
            t.value(''(@DataType)[1]'', ''varchar(128)'') AS ConvertTo, 
            t.value(''(@Length)[1]'', ''int'') AS ConvertToLength, 
            query_plan 
        FROM sys.dm_exec_cached_plans AS cp 
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
        CROSS APPLY query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple'') AS batch(stmt) 
        CROSS APPLY stmt.nodes(''.//Convert[@Implicit="1"]'') AS n(t) 
        JOIN [?].INFORMATION_SCHEMA.COLUMNS AS ic 
            ON QUOTENAME(ic.TABLE_SCHEMA) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)'') 
            AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)'') 
            AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)'') 
        WHERE t.exist(''ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]'') = 1
    
    
    '
    
    exec sp_msforeachdb @sql
    
    • 1

relate perguntas

  • SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

  • Preciso de índices separados para cada tipo de consulta ou um índice de várias colunas funcionará?

  • Quando devo usar uma restrição exclusiva em vez de um índice exclusivo?

  • Quais são as principais causas de deadlocks e podem ser evitadas?

  • Como determinar se um Índice é necessário ou necessário

Sidebar

Stats

  • Perguntas 205573
  • respostas 270741
  • best respostas 135370
  • utilizador 68524
  • Highest score
  • respostas
  • Marko Smith

    conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

    • 12 respostas
  • Marko Smith

    Como fazer a saída do sqlplus aparecer em uma linha?

    • 3 respostas
  • Marko Smith

    Selecione qual tem data máxima ou data mais recente

    • 3 respostas
  • Marko Smith

    Como faço para listar todos os esquemas no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Listar todas as colunas de uma tabela especificada

    • 5 respostas
  • Marko Smith

    Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

    • 4 respostas
  • Marko Smith

    Como você mysqldump tabela (s) específica (s)?

    • 4 respostas
  • Marko Smith

    Listar os privilégios do banco de dados usando o psql

    • 10 respostas
  • Marko Smith

    Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Como faço para listar todos os bancos de dados e tabelas usando o psql?

    • 7 respostas
  • Martin Hope
    Jin conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane Como faço para listar todos os esquemas no PostgreSQL? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland Listar todas as colunas de uma tabela especificada 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney O MySQL pode realizar consultas razoavelmente em bilhões de linhas? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx Como posso monitorar o andamento de uma importação de um arquivo .sql grande? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison Como você mysqldump tabela (s) específica (s)? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas Como posso cronometrar consultas SQL usando psql? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas Como faço para listar todos os bancos de dados e tabelas usando o psql? 2011-02-18 00:45:49 +0800 CST

Hot tag

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • Início
  • Perguntas
    • Recentes
    • Highest score
  • tag
  • help

Footer

AskOverflow.Dev

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve