How to run SQLCMD script using VB.NET

Question

I am trying to execute SQL query using SQLCMD (SQL Command Prompt) using VB.NET code. I am connecting to the server using Windows Shell script and executing a query and storing the result-set in Excel file. Below is my code which is not working. What is missing in below code?

    Dim Command
    Dim ServerName As String
    Dim DatabaseName As String
    Dim QueryToExceute As String

    ServerName = "IN2175533W1"
    DatabaseName = "C:\FileDirectory\XYZ.mdf"
    QueryToExceute = "Select * from Table_Name"

   Command = """C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE"" " & "-S " & ServerName & " -d " & DatabaseName & " -Q " & QueryToExceute & " -s" & "," & " -o" & "C:\TestQuery.xlsx"

    Dim wsh As Object = CreateObject("WScript.Shell")

    'Using WScript to execute Stuff
    wsh = CreateObject("WScript.Shell")
    wsh.Run(Command)

I tried process class as well which is not working. Below is my code:

    Dim Command
    Dim ServerName As String
    Dim DatabaseName As String
    Dim QueryToExceute As String

    ServerName = "IN2175533W1"
    DatabaseName = "C:\ABC\XYZ.mdf"
    QueryToExceute = "Select * from Quality"


        Dim Process = New Process()
    Process.StartInfo.UseShellExecute = False
    Process.StartInfo.RedirectStandardOutput = True
    Process.StartInfo.RedirectStandardError = True
    Process.StartInfo.CreateNoWindow = True
    Process.StartInfo.FileName = "SQLCMD.EXE"
    Process.StartInfo.Arguments = "-S " & ServerName & "-d" & DatabaseName & "-Q" & QueryToExceute & "-s" & "," & "-o" & "C:\Testing1.xlsx"

    Process.StartInfo.WorkingDirectory = "C:\users\rahul.wankhade\Desktop"
    Process.Start()
    Process.WaitForExit()

SQLCMD is an utility and an application by itself. You should rather use `Process` class — Rahul, Mar 11 '17 at 10:26
@Rahul....I tried process class but unable to execute query. — rahul16590, Mar 11 '17 at 10:37

Karen Payne · Accepted Answer · 2017-03-11T16:17:45.163

Here is what I tested, note I changed the server, database and query to match my machine. I'm using string interpolation via VS2015.

Module Module1
    Sub Main()
        Dim ServerName As String = "KARENS-PC"
        Dim DatabaseName As String = "C:\Data\NORTHWND.MDF"
        Dim DoubleQuote As String = Chr(34)
        Dim QueryToExceute As String =
            $"{DoubleQuote}SELECT CompanyName,ContactName FROM Customers{DoubleQuote}"
        Dim ExportFileName As String =
            $"{DoubleQuote}C:\Data\MyDataFromSqlServer.csv{DoubleQuote}"

        Dim Process = New Process()
        Process.StartInfo.UseShellExecute = False
        Process.StartInfo.RedirectStandardOutput = True
        Process.StartInfo.RedirectStandardError = True
        Process.StartInfo.CreateNoWindow = True
        Process.StartInfo.FileName = "SQLCMD.EXE"
        Process.StartInfo.Arguments =
            $"-S {ServerName} -d {DatabaseName} -E -Q {QueryToExceute} -o {ExportFileName} -h-1 -s"","" -w 700"
        Process.StartInfo.WorkingDirectory = "C:\Data"
        Process.Start()
        Process.WaitForExit()
        Console.WriteLine("Done")
        Console.ReadLine()

    End Sub
End Module

Conventional way without VS2015

Module Module1
    Sub Main()
        Dim ServerName As String = "KARENS-PC"
        Dim DatabaseName As String = "NorthWindAzure"
        Dim DoubleQuote As String = Chr(34)
        Dim QueryToExceute As String =
            DoubleQuote & "SELECT CompanyName,ContactName FROM Customers" & DoubleQuote
        Dim ExportFileName As String =
            DoubleQuote & "C:\Data\MyDataFromSqlServer.csv" & DoubleQuote

        Dim Process = New Process()
        Process.StartInfo.UseShellExecute = False
        Process.StartInfo.RedirectStandardOutput = True
        Process.StartInfo.RedirectStandardError = True
        Process.StartInfo.CreateNoWindow = True
        Process.StartInfo.FileName = "SQLCMD.EXE"
        Process.StartInfo.Arguments =
            "-S " & ServerName & " -d " & DatabaseName & " -E -Q " &
            QueryToExceute & " -o " & ExportFileName & "  -h-1 -s"","" -w 700"
        Process.StartInfo.WorkingDirectory = "C:\Data"

        Process.Start()
        Process.WaitForExit()
        Console.WriteLine("Done")
        Console.ReadLine()
    End Sub
End Module

...Thanks a lot..I am using Visual Studio 2012 where String Interpolation does not work....could you rectified your code to work it in visual studio 2012 — rahul16590, Mar 11 '17 at 16:09
This is why it's a good idea to indicate the version :-) I edited my post with a second version for VS2012, — Karen Payne, Mar 11 '17 at 16:18

score 0 · Answer 2 · answered Oct 12 '18 at 11:19

The other way around which I did is below code, read out all code in a string object, parse it with replacing Go to any other symbol and execute the array one by one.

Sub ExecuteWithCommand()
    Try
        Dim sFile As FileInfo = New FileInfo(Directory.GetCurrentDirectory.ToString() & "\Script.sql")
        If (sFile.Exists = True) Then
            Dim dbObj = New SqlDataAccess()   
           'your SQL data-access class'
            Dim objReader As New System.IO.StreamReader(Directory.GetCurrentDirectory.ToString() & "\Script.sql")
            Dim scriptArr As String()
            Dim i As Integer
            dbObj.Command.CommandText = objReader.ReadToEnd.Replace("GO", "#")  
            'use any other symbol which you want, keeping in mind, its not used elsewhere in script'
            scriptArr = dbObj.Command.CommandText.Split("#")

            For i = 0 To scriptArr.Length - 1
                dbObj.Command.CommandText = scriptArr.GetValue(i)
                dbObj.Command.ExecuteNonQuery()
            Next

        End If
    Catch ex As Exception            
        Console.WriteLine(ex.Message)
    End Try

How to run SQLCMD script using VB.NET

2 Answers2

Linked