Grooper 21.00.0082 is available as of 12-12-2023! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.1.0016 is available as of 03-15-2024! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.00.0042 is available as of 03-22-2024! Check the Downloads Discussion for the release notes and to get the latest version.

External SQl table update From Grooper

Hello!

I see that there is a SQL export activity step available. However, Is there any sql update activity step available as well for external SQL database? If not I would like to write an update statement within task process step and how should I start with? Please advise.

Thank you.

Answers

  • DungVuDungVu Posts: 88
    Please advise how to passing the parameters variables to the storedProcedure below command:

     Public Overrides Sub ProcessTask(CurNode As BatchObject)
            Dim CurFolder As BatchFolder = CurNode
            Dim dc As DataConnection = DataConnection
            Dim Settings As SqlConnectionSettings = dc.ConnectionSettings()
            Dim db As SqlDatabase = New SqlDatabase(Settings)

            If (db.Connect() = False) Then Throw New Exception("Unable to connect to the database.")
            Dim comm As New SqlClient.SqlCommand()
            db.ExecuteNonQuery("exec " & _StoredProcedureName, "")
            db.Disconnect()
        End Sub

  • hjanumhjanum Posts: 110 ✭✭
    DungVu said:
    Hello!

    I see that there is a SQL export activity step available. However, Is there any sql update activity step available as well for external SQL database? If not I would like to write an update statement within task process step and how should I start with? Please advise.

    Thank you.
    We handle updates to SQL Server tables using the following method.

    Define a new table 'eg GrooperInsert' that Grooper inserts into using Database Export.

    Define a trigger on the new table for inserts. In the trigger update the table(s) you want to update.
    This way you can do complex lookups and updates based on a simple Grooper insert statement.

    CREATE TRIGGER [dbo].[GrooperInsert_INSERT]
       ON [dbo].[GrooperInsert]  
       AFTER INSERT
    AS 
    BEGIN
      **** Add your lookups/updates here ****
    END



  • DungVuDungVu Posts: 88
    Thank you hjanum for quick response. However, It seems like that I am lost here! because I don't have the Database Export object library code available so I can't define the trigger into Database Export unattended Activity. Could you please provide me the Database Export Object Library?

    Thank you very much for your help.

  • hjanumhjanum Posts: 110 ✭✭
    You would define the SQL Table trigger using your standard SQL management tools. The tools vary based on your SQL product. In the case of Microsoft SQL Server, you would use SQL Server Management Studio (SSMS).

    You can download it here:
    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

    If you are unsure of how to do this, then I would recommend talking to your company's Database Base Administrator (DBA).
  • DungVuDungVu Posts: 88
    Thank you hjanum. I figured it out to run with SQL update statement and it is working well.

    db.ExecuteNonQuery(Updatesqlcommand, "")

Sign In or Register to comment.