通过Excel数据连接调用的存储过程失败 [英] Stored Procedure called via Excel data connection fails

问题描述

我在Excel 2007工作簿中有表。该表具有到存储过程的外部SQL数据连接。在存储过程中，我想在查询数据之前清除并填充一个中间表。

如果我没有 INSERT 中的语句（我将数据插入到Excel之外），但是一旦我尝试在存储过程中插入临时表，我会收到一个错误查询没有运行或数据库无法打开。

有趣的是，尽管有错误，插入存储程序仍然被执行 - 错误后数据就在那里。任何人都知道如何在不触发错误的情况下进行插入？

解决方案

我做了更多的挖掘，我遇到这篇文章包含答案：
运行高级从Excel的SQL存储过程

在插入之前，我必须打开SET NOCOUNT，插入后，我添加SET NOCOUNT OFF。

I have table in a Excel 2007 workbook. The table has an external SQL data connection to a stored procedure. Inside the stored procedure I'd like to clear and populate an intermediate table before querying data from it.

The stored procedure works if I don't have the INSERT statement in it (I insert the data outside Excel), but as soon I try to insert into my temporary table in the stored procedure, I get an error "The query did not run or the database could not be opened".

What's interesting too is that despite the error, the insert inside the stored procedure still gets executed - the data is there after the error. Anybody knows how I can do the insert without triggering the error?

解决方案

I did some more digging and I came across this article that contained the answer: Running Advanced SQL Stored Procedures from Excel

Basically before my insert I have to put SET NOCOUNT ON and after the insert, I add SET NOCOUNT OFF.

这篇关于通过Excel数据连接调用的存储过程失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！