사용자 공유 쿼리 : 동적 SQL과 SQLCMD varchar(50) = ‘REDACTED’; declare

foo.sqlDB 기술 지원 팀 (고객 구성 및 이와 유사한 사항)이 공유 할 여러 쿼리 를 리팩터링하고 문서화 해야합니다. 각 고객마다 고유의 서버와 데이터베이스가있는 경우 정기적으로 제공되는 티켓 유형이 있지만, 그렇지 않으면 전체적으로 스키마가 동일합니다.

저장 프로시 저는 현재 옵션이 아닙니다. 동적 또는 SQLCMD를 사용할 지에 대해 토론하고 있는데 SQL Server에서 조금 새로 왔기 때문에 많이 사용하지 않았습니다.

SQLCMD 스크립팅 필자는 필자에게 “깨끗해 보이는”느낌이 들며, 필요에 따라 쿼리를 쉽게 읽고 약간의 변경을 수행 할 수 있지만 사용자는 SQLCMD 모드를 활성화해야합니다. 문자열 조작을 사용하여 작성된 쿼리로 인해 구문 강조 표시가 손실되므로 동적이 더 어렵습니다.

이들은 Management Studio 2012, SQL 버전 2008R2를 사용하여 편집 및 실행되었습니다. 두 가지 방법의 장단점은 무엇입니까? 또는 SQL Server “모범 사례”중 하나 또는 다른 방법은 무엇입니까? 그들 중 하나가 다른 것보다 “더 안전”합니까?

동적 예 :

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);

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));

답변

이것들을 방해하지 않으려면 :

  • 기술적으로 말해서,이 두 옵션은 모두 “동적”/ 임시 쿼리이며 제출 될 때까지 구문 분석 / 검증되지 않습니다. 그들이 SQLCMD 스크립트에 불구하고 (파라미터되지 않기 때문에 당신은 당신이 대체 할 수있는 기회가 않는 CMD 스크립트에서 변수를 전달하는 경우 그리고 둘, SQL 인젝션에 취약 '와 함께 ''여부를 작업 위치에 따라 않을 수는 변수가 사용되고 있습니다).

  • 각 접근 방식에는 장단점이 있습니다.

    • SSMS의 SQL 스크립트는 쉽게 편집 할 수 있으며 (필요한 경우 유용) SQLCMD의 출력보다 결과 작업이 더 쉽습니다. 단점은 사용자가 IDE에 있으므로 SQL을 쉽게 망칠 수 있으며 IDE를 사용하면 SQL을 알지 않고도 다양한 변경을 쉽게 할 수 있습니다.
    • SQLCMD.EXE를 통해 스크립트를 실행하면 사용자가 편집기에서 스크립트를 편집 한 후 먼저 저장하지 않고 쉽게 변경할 수 없습니다. 사용자가 스크립트를 변경하지 않아야하는 경우 유용합니다. 이 방법을 사용하면 각 실행을 기록 할 수도 있습니다. 단점으로, 정기적으로 스크립트를 편집해야 할 경우 상당히 번거로울 수 있습니다. 또는 사용자가 100k 행의 결과 집합을 스캔하거나 해당 결과를 Excel 또는 다른 곳에 복사해야하는 경우에도이 방법이 어렵습니다.

지원 담당자가 임시 쿼리를 수행하지 않고 해당 변수를 채우는 경우 해당 스크립트를 편집하고 원치 않는 변경을 수행 할 수있는 SSMS에있을 필요는 없습니다.

CMD 스크립트를 만들어 사용자에게 원하는 변수 값을 묻는 메시지 를 표시 한 다음 해당 값으로 SQLCMD.EXE 를 호출 합니다. CMD 스크립트는 실행 시간을 파일에 기록하고 타임 스탬프 및 제출 된 변수 값으로 완료 할 수도 있습니다.

SQL 스크립트 당 하나의 CMD 스크립트를 작성하고 네트워크 공유 폴더에 배치하십시오. 사용자가 CMD 스크립트를 두 번 클릭하면 작동합니다.

다음은 그 예입니다.

  • 사용자에게 서버 이름을 묻는 메시지를 표시합니다 (아직 오류 확인 없음)
  • 데이터베이스 이름을 묻는 메시지를 표시합니다
    • 비워두면 지정된 서버의 데이터베이스가 나열되고 다시 프롬프트됩니다.
    • 데이터베이스 이름이 유효하지 않으면 사용자에게 다시 프롬프트가 표시됩니다
  • 사용자에게 OrderIDsSeparatedByCommas를 프롬프트합니다.
    • 비어 있으면 사용자에게 다시 프롬프트
  • %OrderIDsSeparatedByCommas%SQLCMD 변수로 값을 전달하여 SQL 스크립트를 실행합니다.$(OrderIDsSeparatedByCommas)
  • 실행 날짜, 시간, ServerName, DatabaseName 및 OrderIDsSeparatedByCommas를 스크립트를 실행하는 Windows 로그인의 이름이 지정된 로그 파일에 기록합니다 (이 방법으로 로그 디렉토리가 네트워크이고이를 사용하는 여러 사람이있는 경우에는 쓰기가 없습니다) USERNAME이 항목 당 파일에 기록 된 경우와 같이 로그 파일에 경합
    • 로그 파일 디렉토리가 존재하지 않으면 작성됩니다.

SQL 스크립트 테스트 (이름 : FixProblemX.sql ) :

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

CMD 스크립트 (이름 : 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

ScriptLogPath스크립트 상단으로 변수 를 편집하십시오 .

또한, (에 의해 지정된 SQL 스크립트 -i에 대한 명령 줄 스위치 sqlcmd.exe를가 ) 수있는 완전한 경로가 혜택을 얻을하지만 완전히 확실하지 않다.

답변