Insert #Temp Exec Server.Master.dbo.xp_fixeddrives

• Ronald San Juan

  SSCrazy

  Points: 2937

  More actions

  January 19, 2007 at 2:36 pm

  #115901

  Hello everyone,

  I'm trying to automate my monitoring of SQL Server fixed drives using XP_FixedDrives. The procedure below works for the EXEC part but produces an error when Insert .. Exec() is used.

  Please help.

  --Preparations

  CREATE TABLE [MonitoredServerNames] (

   [ID] [int] IDENTITY (1, 1) NOT NULL ,

   [ServerName] [nvarchar] (130) )

  GO

  -- Insert all monitored servers

  Insert into MonitoredServerNames(ServerName)

  Values ('[CCNT_ASAP]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[COMMERCEDBSQL1]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[CRMCLUSTERSQL1]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[CRMCLUSTERSQL2\NODE2]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[CSESQL2]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[HQCORPSQL\NODE4]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[HQJOBS1]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[HQSEARCH3]')

  Insert into MonitoredServerNames(ServerName)

  Values ('[ISB]')

  GO

  -- Procedure begins here

  Create Table #Drives

  (drive char(1), FreeMB int)

  declare @ServerName varchar(100)

  declare @SQL varchar(200)

  declare ServerNames cursor for

  select ServerName from MonitoredServerNames (nolock)

  Open ServerNames

  Fetch next from ServerNames into @ServerName

  While @@Fetch_Status = 0

   Begin

    Set @SQL = 'EXEC ' + @ServerName + '.master..xp_fixeddrives'   

    Insert #Drives --Comment this line and it works!

    exec (@SQL)

    /*

    Select 'Server' = @ServerName, Drive, FreeMB,

     Status = case when FreeMB < 2000

      then 'Critical'

      else 'OK' end

    from #Drives

    truncate table #Drives

  */

    Fetch next from ServerNames into @ServerName

   End

  Close ServerNames

  Deallocate ServerNames

  Drop table #Drives

   

  Server: Msg 7391, Level 16, State 1, Line 1

  The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

  OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

  ---

  Ronald San Juan | SQL DBA
  ID 710124 ~ Code "Northwind"

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 19, 2007 at 11:01 pm

  #684495

  The column names in the #Drives table must be the same as those returned by the xp.  You have FreeMB as one of the column names... you need to either change it to [MB Free]...

  OR

  You need to name the insert list (probably the more logical way to do it).... like this....

   INSERT INTO #Drives (Drive,FreeMB)

     EXEC (@SQL)

  Also, you probably have SA privs if you are writting such routines... if so, give this baby a whirl... capture the output from THAT and you'll automatically pickup new servers on the network... some you might have even missed...

  EXEC Master.dbo.xp_CmdShell 'OSQL -L'

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions