>>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.

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

My concern though is that I might have so many ItemNo from the first select that some 254 character string limit might get exceeded. That leaves me with doing, say, 40 selects on T2, (one per ItemNo) and appending each result to a growing local cursor which I could use as my final resulting cursor. I'm resisting this solution, but it may be the only one available.