I would like to say a huge and warm thank you to all who attended my sessions for SQLSaturday#43 in Redmond. It was such a great event, and I hope you enjoyed the sessions – and enjoyed the whole day with all the wonderful speakers.

As promised here are the files for my sessions:
Read the rest of this entry »

I created a 2 part webcast on SQL Server and PowerShell last November/December, and I just wanted to re-post it in my site.

SQL Server PowerShell: Part I

SQL Server PowerShell: Part II

To view your SQL Server (or SSRS) reports using PowerShell, you can either use

• plain browser + URL string combo
• Report Viewer
• Web Services

The script below shows the first two options.
If you are going to go with Report Viewer (and personally, that’s my preference), you will need to download the ReportViewer redistributable package

• Report Viewer Redistributable 2005 SP1
• Report Viewer Redistributable 2008 SP1

One of these days I will hammer through the Web Services option, and also play more with what can be done with Report Viewer. And when I have good demos, I definitely will post them here

#==============================================================
#VANPASS August 2009
#PowerShell and SSRS (SQL Server Reporting Services)
#Donabel Santos
#http://www.sqlmusings.com / http://www.twitter.com/sqlbelle
#==============================================================
 
#--------------------------------------------------------------
#Approach 1: simplistic approach - accessing report via URL
#--------------------------------------------------------------
Set-Alias ie "$env:programfiles\Internet Explorer\iexplore.exe"
 
#note you can control how the report is rendered via the parameters in the URL string
ie "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReports%2fSample+Report&rs:Command=Render"
 
 
#--------------------------------------------------------------
#Approach 2: using the ReportViewer
#You need to download the ReportViewer redistributable package
#--------------------------------------------------------------
#I am qualifying this because I have more than one version in my system
#If you need webforms, use Microsoft.ReportViewer.WebForms
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
	Version=9.0.0.0, Culture=neutral, `
	PublicKeyToken=b03f5f7f11d50a3a")
 
#Windows.Forms for viewing dialog box
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
 
#System.IO because I want to export to Excel
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO")
 
#System.IO because I want to open Acrobat
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Diagnostics")
 
#for credentials, if needed
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
 
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ProcessingMode = "Remote";
$rv.ServerReport.ReportServerUrl = "http://localhost/reportserver";
$rv.ServerReport.ReportPath = "/Reports/Sample Report";
 
#if you need to provide basic credentials, use the following
#$rv.ServerReport.ReportServerCredentials.NetworkCredentials= 
#	New-Object System.Net.NetworkCredential("myuser", "mypassword");
 
$rv.Height = 600;
$rv.Width = 800;
$rv.RefreshReport();
 
#--------------------------------------------------------------
#Show as Dialog Using Windows Form
#--------------------------------------------------------------
#create a new form
$form = New-Object Windows.Forms.Form;
 
#we're going to make it just slightly bigger than 
$form.Height = 610;
$form.Width= 810;
$form.Controls.Add($rv);
$rv.Show();
$form.ShowDialog();
 
#--------------------------------------------------------------
#Export to Excel
#--------------------------------------------------------------
#now let's try exporting to Excel
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;
 
$bytes = $rv.ServerReport.Render("Excel", $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
				[ref] $extension, 
				[ref] $streamids, 
				[ref] $warnings);
$file = "C:\VANPASS\samplereport.xls";
$fileStream = New-Object System.IO.FileStream($file, 
              [System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
 
#let's open up our excel application
$excel = New-Object -comObject Excel.Application
$excel.visible = $true;
$excel.Workbooks.Open($file) | Out-Null;
 
#--------------------------------------------------------------
#Export to PDF
#--------------------------------------------------------------
$bytes = $rv.ServerReport.Render("PDF", $null, 
                                [ref] $mimeType, 
                                [ref] $encoding, 
				[ref] $extension, 
				[ref] $streamids, 
				[ref] $warnings);
$file2 = "C:\VANPASS\samplereport.pdf";
$fileStream = New-Object System.IO.FileStream($file2, 
             [System.IO.FileMode]::OpenOrCreate);
$fileStream.Write($bytes, 0, $bytes.Length);
$fileStream.Close();
[System.Diagnostics.Process]::Start($file2)
 
 
#--------------------------------------------------------------
#Other ways to render or access your reports:
#SSRS Web Services
#--------------------------------------------------------------

I did a presentation today for VANPASS on PowerShell and SQL Server – thank you to those who attended! It was a great lively crowd Thanks to Idera too – for the freebies – and to Black Ninja Software for the pizza and pop!

Here are the files as promised: VANPASS – SQL Server and PowerShell – Donabel Santos

The zipped file contains the presentation and all of the samples (ones that I demo’d, and ones I didn’t have time to demo). The demos are:

• Demo 01 – Common PowerShell Tasks – Complete.PS1
• Demo 02 – PowerShell Basics – Complete.PS1
• Demo 03a – SQL Server 2008 Mini Shell – Complete.PS1
• Demo 03b – Server and Database Properties.PS1
• Demo 03c – SQL Server Services and Jobs.PS1
• Demo 03d – Scripting SQL Server Objects.PS1
• Demo 03e – Searching for Objects.PS1
• Demo 03f – Basic DDL and DML Import.PS1
• Demo 03f – Basic DML.PS1
• Demo 03g – Backup and Restore.PS1
• Demo 04 – SQL Server PSX – Complete.PS1
• Demo 05 – SQL Server PSSnapin – Complete.PS1
• Demo – SSIS.PS1
• Demo – SSRS.PS1
• Out-Report from Chad Miller

I have used a lot of resources when I was creating all these samples, and I have tried to enumerate them in my presentation. And I also just realized I had a lot of samples and was only able to get through 2/3 of them.

I enjoy presenting at VANPASS, it is always a great crowd. Please feel free to drop me a line, or comment on my blog, or message me on twitter(sqlbelle) – if you have other SQL Server PowerShell questions, or if you had questions at the presentation that I wasn’t able to address.

Thanks to Richard Baumet and Scott Stauffer for inviting me to speak.
And thanks to Idera too – I love using your PowerShell Plus!

If you have the slightest interest in PowerShell, and you’re keen on learning more about it, I strongly recommend you download this book:

Mastering PowerShell (from PowerShell.com and written by Dr. Tobias Weltner)

It is an awesome, well written 567 page book that covers PowerShell through and through. Here are the chapters:

1. The PowerShell Console
2. Interactive PowerShell
3. Variables
4. Arrays and Hashtables
5. The PowerShell Pipeline
6. Using Objects
7. Conditions
8. Loops
9. Functions
10. Scripts
11. Finding and Avoiding Errors
12. Command Discovery and Scriptblocks
13. Text and Regular Expressions
14. XML
15. The File System
16. The Registry
17. Processes, Services, Event Logs
18. Windows Management Instrumentation
19. User Management
20. Your Own Cmdlets and Extensions

Thanks PowerShell.com!

I have posted previously 3 different ways of searching for SQL Server Objects (How to Search for Columns in SQL Server ).

Here’s a fourth one!

Check out how powerful and flexible PowerShell is when you need to look for a database object. In the script below, I only search databases, tables, columns, and indexes. But in reality, really, sky is the limit!
Read the rest of this entry »

Here is a simple script to audit your SQL Server Instance Properties.

Note that the property names are not hardcoded. We query each of these properties, and use those to display the property values.

If you prefer, you can also query directly each of the properties of an instance. If this is the case, just specify your server object and then the property name. For example:

$serverObject.BackupDirectory

The list of properties can be found at the end of the post.
Read the rest of this entry »

A follow-up to my previous post: How to Restore SQL Server Databases Using SMO and PowerShell

In this post I will show you how you can restore your database to :

1. the existing database (same database name)
2. a different database (different database name, different mdf and ldf)

As with the previous post, to do a SQL Server restore in SQL Server, you will need to use the SMO SqlBackup method. In SQL Server 2008, you will need to load Microsoft.SqlServer.SmoExtended assembly otherwise, you will get the following error:

Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type is loaded.

Other assemblies you may want to load are:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

Read the rest of this entry »