1 PREMIER SPONSOR GOLD SPONSORS SILVER SPONSORS BRONZE SPONSORS SUPPORTERS
2 PowerShell, SQLCMD and SQLIt’s Easy When You Know How … Dave McMahon – Head of Data Platform, Ridgian (a RICOH Company) @mcmahond Welcome to SQL, SQLCMD and Powershell, which really should be entitled Powershell, SQLCMD and SQL, because that’s how we’re gogin to look at it.
3 Your Speaker’s Employer“We help Customers make sense of their business data by providing better Information, visualisation and Insights to drive effective business decisions, actions and outcomes”.
4 Business Intelligence WINDOWS AZURE & ON-PREMISEYour Speaker’s Employer SharePoint Custom Dev and Cloud Solutions / Products Business Intelligence and Data Platform SQL Server Analysis, Integration and Reporting Services PowerBI Excel PowerPivot Qlik Teradata (Barclays) Ridgian InformMe InformMe Engage Service Delivery Dashboards ResourceHub Time Management SharePoint Office 365 TermSet Point8020 Nintex Ridgian Engage Enterprise data centric Web Apps Responsive browser Apps Windows 8/10 native Apps iOS / Android Apps Microsoft Azure / Hybrid WINDOWS AZURE & ON-PREMISE 4
5 About Your Speaker I’ve not written any books, neither do I have a technical blog (well not any more), I don’t contribute to forums and I’m not an MVP (anymore). I have however spoken at TechEd Europe, at SharePoint Conferences in the UK and the US, at SQLBITs and at SQL Saturday and at multiple DDD events around the country and in Ireland. I was a co-founder of the NxtGen User group but retired from that a few years back. I basically am an IT consultant who is reasonably experienced in a number of areas from .NET and Web Development, SharePoint, Systems Management and SQL Server more recently. My speciality is in completing Discovery, Health Checks, Planning, Designing, Upgrading, Migrating and Implementing Standalone or High Availability Clusters of SQL Server. I enjoy speaking at events like these, but I’m most certainly not a “guru”, nor even an “expert” in my eyes, but I do have a bit of useful knowledge I’d like to share with you, and hope that it may be of help to you. If there is anything you’d like to get back to me on in my talk afterwards, or any questions you might have, then feel free to ask them as we go along, but I may respond with “I’ll take that offline with you” which means –either I don’t have a clue what you’re talking about OR I think I know what you’re asking me but the answer is too long to cover off right now.
6 PowerShell, SQLCMD and SQLIt’s Easy When You Know How … Welcome to SQL, SQLCMD and Powershell, which really should be entitled Powershell, SQLCMD and SQL, because that’s how we’re gogin to look at it.
7 Agenda The Why 3 Things about SQLCMD The Thing about Dynamic SQL7 Things about PowerShell 3 Things about SQLCMD The Thing about Dynamic SQL Putting It All Together
8 Agenda The Why 7 Things about PowerShell 3 Things about SQLCMDThe Thing about Dynamic SQL Putting It All Together
9 The Why Scripting Automation Common Language Faster Release
10 Agenda The Why 3 Things about SQLCMD The Thing about Dynamic SQL7 Things about PowerShell 3 Things about SQLCMD The Thing about Dynamic SQL Putting It All Together
11 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
12 PowerShell Naming Convention{Verb}-{Noun} Get-User Set-DnsClient Invoke-SqlCmd
13 Naming Convention get-command Cmdlet Write-Debug Microsoft...Cmdlet Write-DfsrHealthReport DFSR Cmdlet Write-DfsrPropagationReport DFSR Cmdlet Write-Error Microsoft... Cmdlet Write-EventLog Microsoft... Cmdlet Write-Host Microsoft... Cmdlet Write-Output Microsoft... Cmdlet Write-Progress Microsoft... Cmdlet Write-Verbose Microsoft... Cmdlet Write-Warning Microsoft... PS C:\Users\MyUsername>
14 Naming Convention get-command | Out-Host -PagingCommandType Name ModuleName Alias Add-AzureHDInsightConfigValues Azure Alias Add-AzureHDInsightMetastore Azure Alias Add-AzureHDInsightStorage Azure Alias Add-ProvisionedAppxPackage Dism Alias Add-VMToCluster FailoverC... Alias Add-WAPackEnvironment Azure Alias Add-WindowsFeature ServerMan... Alias Apply-WindowsUnattend Dism Alias Begin-WebCommitDelay WebAdmini...
15 Naming Convention get-command -Verb “Get” | Out-Host -PagingCommandType Name ModuleName Alias Get-PhysicalDiskSNV Storage Alias Get-ProvisionedAppxPackage Dism Function Get-AppBackgroundTask AppBackgroundTask Function Get-AppxLastError Appx Function Get-AppxLog Appx Function Get-AssignedAccess AssignedAccess Function Get-BCClientConfiguration BranchCache Function Get-BCStatus BranchCache Function Get-BitLockerVolume BitLocker
16 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
17 PowerShell - Getting Helpget-help TOPIC Windows PowerShell Help System SHORT DESCRIPTION Displays help about Windows PowerShell cmdlets and concepts. LONG DESCRIPTION Windows PowerShell Help describes Windows PowerShell cmdlets, functions, scripts, and modules, and explains concepts, including the elements of the Windows PowerShell language. Windows PowerShell does not include help files, but you can read the help topics online, or use the Update-Help cmdlet to download help files…
18 PowerShell Getting Helpget-help get-service NAME Get-Service SYNTAX Get-Service [[-Name]
19 PowerShell Getting Helpget-help get-service -Full … Get-Service [-ComputerName
20 PowerShell Getting Helpget-help get-service -Examples NAME Get-Service ALIASES gsv REMARKS Get-Help cannot find the Help files for this cmdlet on this computer. It is displaying only partial help. -- To download and install Help files for the module that includes this cmdlet, use Update-Help. -- To view the Help topic for this cmdlet online, type: "Get-Help Get-Service -Online" or go to
21 PowerShell Getting Helpget-help get-service -Examples NAME Get-Service ALIASES gsv REMARKS Get-Help cannot find the Help files for this cmdlet on this computer. It is displaying only partial help. -- To download and install Help files for the module that includes this cmdlet, use Update-Help. -- To view the Help topic for this cmdlet online, type: "Get-Help Get-Service -Online" or go to
22 PowerShell Getting Helpget-help get-service -Online
23 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
24 PowerShell Declaring Variables$myServer = hostname PS C:\Users\MyUsername> $myServer MYSQLSERVER PS C:\Users\MyUsername>
25 PowerShell Declaring Variables$myServer = hostname PS C:\Users\MyUsername> $MYSERVER MYSQLSERVER PS C:\Users\MyUsername> Variable names etc. are not Case Sensitive, but like all programming languages, it is best to stick to a code standard. I like Pascal naming convention. First letter lower case, and first letter of included words are capitalised.
26 PowerShell Declaring Variables$services = get-service PS C:\Users\MyUsername> $services Status Name DisplayName Running AdobeARMservice Adobe Acrobat Update Service Stopped AeLookupSvc Application Experience Stopped ALG Application Layer Gateway Service Running AppHostSvc Application Host Helper Service Stopped AppIDSvc Application Identity Running Appinfo Application Information Running AppMgmt Application Management Stopped aspnet_state ASP.NET State Service … PS C:\Users\MyUsername> Variables can hold objects too, for example the list of services on a machine
27 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
28 PowerShell Collections$services = get-service | where {$_.Name –eq ‘ALG’} PS C:\Users\MyUsername> $services Status Name DisplayName Stopped ALG Application Layer Gateway Service PS C:\Users\MyUsername> Variables can hold objects too, for example the list of services on a machine
29 PowerShell Collections$services = get-service | where {$_.Name –eq ‘ALG’} Variables can hold objects too, for example the list of services on a machine Pipe Filter Enumerator Property Operator Comparison
30 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
31 PowerShell Functions and Parametersget-service –DisplayName “Application Layer Gateway Service” Status Name DisplayName Stopped ALG Application Layer Gateway Service PS C:\Users\MyUsername> Variables can hold objects too, for example the list of services on a machine
32 Function Parameters PowerShell Functions and Parametersget-service –DisplayName “Application Layer Gateway Service” Parameter Indicator Parameter Name Space Parameter Value
33 Except …
34 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
35 PowerShell Boolean Valuesget-service –RequiredServices:$true Status Name DisplayName Running RpcSs Remote Procedure Call (RPC) Running CryptSvc Cryptographic Services Stopped AppID AppID Driver Running ProfSvc User Profile Service Running rpcss Remote Procedure Call (RPC) Running MMCSS Multimedia Class Scheduler … PS C:\Users\MyUsername> Variables can hold objects too, for example the list of services on a machine
36 “Boolean” Parameters PowerShell Boolean Valuesget-service –RequiredServices:$true No need to provide a parameter value for switch parameters Parameter Indicator Parameter Name Colon $true or $false
37 “Switch” Parameters PowerShell Boolean Valuesget-service –RequiredServices No need to provide a parameter value for switch parameters Parameter Indicator Parameter Name Only
38 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
39 Notepad++ https://notepad-plus-plus.org/ PowerGui http://powergui.orgScripting Notepad Notepad++ https://notepad-plus-plus.org/ PowerShell ISE PowerGui
40
41 Run
42
43
44 Run
45 Result
46 7 Things about PowerShellNaming Convention Getting Help Variables Collections Functions and Parameters Boolean Values Scripting
47 Agenda The Why 7 Things about PowerShell 3 Things about SQLCMDThe Thing about Dynamic SQL Putting It All Together
48 Command Line SQL Server Management Studio Invoke-Sqlcmd3 Things about SQLCMD Command Line SQL Server Management Studio Invoke-Sqlcmd
49 SQLCMD Command Line sqlcmd 1> select getdate() as Now 2> go Now :20:22.263 > By default sqlcmd uses Windows Auth and connects to the default instance on the machine. This will only work if Shared Memory Protocol is enabled and you are running sqlcmd on the actual SQL Server machine.
50 SQLCMD Command Line sqlcmd -S MYSQLSERVER -E 1>select getdate() as Now 2> go Now :20:22.263 1> Normally can connect to serves using TCP/IP
51 SQLCMD Command Line sqlcmd -S MYSQLSERVER -E 1>SELECT counter_name,cntr_value FROM sys.dm_os_performance_counters 2> WHERE [counter_name] = 'Batch Requests/sec' 3> GO counter_name cntr_value Batch Requests/sec 30876 (1 rows affected) 1> Can execute SQL statements after issuing a GO statement
52 SQLCMD Command Line sqlcmd -S MYSQLSERVER -E 1>SELECT CAST(counter_name AS VARCHAR(25)) AS counter_Name 2> ,CAST(cntr_value AS INT) AS cntr_value 3> FROM sys.dm_os_performance_counters 4> WHERE [counter_name] = 'Batch Requests/sec' 5> go counter_name cntr_value Batch Requests/sec (1 rows affected) 1> Normally connect via TCP/IP
53 SQLCMD Command Line sqlcmd -S MYSQLSERVER –E –o myresults.txt 1>SELECT CAST(counter_name AS VARCHAR(25)) AS counter_Name 2> ,CAST(cntr_value AS INT) AS cntr_value 3> FROM sys.os_dm_performance_counters 4> WHERE [counter_name] = 'Batch Requests/sec' 5> go 1> So by providing the –o and a file name we have created the output file. Subsequent runs will append the results to the file for that session. A new session will overwrite the entire file.
54 SQLCMD Command Line sqlcmd -S MYSQLSERVER –E –o myresults.txt 1>:setvar servername SERVERPROPERTY(‘MachineName’) 1> SELECT CAST($(servername) AS VARCHAR(25)) AS [ServerName] 2> GO 1> :connect MYSQLSERVER2 Sqlcmd: Successfully connected to server 'MYSQLSERVER2'. 1> :setvar servername SERVERPROPERTY(‘MachineName’) 2> SELECT CAST($(servername) AS VARCHAR(25)) AS [Server2Name] 3> GO Can use the : to declare variables using setvar or do an action like :connect to connect to a different instance. 1>
55 SQLCMD Command Line sqlcmd -S MYSQLSERVER –E –h -1 –o myresults.txt1> SET NOCOUNT ON 2> SELECT CAST(counter_name AS VARCHAR(25)) AS counter_Name 3> ,CAST(cntr_value AS INT) AS cntr_value 4> FROM sys.dm_os_performance_counters 5> WHERE [counter_name] = 'Batch Requests/sec' 6> go 1> The –h option removes the headers and
56 Command Line SQL Server Management Studio Invoke-Sqlcmd3 Things about SQLCMD Command Line SQL Server Management Studio Invoke-Sqlcmd
57
58
59
60 Command Line SQL Server Management Studio Invoke-Sqlcmd3 Things about SQLCMD Command Line SQL Server Management Studio Invoke-Sqlcmd
61
62
63
64
65 Run
66
67
68 Run
69
70
71 Command Line SQL Server Management Studio Invoke-Sqlcmd3 Things about SQLCMD Command Line SQL Server Management Studio Invoke-Sqlcmd
72 Agenda The Why 7 Things about PowerShell 3 Things about SQLCMDThe Thing about Dynamic SQL Putting It All Together
73 The Thing about Dynamic SQLIf you've been told "don't ever use dynamic SQL" please ... that is a myth put out by our .NET developer friends, who's only contact with SQL is SELECT TOP 100 PERCENT * from dbo.Customer . So engage brain and remind yourself, that the only absolute thing in the universe is the speed of light in a vacuum, everything else is relative and that includes the use of dynamic SQL. In development/application scenarios I **totally** subscribe to the view that you should make every effort to eliminate Dynamic SQL through use of stored procedures, inline views, sp_executesql et al. However , if you are an admin, working in SSMS or a consultant also working in SSMS, please get real and make sure your dynamic SQL is up to scratch, plus the use of cursors and of sp_executesql. That's the 1 things about SQL you need.
74 The Thing about Dynamic SQL
75
76 Agenda The Why 7 Things about PowerShell 3 Things about SQLCMDThe Thing about Dynamic SQL Putting It All Together
77
78
79 Agenda The Why 7 Things about PowerShell 3 Things about SQLCMDThe Thing about Dynamic SQL Putting It All Together
80 Resources https://www.simple-talk.com/dotnet/net-framework/powershell-for- sharepoint-developers/ (Article on PowerShell basics) https://www.microsoft.com/en-us/download/details.aspx?id= (SQL Server 2016 Feature Pack)
81 PowerShell, SQLCMD and SQLIt’s Easy When You Know How … Dave McMahon – Head of Data Platform, Ridgian (a RICOH Company) @mcmahond Welcome to SQL, SQLCMD and Powershell, which really should be entitled Powershell, SQLCMD and SQL, because that’s how we’re gogin to look at it.
82 Please give us your feedback:sqlrelay.co.uk/feedback Thank you