March 9, 2012 at 8:24 am
I need to somehow have two iterations, one to iterate through a table of server names and one to iterate through the databases on each instance. This is what I'm playing with so far. My goal is to go out to each instance from one instance in order to gather file space statistics and insert it into a table. I am trying to do it all from one instance instead of having a job on each instance to insert into the master instance (That I have gotten to work). Hopefully this makes sense, please let me know if I can clarify. Thanks!
DECLARE @server sysname
DECLARE @DB sysname
DECLARE cSERV CURSOR FOR
SELECT server_name FROM SpaceCheck.dbo.ServerNames
OPEN cSERV
FETCH NEXT FROM cSERV INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cDBs CURSOR FOR
SELECT name FROM ('+ @server + '.sys.databases)
OPEN cDBs
FETCH NEXT FROM cDBs INTO @DB
March 9, 2012 at 9:24 am
We designed this at my old place. We called it a monitor. It would allow us to monitor all sorts of things from a single monitoring instance. It can get a bit involved and I will need to reference some of my old stuff so I may not get back to you till mid next week.
But if I were to try not to over complexify things I would say off hand your either going to need to use linked sql or open query or both and dynamic sql. The dynamic sql makes it a bit more challenging or at the very least not very readable.
Regardless, I’ll try to get back to u when I can.
March 9, 2012 at 9:25 am
also, u may want to use the sp_msforeachdb functionality. Me personally, i stay away from it but that only cause old habit die hard.
March 12, 2012 at 5:55 am
Cool, thank you, any help would be appreciated. I am trying to use that cursor with a combination of linked servers and dynamic SQL now but you are right, it makes things complicated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply