Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to variable - not wanting table style result Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 177, Visits: 145
Hi

I am trying to run a script that queries the last full backup file for a specific database. Invoke line formated for ease of reading.

# Script is ran on the SQL server (not remotely)
# Instance is called sql-instance
# database is called sql-data

import-module SQLPS -disableNameChecking
$instanceName = "$($env:computername)\sql-instance"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$lastBackupFile = Invoke-Sqlcmd -Query "
SELECT TOP 1 msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D' and msdb.dbo.backupset.database_name = 'sql-data'
ORDER BY msdb.dbo.backupset.backup_finish_date desc"


The result I get with this is in a table format:
physical_device_name
--------------------
C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSQL\Backup\full_sql-data_20121101.bak


Is there any way to just get the result and not the heading stuff?
I need just the path and filename for using elsewhere in the script.

I tried searching around but only found articles that give the table format and I don't know enouth to trim this down with confidence.

Thanks in advance
Post #1392510
Posted Tuesday, December 4, 2012 10:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,003, Visits: 3,408
If it's one result, you can get it like this:
$lastBackupFile.physical_device_name

This can be used in other methods, e.g.:

($lastBackupFile.physical_device_name).SubString(0, 30)

If it's multiple rows you can do something similar but include the row number:
$lastBackupFile[0].physical_device_name
Post #1392609
Posted Wednesday, December 5, 2012 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 177, Visits: 145
Many thanks for that.

It must seem like common sense to you but I didn't realize that queries would actually return results with members that can be seperated. This opens up a world of possibility and extra knowledge for me.

Query resolved.
Post #1392850
Posted Wednesday, December 5, 2012 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,003, Visits: 3,408
No problem.
Still getting to grips with Powershell and very little of it is common sense so far

The hardest bit has been linking all the individual steps together - once you start seeing how you can pass info from one step to the next it really does become an amazing tool!

Cheers
Gaz
Post #1392875
Posted Monday, June 24, 2013 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:57 PM
Points: 44, Visits: 175
This is the closest I've come to finding a topic on what I'm trying to do. I'm setting a PS variable equal to the results from a query that returns two values per row and there will be multiple rows returned. Here is the PS code:


$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"
$Database = SQLCMD -S "server" -U "User" -P "password" -Q $sql


Using the information above I figured out that $Database[2] would give me my first result row since 0 and 1 were the headers for the result set. I'm trying to use the results in a FOREACH. I'd like to put the DBName in one variable and the SFTPFolder in a different variable within the FOREACH. I tried $Database[2].DBName but it doesn't return the dbname, at least when I'm playing with it from the PS command line. So I'm a bit at a loss. Any help would be most appreciated.
Post #1466966
Posted Tuesday, June 25, 2013 7:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,003, Visits: 3,408
Hi Jack, try using Invoke-SqlCmd in place of SQLCMD - it gives the results in a data row format, whereas I think SQLCMD just gives you a string that you won't be able to loop through.
You can pipe the results straight into a foreach loop, or use the variable like so:

$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"
Invoke-SqlCmd -Serverinstance "server" -Username "User" -Password "password" -Query $sql | `
foreach {code block - use $_.DBName and $_.SFTPFolder to access the values}

or

$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"
$Database = Invoke-SqlCmd -Serverinstance "server" -Username "User" -Password "password" -Query $sql

foreach ($d in $Database)
{
code block - use $d.DBName and $d.SFTPFolder to acces the values
}


Hope that helps.

Cheers
Gaz
Post #1467133
Posted Tuesday, June 25, 2013 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:57 PM
Points: 44, Visits: 175
Awesome! Thanks Gazareth!

Now that I've read your post I vaguely remember hearing that invoke-sqlcmd returns the table and sqlcmd returns a string. There's no way I would have pulled that nugget of information out of the dusty recesses of my brain though. Thanks again.
Post #1467259
Posted Wednesday, June 26, 2013 3:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,003, Visits: 3,408
No worries mate

If in doubt, you can check the type & properties of something by piping it to get-member:

$Database | get-member
Post #1467537
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse