How do I assign three variables using Invoke-Sqlcmd

PowerShell Help
1

For example, I have

Invoke-Sqlcmd "SELECT top 1 sqldbname= bkups.database_name, sqlbktype = bkups.type
	, sqlbkupLoc = bkmf.physical_device_name
FROM msdb.dbo.backupset bkups
JOIN master.sys.sysdatabases dbs ON bkups.database_name = dbs.name
JOIN msdb.dbo.backupmediafamily bkmf ON [bkups].[media_set_id] = [bkmf].[media_set_id]" -ServerInstance localhost | SELECT -ExpandProperty $DBName = sqldbname, $bktype = sqlbktype, $bkpath = sqlbkupLoc

I’m used to piping it to -ExpandProperty with single variable. But, I’m getting error when trying to do the same with 3 variables. Error is A positional parameter cannot be found that accepts argument ‘System.Object’.

How do I assign the values of the 3 columns in this example?

Thank you

2

Please show how you do it with a single variable, as this syntax is invalid. That aside, you can only “Expandproperty” on one property at a time. If you need the values for three properties, there are several approaches you can take. Could you clarify what you’re hoping to end up with? I would guess 3 variables with different values in them. There are probably easier (and better) options available.

I would recommend trying this first

$result = Invoke-Sqlcmd “SELECT top 1 sqldbname= bkups.database_name, sqlbktype = bkups.type
, sqlbkupLoc = bkmf.physical_device_name
FROM msdb.dbo.backupset bkups
JOIN master.sys.sysdatabases dbs ON bkups.database_name = dbs.name
JOIN msdb.dbo.backupmediafamily bkmf ON [bkups].[media_set_id] = [bkmf].[media_set_id]” -ServerInstance localhost

$result.sqldbname
$result.sqlbktype
$result.sqkbkuploc

Each of those properties are easily accessible using dot notation as shown. Also, please edit your post and format your code as preformatted code. (it’s the </> option, it may be hidden behind the settings wheel.) Remember to post any code/errors using this preformatted option as it makes it easier to read, less error prone, and thus easier for us to help you.

3

Than you krzydoug for responding. As mentioned, I was able to use -ExpandProperty with single variable. The issue was with 3.
I’ll try out your suggestions.
Thank you again.

Edit: Any idea why $result.sqlbkuploc has no output in the following?

$result = Invoke-Sqlcmd “SELECT top 1 sqldbname= bkups.database_name, sqlbktype = bkups.type
, sqlbkupLoc = bkmf.physical_device_name
FROM msdb.dbo.backupset bkups
JOIN master.sys.sysdatabases dbs ON bkups.database_name = dbs.name
JOIN msdb.dbo.backupmediafamily bkmf ON [bkups].[media_set_id] = [bkmf].[media_set_id]” -ServerInstance localhost

$result.sqldbname
$result.sqlbktype
$result.sqkbkuploc

Write-Output $result.sqldbname
Write-Output $result.sqlbktype
Write-Output $result.sqkbkuploc

Edit 2: Never mind. It’s the upper case L in sqlbkupLoc.
Thank you again. That works wonderfully now.

5

This topic was automatically opened after 30 days.

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.