I am currently working in the MRE environment on Webfocus 5.33. My code is as follows:

-MRNOEDIT BEGIN

REMOTE DEST = WFSA2003
-REMOTE BEGIN

SQL SQLMSS SET SERVER STTOPC01
END
SQL SQLMSS

ex gm_Report_DowntimeExcelReport_GetIncidentsByStartDate_TimeandEndDate_Time '&StartDate_Time','&EndDate_Time';

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS LISTOWOS
END
-REMOTE END

TABLE FILE LISTOWOS
PRINT *
END

-MRNOEDIT END

I get the following error message

No HTML Output!

--------------------------------------------------------------------------------


(FOC1671) SET SERVER STTOPC01 COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER STTOPC01 COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1400) SQLCODE IS 2812 (HEX: 00000AFC) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Could not find stored
: procedure 'gm_Report_DowntimeExcelReport_GetIncidentsByStartDate_Timeand
: EndDate_Time'.
L (FOC1405) SQL PREPARE ERROR.
(FOC1400) SQLCODE IS 1671 (HEX: 00000687)
: SET SERVER STTOPC01 COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
L (FOC1405) SQL PREPARE ERROR.
0 ERROR AT OR NEAR LINE 21 IN PROCEDURE sqlstoreFOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: LISTOWOS
BYPASSING TO END OF COMMAND

I am getting prompted for the StartDate_Time and EndDate_Time so why would I get the message thatit can't find the stored procedure. So how do I point this stored procedure to the right database on the sql server? Also, at the beginning of the stored procedure it defines the date fields as follows:
--DECLARE @StartDate_Time SMALLDATETIME
--DECLARE @EndDate_Time SMALLDATETIME
--DECLARE @OPID TINYINT
--SET @StartDate_Time = '9/20/2004'
--SET @EndDate_Time = '9/23/2004'

Any help or suggestion would be appreciated.

Here's the syntax I use....

ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA
ENGINE SQLMSS SET DATETIME OFF
ENGINE SQLMSS SET SERVER &DataConnection
SQL SQLMSS EX SelAffirmationUK &IssuerID, &RAID, &PMID, &CompanyID , '&Affirmed', '&DebtClass', &TableID ;


Hope this helps!

Carol, thanks for your example but it still doesn't work. If I explain our environment, maybe the proper syntax will surface from that.

The environment is that there is a webserver (5.3.3) that talks to the reporting server (EDA533P) which talks to the sql adaptor which is mapped to the SQL server. The stored procedure resides on the SQL server and we don't have the proper statements to tell Focus where that procedure is so it can't execute it.

quote:

Originally posted by Tom Flynn:
Heather,

I did a GOOGLE search on stored procedure variables, found this:

Global Variables and Stored Procedure Parameters

Hope it helps, until, someone with more knowledge comes along...

Thanks Tom, this will come in handy once I get the proper syntax working.

Heather

Couple of things,

1. Is the connection name STTOPC1 exists? If exists, which database it points?

2. Do you have the stored procedure in the database where it points?

Check those and let me know if you still have a issue.

When you say you are getting prompted to enter STARTDATE and ENDDATE, that is because those are dialogure manager variable and it doesn't mean SP exists when it prompted.

Kamesh,

1. Yes the connection STTOPC01 exists. It doesn't point to a default database because the user name and password has admin rights to all databases on the server.
2. The stored procedure resides in the database called ShiftLogReporting.

Thnaks
Heather

Try this,


ex ShiftLogReporting.userid.gm_Report_DowntimeExcelReport_GetIncidentsByStartDate_TimeandEndDate_Time '&StartDate_Time','&EndDate_Time';

userid - It depends on who have the access.

example: dbo or any user.

It should work. If not try run this in query analyzer and see whether it returns you data or not.

Hope this helps,

It runs in Query analyzer but not on the web.

Is there a character limit in the MRE environment for a stored procedure name by chance?

This message has been edited. Last edited by: <Heather>,

Instead of calling that Stored Procedure, can you try calling a simple table in that database?

your query will be select top 10 * from tablename;

Somewhat I feel it's not able to connect to that database.

try putting an an END after your ex line and before the TABLE FILE SQLOUT. Had similar error/problem once upon a time and then the problem went away with that fix.

Let me know.

Ira
WF 5.3.6 AIX

Hi Heather,

Try to use [dbname].[creator name].[sp_name] format.
(Three-part format)

Link to sqldts.com is indeed useful - but not in your case.

I suppose that default database for WF Login is another than for login you use with SQL Analyzer.
(So you're in different DBs when you're in WF and isqlw)

Thanks Ira, but it didn't work.

could you change your code to a simple select statement? that will rule out a LOT of possibilities.

quote:

-MRNOEDIT BEGIN

REMOTE DEST = WFSA2003
-REMOTE BEGIN

SQL SQLMSS SET SERVER STTOPC01
END
SQL SQLMSS

ex
'remove from here
gm_Report_DowntimeExcelReport_GetIncidentsByStartDate_TimeandEndDate_Time '&StartDate_Time','&EndDate_Time';
'to here and put in
select * from dbo.someschema.sometable;
'change the schema and table name to something you know exists. It's been a while since I've worked with ms sql, not sure if the dbo. goes first or after schema

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS LISTOWOS
END
-REMOTE END

TABLE FILE LISTOWOS
PRINT *
END

-MRNOEDIT END

I know it seems crazy, but have you tried:
ON TABLE PCHOLD AS LISTOWOS

When you are in a HUB-SUB situation, and using -REMOTE to bypass the HUB server, to get your answer set held on the HUB (not the SUB), you need to use PCHOLD.

This message has been edited. Last edited by: N.Selph,

Heather,
Have you looked at thisarticle?
It states that the first MSSQL connector mentioned in the edasprof (on your subserver WFSA2003) should have (access to) the stored procedure.
Does this help?

It was a nice try but it didn't work.

Using this code, my query runs but returns the column names only. I should have 54 records.

-MRNOEDIT BEGIN

SQL EDA SET SERVER WFSA2003

-REMOTE BEGIN

ENGINE SQLMSS SET DEFAULT_CONNECTION STTOPC01
SQL SQLMSS

SELECT

IssueId,
IssueDesc

FROM ShiftLogReporting.dbo.InspectionIssuesList;

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS LISTOWOS
END

-REMOTE END

TABLE FILE LISTOWOS
PRINT *
END

-MRNOEDIT END

Heather

It sounds like the userid/password combination in the connection string does not have privilege to access the table. For security reasons you do not get a message just 0 records.

Remember that your original stored procedures can run with extra privileges on top of those granted to a user to SELECT.

Try running the SELECT statement (NOT the Stored Procedure) from Enterprise manager with the EXACT connection string you use and you prob will find 0 records retrieved.