I want to export data from database to Excel file. This is my code when I use MS Access:
Dim My_Connection As New System.Data.OleDb.OleDbConnection
Dim sql As String
Dim cmd As System.Data.OleDb.OleDbCommand
My_Connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myDB.mdb;Persist Security Info=False;Jet OLEDB:Database Password=myPassword"
My_Connection.Open()
sql = "SELECT product_ID, client_ID INTO [Excel 12.0;DATABASE=E:\test.xls].[EXPORT] FROM Orders"
cmd = New System.Data.OleDb.OleDbCommand(sql, My_Connection)
cmd.ExecuteNonQuery()
This code work perfectly. I don't have Excel in my PC. I created xls file that I open on another computer with Excel or Apache Calc.
But I move database to SQL Server 2014.
What I have tried:
I change my code:
Dim My_Connection As New System.Data.SqlClient.SqlConnection
Dim sql As String
Dim cmd As System.Data.SqlClient.SqlCommand
My_Connection.ConnectionString = "Data source=localhost;Initial Catalog=myDB;Integrated Security=false;uid=sa;password=myPassword;"
My_Connection.Open()
sql = "SELECT product_ID, client_ID INTO [Excel 12.0;DATABASE=E:\test.xls].[EXPORT] FROM Products"
cmd = New System.Data.SqlClient.SqlCommand(sql, My_Connection)
cmd.ExecuteNonQuery()
When I try to run I just get the error message:
The specified schema name "
Excel 12.0;DATABASE=E:\test.xls
" either does not exist or you do not have permission to use it.