Skip to main content

Troubleshooting DocWatch Queries with SQL

  1. Last updated
  2. Save as PDF

Troubleshooting DocWatch Queries with SQLWhen testing / troubleshooting DocWatches, SQL may be of help to work through the process.  Here are a couple quick tips to help with the troubleshooting.  Any query that can be written in SQL can be used in DocWatch with the understanding that DocWatch requires specific columns in order to run.  As long as those eight columns are in the results, no more and no less, then the query can be customized any way that is practical.  Using SQL will provide a place for checking the results without the graphical interface of the DocWatch form.  From here the query that is built by the Build SQL button in DocWatch can be refined and used as the Custom Query.

First, as mentioned above, the DocWatch query itself can be tested and customized using SQL.  But what if the “Run Now” job requires troubleshooting?  This can be done by running the stored procedure TP__DWS_DocScan (which is the stored procedure that runs when the Run Now button is clicked). If the stored procedure completes with errors, it will be easy to find them and then troubleshoot using SQL.  The example below shows how an error will look.

A.ClientKey) as FoundDocid,

A.CLIENTKEY AS CLIENT,

B.Date_Screening as FoundDocDate,

ABS(DATEDIFF(dd, GETDATE(),

a.admission_date)) as UnitsElapsed,

Case  When DATEDIFF(dd, convert(varchar(10),A.Admission_Date,101),getdate())>2 Then 'Task List 1' END as TaskNameToUse,

A.ClientKey as FaceSheetKey,

A.OP__Docid as SourceDocid,

A.Admission_Date As SourceDate,

(SELECT t.op__statusord FROM t4w_documents t WHERE T.op__id=B.op__Docid) as TgtStatusOrd

FROM FD__AGENCY_ADMISSION A

Left join FD__BEHAVIORAL_HEALTH_SCREEN B on A.ClientKey=B.ClientKey

WHERE ABS(DATEDIFF(dd,Admission_Date,B.Date_Screening)) <2

or (A.Discharge_date is null and B.op__docid is null)

 

Msg 16924, Level 16, State 1, Procedure TP_DWS_DocScan, Line 201Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

 

The text above the error message can be reevaluated and corrected.  This error is indicating that there are either more or less than the required 8 columns for DocWatch.  A quick look at the query above revealed that there was one extra column (CLIENT), which was probably used for troubleshooting but did not get removed when it was added to the DocWatch Custom SQL.  Run the DocScan stored procedure again and see if the error is gone. Keep in mind that when the stored procedure is run from SQL, it runs all the DocWatches and sends all the TMails every time.  If this is a problem, the DocWatches can be temporarily suspended.