如何使用VB.NET运行SQLCMD脚本

admin

如何使用VB.NET运行SQLCMD脚本

sql

我正在尝试使用VB.NET代码使用SQLCMD（SQL命令提示符）执行SQL查询。我正在使用Windows
Shell脚本连接到服务器，并执行查询并将结果集存储在Excel文件中。下面是我的代码不起作用。以下代码缺少什么？

    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)

我也尝试了无法正常工作的过程类。下面是我的代码：

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

阅读 181

2021-07-01

共1个答案

admin

这是我测试的内容，请注意，我更改了服务器，数据库和查询以匹配我的机器。我正在通过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

不带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

2021-07-01