8 Replies Latest reply: May 15, 2014 10:00 AM by Bill Watson RSS

    Execute a Stored Procedure from PostExport

    swayne _

      I would like to be able to call a stored procedure from a Monarch Data Pump 8.5 project in the PostExport script area but I can’t find any examples and the help files are lacking in this area.  Specifically I would like to get the database connection string from the Exports part of the project and then execute the stored procedure using this information.  Any help you can provide would be great.  Thanks.

        • Execute a Stored Procedure from PostExport
          swayne _

          I got this to work.

           

          Enter this in the PostExport Script Tab.     

           

           

               Dim connectionString As String = "Data Source=ServerName;Database=Test;" _

                          & "Integrated Security=SSPI;"

                  Dim command As New SqlCommand("proc_Test")

           

                  Using connection As New System.Data.SqlClient.SqlConnection(connectionString)

                      command.Connection = connection

                      command.CommandType = CommandType.StoredProcedure

           

                      connection.Open()

                      command.ExecuteNonQuery()

                  End Using

           

           

          Enter this under the References and Imports tab.

           

          Assembly

               System.Data

               C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Data.dll

           

          Imports

               System.Data

               System.Data.SqlClient

            • Execute a Stored Procedure from PostExport
              donne4real _

              I tried this but got the following error message:

               

               

              Compiler error:#30002 of severity 0 Type 'SqlCommand' is not defined

                • Execute a Stored Procedure from PostExport
                  mdyoung _

                  Here's the code that I use on my pre & post processes to execute SPs.

                   

                  On the References and Imports tab,

                     Name: System.Data

                     Assembly: C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll

                   

                     Import: System.Data

                   

                  On the Pre or PostProcess tab, paste the following code and change the connection string and name of the SP to fit your needs.

                   

                   

                      ' Set up Connection object and Connection String for a SQL Client

                      Dim SQLCon As New SqlClient.SqlConnection

                   

                      SQLCon.ConnectionString = "Data Source=SQLServer;Initial Catalog=DatabaseName;User Id=UserName;Password=UserPassword;"

                      SQLCon.Open()

                   

                      'Set up command object and properties

                      Dim SQLCmd As New SqlClient.SqlCommand

                   

                      SQLCmd.CommandText = "Schema.SPName" ' Stored Procedure to Call

                      SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type

                      SQLCmd.Connection = SQLCon 'Active Connection

                   

                      Try

                          'Execute command

                          SQLCmd.ExecuteNonQuery()

                   

                      Catch ex As Exception

                          ' Do what you need to here or don't have it do anything

                   

                      End Try

                   

                      ' Dispose objects for garbage collection

                      SQLCmd.Dispose()

                      SQLCon.Close()

                      SQLCon.Dispose()

                    • Execute a Stored Procedure from PostExport
                      Olly Bond

                      Hello everyone,

                       

                      Just a quick note to say thank you for posting this helpful code - we've just used it to implement a Datapump process with a script that executes a stored procedure on our Datawatch|ES user database. This will allow us to automatically manage the user access control for our web-based report mining.

                       

                      Many thanks,

                       

                      Olly

                        • Execute a Stored Procedure from PostExport
                          scottnelson _

                          Just to expand on this thread a bit, I have a clean, neat wrapper function that makes it easy to call SQL Server stored procedures from data pump processes.  And as an added benefit, it will capture the output of any PRINT statements in the job log.  It will also trigger alerts if any errors happen.

                           

                          The function you want to call is ExecuteProcedure().  It takes a connect string parameter and a procedure name parameter.  It returns a boolean to indicate success/failure.

                           

                          The way I use it is to call a parameter-less stored procedure after data pump has loaded all my records from my input files into my SQL table. 

                           

                          I add this code to the global script and I also put my connect strings in there too so I can call this code from any process.

                           

                          Enjoy.

                           

                          public function ExecuteProcedure( ConnectString as string, ProcName as string ) as boolean

                           

                               Dim C as SqlConnection

                               Dim Cmd as sqlCommand

                               dim bSuccess as boolean = true

                               dim Msg as string = nothing

                           

                           

                               try

                                    C = OpenConnection( ConnectString )

                           

                                    cmd = c.CreateCommand()

                                    cmd.CommandType = CommandType.StoredProcedure

                                    cmd.CommandTimeout = 0

                                    cmd.CommandText = ProcName

                                    cmd.ExecuteNonQuery()

                               catch ex as SqlException

                                    Dim err As SqlError

                                

                                       For Each err In ex.Errors

                                           Msg = String.Format("Msg number , " & _                                   "procedure , line : ", _                                   ex.Number, ex.Procedure, ex.LineNumber, ex.Message)                  theJobLog.AddEvent( Msg )              Next           theJobLog.AddEvent("Job has failed", "SystemAlertJobFailed")           theJobLog.AddEvent("Process has failed", "SystemAlertProcessFailed")           theJobLog.AddEvent( ex.StackTrace )           bSuccess = false      catch ex as Exception           theJobLog.AddEvent("Job has failed", "SystemAlertJobFailed")           theJobLog.AddEvent( ex.Message, ex.StackTrace )           bSuccess = false      finally           c.Close()      end try      return bSuccess end function Public Sub OnSQLInfoMessage(ByVal sender As Object, ByVal args As SqlInfoMessageEventArgs)         Dim err As SqlError         Dim Msg As String         For Each err In args.Errors             Msg = String.Format("Msg number , " & _

                                                       "procedure , line : ", _

                                                       err.Number, err.Procedure, err.LineNumber, err.Message)

                           

                                      theJobLog.AddEvent( Msg )

                           

                                  Next

                              End Sub

                           

                          public Function OpenConnection(ByVal ConnectString As String) As SqlConnection

                                  Dim Conn As SqlConnection

                           

                                  Conn = New SqlConnection(ConnectString)

                           

                                  AddHandler Conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnSQLInfoMessage)

                           

                                  Conn.Open()

                           

                                  Return Conn

                          End Function

                            • Execute a Stored Procedure from PostExport
                              Gareth Horton

                              Hi Scott,

                               

                              Just to expand on it a little - you would have to add the following in the References and Imports tab in Global Script:

                               

                              Name: System.Data

                              Assembly: C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll

                               

                              Imports: System.Data

                                          System.Data.SQLClient

                               

                              Also, how your "theJobLog" construct would work is not clear here - I imagine you need another declaration somewhere?

                               

                               

                              Gareth

                               

                              Just to expand on this thread a bit, I have a clean, neat wrapper function that makes it easy to call SQL Server stored procedures from data pump processes.  And as an added benefit, it will capture the output of any PRINT statements in the job log.  It will also trigger alerts if any errors happen.

                               

                              The function you want to call is ExecuteProcedure().  It takes a connect string parameter and a procedure name parameter.  It returns a boolean to indicate success/failure.

                               

                              The way I use it is to call a parameter-less stored procedure after data pump has loaded all my records from my input files into my SQL table. 

                               

                              I add this code to the global script and I also put my connect strings in there too so I can call this code from any process.

                               

                              Enjoy.

                               

                              public function ExecuteProcedure( ConnectString as string, ProcName as string ) as boolean

                               

                                   Dim C as SqlConnection

                                   Dim Cmd as sqlCommand

                                   dim bSuccess as boolean = true

                                   dim Msg as string = nothing

                               

                               

                                   try

                                        C = OpenConnection( ConnectString )

                               

                                        cmd = c.CreateCommand()

                                        cmd.CommandType = CommandType.StoredProcedure

                                        cmd.CommandTimeout = 0

                                        cmd.CommandText = ProcName

                                        cmd.ExecuteNonQuery()

                                   catch ex as SqlException

                                        Dim err As SqlError

                                    

                                           For Each err In ex.Errors

                                               Msg = String.Format("Msg number , " & _                                   "procedure , line : ", _                                   ex.Number, ex.Procedure, ex.LineNumber, ex.Message)                  theJobLog.AddEvent( Msg )              Next           theJobLog.AddEvent("Job has failed", "SystemAlertJobFailed")           theJobLog.AddEvent("Process has failed", "SystemAlertProcessFailed")           theJobLog.AddEvent( ex.StackTrace )           bSuccess = false      catch ex as Exception           theJobLog.AddEvent("Job has failed", "SystemAlertJobFailed")           theJobLog.AddEvent( ex.Message, ex.StackTrace )           bSuccess = false      finally           c.Close()      end try      return bSuccess end function Public Sub OnSQLInfoMessage(ByVal sender As Object, ByVal args As SqlInfoMessageEventArgs)         Dim err As SqlError         Dim Msg As String         For Each err In args.Errors             Msg = String.Format("Msg number , " & _

                                                           "procedure , line : ", _

                                                           err.Number, err.Procedure, err.LineNumber, err.Message)

                               

                                          theJobLog.AddEvent( Msg )

                               

                                      Next

                                  End Sub

                               

                              public Function OpenConnection(ByVal ConnectString As String) As SqlConnection

                                      Dim Conn As SqlConnection

                               

                                      Conn = New SqlConnection(ConnectString)

                               

                                      AddHandler Conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnSQLInfoMessage)

                               

                                      Conn.Open()

                               

                                      Return Conn

                              End Function[/QUOTE]