Hey, thanks in advance for any help.

I am querying tables called payments_and_adjustments & customer_master located in the database named cdwacx. I want to insert the data into a database that is local to the web server the query is running from, (it originates in asp for the project but now is only being tested in query analyzer). The table it is being placed in to is called ar_custcall_settlements and it is in a DB called acx_reporting. Here is what I have so far. I can't figure out how to go from DB to DB (with the SELECT INTO IN statement) that are on different servers. One in WI and one in FL. I get the following error, my code is below it. "Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'IN'." Can you log into another server with in the query? Odd...

THANKS AGAIN,Nick

________BEGIN CODE______________________________________________ ________________________

Select payments_and_adjustments.date_entered,payments_and _adjustments.sequence,
    payments_and_adjustments.customer_number,payments_ and_adjustments.batch_number,
    payments_and_adjustments.amount,
    payments_and_adjustments.code,payments_and_adjustm ents.post_date,
    payments_and_adjustments.statement_date,payments_a nd_adjustments.posted,
    payments_and_adjustments.row_id,payments_and_adjus tments.deposit_type,
    customer_master.customer_name,customer_master.corp orate_number,
    customer_master.sales_region_number

INTO ar_custcall_settlements IN acx_reporting

FROM payments_and_adjustments,customer_master

WHERE (payments_and_adjustments.date_entered BETWEEN '10/28/2003 00:00:00' AND '10/28/2003 23:59:59') AND
    (payments_and_adjustments.customer_number = customer_master.customer_number) AND
    (payments_and_adjustments.code IN('994','992'))

You can query across databases, but not across servers with a default database configuration. You have to link the servers together.

http://www.databasejournal.com/featu...le.php/1438991

http://www.google.com/search?sourcei...linked+servers

Peter
------------------------------------------------------
Work smarter, not harder.

The IN operator returns TRUE if the first operand is equal to any values in the list (or subquery) which is the second operand.

It has nothing to do with cross-database references.

Use the syntax:

    server.database.owner.objectname

to refer to objects (tables, views, etc) in another database, e.g.:

SELECT ... INTO acxreporting.dbo.ar_custcall_settlements ...




P.S. DO yourself a favor and save yourself a lot of typing by using a table alias, e.g.

    SELECT PA.date_entered, PA.sequence ...
        FROM payments_and_adjustments PA ...


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com