January 1, 2012 at 12:00 am #5224
by spoony123 at 2012-10-23 07:38:35
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
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.
You must be logged in to reply to this topic.