Can't create excel file with SQL Server 2000 client.

Hi ;

I am trying to create several Excel sheets using SQL 2000 views like so:

Select * INTO [Excel 8.0;Database="C:\spreadSheets\aNew.xls"] FROM [aView].

When I try and execute this in my app I get the following - Specified owner
name 'Excel 8.0;Database=c:\spreadSheets\aNew.xls' either does not exist or
you do not have permission to use it.

If I use the above Select statement with an OLEDB connection it works.

I am using Imports System.Data.SqlClient, instantiating a new SQlConnection
object, opening the connection, etc..

Thanks,

Gordon

This special select into sql statement syntax only works if you use the Jet OLEDB Provider, it's not supported by SQL Server.

So you need to open a connection to some Access database (even a dummy one will work) and do something like this:

I wrote this KB article about a decade ago (arg where does the time go) that demos the syntax:

200427 How To Export and Import Access Tables Using DAO or ODBC
http://support.microsoft.com/default.aspx?scid=kb;EN-US;200427

Here is some demo code that uses .NET:

System.Data.OleDb.OleDbConnection conn;

System.Data.OleDb.OleDbCommand cmd;

conn = new System.Data.OleDb.OleDbConnection(@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\junk.mdb;");

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * into [Excel 8.0;Database=C:\\Book1.xls;].[MyJunk] FROM [ODBC;Driver=SQL Server;Server=mySQLServer;Database=Pubs;Trusted_Connection=Yes;].[authors]";

cmd.ExecuteNonQuery();

However note there are tons of annoyances with using Jet driver and Excel, the Jet and Excel teams just never seem to want to make this any easier for the customer. The other way to do this is poke values into Excel speadsheet using ole automation (which used to be easy cheesy in VB6 but now is a nightmare in .NET since .NET deprecated COM). With newer version of Excel 2007 I wonder maybe this is easier in .NET.