无法用Select into 语句从SQL导出到EXCEL

Question

各位好。我使用C#执行cmd.ExecuteNonQuery();

SELECT * INTO [Excel 8.0;Database=D:\Source_Codes\SMD_ERP_SYSTEM\SMD_ARM\SMD_ARM\bin\Debug\book1.xls;].[Sheet1] FROM gy_Customer

这个语句的时候，提示失败，原因：

The specified schema name "Excel 8.0;Database=D:\Source_Codes\SMD_ERP_SYSTEM\SMD_ARM\SMD_ARM\bin\Debug\book1.xls;" either does not exist or you do not have permission to use it.

请问如何解决这个。。？我从MSDN上看到这个语句是没有execl也会自动创建excel的。。

Answer 1

为避免数据库造成安全后门，数据库引擎通常都在受限用户身份下运行。

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful.
Visual C++ MVP

Answer 2

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from 表名

這樣用

如果是生成excel時用bcp

--导出查询的情况

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'

---

ROY WU(吳熹)

Answer 3

请问如何解决这个。。？我从MSDN上看到这个语句是没有execl也会自动创建excel的。。

那是建表, 不是建文件

Answer 4

进来看看几位牛人

Answer 5

你这个是服务器本地建立。肯定没问题

我现在是要远程导出到其他机器上。

Answer 6

declare @constr varchar(8000),@cmd varchar(8000),@obj int,@out int

select @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;Database=c:\Book1.xls"'
select @cmd = 'create table [systables]([name] memo)'

exec sp_OACreate 'adodb.connection',@obj out 
exec sp_OAMethod @obj,'open',null,@constr
exec sp_OAMethod @obj,'execute',@out out,@cmd
exec sp_OAMethod @obj,'close',null
exec sp_OADestroy @obj

result:成功建立数据表　c:\book1.xls, Sheet Name: systables


insert into openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=c:\Book1.xls','select [name] from [systables$]')
select name from sys.tables


result:(17 行受影响)

----------------------
安全性
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override

EXEC sp_configure 'Ole Automation Procedures', 1 
RECONFIGURE with override 	

EXEC sp_configure 'Ad Hoc Distributed Queries', 1 
RECONFIGURE with override 	

---

hello