How to store sql result without creating a table t

Question

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

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

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.

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.

Answer 1

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.

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.