Powershell and SQL

by spoony123 at 2012-10-23 07:38:35

Hi There,

Do you guys have tips on using Powershell and SQL, i have only recently started using powershell and have been tasked with connecting to some remote SQL Express Databases (in the same domain), and running a select statement and returning results to an Excel Spreadsheet. I have found this code published - it would appear to do the job for the first part of the problem ie. making the connection and run a select statement, but would need to add more for the excel part.

Also would you advise Powershell remoting to do this? And do i need to add Powershell SQL Snap ins? Any advice or obvious gotchas would be good as this is quite a tall order for me with my lack of experience.

$SQLServer = "MySQLServer" #use Server\Instance for named SQL instances! $SQLDBName = "MyDBName"$SqlQuery = "select * from authors WHERE Name = ‘John Simon’"$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.CommandText = $SqlQuery$SqlCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet$SqlAdapter.Fill($DataSet) $SqlConnection.Close()clear$DataSet.Tables[0]
by spoony123 at 2012-10-23 07:46:52
Just to add i would need all the SQL extracts to go to one single excel file…
by spoony123 at 2012-12-01 12:27:27
I am just wondering if anyone had any nuggets of advice on this one…
by DonJ at 2012-12-01 13:18:12
There’s no need at all to use PowerShell remoting, no. The code you’ve got to query data is fine - that’s the correct way to do it. It uses native .NET functionality and doesn’t require any snap-ins. I did something similar in the "Accessing Databases" chapter in my "Learn PowerShell Toolmaking in a Month of Lunches" book.

In terms of building an Excel file… well, you’ve got two choices. The easy way would be to have each query’s results go into a CSV file, which Excel could open. If you need it to go into an actual XLS or XSLX file, that’s a lot harder, and it’s not something I can help you with, as I go out of my way to avoid screwing around with Excel’s automation object. If you’re looking for help with Excel, consider re-titling your post appropriately (right now it’s just "PowerShell & SQL" which is kind of vague and doesn’t mention the Excel bit). I did a bit of searching and found a few pages that might help you get started:

http://www.google.com/url?sa=t&rct=j&q= … 2VemI-5GGA

http://www.google.com/url?sa=t&rct=j&q= … a5xOY5Cjag

I just Googled "powershell excel com" - again, not a complete solution but it might help you learn what you need to learn in order to get started.

If I were tasked with this, I wouldn’t use PowerShell at all. I’d probably use SQL Server Integration Services (SSIS), which is built into the product and is wonderful at exporting query results to a huge variety of formats, including Excel. However, SSIS is a feature of the full SQL Server product, not Express. You’d need a full SQL Server edition on your network somewhere - it’s copy of SSIS could connect to your Express database and query data from it. It’s really a powerful tool, and offers graphical interfaces for building extractions and transformations.

(also… as a future tip for the forums, here, try not to leave replies to your own question. A lot of us look for "unanswered posts" as ones that need attention, but yours didn’t show up because you’d posted a reply within a few minutes of the original post. Hope that helps in the future!)
by spoony123 at 2012-12-29 02:53:06
Don - thanks ever so much. Some fantastic assitance here. Point noted about better titles in future posts. Thanks very much for your time on this one.