Solved

Deleting a SQL record via Powershell

Posted on 2016-07-18
8
47 Views
Last Modified: 2016-07-18
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
0
Comment
Question by:ARM2009
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717423
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
 

Author Comment

by:ARM2009
ID: 41717442
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
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717460
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
 

Author Comment

by:ARM2009
ID: 41717486
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 500 total points
ID: 41717491
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
 

Author Comment

by:ARM2009
ID: 41717501
Dustin...

that looks good, how do i log that in a csv file so i can keep record of the record being deleted?
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717664
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
 

Author Closing Comment

by:ARM2009
ID: 41717784
Thanks... worked as described!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now