Need help: No result when querying SQL data in Powershell

PowerShell Help
1

Hi All,

I am a newbie to Powershell and below is the scenario where I’m puling SQL data in powershell and inserting records into SQL.

Scenario:

In table1, we have records with series like M100 and its series like “M100-01”,“M100-02”,“M100-03”,“M100-04”,“M100-05”,“M100-06”,“M100-07”,“M100-08”,“M100-09” etc.

Similarly for other M200, M300 etc. records.

I need to pull up al records with are under series like M100-01",“M100-02”,“M100-03” etc and insert new records under single name M100

Below is the logic I used but output does not show any results though DB has values.

 

$items = @(“M100”,“M200”,“M300”,“M400”,“M500”,“M600”)
foreach($item in $items)
{

Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query “Delete from <table1> where MetricID = (Select _ID From <table2> where Name = ‘$item’)” -username $dbUsername -password $dbPassword

$findingsinv=Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query “Insert table1(MetricID,Resource,Type,Details,LastModified)
Select (Select _ID From table2 where Name = ‘$item’) As ‘MetricID’,d.Resource,d.Type,d.Details,d.LastModified from table1 d
Join table 2 c on c._id = d.metricid
where Name like ‘$item%’” -username $dbUsername -password $dbPassword

}

I doubt this condition of Name like ‘$item%’" is not taking the series. I tried multiple ways of giving to pull all series records but just does not work. Can someone assist here?

Thanks!

 

 

2

Please use the pre-tags when you post code so it’s easier to read.

Whenever troubleshooting SQL stuff, start with checking that it actually works in e.g. SQL Management Studio or whatever SQL tool you use.
If that doesn’t work then it won’t work using powershell.

Also the first query looks abit weird with (SELECT _ID From where Name = ‘$item’)
You don’t state the table name there.

So start checking if the query works in the first place.

3

You can also use .NET object SqlConnection for all SQL Server operations. Like this:

$Sql = "SELECT * FROM [YourTableName] WHERE YouRecordID LIKE 'M100-%'"

$SqlServer = 'SqlServerName'
$SqlDb = 'MyDbName'
$SqlUser = 'SqlServerUser'
$SqlPassword = 'SqlServerPassword'

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDb;User ID=$SqlUser;Password='$SqlPassword'"
$SqlConnection.Open()

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand) 
$SqlAdapter.Fill($DataSet) > $null


foreach($table in $DataSet.Tables.GetEnumerator())
{
$table.DefaultView | fl 
}

See details here: SqlConnection Class (System.Data.SqlClient) | Microsoft Docs

Also you can take a look to my test PowerShell script for execution of SQL Server commands: https://1drv.ms/u/s!AmLrzNwOB1OBiZxj2n8AiLUY2gkpCw

Feel free for ask any questions about this script

4

Thanks Fredrick and Andy for the suggestions!

I should have added more details in my initial email.

I did verify the query in SQL and it works fine and I did use the required connectors as other SQL queries in PS script work fine. Its just the insert statement (below) is not inserting records into DB.

 

The insert query completes but they are no records inserted and no records in variable $findingsinv. But indeed data exists in DB.

$items = @("M100","M200","M300","M400","M500","M600")

foreach($item in $items)

{

Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from table1 where MetricID = (Select _ID From table2 where Name = '$item')" -username $dbUsername -password $dbPassword  #delcared server instance earlier and this query works fine

$findingsinv=Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Insert table1(MetricID,Resource,Type,Details,LastModified)

 Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 

Join table2 c on c._id = d.metricid

 where Name like '$item-%'" -username $dbUsername -password $dbPassword
5

Not a DB guy, an update on DBATools.

6

Maybe if you try some simple INSERT query as a test first just to see that it actually work?

E.g. as far as I know you state INSERT INTO (you’re missing INTO).
It also might be a good idea to create query as a here-string e.g.

# NOTE: the last "@ need to be on a new line and at the beginning of that line, so no indentation.

$query = @"
  INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
  VALUES values_for_whatever_data_types_you_use
"@

Except for any parameters that you want to insert in the query using the powershell version of query.
You can pretty much copy/paste from the standard SQL-statement.

Meaning if your statement works in e.g. SQL Management Studio.
Copy that query into a here-string as per example above.
Does it work?

If it does then it’s not an issue with powershell or the cmdlet.
It’s how the query is constructed.
Benefit with the here string is that you write that out to console to check that the query actually looks correct when you run it.

7

For inserting you can use the next code:

$sql = @"
INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
VALUES values_for_whatever_data_types_you_use
"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server='SqlServer';Database='SqlDb';User ID='SqlUser';Password='MyP@SSword'"
$SqlConnection.Open()

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)
$SQLCommand.CommandText = $Sql
$SqlCommand.ExecuteNonQuery()
8

Thanks Fredrick, Prasoon and Andy for the suggestions!

What I noticed was when I give the query with value directly instead of variable like $item, it worked.

This worked: ( but I had to give this statement 6 times, as we have M100-M600)

$query6= @" 
Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
   Select (Select _ID From table2 where Name = 'M600') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 
   Join table2 c on c._id = d.metricid where Name like 'M600%'
"@

$inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query -username $dbUsername -password $dbPassword

This still does not work when I give as variable $item: No error but values are not inserted into table

 

items = @("M100","M200","M300","M400","M500","M600")


foreach($item in $items)


{


Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from table1 where MetricID = (Select _ID From table2 where Name = '$item')" -username $dbUsername -password $dbPassword 
$query= @" 
Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
   Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 
   Join table2 c on c._id = d.metricid where Name like '$item%'
"@

$inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query -username $dbUsername -password $dbPassword

 

 

 

 

9

Instead of running the query e.g. the $inv line (just comment it out).
Then do a Write-Output $query.

Then you can see the whole query without running it, and you can see what the query looks like after the variable have been expanded into it.