Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: verifying a table's column count across servers?

    Hi folks!

    The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.

    The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.

    I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:

    So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.

    For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.

    Here is the code I think (sawdust) can be used:
    Code:
    select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns
    where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns
    where table_name = 'Dest')) as ColumnDiff
    Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.

    Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.

    Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    SELECT lo.name, lc.name, ro.name, rc.name
       FROM dbo.sysobjects AS lo
       JOIN dbo.syscolumns AS lc
          ON (lc.id = lo.id)
       LEFT OUTER JOIN server.db.dbo.sysobjects AS ro
          ON (ro.name = lo.name)
       FULL OUTER JOIN server.db.dbo.syscolumns AS rc
          ON (rc.id = ro.id
          AND rc.name = lc.name)
       WHERE  'mytablename' = lo.name
    You can get fancy and exclude the two columns that you expect to be different, but I'd personally prefer to get the full list and sort it out later if needed.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How about retaining a count of the columns in the source table yesterday, and then comparing it to the count of the columns in the source table today (just before transferring the rows across servers. If it succeeds, send away, otherwise fail the job and output a message via raiserror !!


    That way you can use information_schema on the local machine.

    HTH
    Tom

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    ...still working on this...

    Tom, I'm not sure what you are suggesting - is it that I maintain a new column in some table on the source db that contains the current row count, then compare this number each day to the current column count? If so, though that would work, there's GOT to be a way to do it without the need to store the count physically somewhere. In situations like this, I like to try to get the info dynamically, to save storage space (small as it may be in this case) and perhaps more importantly, to reduce the chance of error related to improper or missing updates to the physically-stored column. Remember, I'm already trying to reduce the "remember to do that" factor here

    Pat, still trying to figure out yours as well. Seems like that is a solution that would work...just don't use complex (?) joins like that very often. Your example, modified to work in my world (read that, "after changing table/database names) returns NULL in the rc.name column, though it does return a row for each column in the target table (which, today, is two more than the destination table). SO I'm trying to figure out the miscalculation

    Just wanted to touch base and let y'all know I'm NOT an ingrate...just still workin' on the tweaks. Thanks for the suggestions!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hmmm.... I just tested it, and the SQL doesn't produce the results I think that it should. I'll have to cook up a test for this tomorrow. For the short term, you can run this from the "short" side of the transfer (the one with fewer columns):
    Code:
    SELECT ro.name, rc.name
       FROM server.db.dbo.sysobjects AS ro
       JOIN server.db.dbo.syscolumns AS rc
          ON (rc.id = ro.id)
       WHERE 'tcb' = ro.name
          AND NOT EXISTS (SELECT *
             FROM dbo.sysobjects AS lo
             JOIN dbo.syscolumns AS lc
                ON (lc.id = lo.id)
             WHERE  lo.name = ro.name
                AND lc.name = rc.name)
    -PatP

Posting Permissions

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