Lets jump into a practical example of openrowset in action. A common issue among dataservers is disk space so it only makes sense to monitor for thresholds so that you can fix an issue before it becomes an emergency. Thresholds are great, and you probably have an alerting tool like HP Service Center to take care of that for you. However, what about trending and proactive issue resolution?
When you must maintain hundreds of machines, space trending is practically a requirement. You will likely want to monitor (and trend) disk space on the database level as well, and we will get to that in a separate post.
First, my approach toward the openrowset:
SET @LocalQuery = ‘
INSERT INTO #freeSpace
SELECT * FROM
OPENROWSET(‘‘SQLNCLI’‘, ‘‘Server=’ + @RemoteInstance + ‘;Trusted_Connection=yes;’‘,’” + @RemoteQuery + ”‘); ‘
EXEC master.sp_executesql @LocalQuery
Here, we figure we will try and keep everything as dynamic as possible. The only real things we need to specify are the driver and that this will be a trusted connection. We will construct the remote query dynamically in the code before openrowset and the remote instance will be passed into the instance upon execution (exec p_get_drive_details ‘HOSTNAME\INSTANCENAME’).
SET @RemoteQuery = ‘SET NOCOUNT ON; SET FMTONLY OFF; exec master.xp_fixeddrives’
To setup, first create a table drive_details with columns for: Date, InstanceName, DriveLetter, TotalMB, FreeMB
After that, create the procedure and run. In our crawler, which we will discuss later, we can loop through all of our available instances (we will get into error checking later too).
Bonus: Create a view on top of your table called drive_details_recent and have it just show the last run. This way we can have a history table for trending yet quickly look up the most recent info for either displaying on a front end or to just hide old data from view.