OSQL Output File Garbage

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bob
    #1

    OSQL Output File Garbage

    Everybody,

    I've been doing a lot of on-line research and cannot find
    any reference to the exact problem I'm having.

    Let me preface this question with the fact that I'm coming
    from an Oracle background so my approach may not be the best
    way to tackle this. However, from the research I have done
    this approach seems reasonable. Also, I know about the
    undocumented procedure sp_MSforeachtab le. That can give me a
    result similar to what I'm looking for but the format of the
    output is not what I need.

    Now the problem. I'm trying to write a reusable script to give
    me a list of all the tables in a database that have 1 or more rows.
    My approach is to a BAT file (see script 1 below) that calls OSQL
    twice, once to call a SQL script (see script 2 below) that uses the
    Information_Sch ema views to generate the SELECT COUNT(*) statements
    and fill in all the tables names in the database, write this to a
    temporary output file and the second OSQL command to read the
    temporary output file and generate me the results formatted the
    way I need.

    The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>
    6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.
    Because of this garbage the 2nd OSQL command blows up! Anyone have
    any idea what is generating this garbage?

    If I manually edit out the garbage and then just run the 2nd OSQL
    command
    I get similar garbage in the final result file (see 2nd result file
    below).

    In Query Analyzer, when I run the GET_TABLE_COUNT .SQL Script manually
    then take its output and copy and paste it to a new query window and
    run that it works OK except for generating lots of blank lines where
    the result of the tables that have zero rows are. I am suppressing
    headings but am still getting the blank lines but at least it works!

    Any ideas anybody? Thanks For Any Help
    FYI -- SQL Server 2000 with SP3a.
    Bob

    =============== === Script 1 - BAT File to Call OSQL ===============

    @echo off
    @echo *************** *************** *************** *************** ***
    @echo .
    @echo get_table_count .bat
    @echo .
    @echo Before you run this script change to the drive and directory
    @echo where the input SQL script is located!
    @echo .
    @echo Input parameters:
    @echo 1) SQL Server userid
    @echo .
    @echo You will be prompted twice for your password!
    @echo .
    @echo The output is written to file TABLE_COUNT_RES ULT.TXT
    @echo .
    @echo *************** *************** *************** *************** ***
    pause
    osql -U %1 -S devkc-db -d C3T_Architectur e -i get_table_count .sql -o
    temp_table_coun t_query.txt -h-1 -w500
    osql -U %1 -S devkc-db -d C3T_Architectur e -i
    temp_table_coun t_query.txt -o table_count_res ult.txt -h-1 -w500
    del temp_table_coun t_result.txt
    @echo on

    =============== =============== =============== =============== ==========

    =============== = Script 2 - GET_TABLE_COUNT .SQL Script ===============

    set nocount on
    select 'set nocount on'
    select 'select ''Table Name Count'''
    select 'select ''========== ====='''
    select 'select '''
    + table_name
    + ''', count(*) from '
    + table_name
    + ' having count(*) > 0 '
    from information_sch ema.tables
    where table_type = 'BASE TABLE'
    order by table_name

    =============== =============== =============== =============== ==========


    ============ Partial Result of 1st OSQL Run =============== ===========

    1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount on

    select 'Table Name Count'

    select '========== ====='

    select 'ACT_ASSERTION_ RULE', count(*) from ACT_ASSERTION_R ULE having
    count(*) > 0
    select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0
    select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0

    =============== =============== =============== =============== ==========


    ============ Partial Result of @nd OSQL Run =============== ===========

    1> 2> 3> 4> ... I edited out the intervening numbers for this message
    .... 664> 665> 666> 667> Table Name Count

    ========== =====

    .... I edited out lots of blank lines in the result for this message
    before I get to the first table with 1 or more rows ...

    ARCH 6

    =============== =============== =============== =============== ==========
    Tags: osql 1 2
    • Dan Guzman
      #2
      Re: OSQL Output File Garbage

      You can remove numbering with the '-n' OSQL parameter.

      However, you might consider using dynamic SQL to accomplish the task.
      Example below:

      SET NOCOUNT ON
      CREATE TABLE #TableRowCounts
      (
      TableName nvarchar(261) NOT NULL,
      TableRowCount bigint NOT NULL
      )
      DECLARE
      @TableName nvarchar(261),
      @SqlStatement nvarchar(500)
      DECLARE TableList CURSOR
      LOCAL FAST_FORWARD READ_ONLY FOR
      SELECT
      QUOTENAME(TABLE _SCHEMA) +
      '.' +
      QUOTENAME(TABLE _NAME) AS TableName
      FROM INFORMATION_SCH EMA.TABLES
      WHERE OBJECTPROPERTY(
      OBJECT_ID(
      QUOTENAME(TABLE _SCHEMA) +
      '.' +
      QUOTENAME(TABLE _NAME)),
      'IsMSShipped') = 0
      OPEN TableList
      WHILE 1 = 1
      BEGIN
      FETCH NEXT FROM TableList INTO @TableName
      IF @@FETCH_STATUS = -1 BREAK
      SET @SqlStatement =
      N'INSERT INTO #TableRowCounts
      SELECT ''' + @TableName + N''', COUNT(*)
      FROM ' + @TableName + N' WITH (NOLOCK)'
      EXEC (@SqlStatement)
      END
      CLOSE TableList
      DEALLOCATE TableList

      SELECT *
      FROM #TableRowCounts
      WHERE TableRowCount > 0
      ORDER BY TableName


      --
      Hope this helps.

      Dan Guzman
      SQL Server MVP

      -----------------------
      SQL FAQ links (courtesy Neil Pike):

      Microsoft Azure Web App - Error 404
      http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800

      http://www.sqlserverfaq.com

      http://www.mssqlserver.com/faq

      -----------------------

      "Bob" <rms@robertsieg el.net> wrote in message
      news:91f443f8.0 311141523.10db2 7e5@posting.goo gle.com...[color=blue]
      > Everybody,
      >
      > I've been doing a lot of on-line research and cannot find
      > any reference to the exact problem I'm having.
      >
      > Let me preface this question with the fact that I'm coming
      > from an Oracle background so my approach may not be the best
      > way to tackle this. However, from the research I have done
      > this approach seems reasonable. Also, I know about the
      > undocumented procedure sp_MSforeachtab le. That can give me a
      > result similar to what I'm looking for but the format of the
      > output is not what I need.
      >
      > Now the problem. I'm trying to write a reusable script to give
      > me a list of all the tables in a database that have 1 or more rows.
      > My approach is to a BAT file (see script 1 below) that calls OSQL
      > twice, once to call a SQL script (see script 2 below) that uses the
      > Information_Sch ema views to generate the SELECT COUNT(*) statements
      > and fill in all the tables names in the database, write this to a
      > temporary output file and the second OSQL command to read the
      > temporary output file and generate me the results formatted the
      > way I need.
      >
      > The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>
      > 6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.
      > Because of this garbage the 2nd OSQL command blows up! Anyone have
      > any idea what is generating this garbage?
      >
      > If I manually edit out the garbage and then just run the 2nd OSQL
      > command
      > I get similar garbage in the final result file (see 2nd result file
      > below).
      >
      > In Query Analyzer, when I run the GET_TABLE_COUNT .SQL Script manually
      > then take its output and copy and paste it to a new query window and
      > run that it works OK except for generating lots of blank lines where
      > the result of the tables that have zero rows are. I am suppressing
      > headings but am still getting the blank lines but at least it works!
      >
      > Any ideas anybody? Thanks For Any Help
      > FYI -- SQL Server 2000 with SP3a.
      > Bob
      >
      > =============== === Script 1 - BAT File to Call OSQL ===============
      >
      > @echo off
      > @echo *************** *************** *************** *************** ***
      > @echo .
      > @echo get_table_count .bat
      > @echo .
      > @echo Before you run this script change to the drive and directory
      > @echo where the input SQL script is located!
      > @echo .
      > @echo Input parameters:
      > @echo 1) SQL Server userid
      > @echo .
      > @echo You will be prompted twice for your password!
      > @echo .
      > @echo The output is written to file TABLE_COUNT_RES ULT.TXT
      > @echo .
      > @echo *************** *************** *************** *************** ***
      > pause
      > osql -U %1 -S devkc-db -d C3T_Architectur e -i get_table_count .sql -o
      > temp_table_coun t_query.txt -h-1 -w500
      > osql -U %1 -S devkc-db -d C3T_Architectur e -i
      > temp_table_coun t_query.txt -o table_count_res ult.txt -h-1 -w500
      > del temp_table_coun t_result.txt
      > @echo on
      >
      > =============== =============== =============== =============== ==========
      >
      > =============== = Script 2 - GET_TABLE_COUNT .SQL Script ===============
      >
      > set nocount on
      > select 'set nocount on'
      > select 'select ''Table Name Count'''
      > select 'select ''========== ====='''
      > select 'select '''
      > + table_name
      > + ''', count(*) from '
      > + table_name
      > + ' having count(*) > 0 '
      > from information_sch ema.tables
      > where table_type = 'BASE TABLE'
      > order by table_name
      >
      > =============== =============== =============== =============== ==========
      >
      >
      > ============ Partial Result of 1st OSQL Run =============== ===========
      >
      > 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount on
      >
      > select 'Table Name Count'
      >
      > select '========== ====='
      >
      > select 'ACT_ASSERTION_ RULE', count(*) from ACT_ASSERTION_R ULE having
      > count(*) > 0
      > select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0
      > select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0
      >
      > =============== =============== =============== =============== ==========
      >
      >
      > ============ Partial Result of @nd OSQL Run =============== ===========
      >
      > 1> 2> 3> 4> ... I edited out the intervening numbers for this message
      > ... 664> 665> 666> 667> Table Name Count
      >
      > ========== =====
      >
      > ... I edited out lots of blank lines in the result for this message
      > before I get to the first table with 1 or more rows ...
      >
      > ARCH 6
      >
      > =============== =============== =============== =============== ==========[/color]


        Comment

        • Bob
          #3
          Re: OSQL Output File Garbage

          Dan,

          Thanks for the answers. I completely missed the -n option in the BOL.

          I also like your alternative. Someone I work with suggested using a
          temporary table then just selecting what I want but your approach
          seems even more sophicated. I'm out of the office today so haven't
          had a chance to try either answer but will as soon as possible.

          Thanks so much,
          Bob

          "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message news:<Rkftb.917 $Rk5.180@newsre ad1.news.atl.ea rthlink.net>...[color=blue]
          > You can remove numbering with the '-n' OSQL parameter.
          >
          > However, you might consider using dynamic SQL to accomplish the task.
          > Example below:
          >
          > SET NOCOUNT ON
          > CREATE TABLE #TableRowCounts
          > (
          > TableName nvarchar(261) NOT NULL,
          > TableRowCount bigint NOT NULL
          > )
          > DECLARE
          > @TableName nvarchar(261),
          > @SqlStatement nvarchar(500)
          > DECLARE TableList CURSOR
          > LOCAL FAST_FORWARD READ_ONLY FOR
          > SELECT
          > QUOTENAME(TABLE _SCHEMA) +
          > '.' +
          > QUOTENAME(TABLE _NAME) AS TableName
          > FROM INFORMATION_SCH EMA.TABLES
          > WHERE OBJECTPROPERTY(
          > OBJECT_ID(
          > QUOTENAME(TABLE _SCHEMA) +
          > '.' +
          > QUOTENAME(TABLE _NAME)),
          > 'IsMSShipped') = 0
          > OPEN TableList
          > WHILE 1 = 1
          > BEGIN
          > FETCH NEXT FROM TableList INTO @TableName
          > IF @@FETCH_STATUS = -1 BREAK
          > SET @SqlStatement =
          > N'INSERT INTO #TableRowCounts
          > SELECT ''' + @TableName + N''', COUNT(*)
          > FROM ' + @TableName + N' WITH (NOLOCK)'
          > EXEC (@SqlStatement)
          > END
          > CLOSE TableList
          > DEALLOCATE TableList
          >
          > SELECT *
          > FROM #TableRowCounts
          > WHERE TableRowCount > 0
          > ORDER BY TableName
          >
          >
          > --
          > Hope this helps.
          >
          > Dan Guzman
          > SQL Server MVP
          >
          > -----------------------
          > SQL FAQ links (courtesy Neil Pike):
          >
          > http://www.ntfaq.com/Articles/Index....partmentID=800
          > http://www.sqlserverfaq.com
          > http://www.mssqlserver.com/faq
          > -----------------------
          >[/color]

            Comment

            Previous template Next
            Working...