从Excel运行SQL Server过程 [英] Run a SQL Server procedure from Excel

问题描述

我正在使用SQL Server 2008 Enterprise。我在一个数据库中创建了一个过程。该过程由对不同数据库的多个查询组成，最终组合结果集正在显示。

我尝试通过Excel执行它，因此结果将自动显示Excel表，但我收到错误：

查询没有运行，或数据库表无法打开。检查数据库服务器或联系您的DBA。确保外部数据库可用，未被移动或识别，然后再次尝试操作

我创建了一个更简单的过程只查询一个数据库，结果显示在Excel表中，没有任何问题。

因此，我怀疑原始的程序由于我正在查询的事实而失败程序中的几个数据库，当在外部数据属性的连接详细信息中，只提及一个数据库。

我的问题是 - 可以解决吗？我可以在程序中使用多个数据库，并在Excel中看到吗？

谢谢，
Roni

解决方案

我将过程转换为具有表变量而不是临时表，我已经将set nocount on添加到过程的开头。

第二个动作解决了这个问题。

第一个动作改善了程序的响应时间。

I'm using SQL Server 2008 Enterprise. I created a procedure in one database. The procedure is composed of several queries to different databases and the final combined result set is being displayed.

I try to execute it via Excel, so the results will appear automatically in Excel sheet, but I'm getting the error:

The query did not run, or the database table could not be opened. Check the database server or contact your DBA. Make sure the external database is available and hasn't been moved or recognized, then try the operation again

I created a simpler procedure that queries only one database, and the results displayed at the Excel sheet with no issues.

Hence I suspect that, the original procedure failed due to the fact that I'm querying several databases in the procedure, when in the connection details of the "External Data Properties", only one database is mentioned.

My question is - can it be solved? Can I use multiple databases in the procedure and see it in the Excel?

Thanks, Roni

解决方案

I transformed the procedure to have Table Variables instead of Temporary tables and I've added "set nocount on" to the beginning of the procedure.

The second action solved the issue.

The first action improved the response time of the procedure.

这篇关于从Excel运行SQL Server过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！