|
|
Posted by Erland Sommarskog on 10/08/07 22:03
(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|