exporting query result to excel

I am trying开发者_开发问答 to execute the below sql but I am getting "Invalid object name '.Sheet1$'."

INSERT INTO OPENDATASOURCE 
    ('Microsoft.Jet.OLEDB.4.0', 
    'Database=c:\test.xls;Extended Properties=Excel 8.0')..[Sheet1$])
    SELECT col1 FROM table;

its in mssql 2005.

any help is appreciated.

If you have xp_cmdshell enabled you can do this to export to delimited text file, which will open perfect in Excel.

EXEC xp_cmdshell 'SQLCMD -S [SERVERNAME] -d [DBNAME] -o "C:\Output.txt" -s "," -U "[USERNAME]" -P "[PASWORD]" -Q "SELECT TOP 10 * FROM table"';

According to this posting (and a few other samples Google found for me), you need three dots before the table: 8.0)...[Sheet1$]. (Don't ask me why).

Added: The German translation of this provides a complete example of Excel access:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;