Help a newb (issue with excel, ms access and SQL Server)

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

Re: Help a newb (issue with excel, ms access and SQL Server)

(joaotsetsemoita@gmail.com) writes:
> 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??

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx