如何在不创建表来存储存储过程结果的情况下存储sql结果 [英] How to store sql result without creating a table to store the result of a stored procedure

问题描述

我们正在运行 exec xp_fixeddrives 来获取与SQL Server关联的每个物理驱动器的可用空间。

We are running exec xp_fixeddrives to get the free space for each physical drive associated with the SQL Server.

我正在将此代码作为SSIS程序包的一部分运行，该程序可获取所有SQL Server的可用空间。

I am running this code as part of an SSIS package which fetches all the SQL servers free space.

当前，我正在 tempdb中创建一个表并将 exec xp_fixeddrives 的结果插入到此表中。但是我面临的问题是，无论何时重新启动服务器，由于表位于 Tempdb 上，我都面临访问问题。

Currently I am creating a table in tempdb and inserting the results of exec xp_fixeddrives into this table. But the issue I am facing is, when ever the server is restarted I am facing access issue as the table is on Tempdb.

我不太喜欢在 Master DB或 Model DB中创建表的想法。我面临的一个挑战是我们在2000年至2014年的不同版本的SQL Server实例上执行。因此，显然我要牢记一些问题。

I don't really like the idea of creating a table in Master DB or Model DB. A challenge I am facing is we execute on difference instance of SQL Server versions ranging from 2000 - 2014. So obviously there are issues I have to keep in mind.

任何建议在此非常感谢。

Any suggestion on this are much appreciated.

推荐答案

很明显，只要重新启动SQL Services，SQL Server就会重置TempDb。在这种情况下，您将遇到访问问题，因为该表不存在。如果我也想存储历史检查信息，我可能会创建自己的表来存储详细信息。

That's obvious, SQL Server reset TempDb whenever SQL Services is restarted. In that case, you will face access issues because that table won't exists. I would probably create my own table to store the details if I want to store historical check information also.

如果您正在从SSIS运行代码并希望发送邮件验证之后，您甚至不必创建任何表。只需从将在查询下运行的执行SQL任务中的SSIS中填充一个对象变量即可。

If you are running your code from SSIS and you want to send mail just after validating it then you don't even have to create any table. Just fill a object variable in SSIS from execute SQL task which will be running below query

DECLARE @t TABLE 
(Drive VARCHAR(1),Size INT)

INSERT INTO @t 
EXEC MASTER..xp_fixeddrives

SELECT * FROM @t

在脚本任务中读取此对象变量以发送邮件。

Read this object variable in script task to send mail.

这篇关于如何在不创建表来存储存储过程结果的情况下存储sql结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！