检索字段架构中的存储过程的Visual Studio，它使用临时表 [英] Retrieve fields schema in Visual Studio of stored procedure which uses temp tables

问题描述

我有一些复杂的存储过程拉着使用链接的服务器上的其他数据库中的数据。该数据被放入它们加入输出选择查询的临时表。该程序工作正常，但在Visual Studio中，如果我试图用我的错误无效的对象名称#tmp或任何第一次临时表被称为设计师存储过程添加到数据集。这是无法检索数据库模式。这是相同的ASP.NET中使用和SqlDataSource的。

I have some complex stored procedures pulling data from other databases using linked servers. This data is put into temp tables which are joined in a select query for output. The procedures work fine but in visual studio if I try to add the stored procedure to a dataset using the designer I get the error invalid object name #tmp or whatever the first temp table is called. It is unable to retrieve the database schema. It's the same for using and sqldatasource in ASP.NET.

该程序仍然是可用的，但我必须手动添加的所有列它应该输出到数据表。这将是一个痛苦的手工做的，我认为它是做与Visual Studio从存储过程采集输出域的方式，它似乎并没有以正常方式运行。是否有办法来纠正这个，因为我有相当多的这些做，不想必须手动添加的所有列，这是耗时且容易出错。

The procedure is still usable but I have to manually add all the columns it should output to the datatable. This is going to be a pain to do manually and I assume it is to do with the way visual studio gathers the output fields from the stored procedure, it doesn't seem to run it in the normal way. Is there a way to correct this as I have quite a lot of these to do and don't want to have to add all the columns manually, which is time consuming and error prone.

推荐答案

在Google上搜寻了一​​段时间后，我发现一个奇怪的解决方案将：

After googling for a while I found a bizarre solution putting:

IF 1=0 BEGIN
SET FMTONLY OFF
END

在这个存储过程的开始允许设计者获取正确的架构信息。这code显然是没有运行，但它解决了问题。 FMTONLY是相关的，只返回有关存储过程的元数据。我使用的SQL Server 2005。

At the start of your stored procedure allows the designer to correctly get the schema information. This code obviously never runs but it solves the issue. FMTONLY is related to only returning meta data about a stored procedure. I am using sql server 2005.

这篇关于检索字段架构中的存储过程的Visual Studio，它使用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！