[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deleting a SQL record via Powershell

Posted on 2016-07-18
8
Medium Priority
?
964 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 14

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 14

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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

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
 
LVL 14

Accepted Solution

by:
Dustin Saunders earned 2000 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 14

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

834 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