Kategorien
VB.NET

Execute MSSQL Task with VB.NET Console Application

Download the latest Visual Studio Community Edition (about 4gb). Then start a new Project “Console VB.NET” and use the following code for executing a stored procedure on your MS SQL Server:

Imports System.Data.SqlClient

Module Module1

    Sub Main()

        Dim jobConnection As SqlConnection
        Dim jobCommand As SqlCommand
        Dim jobReturnValue As SqlParameter
        Dim jobResult As Integer

        jobConnection = New SqlConnection("Data Source=(localhost);Initial Catalog=LN_DataReplication;Integrated Security=SSPI")
        jobCommand = New SqlCommand("ReplicateTables", jobConnection)
        jobCommand.CommandType = CommandType.StoredProcedure
        jobCommand.CommandTimeout = 0

        jobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
        jobReturnValue.Direction = ParameterDirection.ReturnValue
        jobCommand.Parameters.Add(jobReturnValue)

        jobConnection.Open()
        jobCommand.ExecuteNonQuery()
        jobResult = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer)
        jobConnection.Close()

        Select Case jobResult
            Case 0
                Console.WriteLine("SQL Server Agent job, ReplicateTables, executed successfully.")
            Case Else
                Console.WriteLine("SQL Server Agent job, ReplicateTables, failed to execute.")
        End Select
        Console.Read()

    End Sub

End Module