DBCC linked server

• tutupouet

  SSCommitted

  Points: 1512

  More actions

  May 20, 2010 at 8:02 am

  #156971

  Hi,

  i want to get informations of a linked server so i do that :

  declare @tran_log_space_usage table(

  database_name sysname collate SQL_Latin1_General_CP1_CI_AS,

  log_size_mb float,

  log_space_used float,

  status int)

  insert into @tran_log_space_usage

  exec [LinkedServer].master.dbo.sp_executesql N'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS'

  and i have this error

  OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

  Msg 7391, Level 16, State 2, Line 48

  The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServer" was unable to begin a distributed transaction.

  but if i only do the exec, I have the result !!!

  can someone help me?

  thx

• Lowell

  SSC Guru

  Points: 323482

  More actions

  May 20, 2010 at 9:22 am

  #1170210

  i have this in my snippets as teh way to run DBCC commands on linked servers:

  SELECT * FROM OPENQUERY( [linked server],'SET FMTONLY OFF; EXEC (''USE DATABASE; DBCC showfilestats WITH NO_INFOMSGS '')')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• tutupouet

  SSCommitted

  Points: 1512

  More actions

  May 20, 2010 at 9:38 am

  #1170233

  Thank you,

  It works very well with the option SET FMTONLY OFF :w00t: