powershell parameters

I've adjusted and combined the code at http://blogs.technet.com/b/sqlthoughts/archive/2008/10/03/out-sql-powershell-function-export-pipeline-to-a-new-sql-server-table.aspx with some of my own to import some data into a sql table.
I strugged with it for a while, because I set my function up with cmdlet binding:

  [cmdletbinding()] param (
 [Parameter(Mandatory=$true,position=1,valuefrompipeline=$true,ValueFromPipelineByPropertyName=$true)]
 [string]$sqlserver='xxxxxx',

[Parameter(Mandatory=$true,position=2,valuefrompipeline=$true,ValueFromPipelineByPropertyName=$true)]
[string]$database='xxxxxxx',

[Parameter(Mandatory=$true,position=3,valuefrompipeline=$true,ValueFromPipelineByPropertyName=$true)]
 [string]$table='xxxxxxxxxx'
  )

Open in new window


Stepping through the code, it would take all of the data passed to it (via a foreach loop) and combine it together into each of the varaibles above...duplicating for each.

Not until I adjusted the function, removing the above, and passing the arguments like so, did it work...
function out-sql ($sqlserver='xxxxxx',$database='xxxxxxx',$table='xxxxxx') {

Open in new window


I'd like to know the reason it works with these parameters differently.  Ultimatley, I'd rather have my function setup with cmdlet binding, so that it matches my other functions.
Any ideas?
LVL 67
sirbountyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You may not use [Parameter(ValueFromPipeline=$true)] more than once for a single parameter set.
You should only use it instead of a ValueFromPipelineByPropertyName parameter, with the exception when defining a "default pipeline" parameter which can take values both by property name or by default (e.g. if you want to allow for providing strings instead).
In this case, since all parameters are mandatory, you should only use ValueFromPipelineByPropertyName to keep away from fuzzy assignments.
[cmdletbinding()] param (
 [Parameter(Mandatory=$true,position=1,ValueFromPipelineByPropertyName=$true)]
 [string]$sqlserver='xxxxxx',

[Parameter(Mandatory=$true,position=2,ValueFromPipelineByPropertyName=$true)]
[string]$database='xxxxxxx',

[Parameter(Mandatory=$true,position=3,ValueFromPipelineByPropertyName=$true)]
 [string]$table='xxxxxxxxxx'
  )

Open in new window

0
sirbountyAuthor Commented:
When I try that, I get

out-sql : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and
its properties do not match any of the parameters that take pipeline input.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then it is at it says. Whatever you stuff into the pipe, it hasn't the necessary properties. All mandatory properties have to be there. Or you have to provide them as parameters when calling out-sql.

Without seeing more of the code you use out-sql in, I can't provide more help than that.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

sirbountyAuthor Commented:
Well, I call it using

Get-MailLogs | out-sql -sqlserver 'xxxxx' -database 'xxxxxx'  -table 'xxxxx'

My main function grabs some logging information, and outputs it as a psobject:

$obj = new-object psobject
....all using NoteProperty
write-output $obj

function out-sql {
    [cmdletbinding()] param (
    [Parameter(Mandatory=$true,position=1,ValueFromPipelineByPropertyName=$true)]
    [string]$sqlserver='xxxxx',

    [Parameter(Mandatory=$true,position=2,ValueFromPipelineByPropertyName=$true)]
    [string]$database='xxxxx',
  
    [Parameter(Mandatory=$true,position=3,ValueFromPipelineByPropertyName=$true)]
    [string]$table='xxxx'
    )

Open in new window


Need anything else?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is no parameter to consume the pipeline input. That didn't matter before, because without CmdLetBinding the number of parameters is not checked. I should have seen that earlier, but was focussing on the error you got first.

You should define the function as a filter function, only expecting the objects to dump in a table in the pipeline. Anything else should be provided as parameter. But you can also do a mix if you still like, but then I would filter the "parameter" properties out of the object (else they get dumped in the table, too).

Summary of options:
a) Don't use CmdletBinding.
b) Only allow for piping of objects to dump, and require the connection/table info to be put as a parameter:
function out-sql {
    [cmdletbinding()] param (
    [Parameter(Mandatory=$true,position=1)]
    [string]$sqlserver',

    [Parameter(Mandatory=$true,position=2)]
    [string]$database,
  
    [Parameter(Mandatory=$true,position=3)]
    [string]$table,

    [Parameter(Mandatory=$true,position=4,ValueFromPipeline=$true)]
    $obj
    )

Open in new window

I don't know if you need to use $obj instead of $_ - just try.
Call:
Get-Something | out-sql 'srv' 'db' 'tbl'

Open in new window

BTW, doesn't make sense to use default values for mandatory parameters ...
c) Allow for providing all data via pipeline.
function out-sql {
    [cmdletbinding()] param (
    [Parameter(Mandatory=$true,position=1,ValueFromPipelineByName=$true)]
    [string]$sqlserver',

    [Parameter(Mandatory=$true,position=2,ValueFromPipelineByName=$true)]
    [string]$database,
  
    [Parameter(Mandatory=$true,position=3,ValueFromPipelineByName=$true)]
    [string]$table,

    [Parameter(Mandatory=$true,position=4,ValueFromPipeline=$true)]
    $obj
    )
# ... and check for property names sqlserver, database, table, which should *not* get dumped

Open in new window

Usage:
Get-Something | select a, b, c, @{n = 'table'; e={'tbl'}} | out-sql -sqlserver 'srv' -database 'db'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sirbountyAuthor Commented:
I think I prefer option B, and I only provided the defaults when I was testing because I couldn't get it to work.
I'll give that a shot - thanks!
0
sirbountyAuthor Commented:
Ok, so for option B, I've been trying to drop 4 items into the pipeline, while only accepting 3.  Is that why I was getting the error?
Seems to be working now, though I have to do some database cleanup first to be certain...
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Exactly. CmdletBinding checks occur before any processing, so the parameter count not fitting was enough to fail, though the code would have worked that way (probably).
0
sirbountyAuthor Commented:
Thanks very much for your help.  I'm trying to increase the points, but apparently there's an issue currently...  I'll try again later, but this is all sorted now.  Thanks again! :^)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.