Windows batch and bcp of sys table data

• Wednesday, March 21, 2012 10:14 AM

   

   
  

  0

  Hi all,
  
  I was trying to schedule the below script in a windows batch file and for some reason the file is being created only with the header row. The sql itself runs well in SSMS and returns rows.

  This is the first time I am trying to do bcp on sys tables. Similar batch files for other user defined tables are working well.

  Any help in the right direction will be much appreciated. Thank you.
  
  SQL
  select
  Db.name as DatabaseName
  , Obj.name as TableName
  , sum((Stat.user_seeks+Stat.user_scans+Stat.user_lookups)) as NumOfReads
  , sum(Stat.user_updates) as NumOfWrites
  from 
  [Eclipse].sys.dm_db_index_usage_stats as Stat
  , [Eclipse].sys.objects as Obj
  , [Eclipse].sys.databases as Db
  where
  Stat.database_id = Db.database_id
  and Stat.object_id = Obj.object_id
  and ((Db.name like '%Eclipse%') or (Db.name like '%Telecom%'))
  group by
  Db.name
  , Obj.name
  order by
  DB.name
  , Obj.Name
  
  Windows Batch File
  @echo off
  set DATEVALUE=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
  set TIMEVALUE=%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
  set FILEEXTN=%DATEVALUE%%TIMEVALUE%
  if exist ServerStats.csv RENAME ServerStats.csv ServerStats.csv.%FILEEXTN%
  echo DatabaseName, TableName, NumOfReads, NumOfWrites > ServerStats.csv
  bcp "select Db.name as DatabaseName, Obj.name as TableName, sum((Stat.user_seeks+Stat.user_scans+Stat.user_lookups)) as NumOfReads, sum(Stat.user_updates) as NumOfWrites from [Eclipse].sys.dm_db_index_usage_stats as Stat, [Eclipse].sys.objects as Obj, [Eclipse].sys.databases as Db where Stat.database_id = Db.database_id and Stat.object_id = Obj.object_id and ((Db.name like '%Eclipse%') or (Db.name like '%Telecom%')) group by Db.name, Obj.name order by DB.name, Obj.Name" queryout "E:\Data Transfer\ServerStats\TempExport.csv" -S SERVER2000BC\SQL2008 -c -T -t","
  type TempExport.csv >> ServerStats.csv
  del TempExport.csv
  

  • Reply
  • Quote