Post reply

linked server - dbcc showfilestats

  • November 22, 2011 at 1:22 pm

    #250994

    I need to obtain the showfilestats info from a linked server. I cannot create a stored proc on the linked server and run it remotely. The linked server is set to default to the proper database, so I don't need a "use" statement to set database context.

    Here's the latest attempt of what I've been trying:

    select * from openquery (linkedservername,

    'set fmtonly off; set nocount on; dbcc showfilestats with no_infomsgs;')

    Returns error: Cannot process the object "set fmtonly off; set nocount on; dbcc showfilestats with no_infomsgs". The OLE DB provider "SQLNCLI10" for linked server "linkedservername" indicates the object has no columns or the current user does not have permissions on that object.

    I can run the command from the linked server, so I don't believe it is permissions and I am receiving only one row returned with column names.

    Any suggestions?

    Thanks,

    Cindy

  • November 22, 2011 at 2:16 pm

    #1411820

    If you are using windows authentication it sounds like permissions on a double hop. Are you running this in your local laptop/desktop connected to a SQL server where there is a Linked Server configured to some other machine?

    When you connect from your laptop/desktop to the first server it authenticates you then when you try to use the link your authentication SID does not make it over to the next server. This will work if kerberos authentication is completely set up with server SPNs configured etc.

    I'm not sure this is your issue but your description fits this problem.

  • November 22, 2011 at 2:33 pm

    #1411842

    Interesting thought. I am double-hopping. So I just setup two local accounts on each server with sysadmin (it's dev). The logins and passwords are the same on each server.

    I logged into the server with the local account and when I ran the openquery against the linked server, I got the same exact error.

    Any other ideas that haven't occurred to me?

    Thanks,

    Cindy

  • November 22, 2011 at 4:49 pm

    #1411910

    Try this:

    EXEC ('set fmtonly off; set nocount on; DBCC Showfilestats WITH no_infomsgs') at [Nameoflinkedserver]

  • November 23, 2011 at 6:18 am

    #1412113

    Thank-you! That worked. I hadn't seen that syntax for EXEC before, so I greatly appreciate your help.

    Cindy

  • November 23, 2011 at 3:05 pm

    #1412501

    You're welcome. I think I read somewhere that this is the go forward method of using linked servers that the "openquery" method would be depricated at some point, along with the four part name reference.

Viewing 6 posts - 1 through 5 (of 5 total)

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