Post reply

Last good DBCC CHECK date.

  • December 21, 2011 at 2:38 am

    #250244

    Hi,

    I wondered if someone may be able to help me out with something.

    The task is to poll from a central server all the other SQL servers to find out the last known good DBCC CHECKDB date.

    I can get this information from DBCC dbinfo() WITH TABLERESULTS.

    The issue is how I get this to run across all servers.

    My initial thoughts were to use OPENROWSET and ms_spforeachdb, however OPENROWSET does not support multiple record sets, so I did some playing around, I know all the servers and all the db’s so I thought I could just loop over these and run the DBCC dbinfo command for each, however I can’t get the results into a temp table visible to the polling server, the error I get says that OPENROWSET is expecting a table and the DBCC is returning one (by default), even though I have specified with TABLERESULTS.

    “Msg 7357, Level 16, State 2, Line 1

    Cannot process the object "DBCC dbinfo() WITH TABLERESULTS". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.”

    I do have other options, such as I could generate the dates on each server compile to table and then query that but that means I have to deploy to every server I wish to monitor.

    Doe’s anyone have any other idea’s?

    Thanks,

    Nic

  • December 21, 2011 at 2:47 am

    #1424093

    Think I may have resolved it, bit more research and it turns out the below works;

    'SET NOCOUNT ON; SET FMTONLY OFF; exec (''dbcc dbinfo() WITH TABLERESULTS'')')

    It's the FTMONLY OFF which resolves the issue.

    Also see this post, http://www.sqlservercentral.com/Forums/Topic563105-338-1.aspx#bm563796

    Thanks,

    Nic

  • December 21, 2011 at 3:14 am

    #1424103

    My final issue now is getting it to run on a specific database, rather than just on the master db.

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes;',

    ' USE dbname;SELECT db_name();')

    When I have the above as my command I get the following error;

    Cannot process the object " USE dba;SELECT db_name();". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    Any idea's on how I can get round this?

    Thanks,

    Nic

  • December 21, 2011 at 3:43 am

    #1424121

    Cracked it, I was being silly, the DBCC dbinfo() command takes the dbname, so I can just pass it in to that.

    Problem solved.

    Regards,

    Nic

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply