>>>>I wish to create a local cursor, for reporting, from the data in two separate remote tables, each in their own database.
>>>>
>>>>Database D1, table T1 holds Orders and ItemNo
>>>>Database D2, table T2 holds ItemNo and ItemDetail
>>>>
>>>>Both tables are too large to consider bringing either down to my local machine in their entirity.
>>>>
>>>>I currently do:
>>>>
>>>>LcSearchString = “12345”
>>>>
>>>>Handle1 = SQLConnect(“One”)
>>>>LcSQLCode1 = “select T1.Orders, T1.ItemNo FROM T1 WHERE T1.Orders = ?lcSearchString”
>>>>SQLExec(Handle1, lcSQLCode1, “Cursor1”)
>>>>
>>>>Handle2 = SQLConnect(“Two”)
>>>>LcSQLCode2 = “select T2.ItemNo, T2.ItemDetail FROM T2 INNER JOIN Cursor1 ON T2.ItemNo = Cursor1.ItemNo”
>>>>SQLExec(Handle2, lcSQLCode2, “Cursor2”)
>>>>
>>>>The above fails with a misleading message that is sort of the equivalent of file not found. I believe that the last SQLExec can’t see Cursor1
>>>>
>>>>Any thoughts …
>>>
>>>You're right, it can't. THe server can't see cursors on the client. YOu need to combine the two above select statements into one:
>>>
>>>SELECT T2.ItemNo, T2.ItemDetail FROM T2 INNER JOIN T1 ON T2.ItemNo = T1.ItemNo AND T1.Orders = ?lcSearchString
>>
>>Erik
>>
>>Although its bad news, thanks for confirming that the server's SQL engine can't see the local cursor. Unfortunately this probably means that one server's engine can't see another server's engine either and that's what I've got here.
>>
>>I didn't set up this system, I'm just putting together some reports. The back end is Btrieve (through ODBC) but that shouldn't matter - I assume the same answer for multiple SQL Server databases etc.
>>
>>When I set up a Data Source Name (DSN) for my tables, I must specify a directory for the data (the database). In my case DSN for D1 is "One", DSN for D2 is "Two". Your sample will work fine if both tables T1, and T2, were in the same database (same DSN) but in my case they aren't and I seem so be stuck. I won't be able to try this until Monday, but could I connect to the D1 database and refer to the table in the D2 connection as Two!T2.ItemNo ... I seem to doubt it.
>>
>
>I don't think so, but that's an issue I have no eperience with. But there is probably a way on the server (stored procedure or other) to make the first server database aware of the other one so it you can query both sources through one of them. This is conjecture, so take it as such...
>
>
>
>>My other idea is to create a list of ItemNo from my first SQLSelect such as "'12345','11111',22222'" then use them like:
>>
>>lcListOfItemNo = (create a formatted list of ItemNo here from the first SQLSelect() results)
>>SELECT T2.ItemNo, T2.ItemDetail FROM T2 WHERE T2.ItemNo IN ?lcListOfItemNo
>>
>
>There is probably SQL Server syntax that serves this purpose, but again, I don't know it. If all else fails, you might think about creating a table on the server that you can dynamically add records to for the sole purpose of joining. YUou could fill your single field temp table with a record for each join value, and hten use it for the join. I am pretty confident that there is a more straigtforward way to do this, but someone more familiar with SQL server ill have to tell you... (Michael L? JVP?)

My attempt to refer to Two!T2.ItemNo as mentioned above didn't work. Hopefully someone else might jump in here. Thanks for the ideas Erik.
Bob