Providing database query results for SQL Server users

Version 1

    Details

    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.


    Resolution

    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
    A 3rd party tool, such as Navicat, can export in this format. If a 3rd party tool is available that can do this, then this would probably be the easiest choice.

    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:

    Run:
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100

    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 123.123.123.123 is the IP or hostname and instancename is the Instance in question:
    -HostName 123.123.123.123
    -ServiceInstance instancename

    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\