External SQl table update From Grooper

DungVu

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.

DungVu

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

hjanum

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

DungVu

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.

hjanum

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

DungVu

Thank you hjanum. I figured it out to run with SQL update statement and it is working well.

db.ExecuteNonQuery(Updatesqlcommand, "")