SQLCMD to export data to CSV

Question

I am using SQLCMD (SQL Command Prompt) to export data to CSV, but it is throwing an access control related issue:

Login failed for user 'MEA\UserName'....Cannot open database "C:\ABC.mdf " But User(UserName) has already provided full access control for this database.

Below is my code:

Dim ServerName As String = System.Net.Dns.GetHostName()

Dim DoubleQuote As String = Chr(34)

Dim DatabaseName As String = DoubleQuote & "C:\Giv 15Mar2017\WindowsApplication1_Givaudan_new\WindowsApplication1_Givaudan_new\WindowsApplication1\Database\ABC.mdf" & DoubleQuote

Dim QueryToExceute As String = DoubleQuote & "set nocount on;Select  * from EKPO" & DoubleQuote

Dim ExportFileName As String = "C:\Data\TableData.csv"

Dim QueryToExceute1 As String = DoubleQuote & "set nocount on;Select * from EKPO" & DoubleQuote

Dim ExportFileName1 As String = "C:\Data\TableDataCorrection.csv"

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 " &
QueryToExceute1 & " -o " & ExportFileName & "  -s""|"" -W"

Process.StartInfo.WorkingDirectory = "C:\Data"

Process.Start()
Process.WaitForExit()

score 0 · Answer 1 · answered Mar 16 '17 at 09:35

You have to specify database name not MDF path.

sqlcmd -S "YOURSERVER\YOURINSTANCE" -d YOURDATABASENAME

Even if you use LocalDb you can't directly access to MDF file but you have to use this sintax:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d YOURDATABASENAME

SQLCMD to export data to CSV

1 Answers1