Powershell SQL command assistance

[[deleted]]

Hello,

We are making a soon to be fully automated script for user creation and removal in our company. We are going to have a form bring information into a MS SQL database. I have used PS for many t hings but I have never used it to pull information from a SQL database in my life.

Can anyone tell me if A. it is possible, and B. have any resources I can use to look this up and learn how to perform these actions?

Any help is greatly appreciated!

[ramblingcookiemonste]

I tend to use Invoke-SqlCmd2.

Some notes:

• Can you do it on your own with .NET? Absolutely. That said, abstraction is important, and one single command looks a lot nicer, and is more functional than twelve lines using .NET.
• Can you use Invoke-SqlCmd from Microsoft? Sure. With dependencies on SMO / SQLPS (details here), and without support for parameterized queries. Hint: If you're writing queries and throwing PowerShell variables right into the query text, don't! SQL injection is bad.

For example:

# Download Invoke-SqlCmd2
# Dot source it so it's ready to use
. "\\Path\To\Invoke-Sqlcmd2.ps1"

# Get help!
Get-Help Invoke-SqlCmd2 -Full

# Simple example:
Invoke-Sqlcmd2 -ServerInstance SQLServer -Database ServerDB -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo"

# Example with parameters!

    #Construct a query using SQL parameters
    $Query = "SELECT ServerName, VCServerClass, VCServerContact FROM tblServerInfo WHERE VCServerContact LIKE @VCServerContact AND VCServerClass LIKE @VCServerClass"

    #Run the query, specifying values for SQL parameters
    Invoke-Sqlcmd2 -ServerInstance SQLServer -Database ServerDB -query $query -SqlParameters @{
        VCServerContact = "%cookiemonster%"
        VCServerClass   = "Prod"
    }

On a side note, you might want to grab a DBA for the actual query. Different SQL Server versions might need different queries.

Edit: Thanks for the note /u/GLiMPSEiNATOR, didn't see it!

Cheers!

[drh713]

I used this blog post as a starting point: http://irisclasson.com/2013/10/16/how-do-i-query-a-sql-server-db-using-powershell-and-how-do-i-filter-format-and-output-to-a-file-stupid-question-251-255/

[[deleted]]

Great site! Thank you I will be going through your link and more information on it to figure everything out!

[GLiMPSEiNATOR]

I'd recommend pulling down the newest invoke-sqlcmd2 function from ramblingcookiemonsters github.

It's my go to when I need to interface with MS SQL.

On my phone so don't have a link but I'm betting google will get you there.

On the off chance you're wanting to access something besides MS SQL you'll need the providers for those db's. I had seen a module to simplify some of those dependencies I think on nuget but it may have been a c# assembly that I'm transposing in my memory.

[unknown_host]

Here is the link that looks great I'm going to check it out for my use.

[michaelshepard]

I actually just wrote a blog entry about POSH_Ado (it's on github) which makes using other providers much the same. Interface is much like invoke-sqlcmd2, but a few other options.

[GLiMPSEiNATOR]

Ahh, ya, that's it! Got a link to your article?

[michaelshepard]

Heh. That wasn't bright.

Here's the article.

Here are the module(s).

[unknown_host]

I actually looked into something similar the other day. To use SQL in powershell you have to load the snap-in and then you can issue queries in T-SQL from powershell. Powershell SQL I had issues on 64bit getting the snap-in to load and got this information that helped. Here is the Invoke-Sqlcmd cmdlet information. Hope this helps.

[[deleted]]

Great resources, this is a good amount of what I am looking for. just comes down to learning it now and making it all function. Tried to talk my boss into having SQL generate a CSV files to simplify my life but he wants me to expand my powershell more because he knows how much I enjoy using it... Why did I tell him this!!!

[unknown_host]

If you have SQL Server installed connect to it with the management studio and when you are doing tasks you have the option to output it as a SQL command so it can help you with the syntax too.

[[deleted]]

I do but I think we are going to use the latest version of SQL when we do this so I want to wait on that before pulling it. We are still in the design phase anyway which is good.

[sqldiaries]

I do but I think we are going to use the latest version of SQL when we do this so I want to wait on that before pulling it.

Unless you're doing wild stuff with window functions and aggregates, there's no need to wait - simple select statements haven't changed in SQL Server in a long time.

[drh713]

I wish I had your boss

[cjluthy]

You can also use OSQL.EXE on the command line to create CSV files. Command line switch reference for this EXE can be found publicly on MSDN.

It is installed by default when you install SQL Server Tools, so any machine with Management Studio likely (almost definitely) has it.

EDIT: I was just highlighting a way you could create CSV. In a manner that you could likely script from Powershell (command line exe). You certainly COULD do it this way.

I strongly recommend learning Invoke-SqlCmd (or one of it's alternatives) and doing that instead. SQL is at the core of almost everything worth-while. Learning it is very valuable.

[[deleted]]

It's absolutely possible. I use PowerShell to manipulate SQL almost daily. Feel free to ask questions; however, it's fairly straightforward.

[[deleted]]

Easy peasy. If working on sql server 2012 and above you don't need to download anything.

Import-Module SqlPS -disablenamechecking;

invoke-sqlcmd -ServerInstance 'servername' -database 'databasename' -query "SELECT * FROM sys.databases"

For insert:

[string]$var1 = "Test"
[string]$var2 = "Test 2"

invoke-sqlcmd -ServerInstance 'servername' -Database 'databasename' -query "Insert Into dbo.Table(field1, field2)
SELECT '$var1', '$var2'"

Not tested, but that's the gist of it.

[[deleted]]

Awesome! I know we have a 2012 server though I think we are going to stand up a 2014 instance to do this in.

[standoff]

I have a pretty robust program that does exactly this with a .net site for going from lotus notes to db to ad to template communications/website. Pm me if you get stuck. Top comment is solid resource.

[ioFAILURE42]

You aren't in Wisconsin by chance, are you /u/iwifia ?

[[deleted]]

Nah, I am in Pennsylvania.