Hi Folks,
I’ve made a few false starts over the years in attempting to switch from vbScript to PowerShell. I’m in a place where tight reigns on IT spend prevented us from moving from Windows 2003 DCs until now that they are end of support.
So I’m going to finally switch by recoding an SCCM-like tool I wrote in vbScript/ASP/SQL to Powershell/?/SQL.
Learning vbScript meant you knew ASP. With Powershell I don’t think that’s the case. I assume ASP.NET is the answer. If so is there any common ground with PowerShell? Will I need to learn two languages?
Thanks.
Michael
The most important thing to remember, is PowerShell is a interactive command line shell. So to learn it, start with the shell, and not with scripting.
I remember when I started learning it; I did it in the wrong way. I didn’t try to discover the shell. I jumped immediately into scripting.
This is the wrong approach. Start with the basics.
For example, try
Get-Process
Then try:
Get-Help Get-Process
Then try:
Get-Help Get-Process -examples
PowerShell let’s you discover itself. But only when master a few basics.
These basics are described in the following book: Learn PowerShell 3 in a month of lunches, 2nd Edition
You don’t need to learn two languages, PowerShell is the only one you need to know. Of course it helps when you are proficient in a programming language like C#. This is not mandatory to learn PowerShell. My advice, learn the basics, and learn them well. Advanced stuff will be a result of knowing the basics.
Thanks Richard, I bought the Don Jones book about a year ago. I’m back on it again after getting stalled. I need to get some practical ‘hands on’ now I guess.
I wanted to set myself a challenge, to gather inventory information from machines on my domain and send this back to a SQL database and query it via a web page. From what I gather you’re saying I’m trying to run before I can walk.
However, even if this might be too much to accomplish for a beginner can anyone give me an overview of how Powershell can be used to write to SQL? I’ve read about invoke-sqlCmd but it looks a bit clumsy.
Thanks,
Michael
Hi Michael,
You can also use .NET if you want to perform SQL operations. I’ve put an example below, which I use from time to time for getting sql version information. Take a look at that, and I’d suggest referring to the MSDN documentation on the system.data namespace for more information about the specific things you want to do.
function Invoke-SQLQuery
{
[CmdletBinding()]
[OutputType([psobject])]
param
(
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Query')] [string[]] $Query,
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Database')] [string] $Database = 'master',
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, HelpMessage = 'Instance')] [string] $SQLServer
)
Process
{
$ErrorAction = 'Stop'
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$($SQLServer);Database=`'$($Database)`';Integrated Security=SSPI;"
$SqlConnection.Open()
ForEach ($qry in $Query) {
$SqlCmd = New-Object -TypeName System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $qry
$sqlcmd.Connection = $SqlConnection
$SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object -TypeName System.Data.DataSet
Try {
$nSet = $SqlAdapter.Fill($DataSet)
}
Catch {
}
}
$SqlConnection.Close()
$Tables = $DataSet.Tables
$Tables
}
}
$SQLquery = @'
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
'@
Invoke-SQLQuery -Query $SQLquery -Instance 'myserver\inst1'
Hello!
Ed’s recent scripting guy post is quite on target here. At the very least, try to break things down into a series of solutions, rather than one monolithic script.
If you’ve been working with languages a while, you might be fine. If not, this could be tough. Take a peak at ASP.NET backed by C# on the web side. It’s a big bite to take for a yak shaving exercise, but it sounds like the direction you might be looking for.
Lastly, on a side note, I tend to use Invoke-SqlCmd2 for SQL queries in PowerShell. Started by Chad Miller, with contributions from the community, it gives a bit more flexibility than Invoke-SqlCmd, including parameterized queries (how the heck did MSFT overlook this?), without any pre-requisite install.
Good luck!
Here is a module, OneSql, I recently wrote to execute SQL queries against any relational databases.
Thanks that’s what I was after and there’s some good options there.
I’ve written tools for SCCM OSD in C#\ASP.NET and it is a far, far, far cry from ASP. There are typically benefits to web interfaces since you can give someone a GUI and your web server and data sources are all in a datacenter. The problem is I’m a sys admin, not a web developer, so I was the only person that had the skillset to write and maintain the code. If you write it in Powershell with a GUI interface, you might be making queries across the LAN\WAN, but you have something that more than just yourself can support. When you leave the company or move to another team or management, is it going to be easier to find a SCCM person that knows powershell or a SCCM person that knows ASP.NET, C#, JQuery, JavaScript, n-Tier, CSS, HTML, LINQ, SQL…
There are COTS solutions like RES Software ITStore that interface with SCCM and provide a web-based user interface, but from my past experience I would think about supportability for the tool.
Since you are going to PowerShell I advise to stick with .NET programming. I know it is daunting for a Sys Admin to develop a Web Forms app. I would consider trying out Visual Studio Lightswitch https://msdn.microsoft.com/en-US/vstudio/ff796201
This will give you an easy onboarding to write simple CRUD app against the SQL Data Store you are using PowerShell with.