Saturday, July 7, 2012

PowerShell: Query Remote SQL Server Data Using Stored Procedure

Found a while back a cool blog entry on "Database Access within PowerShell". Using that as a reference, I started configuring a few scripts to utilize stored procedures when accessing SQL Server data. Below is a quick example. Enjoy.
#Var for SQL Server FQDN 
$SQLServerFQDN = "";

#Var for SQL Instance Name
$SQLInstance = "MyInstance";

#Var for SQL Database
$SQLDatabase = "MyDatabase";

#Create Empty Array for Storing Server Names
$servers = @();

#Load .NET System.Data DLL

#Connection String Settings (Using Integrated Security So No UserID and Password Needed)
$sqlConString = "Server=$SQLServerFQDN\$SQLInstance;Database=$SQLDatabase;Integrated Security=SSPI;";

#Stored Procedure Name for Servers Select Statement
$spSelectServers = "Get_All_Servers";

#Create a SQL Connection Object 
$sqlCon = New-Object System.Data.SqlClient.SqlConnection($sqlConString);

#Create and Configure a SQL Command
$sqlCommSR = New-Object System.Data.SqlClient.SqlCommand($spSelectServers,$sqlCon);

#Set the Command Type as a Stored Procedure
$sqlCommSR.CommandType = [System.Data.CommandType]::StoredProcedure;

#Open the SQL Connection

#Execute the Command
$sqlRdrSR = $sqlCommSR.ExecuteReader();

#Read Through the Returned Data
#This Specific Stored Procedure Returns a "Server_Name" Column
while ($sqlRdrSR.Read()) 
    $servers += $sqlRdrSR["Server_Name"].ToString().Trim();

##Close the SQL Reader and Connection

#Loop Through Array and Output Server Name
foreach($server in $servers)
    Write-Output $server;

No comments: