trouble with SQL passthru

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

WF7.7
WIN 08 SERVER SP2
EXL2K, HTML, PDF

Posts: 14 | Registered: July 18, 2011

Waz

Expert

posted

Hide Post

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.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

Guru

posted

Hide Post

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

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Show Post

BECKIE

Member

posted

Hide Post

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.

WF7.7
WIN 08 SERVER SP2
EXL2K, HTML, PDF

Posts: 14 | Registered: July 18, 2011

Ignored post by BECKIE posted

Show Post

Tom Flynn

Expert

posted

Hide Post

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

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Show Post

BECKIE

Member

posted

Hide Post

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.

WF7.7
WIN 08 SERVER SP2
EXL2K, HTML, PDF

Posts: 14 | Registered: July 18, 2011

Ignored post by BECKIE posted

Show Post

Waz

Expert

posted

Hide Post

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.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!