question

TimAnymouse avatar image
0 Votes"
TimAnymouse asked TimAnymouse commented

Powershell piping note property to Write-SqlTableData

Hello,
I'm trying to write some data to an SQL database using Write-SqlTableData.

I've tried the following code
$path = 'D:\';
$pathobject = [PSCustomObject] @{message = "Directories within $path"}
Write-SqlTableData -ServerInstance $server -credential $PSCredential -DatabaseName "log" -SchemaName "dbo" -TableName "log" -Force -InputData $pathobject

I've also tried
$pathobject = [PSCustomObject] @{message = "Directories within $path" } $pathobject| Select-Object -Property message | Write-SqlTableData -ServerInstance $server -credential $PSCredential -DatabaseName "log" -SchemaName "dbo" -TableName "log" -Force

The code seems to work until it is piped to the Write-SqlTableData which doesn't seem to be accepting the note property as input. I initially tried just passing as a string but this didn't work either. No error is given but no data is written.
I looked at the documentation
https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps
and this lists System.String[] as an input (bottom of page)

Before I give up and use an alternative command to write to SQL I thought I would ask here. I can find no documentation clearly saying if a note property will be accepted as input.

Kind regards,
Tim

sql-server-generalwindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered TimAnymouse commented

Hi,

Try converting $pathobject to an array

 [array]$pathobject = [PSCustomObject] @{message = "Directories within $path" } 
 $pathobject| Select-Object -Property message | 
     Write-SqlTableData -ServerInstance $server -credential $PSCredential -DatabaseName "log" -SchemaName "dbo" -TableName "log" -Force

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unfortunately that doesnt work and gives an error
Write-SqlTableData : The given ColumnMapping does not match up with any column in the source or destination.
The log table is also not created.

Kind regards,
Tim

0 Votes 0 ·

I've edited the answer to use the parameter "-Property" and a table was created successfully per my test.

0 Votes 0 ·

unfortunately this does not work for me

[array]$pathobject = [PSCustomObject] @{message = "Directories within $path" }
$pathobject| Select-Object -Property message |

produces a table output to powershell (my original code also did) but below code doesnt create a table in sql server.

Write-SqlTableData -ServerInstance $server -credential $PSCredential -DatabaseName "log" -SchemaName "dbo" -TableName "log" -Force



My Powershell version is as follows
PS D:\Code> Get-Host | Select-Object Version
Version


2021.2.2

Kind regards,
Tim

0 Votes 0 ·
Show more comments

Thank you for your help. Unfortunately I've located the error between my chair and the computer. I made a typo, creating the table in a different database than intended. Probably a consequence of evening coding and several similar lines of code.

0 Votes 0 ·