I need to create a stored procedure across multiple databases for a Crystal
Report;Crystal version 8.0.1.0. Even then, I don't know if the 8.0 version of
Crystal will enable me to use a stroed procedure across databases.

Can anyone enlighten me about how to go about creating a strored procedure
and am I barking up the right tree envisioning using a stored procedure for
this process??? And will Crystal 8.0 allow me to use it???

I think I have to define the Procedure under "master" to go across databases
and
the SQL is...

SELECT    drvtbl."loan_id",
    drvtbl."corr_name",
    drvtbl."corr_address_1",
    drvtbl."corr_city",
    drvtbl."corr_state",
    drvtbl."miscfield",
    GEN."loan_amt",
    GEN."int_rate"
FROM    (SELECT    ms_address_information."loan_id",
        ms_address_information."corr_name",
        ms_address_information."corr_address_1",
        ms_address_information."corr_city",
        ms_address_information."corr_state",
        CAST(ms_loan_misc_fields."misc_field_value"    AS    CHAR(15))    AS    miscfield
    FROM    { oj "fics"."dbo"."ms_address_information" ms_address_information
            LEFT OUTER JOIN "fics"."dbo"."ms_loan_misc_fields" ms_loan_misc_fields
            ON ms_address_information."loan_id" = ms_loan_misc_fields."loan_id"
            AND ms_loan_misc_fields."misc_field_id"    =    10079})    drvtbl
    LEFT OUTER JOIN "DataTrac"."dbo"."GEN" GEN
    ON drvtbl."miscfield" = GEN."loan_num"

This might be a question, perhaps for Crystal Report forums, if there is
one. You cannot create a stored procedure from within a Crystal Report. What
you have posted seems to be a SQL query generated using its query builder
wizard (?) using its own dialect.

Stored procedures can be used for generating reports. However, you will have
to create the procedure in SQL Server. For details refer to the topics
"CREATE PROCEDURE" and "Creating a stored procedure" in SQL Server Books
Online. Once created, the report-writer will allow you to select the
procedure for generating the report.

Show quote Hide quote Anith, you'd spot on with that.

I used Crystal 8 about 5 years ago when it first came out.  The stored
procedure does need to be created in the database prior to linking it to
Crystal.

wnfisba, there's two piece of key information that you will need to know.

First read the stuff that Anith suggests.   To avoid confusion, when your
write your stored procedure use a three part naming format for all the
tables referenced in your select query.

For example

    Select a.Column1 From MyDB.dbo.MyTable a

Here's how this works... MyDB is the database where the table is stored.
dbo signals that the table is owned by the database owner, and obviously
MyTable is the table.  The "a" afterwards is an alias which makes is quicker
to reference this instance of the table again elsewhere in the query - as I
do in a.Column1.    To be honest using "a" is a really bad idea, as this can
lead to unclear code later on, you would be better using an abbereviation
instest perhaps  "mt"

Regards

Colin Dawson
www.cjdawson.com

[OT] SQL Humor
composite key structure
Default Column Values
Updating Tables with Computed Columns - SET ARITHABORT
Summing muliple variable columns