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

(joaotsetsemoita@xxxxxxxxx) 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@xxxxxxxxxxxxx

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