Question:
I’m trying to pass a SQL adapter object to a PowerShell function but I’m getting this error:
executeQueryAndFillTable : Cannot process argument transformation on
parameter ‘da’. Cannot convert the “System.Object[]” value of type
“System.Object[]” to type “System.Data.SqlClient.SqlDataAdapter”.
Here’s the code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
function sql_pull { # define Objects $xmlDoc = New-Object System.Xml.XmlDocument $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlCommand = New-Object System.Data.SqlClient.SqlCommand $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $connectionString = "Password=$password;Persist Security Info=True;User ID=$userId;Data Source=$dataSource" $counter = 0 # database queries $queries = @( "Select * from sys.configurations for xml Raw ('Cretiria'), type, ROOT('sys.configurations'), ELEMENTS"); $sqlConnection.ConnectionString = $connectionString $sqlCommand.Connection = $sqlConnection try { $sqlConnection.Open() foreach($q in $queries) { $sqlCommand.CommandText = $q $sqlAdapter.SelectCommand = $sqlCommand.CommandText $sqlAdapter.SelectCommand.CommandTimeout = 300 $res = executeQueryAndFillTable($sqlAdapter, $sqlCommand) } $sqlConnection.Dispose() $sqlCommand.Dispose() $sqlAdapter.Dispose() } catch { Throw } } function executeQueryAndFillTable { param( [System.Data.SqlClient.SqlDataAdapter]$da, [System.Data.SqlClient.SqlCommand] $command ) $dataTable = New-Object System.Data.DataTable $da.SelectCommand = $command $da.Fill($dataTable) #check $data = $dataTable.Rows[0][0] return $data } |
Answer:
Two things:
First : In PowerShell function should be declare before usage.
Second : The way the function is called.
|
1 2 |
executeQueryAndFillTable($sqlAdapter, $sqlCommand) |
This is not the right way to call a function in PowerShell. If you call it this way PowerShell thinks you are calling the function with only one parameter which is an array (very important , is the array operator in PowerShell) of two elements of distinct types (the reason why System.Object[] in the error).
The right way is :
|
1 2 |
executeQueryAndFillTable $sqlAdapter $sqlCommand |