sql2005使用master..xp_cmdshell bcp导出excel文件默认格式问题

Question

最近遇到一个问题，需要定期将查询结果生产EXCEL文件，并发送到各个部门。
在网上查询sql导出excel文件，都是说使用master..xp_cmdshell bcp。导出现在是没问题了，只不过导出的文件默认是文本格式，在excel打开或保存的时候，都是提示数据兼容性问题。求大神解决。
sql语句如下
EXEC master..xp_cmdshell 'bcp "SELECT * from budgetemporary" queryout "E:\budget\qiguanbu\renliziyuanbu\月度费用报表.xls" -c -S "sqlservername" -U "user" -P "key"'

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

你使用的SQL Server 2008 R2，其版本中没有提供BCP工具，因此无法直接使用master..xp_cmdshell来执行SQL语句。你可以尝试使用以下替代方案：

1. 使用ADO.NET API来读取数据库并写入Excel文件。

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    using (SqlCommand command = new SqlCommand("SELECT * FROM budgettemporary", connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Write data to Excel file here
            }
        }
    }
}

1. 使用ODBC驱动程序来读取数据库并写入Excel文件。 如果你有一个ODBC驱动程序，你可以使用以下命令来连接到你的数据库并执行SQL语句：

EXEC sp_executesql N'SELECT * FROM budgettemporary', N'N', 'yourconnectionstring'

然后，使用以下命令来创建一个新的Excel文件并写入数据：

CREATE EXCEL TABLE [Sheet1] (
   [Column1] VARCHAR(255),
   [Column2] VARCHAR(255)
);
INSERT INTO [Sheet1] ([Column1], [Column2])
SELECT [Column1], [Column2]
FROM [BudgetTemporary];

这将创建一个名为Sheet1的新工作表，并插入所有数据行到它。你可以根据需要修改这个命令以适应你的需求。

1. 如果你的数据库支持XML存储过程（例如，如果它是SQL Server 2016或更高版本），你可以使用以下命令来执行SQL语句并获取结果集：

DECLARE @cmd AS NVARCHAR(MAX) = N'BULK INSERT dbo.BudgetTemporary
SET IDENTITY_INSERT dbo.BudgetTemporary ON
INSERT INTO dbo.BudgetTemporary
SELECT *
FROM OPENROWSET(N'Microsoft.ACE.OLEDB.12.0',N'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\path\to\database.accdb',N'Select * from BudgetTemporary') AS tempTable
SET IDENTITY_INSERT dbo.BudgetTemporary OFF';

EXEC master..XP_EXECUTESQL @cmd

注意：这可能需要在你的SQL Server服务器上安装OLE DB 12.0驱动。