See CallDetail from SQL Server Linked Server

[Moebius01]

For anyone who's played with importing data from calldetail, you've probably noticed the table is not visible when trying to link to it from various sources. However, you can create direct queries that will run against it without issue.

Apparently SQL Server is not so tolerant. I have linked servers set up for each of my switches, and for any other table I've been able to schedule jobs to pull data on given intervals (usually from call_today).

However, I now have a need to pull data from call detail using a link server in SQL 2k. If I try to query it, SQL returns an error that the table does not exist. I know calldetail is a special public synonym in the Oracle side of things.

Has anyone tried accessing this table from SQL Server with any success? If so, what'd you do?

 

[700503]

Moebius01,

Yes, I extract data daily from calldetail table in SQL2K and no error encounters so far. I just setup ODBC connection to the oracle and then setup a DTS package to import the data. By using the link server, I am not sure whether it works or not as I am not good in SQL2K.

Regards,

Samuel

 

[Moebius01]

I was thinking of trying it as a DTS but hadn't tried it yet. After some digging, I think the reason the linked server doesn't work. It seems that when Aspect set up the public synonym in Oracle for calldetail, they didn't do it quite right. It's an unknown synonym type, where all the others are defined correctly as tables.

Thanks for the tip.

 

[steveh888]

I am not sure what they did or didn't do right since I am not familiar with Oracle, or perhaps they did it on purpose, but as you already know, the CALLDETAIL synonym/table is visible under MS Access but not SQL Server.

What I did was I created a blank DB on my SQL Server, select Import Data, and copied the CALL_TODAY table first since it shared the same properties as the CALLDETAIL table. I then imported data again, except instead of copy table, I selected used query and simply select * from calldetail. After that, for all the daily updates, I had a stored procedure scheduled as a job to run everyday to pull previous day's call detail records. Hope that helps.

 

[700503]

One more finding is that calldetail in SQL2K will set as view instead of table. I don't know why it is located/classified as view.

Moreover, I have an question whether we can copy the data from call_today instead of calldetail (a big table). The problem is how to ensure the full data copied as at midnight data will moved from call_today to calldetail. Any idea?

Thanks

Samuel

 

[Moebius01]

That would be nearly impossible to get the proper data. If you were too late the collapse would start and lock you out. Too early and you're likely to miss a few records (assuming this is a 24 hour switch).

The easier solution would be to pull calldetail for the previous day only. As long as you're pulling by term_date, the pull is pretty quick. Pulling by orig_date is not as fast as it's not indexed properly.

You have to do a little sql fun to automate the previous day bit, but it's not too hard. If you need I can post a few tips on getting the previous day.

 

[700503]

thanks, currently i am using the same tech to extract data based on sql by condition on the term_date. it is fast but still taking around a few mins to complete. as we have over 10 sites, the volume of data is also growth. I am afraid it will take too long to be done. moreover, the most hard thing is that my user want to have report ready by 6:00am.

 

[Moebius01]

To really work with Call_Today, you'd have to build a pretty intuitive ETL style interface that would pull call_today as close to midnight as possible, then after the collapse, pull the last x number of records missing from calldetail. This would be farily fast, but quite a challenge to code properly.

An alternative approach you might consider would be to compartmentalize the pull a bit. Create a program to connect to the switch and pull the data to insert into the sql server DB. That way you could have 3 or 4 boxes run the pulls, and have all your data imported by around 2:30 or so (local site times of course). That should leave plenty of time to extract reports.