how to add data from a file to an open variable in powershell

Question

I am trying to add an open varible to this command so when the data pulls from line 24 it adds it to the variable and makes it executable.

 $data = Get-Content "C:\Users\bgriffiths\Documents\test.dat"
 $data[24]

I have tried adding different formats to do this and nothing seems to work.

one command i tried was

invoke-command sql -query = $data

I get an error telling me

Invoke-Command : A parameter cannot be found that matches parameter name 'query'.
At line:4 char:26
+ invoke-command sql -query <<<<  = $data
+ CategoryInfo          : InvalidArgument: (:) [Invoke-Command], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.InvokeCommandCommand

another command I have been trying to run is

  $Command.CommandType.text = $data

the only error I get from this is

  Property 'text' cannot be found on this object; make sure it exists and is settable.
  At line:10 char:30
  +         $Command.CommandType. <<<< text = $data               
 + CategoryInfo          : InvalidOperation: (text:String) [], RuntimeException
 + FullyQualifiedErrorId : PropertyNotFound

I am at a lost on how to import file data into the script and have it run it.

What is that line? What command are you trying to get to execute? And $data[24] will reference the 25th line since the array $data starts at record 0 not record 1. — TheMadTechnician, Jun 23 '14 at 20:00
the command it is trying to run is DROP SYNONYM DATABASE1.BTXSUPB; — bgrif, Jun 23 '14 at 20:06
Why are you using `Invoke-Command`? Are you trying to execute the command on another computer? Is `sql` an exe you're trying to execute? — Keith Hill, Jun 23 '14 at 20:15
I am remote connecting to another server and the file is a .dat that I need to pull lines out of it and inport into a script and run that command on the server it is connected to. It doesnt have to be an invoke-command i was trying different commands to try pulling that line into the script — bgrif, Jun 23 '14 at 20:19
Do you have the SQL Server Client Tools installed on the machine that you are trying to run the command? I think you need to worry about how to execute a SQL query from PowerShell before you concern yourself with including text from a file in that process. — TheMadTechnician, Jun 23 '14 at 20:23
The real question seems to be "how to query Sql Server with Powershell" - which is [answered already](http://stackoverflow.com/q/8423541/503046) in another a thread. — vonPryz, Jun 23 '14 at 20:23
I do have the SQL server Client Tools installed on the computer I am running everything through — bgrif, Jun 23 '14 at 20:29
thank you for the comments I will look at the other tread and work form there on getting the commands need to run. — bgrif, Jun 23 '14 at 20:30

bgrif · Accepted Answer · 2014-06-27T20:31:46.700

I figured out how to run commands for sql through my powershell I had to actaully import the assembly list for the sql ps and then I was able to run the Invoke-SqlCmd

the script to add the information for sqlps to your windows ps is -

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Powershell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Preload the assemblies. Note that most assemblies will be loaded when the provider
# is used. if you work only within the provider this may not be needed. It will reduce
# the shell's footprint if you leave these out.
#
$assemblylist = 
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"


foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

#
# Set variables that the provider expects (mandatory for the SQL provider)
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'
Write-Host
Write-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'
Write-Host
Write-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

the link where I found this is http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

how to add data from a file to an open variable in powershell

1 Answers1