Post reply

Unable to run the query DBCC sqlperf(logspace) using openrowset

  • September 3, 2008 at 9:24 am

    #212055

    Hi All,

    Have any one of you tried running dbcc commands using openrowset.Please find the code below:-

    select

    a.*

    from openrowset('SQLNCLI',

    'server=EMEA-DBA-DEV\DEV2005;trusted_connection=yes;',

    'dbcc sqlperf(logspace)') as a

    Error message obtained :-

    Msg 7357, Level 16, State 2, Line 2

    Cannot process the object "dbcc sqlperf(logspace)". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    -- or

    select

    a.*

    from openrowset('SQLNCLI',

    'server=EMEA-DBA-DEV\DEV2005;trusted_connection=yes;',

    'set fmtonly off;exec dbcc sqlperf(logspace)') as a

    Error message obtained:-

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".

    Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'dbcc'.

    Please let me know your views.

    Thanks.

  • September 4, 2008 at 4:01 am

    #867021

    If anyone has a solution then please reply.

  • September 4, 2008 at 7:29 am

    #867127

    select

    a.*

    from openrowset('SQLNCLI',

    'Server=servername;Uid=sa;Pwd=password;Database = database',

    'set fmtonly off;exec(''dbcc sqlperf(logspace)'')') as a

  • September 4, 2008 at 8:13 am

    #867166

    It does work fine now:)..thanks.

  • November 14, 2008 at 12:16 pm

    #898812

    Thanks Mate,

    This is really a good suggestion to use the EXEC option for dbcc commands

    I used this command in this manner

    SELECT 'shashi', a.* FROM OPENROWSET('SQLOLEDB',

    'DRIVER={SQL Server};SERVER=.;Trusted_Connection=yes;',

    'SET NOCOUNT ON;SET FMTONLY OFF; exec (''dbcc sqlperf(logspace)'')') AS a

    Regards

    Shashi Kant Chauhan

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

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