Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CR XI - Data Tables on SAME server vs Different servers 1

Status
Not open for further replies.
OrionStar

OrionStar

Technical User
Dec 3, 2004
336
US
Question please.

I'm dealing with 2 separate servers, ONE has SQL SERVER DB's and the other has INTERSYSTEMS CACHE ODBC DB's.

My queries are taking a VERY long time when I pull data from BOTH systems, as compared to pulling data from each system separately.

Here's my question: Generally speaking, do you think it would be FASTER to export the data that I need from my SQL SERVER DB into a .MDB (Access) file and save as a table on my INTERESYSTEMS ODBC Server.

I think so, but I'm not an expert in DB's or SERVERS for that matter. Do you think it would help speed things up?

Would the data table need to be in the same DATABASE???? or can it just reside on the same SERVER and I ask Crystal to add this as a separate table.

Please advise and your expertise is greatly appreciated. Let me know if you need additional information.



Thanks:
Mike Vega

"What would you attempt to do... if you knew you could NOT fail?
 
Sort by date Sort by votes
You should be able to speed things up by linking from SQL Server to the external tables on the other ODBC data source. Then have Crystal use these virtual tables in SQL Server so that the heavy lifting is done by SQL Server rather than by Crystal.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Upvote 0 Downvote
Thank you, can you tell me how to accomplish this? The MAIN bulk of my data is residing on the INTERSYSTEMS CACHE ODBC server and I'm pulling a couple of data elements from the SQL SERVER side.

So are you recommending I use the SQL side as my main table and then use the INTERSYSTEMS table as my second and third tables?

What would the SQL expression look like for me to link from SQL to INTERSYSTEMS???



Thanks:
Mike Vega

"What would you attempt to do... if you knew you could NOT fail?
 
Upvote 0 Downvote
Linking to external tables is a feature of the DBMS (it's not an SQL expression). The other DBMS may provide the same feature. You would need to find information about how to do this in their documentation.

You could link to both data sources from Access. Here's one source describing how:
Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Upvote 0 Downvote
IdoMillet, can you elaborate just a little on what you meant by this comment? I'm very interested. We are currently using a DTW package in SQL Server to physically COPY my reporting tables FROM the "Intersystems Cache" Server to the SQL Server. Once copied they work GREAT.

However, the copy process is failing every night and so we need to constantly monitor it to make sure it takes place.

Can you explain the VIRTUAL TABLE type setup you mentioned below?

THANKS.....MV

IdoMillet (Instructor) 4 Aug 05 12:15
You should be able to speed things up by linking from SQL Server to the external tables on the other ODBC data source. Then have Crystal use these virtual tables in SQL Server so that the heavy lifting is done by SQL Server rather than by Crystal.

hth,
- Ido



Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
To elaborate on the Access solution Ido suggests, create an MS Access database, and then select New->Link and point to the ODBC datasource, select the tables you need, and DON'T forget to select Save Password. Then do the same for the other ODBC datasource.

Now you can build out a query in MS Access using both tables, and then use the Query as the datasource for your Crystal Report.

Remember to turn on the option of File->Options->Database->Show Views to see the Access query within CR.

Access is much better at joining disparate data then CR, and you will have a reusable data source for other tools as well.

-k
 
Upvote 0 Downvote
The steps required to cerate a linked server in SQL Server are pretty straight forward.

Open Enterprise manager and navigate SQL Server Group -> Your SQL Server -> Security -> Linked Servers.

Add a new linked server and select server type = Microsoft OLEDB Provider for ODBC Drivers

Enter the data source and security information as required

Once the server is linked you can then create views of these tables in your SQL Server database using an OPENROWSET or OPENQUERY statement i.e.

Code: 
CREATE VIEW dbo.[TableName]
AS
SELECT     *
FROM OPENQUERY([Linked server name], 'SELECT * FROM [TableName]') Rowset_1

Once the views are created then these will be seen by crystal when you make a connection to your SQL Server.

HTH

Gary Parker
MIS Data Analyst
Manchester, England
 
Upvote 0 Downvote
Why does MS ACCESS have to be involved? Is this the ONLY way to accomplish this?

I do not use ACCESS. My original question stemmed from my grasping for straws (ideas) on how to accomplish combining DBs from different servers.

Here is my situation:

I have Server "A" using INTERSYSTEMS CACHE and Server B using SQL SERVER. Our DBA is copying tables from "A" To "B" for me. SQL Server is dramatically faster when I query the tables.

QUESTION: Is it possible to create a "virtual" DB setup without using Access and only using DTS in SQL Server?

Let me know if you need additional info.

M



Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
My earlier post explains exactly how to do this using SQL server, did you try it ?



Gary Parker
MIS Data Analyst
Manchester, England
 
Upvote 0 Downvote
Ok. thanks...I'll pass this idea along to my DBA.

THANKS again.
M

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
One more thing. So when the server is "linked" it says that VIEWS would need to be created.

So then someone would need to create all of the views needed before queries can be done, or are the tables ready for queries at the point of the LINK?

Sorry, just not understanding the VIEW CREATION yet. We have many different tables and VIEWS within the originating DB. We don't need additional views...but if this is REQUIRED for the LINK, then this is probably not the best way for us to go?? Can't the DB be linked so that the VIEWS are identical to the original DB?

thx

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
Once the server is linked then SQL queries can be written
against the SQL Server database, this will in turn use a pass through query to the Linked server.

However if you want to be able to use the linked server in crystal, then the easiest way is to create views on the SQL Server db which return all data from the linked servers tables

Our DBA is copying tables from "A" To "B" for me. SQL Server is dramatically faster when I query the tables.
Click to expand...

Instead of copying the tables create a view on SQL Server using the OPENQUERY syntax in my earlier post

Hope this makes things a bit clearer.

Gary Parker
MIS Data Analyst
Manchester, England
 
Upvote 0 Downvote
That's the best way to go, *Gary*.

We use it at one client, however I rarely suggest it on tek-tips, but I guess I should start doing so in lieu of the user friendly MS Access method.

-k
 
Upvote 0 Downvote
ok..ok... one more question.

When I query the original DB (Intersystems Cache ODBC) my queries are slow. I have other issues as well, no left outter joins permitted...and other junk....

When the tables are copied into SQL Server, tables are MEGAFAST.....and all joins are working correctly.

What kind of speed can I expect from the LINKED / VIEW CREATION method? Will the Left Outer Joining work properly?

THANKS

Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
Using a linked server will definitely not be as fast as copying the data into the SQL Server, but that can be said about any method that uses linked servers/tables whether it be access, SQL Server, Oracle or any other database.

If I had known your INTERSYSTEMS CACHE ODBC DB's also had problems of limited query capabilty and slow performance I would not have even suggested this method.

If it's performance and flexibility you're after, and you can live with the data not being up to the minute, then I suggest you stick with the method you are already using and concentrate your efforts on getting the copy process correct.

HTH


Gary Parker
MIS Data Analyst
Manchester, England
 
Upvote 0 Downvote
Thank you so much for your expertise. Yes emailing comments/questions is a bit difficult to cover our needs.

But believe me this thread has shed much light on which direction we could go. I do think that the table copy using DTS is what we need to do.

Thank you again... I do have a better idea on what you are talking about.



Thank you.

MV

"A man is only as happy, as he makes up his mind to be....
 
Upvote 0 Downvote
Glad I could help.

BTW thanks for the star SV.

Gary Parker
MIS Data Analyst
Manchester, England
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

G
  • Locked
  • Question
Multiple groups in same level
Replies
0
Views
196
george1610
G
bbranco
  • Locked
  • Question
Query Help - Group By 2 tables
Replies
0
Views
437
bbranco
bbranco
Kennymc
  • Question
Has anyone used Crystal reports with online Microsoft Dynamics 365
Replies
0
Views
139
Kennymc
Kennymc
K
  • Locked
  • Question
We're using command in crystal report
Replies
1
Views
597
hilfy
hilfy
D
  • Locked
  • Question
Crystal Reports for Visual Studio 2022 Download
Replies
2
Views
7K
Joshua44
J

Part and Inventory Search

Sponsor

Back
Top