用什么方法导入EXCEL报表文件呢?exe master..xp_cmdshell 'bcp "select * from database.dbo.tableName" queryout c:\test.xls -c -q -SserverName -Usa -Ppwd'or exe master..xp_cmdshell 'bcp databaseName.dbo.tableName out c:\test.xls -c -q -SserverName -Usa -Ppwd'其中tableName是你用存储过程动态生成的存放报表数据的表
用BCP 能使用复杂查询么?能使用存储过程么?我没做过,请指教
, 'set fmtonly off exec sp_who2' )orINSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' select * from openrowset ( 'SQLOLEDB' , 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes'
, 'set fmtonly off exec sp_who2' )
but bcp is probably a better way, or you can invoke osql command in your codeosql /U sa /P password /d pubs /S Server9 /Q "sp_help" -o ofile.txtsee
http://www.sqlteam.com/item.asp?ItemID=744
http://www.sqlteam.com/item.asp?ItemID=4722
exec master..xp_cmdshell 'bcp "select * from database.dbo.tableName" queryout c:\test.xls -c -q -SserverName -Usa -Ppwd'or exec master..xp_cmdshell 'bcp databaseName.dbo.tableName out c:\test.xls -c -q -SserverName -Usa -Ppwd'
bcp 实用工具在 Microsoft® SQL Server™ 2000 实例和数据文件之间以用户指定的格式复制数据。语法
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]