CJSmith dot me

I dump stuff I find useful here

Category: V1 Document Management

V1 DBAuthorise grab a list of Purchase Orders

Published / by Chris Smith / Leave a Comment

V1 DBAuthorise can be setup to have multiple Purchase orders, so I use a Common Table Expression in a view for any time I need to get a list of Purchase Orders and Sage Company that is not in ~Accepted and State OK (1024).
user5 for us is the PO number and user7 the Sage Company Name, change if different on your own system.
I encountered a looping issue so use maxrecursion 10 to ensure there is nothing wrong. 10 because there may be multiple invoices per Purchase Order.

with cte (user5,user7) as
(
  select cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)) [user5],LEFT(user7,3) as user7
  from .[dbo].[DBAUTH]
                where stateNumber != '1024' and user5 != '' --and user7 = ''
  union all
  select
    cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)),LEFT(user7,3) as user7
  from cte
  where RTRIM([user5]) !='' and [user5] IS NOT NULL and [user5] LIKE '%,%'
)
select [user5],user7
from cte
group by [user5],user7
option (maxrecursion 10)

Rebuild a V1 DBCapture Database Queue

Published / by Chris Smith / Leave a Comment

We have an issue where on a rare occasion a scan from V1 DBScanner to DBCapture can corrupt the queue and cause DBCapture Admin to state it is unable to open a document rendering the whole application unusable.
I have a batch script to perform some maintenance. Over time I have commented out lines which are not required. I have left my change drive to d script as this is normally where server applications are installed. I have replaced our port with as this can be different per install (As well as having more than once instance of the service on different ports)
After the queue is rebuilt the program actually starts the server as normal, in my batch the program is executed in a seperate process and then killed off after 400 seconds (As long as the rebuild is complete then V1 is fine with this, if running line by line press Ctrl + C to close the program instead). Depending on the amount of documents in the queue it may need to be increased.

ECHO *********************************
ECHO * Restarting DBCapture Services *
ECHO *********************************
ECHO *********************************
ECHO * Change to drive D             *
ECHO *********************************
d:
ECHO *********************************
ECHO * Stopping V1 Services          *
ECHO *********************************
REM net stop DbADepServer
REM net stop DbCSArchiveServer-<port>
REM net stop DbADepServer-<port>
REM net stop DbCSAuthServer-<port>
net stop DbCapComms-<port>
net stop DbCapOCRServer
REM net stop DbCapturePDFFileWatcher
net stop DbCSCaptureServer
net stop DbCSCaptureServerExport
REM net stop DbCheckFilewatcher
REM net stop DbLoginServer
REM net stop DbWebQLiveCacheClean
REM net stop DbArchiveCacheClean
ECHO *********************************
ECHO * Rebuild DBCapture Queue       *
ECHO *********************************
start "DBCapture" "<path to dbcapture communications server>\dbcapcomms.exe" -debug <port> -queueupdate
ECHO *********************************
ECHO * Waiting 400 seconds           *
ECHO *********************************
rem Credit For lower CPU usage, instead of using WAIT use Ping as suggested on http://ss64.com/nt/timeout.html
PING -n 401 127.0.0.1>nul
ECHO *********************************
ECHO * Kill of DBCapture program     *
ECHO *********************************
Taskkill /IM "DbCapComms.exe" /F
ECHO *********************************
ECHO * Starting V1 Services          *
ECHO *********************************
REM net start DbLoginServer
REM net start DbCapComms-<port>
REM net start DbCheckFilewatcher
REM net start DbWebQLiveCacheClean
REM net start DbArchiveCacheClean
REM net start DbADepServer-<port>
REM net start DbCSArchiveServer-<port>
REM net start DbCSAuthServer-<port>
net start DbCapComms-<port>
net start DbCapOCRServer
REM net start DbCapturePDFFileWatcher
net start DbCSCaptureServer
net start DbCSCaptureServerExport
ECHO *********************************
ECHO * Complete Exiting              *
ECHO *********************************
EXIT

Credits:
SS64 for recommending Ping instead of Wait – SS64
Datel Support for providing the command to rebuild the queue – Datel