Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    9

    Unanswered: Windows batch and bcp of sys table data

    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. 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_loo kups)) 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_loo kups)) 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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What are in the Windows environment variables Eclipse and Telecom? How will those values work once they are injected into your SQL statement by cmd.exe as it executes your BCP command?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    9

    Thanks

    Thanks Pat.

    I was looking for table names like Eclipse or Telecom. Obviously in SQL server to match the string I will add % to the start and end. In the batch file I forgot to add the escape sequence.

    I changed ((Db.name like '%Eclipse%') or (Db.name like '%Telecom%')) to ((Db.name like '%%Eclipse%%') or (Db.name like '%%Telecom%%')) and data is exported just fine.

    Thanks again Pat.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •