PowerShell에서 SQL Server 쿼리를 실행하려면 어떻게 해야 합니까?

PowerShell에서 SQL Server 쿼리를 실행하려면 어떻게 해야 합니까?

로컬 머신에서 Powershell을 사용하여 SQL Server에서 임의 쿼리를 실행하는 방법이 있습니까?

재고만으로 이것을 할 필요가 있는 다른 분들을 위해서.여기서 사용하는 기능은 NET 및 PowerShell(추가 SQL 툴이 설치되지 않음)입니다.

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables

}

이거 너무 오래 써서 누가 썼는지 모르겠어요.이것은 다른 사람의 예에서 추출한 것이지만, 추가 종속성이나 기능 없이 명확하고 필요한 것을 알기 쉽게 하기 위해 단순화되었습니다.

GitHub의 스크립트모듈로 만들 정도로 자주 사용하고 공유하기 때문에 이제 모듈디렉토리에 접속하여 실행할 수 있습니다.git clone https://github.com/ChrisMagnuson/InvokeSQL이후 invoke-sql은 사용 시 자동으로 로드됩니다(PowerShell v3 이후를 사용하는 경우).

를 사용할 수 있습니다.Invoke-Sqlcmdcmdlet

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

http://technet.microsoft.com/en-us/library/cc281720.aspx

이 함수는 쿼리 결과를 powershell 객체의 배열로 반환하므로 필터 및 열에서 쉽게 사용할 수 있습니다.

function sql($sqlText, $database = "master", $server = ".")
{
    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
    $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);

    $connection.Open();
    $reader = $cmd.ExecuteReader()

    $results = @()
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
            $row[$reader.GetName($i)] = $reader.GetValue($i)
        }
        $results += new-object psobject -property $row            
    }
    $connection.Close();

    $results
}

여기 제가 이 블로그에서 찾은 예가 있습니다.

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("dbcc freeproccache", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -ne -1)
{
    echo "Failed";
}
$cn2.Close();

다음과 같은 경우 다른 TSQL 스테이트먼트를 대체할 수 있습니다.dbcc freeproccache.

SQL Server 컨텍스트가 아닌 로컬머신에서 이 작업을 수행할 경우 다음을 사용합니다.저희 회사에서 사용하는 것입니다.

$ServerName = "_ServerName_"
$DatabaseName = "_DatabaseName_"
$Query = "SELECT * FROM Table WHERE Column = ''"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

$ServerName, $DatabaseName 및 $Query 변수만 입력하면 바로 사용할 수 있습니다.

처음에 어떻게 알았는지 모르겠지만, 여기 아주 비슷한 게 있어요.

SQL 쿼리를 실행하는 기본 제공 "PowerShell" 방법은 없습니다.SQL Server 도구가 설치되어 있는 경우 Invoke-SqlCmd cmdlet이 제공됩니다.

PowerShell은 위에 구축되어 있기 때문입니다.NET, ADO를 사용할 수 있습니다.쿼리를 실행하기 위한 NET API.

Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3

varchar 매개 변수를 사용하여 SQL 주입을 방지하려면

function sqlExecuteRead($connectionString, $sqlCommand, $pars) {

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $connection.Open()
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)

    if ($pars -and $pars.Keys) {
        foreach($key in $pars.keys) {
            # avoid injection in varchar parameters
            $par = $command.Parameters.Add("@$key", [system.data.SqlDbType]::VarChar, 512);
            $par.Value = $pars[$key];
        }
    }

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    return $dataset.tables[0].rows

}

$connectionString = "connectionstringHere"
$sql = "select top 10 Message, TimeStamp, Level from dbo.log " +
    "where Message = @MSG and Level like @LEVEL"
$pars = @{
    MSG = 'this is a test from powershell'
    LEVEL = 'aaa%'
};
sqlExecuteRead $connectionString $sql $pars

원하는 대로 문자열을 포맷하고 매개 변수를 전달할 수도 있습니다.

case "ADDSQLSERVERUSER":
    //0 = coprorateName;
    //1 = user password
    //2 = servername
    command = @"$sqlQuery = Use JazzUWS_'{0}' 
                Create login UWSUser_'{0}' with password='{1}';
                Create user UWSUser_'{0}' for login UWSUser_'{0}';
                Grant Execute to UWSUser_'{0}';

                Use ReportSvrUWS_'{0}' 
                Create user UWSUser_'{0}' for login UWSUser_'{0}';
                Grant Execute to UWSUser_'{0}';

                Invoke-Sqlcmd -Query $sqlQuery -ServerInstance '{2}'";
    break;

C# 리모트 실행 코드(자신의 방법을 정리할 수 있습니다)

        string script = PowershellDictionary.GetPowershellCommand("ADDSQLSERVERUSER");
        script = String.Format(script, this.CorporateName, password, this.SQLServerName)
        PowerShellExecution.RunScriptRemote(_credentials.Server, _credentials.Username, _credentials.Password, new List<string> { script });

최적의 SQL Server 모듈인 DBATOLS를 사용할 수 있습니다.또한 여러 SQL 인스턴스에 대한 쿼리를 실행하는 것이 좋습니다.

Install-Module dbatools -Scope CurrentUser

$sql = 'SQL1','SQL1\INSTANCE1','SQL2'
$query = "SELECT 'This query would run on all SQL instances'"

Invoke-DbaQuery -SqlInstance $sqlinstances -Query $query -AppendServerInstance

언급URL : https://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell