SytelineMaster

Here is a useful query to check user section that cause blocking / locking. SELECT d1.session_id, d3.[text], d1.login_time, d1.login_name, d2.wait_time, d2.blocking_session_id, d2.cpu_time, d1.memory_usage, d2.total_elapsed_time, d2.reads,d2.writes, d2.logical_reads, d2.sql_handle FROM sys.dm_exec_sessions d1 JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

In Syteline 9, there is one significant database schema change.  All major tables like item are replaced with a view and _mst table.  There are some context variables need to be set, before you can run query against to the views.  Otherwise query to the view will return no data.  And here is the variable […]

This is a great blog post regarding how to setup SQL Mail.  Need to keep for reference. SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Sometimes a user will accidentally type in a wrong number in the starting check number in A/P check printing and posting. If that check number is higher than the last check number used, the system will prompt to void all the check numbers in between. If that prompt is answered "ok", this will result in […]

The Select … for XML can be useful.  Let say that you want to list out Job Order information, one job per line, and one of the field will be WC, and you want to show all WCs for the job, in the format like WC1, WC2, WC3, …  Here is the select statement will […]

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) There is also a few useful UDF(User Defined Function) in Syteline ConvDate (@pInputDate  DateType, @pFormat   NVARCHAR(10)) Sample SELECT ConvDate(GetDate(), ‘MM/DD/YYYY’) Another useful one, DayEndOf ALTER FUNCTION [dbo].[DayEndOf] (   @Date DATETIME ) RETURNS DATETIME AS BEGIN     –  This function takes the input date and extends the time portion […]

First, determine the current site, after which you must name a configuration, by convention: DECLARE @Site SiteType SELECT @Site = site FROM parms Then determine the current SessionId:   DECLARE @SessionId RowPointerType SET @SessionId = dbo.SessionIdSp() Finally, add the procedure code:   BEGIN TRANSACTION UPDATE coitem SET due_date = dbo.CalcDueDate(@Parm1, @Parm2) WHERE coitem.co_num = @CoNum […]

Something to remember, in programming in multiple site environment, if you have site that has differ time zone than the server time zone, remember to use GetSiteDate function, instead of directly GETDATE().  Below is code sample.    SET @PrintDate = dbo.GetSiteDate(GETDATE())

  1) Update Statistics You should always run the below command nightly Exec sp_updatestats 2) Rebuild Index for key tables It is worth to rebuild index for key tables regularly, like once a week. DBCC DBREINDEX (ledger) GO DBCC DBREINDEX (item) GO DBCC DBREINDEX (customer) GO DBCC DBREINDEX (matltran) GO DBCC DBREINDEX (journal) GO DBCC […]

There is no simple way for reports to be cancelled from the client in Syteline 7 and 8. The Cancel button for Print Previews only kills the client part of a Preview. The background task continues to run on the TaskMan machine (The TaskMan machine is typically the Utility Server where the TaskMan process is […]