Data Extraction into SQL Server Database from Cache ODBC Database

Data analysis
Data warehousing applications
Microsoft Windows
SQL Server
Hi.I am trying to extract data from an intersystems cache database into a sql server database for storage and reporting purposes, on behalf of my company. I have not succeeded so far for the last couple of months as it keeps bringing back far more than the expected number of rows, and is inconsistent with the number of records returned from the same query. I also tried extracting the data into access with the hope of getting it into sql server via access but this has not been successful either as i kept getting odbc error messages.I was able to link to the cache table from access, but could not import data into access. I later realised that the cache database table being queried is actually a view and not a table and was wondering if this may be the cause of the problem. Can you kindly advise on the best way of extracting data into SQL server from a cache odbc database? Many thanks for your help. LOLA

Answer Wiki

Thanks. We'll let you know when a new response is added.

It sounds like the query is not correct. You should query against the base tables and not the view. I expect you are joining the view to some number of tables and returning one or more rows from the child tables resulting in the views rows being returned multiple times. Use Right and Left joins and check join conditions.

You should be able to use DTS to bring records drectly from the CACHE database to SQL Server. I did not see you mention that in your post. This is probably the easiest way to import and export data.

I would be more than willing to look at your query.


Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Upesh1
    hi, u should try getting data from the base by using left & right joint & check the joine. i would like to see yr query which is give better idea for yr problem.u might be use view with multiple column along with child table. there is 1 way to get data from cache database in sqlserver is dns. by this u can extract data.
    0 pointsBadges:
  • DaveInAZ
    Well, I hate to disagree with the previous posters, but it sounds to me like you SHOULD be querying the view, NOT the tables. You say the view returns the "correct", expected number of records, so clearly it already contains the necessary joins and criteria. Why duplicate that effort? I do agree that DTS is the way to go, though. Just point it at the view and let 'er rip.
    0 pointsBadges:
    Hi all, Many thanks for your replies. I am using DTS to extract the data using a simple select statement in a transform data task SQL Query, and using a date criteria to reduce the size of the extract like this: SELECT AKA, Admitting_Clinician, Blood_Bank_Work_Issued, Blood_Group, ??/* Up to about 100 columns */ FROM _SYSTEM.Patient WHERE Date_Patient_Details_Amended >= '2005-04-01' AND Date_Patient_Details_Amended
    0 pointsBadges:
  • FerencMantfeld
    roughly how many records are we talkng about in the query ?
    0 pointsBadges:
  • DaveInAZ
    Boy, am I confused! First you say you "doubt the database providers will be willing to supply the schema", but then you say you "have the structure of the tables in the system" and you "know is that the table is a view and not an actual table". And your query returns different results each time you run it? Jeez! Ok, let's start with some questions for you. 1 Who are these db providers? Are they outside your company? Who does this cache db belong to? If it belongs to your company, can't you negotiate a deal to get the source for the view you're trying to emulate? And, if they let you have access to their data, why wouldn't they be willing to share the SQL source behind the view? 2 How did you recreate the table structure when you don't know the schema? 3 Which table "is a view, not a table"? The _SYSTEM.Patient table in your querying? 4 You say you want to "re-create the view using joins", but your SQL only includes one table. Why? 5 Do you KNOW that the resultset is larger than it should be, or are you guessing? If you do know, HOW do you know? Are you able to run the orginal query you're trying to reproduce? 6 If the recordcounts vary greatly, not just growing, and this db is truly a "cache database", perhaps the cache is being flushed periodically (as it should be, if it's really a cache). That is, maybe it's being emptied and then refilled, but not necessarily with the same data. Caches are often used to temporarily "stage" data prior to some operation like printing invoices or reports. Sorry for all the questions, but I don't think we have a very clear picture of the situation, so it's hard to know what to advise.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: