?
Solved

Deleting a SQL record via Powershell

Posted on 2016-07-18
8
Medium Priority
?
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 13

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 13

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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 13

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 13

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 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