Post reply

DBCC Inputbuffer(spid) for a linked server

  • February 25, 2009 at 8:32 am

    #134945

    hi guys is there any way to execute DBCC Inputbuffer(spid) for a linked server from a stored procedure?

    I currently have this in my script

    SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar, @SPID) + ')'

    is there a way to have this command using my linked server?

  • February 26, 2009 at 7:23 am

    #950579

    Try this:

    SELECT a.* FROM OPENROWSET('SQLOLEDB',

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

    'SET NOCOUNT ON;SET FMTONLY OFF; exec (''dbcc inputbuffer(SPID)'')') AS a

    MJ

  • February 27, 2009 at 9:53 am

    #951272

    hello, thanks for you reply, it looks like the server won't let me unless i change the configuration of the adhoc queries. :crazy:, since is a production server I can't do that.

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

    is there another way?

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

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