none
Purge Data RRS feed

  • Question

  • Using VBScript what is the syntax to purge data from a SQL DB?   The information I need to purge lives in SQL in this location.

    Netbios is in the v_R_System view
    SELECT * FROM v_R_System
    The column name is Netbios_Name0

     

    So if I have a variable called OSDComputername what would the syntax be to delete OSDComputername from the above DB?  

     

    Thank you. 


    mqh7
    • Moved by Richard MuellerMVP Thursday, October 27, 2011 8:56 PM asking for T-SQL to purge data (From:The Official Scripting Guys Forum!)
    Thursday, October 27, 2011 7:26 PM

Answers

  • Here is a VBScript program I have used to drop a table in a database, then recreate it. This may not be what you are doing, but you only need to find the T-SQL statement appropriate for what you do want to do, then assign it as a quoted string to the variable strSQL in this example:

     

    Option Explicit

    Dim adoCommand, strConnect, strSQL

    ' Connection string, assuming Windows Integrated Authentication.
    ' If using the default instance, use "Server=MyServer".
    strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _
        & "DATABASE=MyDatabase;SERVER=MyServer\MyInstance"

    ' Use ADO to connect to SQL Server database.
    Set adoCommand = CreateObject("ADODB.Command")
    adoCommand.ActiveConnection = strConnect

    ' If table Students exists, delete it.
    strSQL = "IF EXISTS " _
        & "(" _
            & "SELECT TABLE_NAME " _
            & "FROM INFORMATION_SCHEMA.TABLES " _
            & "WHERE TABLE_NAME = 'Students'" _
        & ") " _
        & "DROP TABLE Students"
    adoCommand.CommandText = strSQL
    adoCommand.Execute

    ' Create table Students.
    strSQL = "CREATE TABLE MyDatabase.dbo.Students " _
        & "(" _
            & "StudentID INTEGER IDENTITY(1, 1) NOT NULL, " _
            & "FirstName VARBINARY(25) NULL , " _
            & "LastName VARBINARY(25) NULL , " _
            & "CONSTRAINT pk_Students " _
                & "PRIMARY KEY CLUSTERED (StudentID) " _
                & "ON [PRIMARY] " _
        & ") ON [PRIMARY]"
    adoCommand.CommandText = strSQL
    adoCommand.Execute
       
    ' Grant permissions to Students table to the MyDatabaseUser role.
    adoCommand.CommandText = "GRANT SELECT, INSERT, UPDATE, DELETE " _
        & "ON Students TO MyDatabaseUser"
    adoCommand.Execute

    -----

     

    Note in the above I execute three T-SQL statements, one to drop the table, another to create a new table, and finally one to grant permissions to a database role. You will probably only need to run one statement.

     


    Richard Mueller - MVP Directory Services
    • Marked as answer by mqh7 Friday, October 28, 2011 3:02 PM
    Thursday, October 27, 2011 9:59 PM

All replies

  • Using VBScript what is the syntax to purge data from a SQL DB?   The information I need to purge lives in SQL in this location.

     

    Netbios is in the v_R_System view
    SELECT * FROM v_R_System
    The column name is Netbios_Name0

     

    So if I have a variable called OSDComputername what would the syntax be to delete OSDComputername from the above DB?  

     

    Thank you. 


    mqh7


    What is it theat you are trying to delete.  Use an Update to clear the contents of a column.  To clear a table you can use 'truncate table <name>'

    Data base do not really have variables.  They have tables. Tables have rows and columns or fields.

    You are referencing a view.  Most views cannot be updated although some can be.

    This is a question that you should probably ask in the SQLServer forum.  It is not really a scripting question.

     


    jv
    Thursday, October 27, 2011 8:42 PM
  • In VBScript you can connect to an SQL Server database and run T-SQL statements on the database. You question seems to be, what is the T-SQL statement to clear something, a table, a column/field, or a field in a row. You can ask in an SQL Server to Transact-SQL forum, such as:

    http://social.technet.microsoft.com/Forums/en-US/transactsql/threads

     


    Richard Mueller - MVP Directory Services
    Thursday, October 27, 2011 8:54 PM
  • Here is a VBScript program I have used to drop a table in a database, then recreate it. This may not be what you are doing, but you only need to find the T-SQL statement appropriate for what you do want to do, then assign it as a quoted string to the variable strSQL in this example:

     

    Option Explicit

    Dim adoCommand, strConnect, strSQL

    ' Connection string, assuming Windows Integrated Authentication.
    ' If using the default instance, use "Server=MyServer".
    strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _
        & "DATABASE=MyDatabase;SERVER=MyServer\MyInstance"

    ' Use ADO to connect to SQL Server database.
    Set adoCommand = CreateObject("ADODB.Command")
    adoCommand.ActiveConnection = strConnect

    ' If table Students exists, delete it.
    strSQL = "IF EXISTS " _
        & "(" _
            & "SELECT TABLE_NAME " _
            & "FROM INFORMATION_SCHEMA.TABLES " _
            & "WHERE TABLE_NAME = 'Students'" _
        & ") " _
        & "DROP TABLE Students"
    adoCommand.CommandText = strSQL
    adoCommand.Execute

    ' Create table Students.
    strSQL = "CREATE TABLE MyDatabase.dbo.Students " _
        & "(" _
            & "StudentID INTEGER IDENTITY(1, 1) NOT NULL, " _
            & "FirstName VARBINARY(25) NULL , " _
            & "LastName VARBINARY(25) NULL , " _
            & "CONSTRAINT pk_Students " _
                & "PRIMARY KEY CLUSTERED (StudentID) " _
                & "ON [PRIMARY] " _
        & ") ON [PRIMARY]"
    adoCommand.CommandText = strSQL
    adoCommand.Execute
       
    ' Grant permissions to Students table to the MyDatabaseUser role.
    adoCommand.CommandText = "GRANT SELECT, INSERT, UPDATE, DELETE " _
        & "ON Students TO MyDatabaseUser"
    adoCommand.Execute

    -----

     

    Note in the above I execute three T-SQL statements, one to drop the table, another to create a new table, and finally one to grant permissions to a database role. You will probably only need to run one statement.

     


    Richard Mueller - MVP Directory Services
    • Marked as answer by mqh7 Friday, October 28, 2011 3:02 PM
    Thursday, October 27, 2011 9:59 PM
  • For others that read this post, althought the answer above are correct, it is completely unsupported to delete data from SCCM via T-SQL. You MUST use the SDK or the console to delete a PC from SCCM.


    Not all data associated with a PC will be deleted for SQL, if you delete the PC from v_R_System view.


    http://www.enhansoft.com/
    Thursday, October 27, 2011 10:28 PM
  • For others that read this post, althought the answer above are correct, it is completely unsupported to delete data from SCCM via T-SQL. You MUST use the SDK or the console to delete a PC from SCCM.


    Not all data associated with a PC will be deleted for SQL, if you delete the PC from v_R_System view.


    http://www.enhansoft.com/


    Bravo Garth.

    It is this view: v_R_System

    Can't be dropped and recreated and it cannot be deleted frm.

    The API or the utility API interface are the only way toi remove a system because we need to de-provision the system.  The issue is inmore thanone location.

    I was trying to remember where I had seen OSDComputername and now I know.

    You recognized it.  I do not use SCCM very much. 

    Perhaps the OP should post the question here:
     http://social.technet.microsoft.com/Forums/en-US/category/configurationmanager

     


    jv
    • Edited by jrv Thursday, October 27, 2011 10:42 PM
    Thursday, October 27, 2011 10:41 PM