I have tried several ways to connect to my SQL db and retrieve records from a fairly simple table (12 columns). The db is 700+K rows, query runs in seconds on the SQL server but will hang and run indefinitly when trying to retrieve in WF. I've tried different methods (calling from stored proc, with MRNOEDIT and without, etc). If I request a record limit it will return data quickly...not sure why it's choking on the entire table. Any ideas are appreciated. Thanks.


-MRNOEDIT BEGIN
-*
ENGINE SQLMSS SET DEFAULT_CONNECTION CORP-DB-02
SQL SQLMSS

SELECT * FROM ShopperTrak.dbo.ShopperTrakData;

TABLEF FILE SQLOUT
PRINT *
END
-MRNOEDIT END
-EXIT
-RUN

Has your query on SQL server returned all the rows or just the visble ones.

Have you looked at the WebFOCUS servers agent to see what is happening, if the query is finished and the records are being returned, then you will see the count going up.

Looks like you are simply missing an end statement:

ENGINE SQLMSS SET DEFAULT_CONNECTION CORP-DB-02
SQL SQLMSS

SELECT * FROM ShopperTrak.dbo.ShopperTrakData;
END <--
TABLEF FILE SQLOUT
PRINT *
END 

BTW, no need for MRNOEDIT

Thanks Waz and Guru - Yes, END was needed, silly me...however now when we run we can tell thru server agent all 700+K rows are being pulled but the webpage just runs blank then eventually dies, no results displayed. All rows return within 6 seconds when queried in SQL server. We've even tried reducing result set by WHERE clause in fex and still no luck.

700,000 records to the screen???

TABLEF FILE SQLOUT
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

OR

ENGINE SQLMSS SET DEFAULT_CONNECTION CORP-DB-02
SQL SQLMSS
SELECT TOP 1000 * FROM ShopperTrak.dbo.ShopperTrakData;
END
TABLEF FILE SQLOUT
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

We need the entire result set returned from SQL passthru for data to be available from the fex variables defined by the user form. Its unlikely the users will need all 700K rows all at once (and we will discourage the request if they do) however we need to be able to retrieve whatever date, location etc ad-hoc. The query runs but crashes the browser when attempting to display results.

Just a thought,

How long does it take to get the data and hold it, not PCHOLD it.

If its quick, then perahps you can copy the data to a central location, or zip and email, or something.

Thanks for the SQL Select tidbit...handy!

If you really need it in the browser, you could also take a look at the pagination possibility with ON TABLE SET WEBVIEWER ON.