如何使用 VB.NET 运行 SQLCMD 脚本 [英] How to run SQLCMD script using VB.NET
问题描述
我正在尝试使用 VB.NET 代码使用 SQLCMD(SQL 命令提示符)执行 SQL 查询.我正在使用 Windows Shell 脚本连接到服务器并执行查询并将结果集存储在 Excel 文件中.下面是我的代码,它不起作用.下面的代码中缺少什么?
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()
推荐答案
这是我测试过的,注意我更改了服务器、数据库和查询以匹配我的机器.我正在通过 VS2015 使用字符串插值.
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
没有VS2015的常规方式
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
这篇关于如何使用 VB.NET 运行 SQLCMD 脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!