0

I need to capture the result of MSSQL Queries in Ansible output however, there are no supported Ansible modules which help me to achieve this. As a workaround, i have written a powershell script which runs the SQL query and returns the output in a weird format with lots of unnecessary data which i would like to remove.

PowerShell Script

$ServerInstance = 'serverhostname'
$Database = 'master'
$Query = "DECLARE @sqlVers numeric(2,0)
DECLARE @compatlevel numeric(3,0)
SET @sqlVers = left(cast(serverproperty('productversion') as varchar), 2) 
SET @compatlevel=@sqlVers*10
SELECT name,compatibility_level
FROM sys.databases WHERE compatibility_level<>@compatlevel"
$SqlcmdOptions = @"
-S"$ServerInstance" -d "$Database" -Q "$Query"
"@
Start-Process -FilePath "SQLCMD.EXE" -ArgumentList @" 

$SqlcmdOptions
"@ -Wait -NoNewWindow -RedirectStandardOutput C:\temp\SQLCOMPLVL.txt -PassThru
Get-Content C:\temp\SQLCOMPLVL.txt

Powershell script output:

enter image description here

How can format the above either through Powershell script or in ansible in such a way that i get only the data in the following way: name comptibility_level data value

or in short, i could get data between name and (x rows affected) and could convert it into JSON format so that Ansible could collect powershell script output in JSON format.

Ankit Vashistha
  • 101
  • 1
    Try with removed `-PassThru` which generates the unanted output. – LotPings Nov 23 '18 at 17:01
  • @LotPings: Thank you, that one worked. How can i translate this output to JSON now? I tried the following but getting errors: $result = Get-Content C:\temp\SQLCOMPLVL.txt ConvertTo-Json $result ConvertTo-Json : The converted JSON string is in bad format. At line:17 char:1 + ConvertTo-Json $result + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (System.Object[]:Object[]) [ConvertTo-Json], InvalidOperationException + FullyQualifiedErrorId : JsonStringInBadFormat,Microsoft.PowerShell.Commands.ConvertToJsonCommand – Ankit Vashistha Nov 23 '18 at 17:06

2 Answers2

0

To much for a comment, so

Redirecting to standard output you are destroying possibly existing objects by stringifying them.

$ServerInstance = 'serverhostname'
$Database = 'master'

$Query = "DECLARE @sqlVers numeric(2,0)
DECLARE @compatlevel numeric(3,0)
SET @sqlVers = left(cast(serverproperty('productversion') as varchar), 2) 
SET @compatlevel=@sqlVers*10
SELECT name,compatibility_level
FROM sys.databases WHERE compatibility_level<>@compatlevel"

$SqlcmdOptions = @"
-S"$ServerInstance" -d "$Database" -Q "$Query"
"@

Start-Process -FilePath "SQLCMD.EXE" -ArgumentList @" 

$SqlcmdOptions
"@ -Wait | Get-Member

See what Get-Member returns

LotPings
  • 1,015
  • 7
  • 12
  • Not sure if it is missing something. I am getting syntax error. Get-Member : You must specify an object for the Get-Member cmdlet. At line:15 char:12 + "@ -Wait | Get-Member + ~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Get-Member], InvalidOperationException + FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand – Ankit Vashistha Nov 23 '18 at 17:39
  • So it's clear there are no objects and you'll have to parse text output and work with that. – LotPings Nov 23 '18 at 18:08
0

You can use SQLPS Module.

Example:

 Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "MyComputer\MainInstance"

TimeOfQuery

9/21/2017 2:48:24 PM

Check the below links: -

Import the SQLPSModule

SQL Server cmdlets

SQLPS cmdlets

Invoke-SQLCMD

Rajiv Iyer
  • 157
  • 9