If the results of a query from a SQL Server database are required, the default 'SQL Server Management Studio' administration tool has a number of shortcomings that prevent the results being returned in a readily usable format. This article documents how to obtain the results required by our support team.
The results required by support, would be
- with the field headers
- ideally comma separated, as opposed to tab or semi-colon
- rows separated as new lines
- with quotes around each field if the field contains either the field separator or the row separator
However, it is also possible using a Microsoft tool: Powershell for SQL Server.
This may have been installed as part of SQL Server but if not, this can be found on Microsoft's Download site. Links are provided below, if known. If the version doesn't show for your SQL Server installation, please refer to Google or Microsoft's site.
SQL Server 2008 R2 Feature Pack: http://www.microsoft.com/en-us/download/details.aspx?id=16978#PowerShell
Expand Details and look for Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2
Install that on the SQL Server and open up PowerShell from the start menu:
These commands enable the SQL Server Powershell commands for the session.
To then obtain the query results run the following where dbname is the name of the database and the part in quotes is the query and the last part is the filename:
Invoke-SqlCmd -Database dbname -Query "SELECT * FROM INCIDENT WHERE CASE_ID='123456'" | Export-Csv INCIDENT123456.csv
This will run the query as the user running the command prompt. If the logged in user does not hae access to the database add in these arguments to the command, immediately after Invoke-SqlCmd where un and pw are the username and password that does have access:
-Username un -Password pw
In addition if there are multiple instances or this is not running on the server itself then add the following arguments immediately after Invoke-SqlCmd, as required, where 126.96.36.199 is the IP or hostname and instancename is the Instance in question:
The resulting file will be saved in the path the powershell is set to, if it is not set with the filename, which by default is the user's folder, eg. C:\Users\Administrator\