Solved

Deleting a SQL record via Powershell

Posted on 2016-07-18
8
122 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

792 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