Friday, 31 August 2012

Retrieve SQL Data from Stored Procedure using OPENROWSET

So recently I was busy with a performance monitoring and SQL space monitoring quest.

I decided that due to all the irritated managers and people in the office, that I'll put together a report that gets the drive space from all the hard drives of all the servers, and then report on free space, used space, database space usage vs. file usage etc. This should highlight the issues, and then put people in the spotlight so that they can start cleaning their servers up that they're responsible for.

The funny thing was, that the server they game me that had access to email couldn't create linked servers.

Create Linked Server Error.

Unfortunately I don't have the authority or the time to repair the instance, or bother with troubleshooting this error right now (I'm a bad person, I know.), and also because no one else really uses this sever for ANYTHING, I thought i'd play with the OPENROWSET commands.

To use the OPENROWSET clause, you need to make sure that you have 2 options enabled.

Ad hoc distributed queries, and Ole Automation Procedures.

Once you've enabled the procedures, you can then perform OPENROWSET queries. The reason I do this is because I want to consolidate the data form all my servers, and then union the results into one query that I can execute when I need to, or if you've got some reporting knowledge, you can build it into an SSRS report or Crystal Report etc.

To retrieve the required data from my server, and test that it works. I did a simple select from my master.sys.tables.

Now that works fine until you try to execute a stored procedure inside the OPENROWSET.

All I did was replace the SELECT * FROM sys.tables with a simple EXEC Xp_fixeddrives, surely this should have worked, I mean it's also just selecting data. Oh well, lets keep hacking at it :-)

Now there's this nice little function called FMTONLY, which Returns only meta data to the client. You can test it, by turning it ON, it will only return the Columns, and No Rows, so I suspect that It's actually battling to obtain the meta data, so when I force the option to be turned OFF, which is actually it's default setting, and try to run my stored procedure again, from inside the OPENROWSET.

By setting the value OFF explicitly, it returns results when we execute the proc from inside the OPENROWSET.

I hope this post helps other's that's battled with a similar problem.

If you want more information about the FMTONLY statement, then read up about it on BOL

Keep checking my posts, as I generate more traffic, I'll start posting more tips, and tricks.

Kind Regards