SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to variable - not wanting table style result


Query to variable - not wanting table style result

Author
Message
Drenlin
Drenlin
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 165
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
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4006 Visits: 5820
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
Drenlin
Drenlin
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 165
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.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4006 Visits: 5820
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
JackG
JackG
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 237
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.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4006 Visits: 5820
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
JackG
JackG
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 237
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. :-P Thanks again.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4006 Visits: 5820
No worries mate :-)

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

$Database | get-member
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search