Category: SQL Server

Go and get it.  Of course, No SQLServer PowerShell update on this hotfix. But you’ll need this update.

Read more on this link for more information: https://msdn.microsoft.com/en-us/library/mt238290.aspx

If you are still interested of see what’s new in this July Edition of SSMS, check the following Microsoft Blog site:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

 

Sometimes there will be a need to provide a scripting solution to connect to SQL Server to execute either a TSQL query or Stored-Procedure without the need of installing a SQL Server bits.  Here I’m providing two code snippets that gets the job done without the use of SMO (SQL Server Management Object) class.

Executing a T-SQL Query

Use the System.Data.SqlClient namespace from the .NET Framework Data Provider for SQL Server in order to build functions to execute T-SQL statement(s) and/or SQL Stored-Procedure with PowerShell.

Here’s an example in how to execute a T-SQL statement querying a table in its basic form:

[sourcecode language=”powershell”]
## – Set PowerShell variables:
$ConnectionString = ‘server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!’;
$TSQLQuery = "Select * from AdventureWorks2014.dbo.AWBuildVersion;";

## – Connect and Execute Stored-Procedure:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery, $ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
[/sourcecode]

 

Executing a T-SQL Stored-Procedure

Now the code for executing SQL Stored-Procedure will be differect as we are going to use the ‘.sqlCommand‘ class to process the T-SQL Statement to run a custom table update procedure in its basic form:

[sourcecode language=”powershell”]
## – Set PowerShell variables:
$ConnectionString = ‘server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!’;
$TSQLQuery = "Exec [AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion];";

## – Connect and Execute Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open()
$sqlCmd = New-Object Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
$sqlCon.close();
[/sourcecode]

I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.

After executing the non-SMO code then the record did change:

Most important, when using the Data.SqlClient.SqlConnection class, the connection need be ‘.Close()‘ after executing the T-SQL command.

Error Handling

As we are connecting to the SQL Server to execute T-SQL Commands, its important to add some error handling routine to trap errors during the connectivity and/or T-SQL Statement(s) processing.  And, we do this by evolving the basic PowerShell code into a function.

Lets create two functions that will accept two parameters: a connection string, and the T-SQL Query.

* Get-TSQLQuery function

[sourcecode language=”powershell”]
function Get-TSQLQuery
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)
Try
{
## – Non_SMO Get SQL query:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery,

$ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
}
Catch
{
Write-Verbose "Error executing T-SQL Query: `r`n[$($error[0])]" -Verbose;
};
};

[/sourcecode]

To test this function copy/paste the code into your PowerShell console, and the execute the following commands:

[sourcecode language=”powershell”]
## – Set PowerShell variables:
$conn = ‘server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!’;
$tsql = "Select * from AdventureWorks2014.dbo.AWBuildVersion";

## – Execute the function with its parameters:
(Get-TSQLQuery -ConnectionString $conn -TSQLQuery $tsql) `
| Select-Object `
SystemInformationID, ‘Database Version’, VersionDate, ModifiedDate `
| Format-Table -AutoSize;
[/sourcecode]

* Execute-TSQLStoredProc function

[sourcecode language=”powershell”]
function Execute-TSQLStoredProc
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)

Try
{
## – NON-SMO executing TSQL Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open();
$sqlCmd = New-Object Data.SqlClient.SqlCommand;
$sqlCmd.Connection = $sqlCon;
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
}
Catch
{
Write-Verbose "Error executing T-SQL Stored-Procedure: `r`n[$($error[0])]"

-Verbose;
}; $sqlCon.close();

};

## – Set PowerShell variables:
$Conn = ‘server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!’;
$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";

## – Execute the function with its parameters:
Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;
[/sourcecode]

Adding the Try/Catch error handling block helps is trapping and displaying the errors.

This method serves as another alternative to connect to a SQL Server without the need to install an instance on a system.

 

 

Yes! This is month you could learn PowerShell and SQL Server Management Object (SMO) techniques to start building script(s) to manage your SQL Server(s).

I will be speaking at the following events:

1. South Florida SQL Saturday #379 on 06/13/2015 all day event. Session: “PowerShell with Visual Studio SQL Data Tools” (03:00PM – 04:15 PM) Register at:  http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

2. IDERA’s Geek Sync Webinar – “PowerShell Essentials using SQL Server SMO” on 06/16/2015 at 10:00 AM CT (11:00 AM EST). Register at: https://attendee.gotowebinar.com/register/5998631242300195841

At the SQLSaturday event there will some giveaways during my session.  Go ahead and register to any of these events.  It’s Free!

Now that we got the connection to the server there are a couple of lines we could include to avoid connection timeout during execution of our T-SQL script using SMO with PowerShell. So, in this blog will be covering executing a T-SQL script and viewing its results.

The *T-SQL script will build me table with data statistics about my selected Database indexes need to either Reorganize, or Rebuild.

*Note: The following T-SQL script modified from its original “Index optimization – REBUILD vs. REORGANIZE” by Author Sarjen Haque. It can be found at http://sqltouch.blogspot.com/2013/07/index-optimization-rebuild-and.html

[sourcecode language=”powershell”]
$TSQLqry1 = @"
if object_id(‘$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]’) is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
‘$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]’ as [IdxFragStatDatabase] ,
‘$($DatabaseName)’ as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then ‘RELAX’
when dmv.avg_fragmentation_in_percent <= 30 then ‘REORGANIZE’ when dmv.avg_fragmentation_in_percent > 30 then ‘REBUILD’
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N’LIMITED’) dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, ‘ISMSShipped’) = 0
and (i.name is not Null and i.type_desc <> ‘HEAP’)
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

[/sourcecode]

Notice that PowerShell will substitute the values for $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList] within the Here-String @” .. “@. It’s important to notice when using Here-String TABS are not allow at the end of ‘@”;’ or you’ll get an error.

Back to the connection section. We need to include the following just after the ‘$MySQL = New-Object $MySQL = new-object Microsoft.SqlServer.Management.Smo.Server …‘:

$MySQL.ConnectionContext.ConnectTimeout = 21600;   (This is an example (optional) set to 21600 sec = 6hrs)
$MySQL.ConnectionContext.StatementTimeout = 0;   (This is an example set to 0 for no timeout when running T-SQL queries)

Here’s the sample for the code placement:

[sourcecode language=”powershell”]
## – Connect and Execute T-SQL script:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

[/sourcecode]

If you don’t use the ‘ConnectionContext.StatementTimeout’ you’ll get a timeout error after 10 minutes of execution.

Now, we proceed to create our variables use for the T-SQL script:

[sourcecode language=”powershell”]
[string] $SQLServerInstanceName = ‘.’
[string] $DatabaseName = ‘AdventureWorks2014’
[string] $tblSchema = ‘dbo’
[string] $IdxStatDatabase = ‘devMaxTest’

[/sourcecode]

The purpose of the $IdxStatDatabase is to redirect the data index stat report to another Database. Then, we use the following line to execute our T-SQL script using SMO with PowerShell.

[sourcecode language=”powershell”]
$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);

[/sourcecode]

After the PowerShell script execute, open SQL Server Management Studio (SSMS), go to the database where the index stat data is stored, and query the table to view results.

This report will help you identify those indexes that need to be taken care off.

Full sample script below:

[sourcecode language=”powershell”]
## – Set variables for T-SQL script:
$SQLServerInstanceName = ‘.’;
$DatabaseName = "AdventureWorks2014";
$tblSchema = "dbo";
$IdxStatDatabase = "devMaxTest";

## – Build the T-SQL script for execution:
$TSQLqry1 = @"
if object_id(‘$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]’) is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
‘$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]’ as [IdxFragStatDatabase] ,
‘$($DatabaseName)’ as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then ‘RELAX’
when dmv.avg_fragmentation_in_percent <= 30 then ‘REORGANIZE’ when dmv.avg_fragmentation_in_percent > 30 then ‘REBUILD’
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N’LIMITED’) dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, ‘ISMSShipped’) = 0
and (i.name is not Null and i.type_desc <> ‘HEAP’)
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

## – Connect, set timeout values:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

## – Execute T-SQL script:
$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);
Write-Host "End of Script";

[/sourcecode]

Using SQL Server Management Object .NET class can assist in extract information about your SQL Server engine.  This is a sample PowerShell script code using SMO to extract database tables information and display it in the PowerShell console;

[sourcecode language=”powershell”]
## – SMO query for Tables information within the database:
## —

## – Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;

## – Setting variables with Server and Database name:
$SQLInstanceName = ‘WIN81ENT01’; $Global:SourcedbName = ‘AdventureWorks2014’;

## – Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName

## – Uncomment code block within #region – #endregion to enable use of SQL Authentication:
#region – Changing from default Windows to SQL Authentication:

#   ## – Variables required to SQL UserID and SQLPassword:
# $SQLUserName = “YourSQLUserID”; $sqlPwd = “YourSQLUSerPassword”;
#
#   ## – Block of Code required to build SQL Authentication:
# $MySQL.ConnectionContext.LoginSecure = $false;
# $MySQL.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

#endregion

## – Create PSObject with all database table information:
$dbtables = $MySQL.Databases[$Global:SourcedbName].tables;

## – End of Script
[/sourcecode]

Notice that in the code for connecting to SQL Server using SQL Authentication is included.  So, to enabled it, just uncomment the block of code, then provide the SQL UserName and Password.

After you have created the psObject with the database table information, we can proceed to display it on console using the Out-Gridview cmdlet.

[sourcecode language=”powershell”]

## 1 – Display Table Information on a GridView:
$dbtables | Select-Object `
@{ Label = ‘SQLServerName’; Expression = { ($SQLInstanceName) }; }, `
@{ Label = ‘DatabaseName’; Expression = { ($_.Parent) }; }, `
@{ Label = ‘TableName’; Expression = { ($_.Name) }; },
@{ Label = ‘DataSpaceUsed(KB)’; Expression = { ($_.dataspaceused) }; }, `
@{ Label = ‘IndexSpaceUsed(KB)’; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{
Label = “LastTableUpdate”; Expression = { `
$sqlupdtbl = @”
Select
–object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID(‘$($Global:SourcedbName)’)
and Object_ID = Object_id(‘$($_.Name)’)
“@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update;
}} | Out-GridView `
-Title “Display Database: $SourcedbName Tables Information”;

[/sourcecode]

Also, it’s possible to export the psobject information to a *.csv file format for later viewing using the Export-csv cmdlet.
[sourcecode language=”powershell”]

## 2 – Create csv file:
$dbtables | Select-Object `
@{ Label = ‘SQLServerName’; Expression = { ($SQLInstanceName) }; }, `
@{ Label = ‘DatabaseName’; Expression = { ($_.Parent) }; }, `
@{ Label = ‘TableName’; Expression = { ($_.Name) }; },
@{ Label = ‘DataSpaceUsed(KB)’; Expression = { ($_.dataspaceused) }; }, `
@{ Label = ‘IndexSpaceUsed(KB)’; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{ Label = “LastTableUpdate”; Expression = { `
$sqlupdtbl = @”
Select
–object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID(‘$($Global:SourcedbName)’)
and Object_ID = Object_id(‘$($_.Name)’)
“@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update; }} `
| Export-Csv -NoClobber -NoTypeInformation `
-Path “C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv”;

## 3 – Open file:
Invoke-Item “C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv”;

[/sourcecode]

Keep in mind, in order to use load and use the “Microsoft.SQLServer.Smo” assembly, you must had already install at least the SQL Server Management Studio or only using it’s individual components (http://www.maxtblog.com/2012/09/create-powershell-smo-scripts-without-installing-sql-server/).

For more information about SQL Server SMO check my article at:
http://sqlmag.com/powershell/using-sql-server-management-objects-powershell