Início / dba / Perguntas / 111339
Accepted
Phrancis
Asked: 2015-08-18 14:29:23 +0800 CST

Consultas compartilhadas pelo usuário: SQL dinâmico x SQLCMD

  • 772

Tenho que refatorar e documentar uma série de foo.sqlconsultas que serão compartilhadas por uma equipe de suporte técnico do banco de dados (para configurações do cliente e coisas assim). Existem tipos de tickets que vêm regularmente onde cada cliente tem seus próprios servidores e bancos de dados, mas fora isso o esquema é o mesmo em todos os lugares.

Os procedimentos armazenados não são uma opção no momento. Estou debatendo se devo usar dinâmico ou SQLCMD, não usei muito porque sou um pouco novo no SQL Server.

Scripts SQLCMD Eu sinto definitivamente "parece" mais limpo para mim e mais fácil de ler e fazer pequenas alterações nas consultas conforme necessário, mas também força o usuário a habilitar o modo SQLCMD. Dinâmico é mais difícil, pois o realce da sintaxe é perdido devido à consulta sendo escrita usando manipulação de string.

Eles estão sendo editados e executados usando o Management Studio 2012, SQL versão 2008R2. Quais são alguns dos prós/contras de qualquer método ou algumas das "práticas recomendadas" do SQL Server em um método ou outro? Um deles é "mais seguro" do que o outro?

Exemplo dinâmico:

declare @ServerName varchar(50) = 'REDACTED';
declare @DatabaseName varchar(50) = 'REDACTED';
declare @OrderIdsSeparatedByCommas varchar(max) = '597336, 595764, 594594';

declare @sql_OrderCheckQuery varchar(max) = ('
use {@DatabaseName};
select 
    -- stuff
from 
    {@ServerName}.{@DatabaseName}.[dbo].[client_orders]
        as "Order"
    inner join {@ServerName}.{@DatabaseName}.[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ({@OrderIdsSeparatedByCommas});
');
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@ServerName}',   quotename(@ServerName)   );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@DatabaseName}', quotename(@DatabaseName) );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@OrderIdsSeparatedByCommas}', @OrderIdsSeparatedByCommas );
print   (@sql_OrderCheckQuery); -- For debugging purposes.
execute (@sql_OrderCheckQuery);

Exemplo SQLCMD:

:setvar ServerName "[REDACTED]";
:setvar DatabaseName "[REDACTED]";
:setvar OrderIdsSeparatedByCommas "597336, 595764, 594594"

use $(DatabaseName)
select 
    --stuff
from 
    $(ServerName).$(DatabaseName).[dbo].[client_orders]
        as "Order"
    inner join $(ServerName).$(DatabaseName).[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ($(OrderIdsSeparatedByCommas));
sql-server dynamic-sql

1 respostas

  1. Best Answer

    Só para tirar isso do caminho:

    • Tecnicamente falando, ambas as opções são consultas "dinâmicas"/ad hoc que não são analisadas/validadas até serem enviadas. E ambos são suscetíveis a SQL Injection, pois não são parametrizados (embora com os scripts SQLCMD, se você estiver passando uma variável de um script CMD, terá a oportunidade de substituir 'por '', que pode ou não funcionar, dependendo de onde o variáveis ​​estão sendo usadas).

    • Existem prós e contras para cada abordagem:

      • Os scripts SQL no SSMS podem ser facilmente editados (o que é ótimo se for um requisito) e trabalhar com resultados é mais fácil do que com a saída do SQLCMD. No lado negativo, o usuário está em um IDE, então é fácil bagunçar o SQL, e o IDE facilita fazer uma grande variedade de alterações sem conhecer o SQL para fazê-lo.
      • A execução de scripts via SQLCMD.EXE não permite que o usuário faça alterações facilmente (sem editar o script em um editor e salvá-lo primeiro). Isso é ótimo se os usuários não deveriam estar alterando os scripts. Este método também permite registrar cada execução dele. No lado negativo, se houver necessidade de editar rotineiramente os scripts, isso seria bastante complicado. Ou, se os usuários precisarem digitalizar 100 mil linhas de um conjunto de resultados e/ou copiar esses resultados para o Excel ou algo assim, isso também é difícil nessa abordagem.

    Se o seu pessoal de suporte não estiver fazendo consultas ad hoc e apenas preenchendo essas variáveis, eles não precisam estar no SSMS, onde podem editar esses scripts e fazer alterações indesejadas.

    Eu criaria scripts CMD para solicitar ao usuário os valores de variável desejados e, em seguida, chamar SQLCMD.EXE com esses valores. O script CMD pode até registrar a execução em um arquivo, completo com registro de data e hora e valores de variáveis ​​enviados.

    Crie um script CMD por script SQL e coloque em uma pasta compartilhada em rede. Um usuário clica duas vezes no script CMD e ele simplesmente funciona.

    Aqui está um exemplo que:

    • solicita ao usuário o nome do servidor (nenhuma verificação de erro ainda)
    • solicita ao usuário o nome do banco de dados
      • se deixado em branco, ele listará os bancos de dados no servidor especificado e será solicitado novamente
      • se o nome do banco de dados for inválido, o usuário será solicitado novamente
    • solicita ao usuário o OrderIDsSeparatedByCommas
      • se estiver em branco, solicita ao usuário novamente
    • executa o script SQL, passando o valor de %OrderIDsSeparatedByCommas%como a variável SQLCMD$(OrderIDsSeparatedByCommas)
    • registra a data de execução, hora, ServerName, DatabaseName e OrderIDsSeparatedByCommas em um arquivo de log nomeado para o login do Windows executando o script (desta forma, se o diretório de log for rede e houver várias pessoas usando isso, não haverá nenhuma gravação contenção no arquivo de log como poderia haver se o USERNAME fosse registrado no arquivo por entrada)
      • se o diretório do arquivo de log não existir, ele será criado

    Script SQL de teste (chamado: FixProblemX.sql ):

    SELECT  *
FROM    sys.objects
WHERE   [schema_id] IN ($(OrderIdsSeparatedByCommas));

    Script CMD (chamado: FixProblemX.cmd ):

    @ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET ScriptLogPath=\\server\share\RunSqlCmdScripts\LogFiles

CLS

SET /P ScriptServerName=Please enter in a Server Name (leave blank to exit): 

IF "%ScriptServerName%" == "" GOTO :ThisIsTheEnd

REM echo %ScriptServerName%

:RequestDatabaseName
ECHO.
SET /P ScriptDatabaseName=Please enter in a Database Name (leave blank to list DBs on %ScriptServerName%): 

IF "%ScriptDatabaseName%" == "" GOTO :GetDatabaseNames

SQLCMD -b -E -W -h-1 -r0 -S %ScriptServerName% -Q "SET NOCOUNT ON; IF (NOT EXISTS(SELECT [name] FROM sys.databases WHERE [name] = N'%ScriptDatabaseName%')) RAISERROR('Invalid DB name!', 16, 1);" 2> nul

IF !ERRORLEVEL! GTR 0 (
    ECHO.
    ECHO That Database Name is invalid. Please try again.

    SET ScriptDatabaseName=
    GOTO :RequestDatabaseName
)

:RequestOrderIDs
ECHO.
SET /P OrderIdsSeparatedByCommas=Please enter in the OrderIDs (separate multiple IDs with commas): 

IF "%OrderIdsSeparatedByCommas%" == "" (

    ECHO.
    ECHO Don't play me like that. You gots ta enter in at least ONE lousy OrderID, right??
    GOTO :RequestOrderIDs
)


REM Finally run SQLCMD!!
SQLCMD -E -W -S %ScriptServerName% -d %ScriptDatabaseName% -i FixProblemX.sql -v OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%

REM Log this execution
SET ScriptLogFile=%ScriptLogPath%\%~n0_%USERNAME%.log
REM echo %ScriptLogFile%

IF NOT EXIST %ScriptLogPath% MKDIR %ScriptLogPath%

ECHO %DATE% %TIME% ServerName=%ScriptServerName%    DatabaseName=[%ScriptDatabaseName%] OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%   >> %ScriptLogFile%

GOTO :ThisIsTheEnd

:GetDatabaseNames
ECHO.
SQLCMD -E -W -h-1 -S %ScriptServerName% -Q "SET NOCOUNT ON; SELECT [name] FROM sys.databases ORDER BY [name];"
ECHO.
GOTO :RequestDatabaseName

:ThisIsTheEnd
PAUSE

    Certifique-se de editar a ScriptLogPathvariável na parte superior do script.

    Além disso, os scripts SQL (especificados pela -iopção de linha de comando para SQLCMD.EXE ) podem se beneficiar de um caminho totalmente qualificado, mas não totalmente certo.

    • 13

relate perguntas