Yes, you can roll your eyes. Another nube.

I'm trying to call an SQL Server stored procedure from a fex. No arguments passed. What I'm getting is strange.

My stored procedure:

USE [DataMarts]
GO

/****** Object: StoredProcedure [dbo].[TelemedSchedVisitsForClinics] Script Date: 9/20/2019 12:24:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[TelemedSchedVisitsForClinics]
AS
BEGIN
SET NOCOUNT ON;

declare @current date = getdate();
declare @currentEnd date = dateadd(week, 1, @current);
declare @prior date = dateadd(day, -6, DATEADD(wk, DATEDIFF(wk, 6, cast(getdate() as date)), 6));
declare @priorEnd date = dateadd(week, 1, @prior);

-- MAB 9/19 - for Telemedicine Scheduled and Visits Weekly for Clinics
select
coalesce(APPT_LOCATION_DISP, NURSE_UNIT) as Location,
coalesce(a.C1, 0) as [Scheduled_Appts],
coalesce(b.C2, 0) as [VTC_Activity],
@prior as C1Date,
@current as C2Date
from
(select
APPT_LOCATION_DISP,
count(PERSON_ID) as C1
from Scheduling.VTC.ScheduledAppts
where
-- one thing appstudio does that SQL doesn't - not like in
APPT_LOCATION_DISP not like 'APIA%' and
APPT_LOCATION_DISP not like 'CHU%' and
APPT_LOCATION_DISP not like 'EAT%' and
APPT_LOCATION_DISP not like 'ICHC%' and
APPT_LOCATION_DISP not like 'KANA%' and
APPT_LOCATION_DISP not like 'KIT%' and
APPT_LOCATION_DISP not like 'MAN%' and
APPT_LOCATION_DISP not like 'MSTC%' and
APPT_LOCATION_DISP not like 'NSHC%' and
APPT_LOCATION_DISP not like 'SRHC%' and
APPT_LOCATION_DISP not like 'Nutaqsiivik' and
APPT_LOCATION_DISP not like 'YAK%' and
APPT_TYPE_DESCRIPTION not in ('SCF VTC Presenter', 'ANMC VTC Presenter') and
-- this selects the coming week starting from today
BEG_DT_TM >= @current and
BEG_DT_TM <= @currentEnd
group by APPT_LOCATION_DISP
) a
full outer join
(select
NURSE_UNIT,
count(FIN) as C2
from DataMarts.ENC.VTC_Encounters
where
((FACILITY in ('Hospital ANMC', 'Specialty ANMC') and
APPT_TYPE not in ('ANMC Telemedicine Consult', 'Telemed Review')
) or
(FACILITY = 'PCC ANMC' and
NURSE_UNIT in ('CFDS', 'OB\GYN Clinic', 'Pediatrics') and
APPT_TYPE not in ('ANMC Telemedicine Consult', 'Telemed Review')
)) and
-- this selects last week as a range - this would be so much easier in prolog or unix
SERVICEDATE >= @prior and
SERVICEDATE < @priorEnd
group by NURSE_UNIT
) b
on a.APPT_LOCATION_DISP = b.NURSE_UNIT
END

What I'm getting back, no matter what I change is a file WFServlet.ibfs:

000011Pain Center 1 00000102019-09-090000102019-09-20
000016Gastroenterology 2 00000102019-09-090000102019-09-20
000004CFDS 2 00000102019-09-090000102019-09-20
000011Pulmonology 11 00000102019-09-090000102019-09-20
000010Pediatrics 2 80000102019-09-090000102019-09-20
000015Primary Care 1E 1 00000102019-09-090000102019-09-20
000007Int Med 7 20000102019-09-090000102019-09-20
000011Dermatology 1 10000102019-09-090000102019-09-20
000008Diabetes 2 00000102019-09-090000102019-09-20
000012Rheumatology 1 10000102019-09-090000102019-09-20
000010Nephrology 3 20000102019-09-090000102019-09-20
000008Oncology 8 20000102019-09-090000102019-09-20
000010Cardiology 21 180000102019-09-090000102019-09-20
000002OT 2 10000102019-09-090000102019-09-20

Which is the raw output from the stored procedure. Finally, my fex:

SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER SQL-CDW-PROD1
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
-RUN
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
HEADING
"Telemedicine Scheduled and Visits Weekly for Clinics" ;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

I assume the run statement is producing the file and it's not getting piped to SQLOUT, so the report doesn't go. I get a blank Chrome window. I've been reading and reading, nothing works.

This message has been edited. Last edited by: mbondr,

Moved the -RUN after the END to no effect

I'm not positive this is the answer but could you try adding an END right above -RUN?
so your SP will end with ; next line will say END and the line after that -RUN and the line after that TABLE FILE SQLOUT.

  
SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER SQL-CDW-PROD1
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
END
-RUN
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
HEADING
"Telemedicine Scheduled and Visits Weekly for Clinics" ;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

Sigh. It had no effect at all. RUN definitely seems to dump everything to file and ends it there.

Tried trimming it down to:

SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER SQL-CDW-PROD1
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
TABLE FILE SQLOUT
PRINT
SCHEDULED_APPTS
VTC_ACTIVITY
LOCATION
ON TABLE HOLD AS YOURFILE
END
-RUN

Sigh

This is the output of the stored procedure:

Location Scheduled_Appts VTC_Activity C1Date C2Date
Pain Center 1 0 2019-09-09 2019-09-20
Gastroenterology 2 0 2019-09-09 2019-09-20
Int Med 7 2 2019-09-09 2019-09-20
CFDS 2 0 2019-09-09 2019-09-20
Pulmonology 11 0 2019-09-09 2019-09-20
Pediatrics 2 8 2019-09-09 2019-09-20
Primary Care 1E 1 0 2019-09-09 2019-09-20
Dermatology 1 1 2019-09-09 2019-09-20
Diabetes 2 0 2019-09-09 2019-09-20
Rheumatology 1 1 2019-09-09 2019-09-20
Nephrology 3 2 2019-09-09 2019-09-20
Oncology 8 2 2019-09-09 2019-09-20
Cardiology 21 18 2019-09-09 2019-09-20
OT 2 1 2019-09-09 2019-09-20

This produces a blank page labeled "No Output"

SET SQLENGINE = SQLMSS
SQL SQLMSS SET SERVER SQL-CDW-PROD1
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS YOURFILE
END
-RUN

Tried this:

ENGINE SQLMSS SET SQL-CDW-PROD1 DataMarts
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
-RUN
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
END

You seem to be missing an END after your call to run the stored procedure

ENGINE SQLMSS SET SQL-CDW-PROD1 DataMarts
SQL SQLMSS SET NOCOUNT ON
SQL SQLMSS SET ANSI_WARNINGS OFF
SQL SQLMSS EX DataMarts.dbo.TelemedSchedVisitsForClinics ;
END
TABLE FILE SQLOUT
SUM
SCHEDULED_APPTS AS "Planned VTC Visits,Week of: C1Date"
VTC_ACTIVITY AS "VTC Visits,Week of: C2Date"
BY LOCATION AS 'ANMC Dept/Location'
END
  

Cheers

Stu

Mbondr

You can also create a synonym for the stored procedure and create a TABLE request against it.

FP Mod Chuck - That's what I finally did, but it would be cool to be able to eliminate the master file.