Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Extracting data from SSMS for proprietary (COTS) systems?

    We are currently utilizing a risk management software that is using a SSMS back end. However the database is extremely normalized and hard to find data. I am looking for one particular item (column) to bounce against another report I created, to find the delta's.

    I found an XML string that looks to have data captured in it, almost like VB marked up in XML. This XML has the column name (source and the desitination column). However when I go into the other tables I am unable to find either column?

    Could it be that the application is referencing the XML strings in the column to present the data through the application? Is this some sort of way to prevent people from back dooring their data? Maybe I just missed the table with the column name.

    Thoughts? Thanks all!

  2. Best Answer
    Posted by Pat Phelan

    "I know that you have a "home grown data dictionary", so I'll assume that you didn't find the column there.

    I'd try to run some queries against INFORMATION_SCHEMA.COLUMNS and see what that turns up. In particular, the data might be materialized within a view, and I think that your data dictionary excludes those.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that you have a "home grown data dictionary", so I'll assume that you didn't find the column there.

    I'd try to run some queries against INFORMATION_SCHEMA.COLUMNS and see what that turns up. In particular, the data might be materialized within a view, and I think that your data dictionary excludes those.

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

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - Sounds like a plan. I'll run that query less the database while the risk management db is selected, that should pull both views and tables including indexes etc.

    I'll keep you posted. Good times!

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I did run it and dump to Excel earlier no column 4 or column4 or the name of the column. They must have some crazy modeling going on or maybe it is in a view. I'll keep reviewing to see if I can find that data, it has to be in there lol.

  6. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - Still unable to find the data, is there database strategies that can hide the data or break it up some how? Can it be embedded in XML capture in a call and the application calls up that row through ID, etc?

  7. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've been fond of https://en.wikipedia.org/wiki/Hanlon%27s_razor ever since I first heard of it.

    My guess is that the data is stored somewhere other than in the database where you are looking... A different database is the most likely culprit, but an ODBC join could be pulling data from an Excel spreadsheet, or something equally hokey. There is no practical end to the complexity and confusion that can be reached by applications!

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

  8. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Man finally navigated these beast and found the correct levels to join and learned the different levels. Thanks Pat always helpful! You should teach.

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I actually kind of do teach... A large part of my job involves mentoring our DBA team, bringing them up from entry level to as geeky as they want to become.

    I also present for our clients and also at SQL Saturdays, local user groups, etc. A lot of the client sessions are custom built, where I evaluate the needs of the staff and create custom training to help them solve specific business problems.

    I've written a couple of books (don't bother hitting Amazon, all of my publishing is done using pseudonyms). and am considering restarting my public blog and/or bespoke articles for the trade press.

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

Posting Permissions

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