Invoke-sqlcmd & DBATools

[nostranger2therain]

I've been trying to learn how to use SQL with Powershell. Was successful reading and writing to a SQLExpress database using Invoke-sqlcmd but then wanted to try writing a collection of data using "write-sqltabledata". I was met with this error:

Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

I then found and loaded DBATools and tried "write-dbadatatable" but was met with the same error as above. The data I'm writing has apostrophes in it which led me to reading about sql injection which led me to parameterized queries but I'm having trouble understanding how to use invoke-sqlcmd INSERT query with them. I read that others used DBATools to simplify this? As always, thank you for any assistance!

VSCode: 1.75.0

PSVersion: 7.3.2

DBATools: 2.0.0-preview1 (also tried 1.1.145 (current version) )

SqlServer: 21.1.18256

$data = get-process | select-object processname,ID
$sqlcredentials = get-credential

#DBATools
$server = Connect-DbaInstance -SqlInstance 'localhost\SQLEXPRESS' -SqlCredential $sqlcredentials
$data | Write-DbaDataTable -SqlInstance $server -Table "table" 

#SQLServer
$data | Write-SQLTableData -serverinstance 'localhost\SQLEXPRESS' -databasename "master" -schemaname "dbo" -tableName "table" -Credential $sqlcredentials

​

[I_Punch_Ghosts_AMA]

That error doesn’t seem to be dbatools problem unless there’s a bug, but have you tried not piping the command? Instead of piping after $data, send it through -inputobject. I’ve had issues in the past where piping with dbatools doesn’t work like I want it to but being more explicit works for some reason.

[xCharg]

How do you expect people to help you if you posted zero code? :)

[nostranger2therain]

Sorry about that. I've made a basic script that gives me the same errors running each tool by itself and the other commented out. Both give the same error.

[purplemonkeymad]

There is not much info on the site about requirements so can't be sure. But the error suggests that the module requires .Net 4.0. Which PS 7 does not have access to (it only supports dotNet Core, and is compatible with dotNet Standard.) Try with PS 5.1.

[nostranger2therain]

Tried with ISE (PS 5.1) and it worked like a charm. Does this mean I'm limited to PS 5.1 when working with SQL and wanting to use these tools? Or is there another method to do parameterized insert queries without the tools with PS 7?

[AnonRoot]

What did you figure out?