locked
ExecuteNonQuery with Connection and Command, or to a Server and DB SMO Object (Best Practice?) RRS feed

  • Question

  • If you look at the PowerShell below, at the bottom of the script, there are 2 different methods to insert a row of data from a loop using a t-sql insert and ExecuteNonQuery. However, the 1st method does not create a connection and just loops over the ExecuteNonQuery. I also noticed that the 1st method does not create parameters either.

    Method 1 also starts with a database object linked to a server object using SMO. Method 2 creates a connection and a command. I plan on using method 1 to pull Active Directory Data and method 2 to pull from other SQL Servers or external tables (if that even matters).

    I wanted to ask people with experience if I should use either method, or if one is better?

    What is the Best Practice in these methods?

    When do I need to create a connection (method 2) versus just executing against the DB, method 1?

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO"); $ServerName = "testServer" $srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $ServerName $db = $srv.Databases["testDB"] $Groups = Get-ADGroup -Properties * -Filter * -SearchBase "OU=SoftwareUpdates,OU=SCCM,OU=Global,DC=,DC=,DC=com"

    Method 1

    foreach($Group in $Groups) { $GroupCollects = Get-ADGroupMember -Identity $Group foreach($G in $GroupCollects) { $Members = $G.Name $db.ExecuteNonQuery("INSERT ADGroupMembers(GroupName, ComputerName) VALUES ('$Group', '$Members')") } }

    Method 2 $conn3 = New-Object System.Data.SqlClient.SqlConnection("Data Source=" + $destSQL_Server + "; Initial Catalog=" + $destSQL_Database + "; Integrated Security=SSPI") $conn3.Open() $cmd3 = $conn3.CreateCommand() foreach($obj3 in $ResultContact.Data.CustomFields) { $cmd3.CommandText = "INSERT tbl_RLS_Prototype_ETL_Test VALUES (@name, @value)" $cmd3.Parameters.AddWithValue("@name", $obj3.name); $cmd3.Parameters.AddWithValue("@value", $obj3.value + ""); $cmd3.ExecuteNonQuery(); $cmd3.Parameters.clear(); } $conn3.Close()



    Regards,

    Ryan P. Casey, MBA
    SSAS, SSIS, SSRS Architect

    Website and Blog
    http://www.aimYourData.com 


    • Edited by RPCASEY001 Monday, June 3, 2019 8:47 PM
    Monday, June 3, 2019 8:42 PM

All replies

  • The first method can use parameters.  The fist method does create a connection.  The $srv object is a connection .

    The second method is more common although it is written incorrectly and will not perform as expected.

    As an SSAS, SSRS and SSIS architect you should know this cold.  It is the foundation of SSIS, SSRS and most other SQLServer DB components.


    \_(ツ)_/

    Monday, June 3, 2019 10:06 PM
  • I just don't use PowerShell all that often.

    What part of Method 2 is incorrect?

    The code appears to work on my end, so I wanted to understand more incase I have an error that has not shown its self yet.

    Why is method 2 more common?


    Regards,

    Ryan P. Casey, MBA
    SSAS, SSIS, SSRS Architect

    Website and Blog
    http://www.aimYourData.com 



    • Edited by RPCASEY001 Monday, June 3, 2019 10:18 PM
    Monday, June 3, 2019 10:17 PM
  • This is the correct way to do this for performance and memory.

    $connStr = "Data Source=$destSQL_Server;Initial Catalog=$destSQL_Database;Integrated Security=SSPI"
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = 'INSERT tbl_RLS_Prototype_ETL_Test VALUES (@name, @value)'
    $p_name = $cmd.Parameters.Add('@name',[string])
    $p_value = $cmd.Parameters.Add('@value',[string])
    
    $ResultContact.Data.CustomFields |
        ForEach-Object{
            $p_name.Value = $_.Name
            $p.value = $_.Value
            $cmd.ExecuteNonQuery()
        }
    
    $conn.Close()

    You "INSERT" statement is obviously very wrong. 

    INSERT INTO <table>(col1,col2,col3...) VALUES('@p1','@p2',...)


    \_(ツ)_/



    Monday, June 3, 2019 10:17 PM
  • The first method is generally only used to manage a database instance.  It cannot be used where the SQLServer tools are not installed.  The second method is available on all Windows systems.


    \_(ツ)_/

    Monday, June 3, 2019 10:21 PM
  • The code you provided gives a Runtime Error: Exception Calling ExecuteNonQuery with "0" argument(s). No mapping exists from Object Type System.RumTimeType to a known managed provider native type.

    Here is how I changed the code to work on my end:

    I took the loop out just hard coded the string parameters for a simple test.

    $connStr = "Data Source=$destSQL_Server;Initial Catalog=$destSQL_Database;Integrated Security=SSPI"
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = 'INSERT tbl_RLS_Prototype_ETL_Test (name, value) VALUES (@name, @value)'
    $p_name = $cmd.Parameters.Add('@name',[string])
    $p_value = $cmd.Parameters.Add('@value',[string])
    
    # $ResultContact.Data.CustomFields |
    #     ForEach-Object{
            $p_name.Value = "RPC" # $_.Name
            $p.value = "Test Value" # $_.Value
            $cmd.ExecuteNonQuery()
    #     }
    
    $conn.Close()

     


    Regards,

    Ryan P. Casey, MBA
    SSAS, SSIS, SSRS Architect

    Website and Blog
    http://www.aimYourData.com 


    • Edited by RPCASEY001 Monday, June 3, 2019 11:49 PM
    Monday, June 3, 2019 11:48 PM
  • Are you using PowerShell?  Did you add the remaining variables?  Part of an error message does no good.


    \_(ツ)_/

    Monday, June 3, 2019 11:54 PM
  • I found the small issue. The code was missing the .Value on the Value variable of type parameter. Thanks for your help!

    Regards,

    Ryan P. Casey, MBA
    SSAS, SSIS, SSRS Architect

    Website and Blog
    http://www.aimYourData.com 

    • Edited by RPCASEY001 Tuesday, June 4, 2019 12:14 AM
    Tuesday, June 4, 2019 12:14 AM
  • Sorry.  I missed that.  The full error message would have included the exact line and line number as well as the error code in hex format. Thatwould have made the cause obvious.


    \_(ツ)_/

    Tuesday, June 4, 2019 12:42 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, June 14, 2019 6:28 AM