RE: Using SQL to join an i5/os database and a Windows database

John,

You can setup a "linked Server" in SQL Server that points to the i5. Then you could set up a view on
the SQL Server that joins the SQL server table to the i5 table.

Then just have your Crystal report access the view.

http://www.google.com/search?hl=en&rlz=1B3GGGL_enUS253US253&q=%22SQL+Server%22+%22linked+server%22&btn
G=Search




Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Saturday, February 09, 2008 2:22 AM
To: Midrange Systems Technical Discussion
Subject: Using SQL to join an i5/os databasde and a Windows database

I am supporting a Windows application that is built on MS-SQL database. I
write
Crystal reports, among other things.

I need to retrieve data from the i5 (forgive me, I still do not know what
the
current name is) based on data from the MS-SQL database.

Bad news is that the key field I am using on the MS-SQL database is a
character
field, which I convert to a numeric fiel to access the i5. The only
direct way
to do that with standard Crystal syntax is to use a subreport. VERY VERY,
VERY
SSSSSLLLLLLOOOOOOOOOOWWWWWWWWWWW.

In addition, the key field I am using is incomplete, as the database I am
accesing has two key fields: the one I have as second key, and a numeric
field
(fixed value of 1 in this case) as the first key.

Since Crystal does allow SQL to be used, I was wondering if that would
allow for
faster processing. Specifically, can a join be constructed between the
two
systems so that the sub report is not needed?

Specifically, on the MS-SQL database, I have a string field, length 20.
The
numeric field on the (I wish I knew the proper name. Keep wanting to say
AS/400, and I know from reading previous posts that is a dead name) i5 is
numeric, 7 packed, no decimal places. I think I might have tried, but
can't
remember, creating a numeric field on the MS-SQL database and tring to
link
that. If I recall, that fails because the field I create is not a
database
field. Could be mistaken. Been a long time ago.

Sorry for the long winded question. But, I have to do something to
improve
performance. I ran this mess for six hours today, and created a partial
report
about 180 pages. Full report would be around 1000. This can't be good
for
either MS-SQL or the i5 to do this much heavy lifting.

Thanks for suggestions.

John McKee

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.