hello everyone.

I recently changed the database of a project from Ms Access to SQL
server and had to make a lot of adjustments but there's one here where
I can't figure it out how to do this in SQL server.

I was using the following to create an excel file based on the select
from Ms Access.

"SELECT * INTO [Excel 8.0;Database=" & server.mapPath("../documents/
example.xls].[sheet1]")&" FROM (SELECT * FROM TABLE1)"

This was working fine until the bd was changed to Ms Server 2005 where
it occurs an error. I will post the error even if its kind useless.

Error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near ')'.

As I said, the exact same code works fine in access. Any sugestion how
to get this solved??

I know some turn arounds like create a content/type x-excel page and
this allow the user to save the file, but I really need is to create
the file .XLS on the disk to allow the user to download whenever they
want.

Any help is highly appreciated.

Thanks in advance

Joao

(joaotsetsemoita (AT) gmail (DOT) com) writes:
Well, I can explain why you get the error: there are tons of syntax
differences betweeen Access and SQL Server. There is a SELECT INTO
command in SQL Server, but I can't imagine that you can create
Excel files with it.

You can access Excel file with help of OPENROWSET, but I have don't
this myself. I believe there are some examples in Books Online.

Best approach to do this client side. In SQL Server you would have to
do this in a CLR procedure. Of if you are on SQL 2000 with help of
sp_OAmethod and friends, which is no fun exercise at all.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx