Hi Lynda,

Are you sure that standard users can't create temporary tables (with names starting with # or ##) in SQL Server? I'm not in front of a SQL box at the moment, but I seem to recall that they can. This also has the advantage of the temp tables being automatically cleaned up after the connection that created them is closed (in the case of either the # or ## tables, or after all connections referencing the table are closed in the case of the other).

Cheers,

Andrew

>I'm familiar with SPT and the various techniques for building strings but I was hoping to be able to exploit the power and speed of a SQL join for this process.
>
>Using scan to move through hundreds to thousands of records to build a string seemed primitive (and perhaps slow?) by comparison to a join. Another concern might be the length of the SQLcommand. I can't seem to find the maximum length documented anywhere but the string in this case would be over 80,000 characters just for the Where clause. The PK is C(9) and there are typically 6,000 to 7,000 rows in the VFP cursor.
>
>I keep thinking there just has to be a better way, but perhaps not. Here are a few other things I've tried:
>
>I've played around with creating a temporary SQL table to replace the VFP cursor because it CAN be used in a join with SPT. This works for me because I'm the SQL Admin but the typical user won't have rights to create a table on the SQL device.
>
>Another funky solution is to have a permanent (empty) SQL table with one field, the PK, in the SQL database. Append the VFP cursor's data to the table and update. After the SPT join has produced a result set, delete the PK data in the SQL table. As I said, it's funky but actually requires the least code and is relatively fast (at least faster than both methods that follow.)
>
>I've built a SQL stored procedure from the cursor contents, passed it to SQL and executed it on the server. It works but is a bit slow.
>
>I've created a remote view for the SQL table and pulled the entire table (up to 400,000 records) onto the desktop. Then did the join between the view and the cursor in VFP. I'll only use this method as a last resort. It's slow and defies the principles of client server to be moving whole tables over the network.
>
>Thanks for the input. It helps to hear how others approach these problems.