Deleting a SQL record via Powershell

Trying to delete a SQL record via powershell, the record is not in a table but in a view. so doing a query to find the record first and then user that value to find the actual record to delete.

$Query1 = "SELECT [PRINCIPAL_ID] FROM [XDMprod].[dbo].[USER_PROPERTY] Where [NAME] = 'userprincipalname' AND [VALUE] = 'MOBTEST@test.com'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; uid=$User; pwd=$pwd; Integrated Security = False;"
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query1
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCmd1
$USERID = New-Object System.Data.DataSet

$USERID = $SqlCmd.ExecuteScalar()
$Adapter.Fill($USERID)
$Adapter.Dispose()
$SqlConnection.Close()

$UserID.Tables[0] | Export-Csv C:\userinfo.csv

this should return a record for 'MOBTEST@test.com' with [PRINCIPAL_ID]

using [PRINCIPAL_ID] ... that was found...

$Query2 = “DELETE [XDMprod].[dbo].[USER_PROPERTY] WHERE [PRINCIPAL_ID] = '$UserID' AND name = 'homemdb'"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; uid=$User; pwd=$pwd; Integrated Security = False;"
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query2
$rowsAffected = $sqlcmd.ExecuteNonQuery()
$SqlConnection.Close()

trying to combine in one script... goal is as follows

1. when part one runs, if the record does not exit... spit error in a file saying no record found for 'MOBTEST@test.com'
2. if found, then dump data in a txt file with info found
3. match and delete record, and record success or failure
ARM2009Asked:
Who is Participating?
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
You need the select to be in parentheses:

DELETE [XDMprod].[dbo].[USER_PROPERTY] WHERE [PRINCIPAL_ID] = (SELECT [PRINCIPAL_ID] FROM [XDMprod].[dbo].[USER_PROPERTY] Where [NAME] = 'userprincipalname' AND [VALUE] = 'MOBTEST@test.com') AND name = 'homemdb'

Open in new window

0
 
Dustin SaundersDirector of OperationsCommented:
I'm not quite clear on the objective- can you give an example with maybe some table structure?

Why do you have to select from the view then go back and select from a table with info dumped?  Maybe a better query would help do this in one step (but need to see more information).
0
 
ARM2009Author Commented:
Dustin ...

The PrincipalID is the unique field that identifies the user and first query is finding it via the upn. this is a table by itself.

The second info, homemdb for that user which is stored along with the PrincipalID is not ina  table for me to query and update. Thus using the first query to find the PrincipalID and then using that info to find the actual record to delete.
0
Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

 
Dustin SaundersDirector of OperationsCommented:
What I mean is, why do the select, dump the result to a CSV, then load that infomation and do another query?  Is there a reason you can't do the select in the delete?

If so, can you maybe post more info about the DB structure?

DELETE [USER_PROPERTY] WHERE [PRINCIPAL_ID] = (SELECT TOP 1 [PRINCIPAL_ID] FROM [USER_PROPERTY] Where [NAME] = 'userprincipalname' AND [VALUE] = 'MOBTEST@test.com') AND name = 'homemdb'

Open in new window

0
 
ARM2009Author Commented:
I was breaking it apart to see the result in each step... to make sure I am seeing the right record. combing is fine as your break it down...  so I did the following...

$Query2 = "DELETE [XDMprod].[dbo].[USER_PROPERTY] WHERE [PRINCIPAL_ID] = [SELECT [PRINCIPAL_ID] FROM [XDMprod].[dbo].[USER_PROPERTY] Where [NAME] = 'userprincipalname' AND [VALUE] = 'MOBTEST@test.com' AND name = 'homemdb']"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; uid=$User; pwd=$pwd; Integrated Security = False;"
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query2
$rowsAffected = $sqlcmd.ExecuteNonQuery()
$SqlConnection.Close()


I get the following error...

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near the keyword 'FROM'."
At line:18 char:1
+ $rowsAffected = $sqlcmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
 
ARM2009Author Commented:
Dustin...

that looks good, how do i log that in a csv file so i can keep record of the record being deleted?
0
 
Dustin SaundersDirector of OperationsCommented:
Depending on your logging, your MSSQL transaction log should have this information.

If you wanted to log it yourself, you could output your query to a log file using
Add-Content -Value $Query2 -Path $logFile

Open in new window


Or, rather than an ad-hoc query, you can execute a Stored Procedure in SQL that can write audit information to a log table.
0
 
ARM2009Author Commented:
Thanks... worked as described!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.