none
Windows batch and bcp of sys table data

    Question

  • 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

    Wednesday, March 21, 2012 10:14 AM

Answers

  • Got the solution from a different forum. Thanks to Pat Phelan.

    I escaped % in the batch script and it all worked fine.

    I changed ((Db.name like '%Eclipse%') or (Db.name like '%Telecom%'))  to ((Db.name like '%%Eclipse%%') or (Db.name like '%%Telecom%%')) 

    Wednesday, March 21, 2012 5:17 PM