Use the following script to shrink/truncate SQL database log file

USE [Database_Name]
GO
ALTER DATABASE [Database_Name] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DB_Log_File_Logical_Name, 1)
ALTER DATABASE [Database_Name] SET RECOVERY FULL WITH NO_WAIT
GO

Use the following script to check the file size.

USE [Database_Name]
GO

select name,  size from sys.database_files

When you try restoring a SQL database, you may get an error message saying “Exclusive access can’t obtained, database is in use”.   First, of course, stop all Syteline service, like IDO, Taskman.  If it still doesn’t work, try the below to set it to single user mode

Use Master

Alter Database Database_Name  SET SINGLE_USER With ROLLBACK IMMEDIATE

You may run Exec sp_who2 to see the user connection to database.

If this still doesn’t work, try detach/reattach the database.

Requirements

If you are trying to restore database backup of SQL Server 2008 to SQL Server 2005, you are bound to fail. Database backup of SQL Server 2008 is not compatible backward, you cannot restore it to SQL Server 2005. The following is a solution to convert databases of SQL Server 2008 to 2005

Step by Step Guide

1) Start convert wizard

Open SQL Server Management Studio2008. in ‘Object Explorer’, right click the database that you want to convert. Select ‘Tasks’ > ‘Generate Scripts…’.

2) Next

Click ‘Next’.

3) Select database and objects

Select the database that you want to convert, and check on ‘Scripts all objects in the selected databases‘

4) Convert Options

Set options:

'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'
    

5) Output Option

Select option ‘Script to file’, ‘Single file’ and ‘Unicode text’.

6) ‘Finish’

View summary and click ‘Finish’.

7) Result

Now you got a complete database creation script with data. It can be executed on target database server.

8) Amend Script

Open the generated script in SQL Server Management Studio 2005. Find the following section and amend the path to proper data folder

    CREATE DATABASE [StockTraderDB] ON  PRIMARY 
( NAME = N'StockTraderDB', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,
 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StockTraderDB_log', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF',
 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

9) Execute the Script

When finished, You should get converted database of SQL Server 2005

If you have hundreds users, manually rebuilding the User/Module Authorization will be painful.  The below query will copy over the User/Module Authorization from source DB to target DB.  Just run it against your target DB.

insert  modulemembers (objecttype, objectname, modulename, originalmodulename, modulememberspec)
select objecttype, objectname, modulename, originalmodulename, modulememberspec
from [Your_Source_DB_name].[dbo].[modulemembers]
where objectname like ‘OS_%’

If you even need to find out all store procedures that contain certain phrase, such as “PurchaseOrders”, the following query will help.

select so.name, sc.text from syscomments sc inner join sysobjects so on sc.id = so.id
where so.xtype = ‘P’ and sc.text like ‘%PurchaseOrders%’

This can be extended to other object type, for example ‘TR’ for trigger.   Here is a list of object type.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

If you are a Syteline administrator, you will often need to refresh your Test/Training database with update-to-date Production database.

The most common way for copying DBs in SQL server is to use the backup/restore function.

1) Bring down all Infor service.

2) Backup your Syteline production database.

In SQL management studio, right click database name, task-> backup.

3) Restore it to Test/Training DB.

In SQL management studio, right click database name, task-> restore –> database.

Select source from device, give it the backup file location and file name.

Click Option.  Select the “Overwrite the existing database” option.  Provide the restore to DB

4) Once the restore process is done, right click on your Test/Training database name, go “Property –> File”.  Make sure the database owner is sa.  If it is not, change it to sa.

5) Run the below command to your Test/Training database.

ALTER DATABASE dbname SET TRUSTWORTHY ON

6) Bring back all Infor service.

7) Login to your Test/Training Syteline system.  Go to Site/Entities form, change the “Database Name” and “Form Database Name” to correct name.  Since this is a copy of your production database, it carries that Production DB name over, and you will need to change them to correct Test/Training DB name.

8) In a multi-site environment, there are a few more steps need to be completed.  First, if you are using intranet licensing, you may have problem login, even with sa account.  Need to do the following in SQL.

1.  Temporarily disable any replication for the Site Admin category in the database to prevent any later updates from replicating over to the production database. This is generally only an issue if the test, pilot, or development database is housed on the same server as production, but I would strongly recommend disabling replication under any circumstances to avoid any possible unexpected issues.

To disable replication for Site Admin, run the following in SQL against the database that has been copied over:

update rep_rule set disable_repl = 1 where category = ‘Site Admin’

2. With replication disabled in step 1, now update the site record to temporarily disable intranet licensing, which should allow the ‘sa’ user to login without error. To disable intranet licensing, run the following query in SQL:

update site set IntranetLicensing = 0

9) Now, you should be able to login with sa account.  Complete the above step 7) to update the DB name.  This need to be done for all sites.

10) If the Site name is differ between your production and pilot environment, you need to run the “Change Site Name” utility to correct the site name.

11) Finally, regen Replication Trigger.

12) Once all the updates are finished, replication triggers are regenerated,  you should be able to return to the Sites/Entities form and re-enable Intranet Licensing for all sites, and then do the same for Disable Replication in the Replication Rules form.

13) Update the license

Done.

Here is the way to check a form’s mode in script.

If Instr(1, ucase(ThisForm.Caption), “(FILTER IN PLACE)”) > 0  Then
ReturnValue = “-1”
Exit Sub
End If

I think this is one of the FAQ question for T-SQL programmer, and here is some quick sample 

Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) 

Last Day of Current Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

Last Day of Next Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Show name of the month:

select datename (mm, date_field)

Or if you want to only show the first three letters of the month:

select left(datename (mm, date_field), 3)

In Syteline, there are many ways to default field value.  The most simple way is to use the default value property of the field component.  But if you need to set the default value based on some other field value, that simple property value would not cut it.

Syteline Application Case

We want to default ship via code in Customer Ship To form based on customer type.

Syteline Technical Components

Event Handler, Inline Script, SQL Store Procedure.

Customization Solution

Since the customer type is in Customer form, but not in Ship To form, we need to get that in.

1) Create a simple SP: nGetCustType

ALTER PROCEDURE [dbo].[navGetCustType]
(@CustNum [CustNumType],
@CustType [CustTypeType] output)

as

select @CustType = cust_type
from customer
where cust_num = @CustNum and cust_seq = 0

2) Add a new event handler to event：StdObjectNewCompleted, to call the following Inline Script：

Option Explicit On
Option Strict On

Imports System
Imports Microsoft.VisualBasic
Imports Mongoose.IDO.Protocol
Imports Mongoose.Scripting

Namespace SyteLine.GlobalScripts
Public Class EvHandler_StdObjectNewCompleted_5
Inherits GlobalScript

Sub Main()

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “nGetCustType”

nRequest.Parameters.Add(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“CustNum”))
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.Variables(“vCustType”).Value = nRetVal.Parameters(1).ToString

if ThisForm.Variables(“vCustType”).Value = “AAA” or _
ThisForm.Variables(“vCustType”).Value = “BBB”  then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UPRP”)
else
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UP7A”)
end if
ReturnValue = “0”
End Sub
End Class
End Namespace

Within this script, we call the SP, get the Customer Type, then based on the customer type to set the ShipCode value.

We will use a sample to run through the process of adding a CLM into IDO. 

Part of making the Customer Document Profile function available to RMA verification report, we need to create a new CLM to IDO SLRmas.  The CLM is based on SP  navProfileRMAVerificationSp. 

create PROCEDURE [dbo].[navProfileRMAVerificationSp]
(
   @RMAStatusOpen                   ListYesNoType       = NULL,
   @RMAStatusClosed                 ListYesNoType       = NULL,
   @RMAStatusStopped                ListYesNoType       = NULL,
   @RMAStatusHistory                ListYesNoType       = NULL,
   @RMAStarting                     RmaNumType          = NULL,
   @RMAEnding                       RmaNumType          = NULL,
   @RMADateStarting                    DateType            = NULL,
   @RMADateEnding                    DateType            = NULL,
   @WhseStarting                    WhseType            = NULL,
   @WhseEnding                        WhseType            = NULL,
   @CustomerStarting                CustNumType          = NULL,
   @CustomerEnding                  CustNumType          = NULL
) AS

DECLARE @UserParmSite SiteType,
        @RMAStatus nvarchar(4)

if @RMAStatusOpen = 1 set @RMAStatus = @RMAStatus + ‘O’
if @RMAStatusClosed = 1 set @RMAStatus = @RMAStatus + ‘C’
if @RMAStatusStopped = 1 set @RMAStatus = @RMAStatus + ‘S’
if @RMAStatusHistory = 1 set @RMAStatus = @RMAStatus + ‘H’

SELECT @UserParmSite = parms.site FROM parms
SET @WhseStarting    = ISNULL(@WhseStarting, dbo.LowCharacter())
SET @WhseEnding      = ISNULL(@WhseEnding, dbo.HighCharacter())
SET @RMAStarting          = CASE WHEN @RMAStarting IS NULL THEN dbo.LowCharacter()  ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAStarting) END
SET @RMAEnding            = CASE WHEN @RMAEnding   IS NULL THEN dbo.HighCharacter() ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAEnding)   END
SET @CustomerStarting = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerStarting), dbo.LowCharacter())
SET @CustomerEnding = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerEnding), dbo.HighCharacter())

   SELECT
     rma.rma_num
   , rma.cust_num
   , rma.cust_seq
   , CASE WHEN DocProfileCustomer.RowPointer IS NULL THEN 0 ELSE 1 END
   , DocProfileCustomer.Method
   , DocProfileCustomer.Destination
   , DocProfileCustomer.NumCopies
   , ISNULL(Customer.lang_code,
    (SELECT lang_code FROM Customer WHERE Customer.cust_num = rma.cust_num
    AND Customer.cust_seq =0)) as LangCode
   , DocProfileCustomer.CoverSheetCompany
   , DocProfileCustomer.CoverSheetContact
   FROM rma
   LEFT OUTER JOIN Customer
     ON rma.Cust_Num = Customer.cust_num
     AND rma.Cust_Seq = Customer.cust_seq
   LEFT OUTER JOIN DocProfileCustomer
      ON DocProfileCustomer.CustNum = rma.cust_num
     AND DocProfileCustomer.CustSeq = rma.cust_seq
     AND DocProfileCustomer.RptName = ‘RMA Verification Report’
     AND DocProfileCustomer.active = 1
   WHERE  CHARINDEX(rma.stat, @RMAStatus) <> 0
     AND rma.rma_num BETWEEN @RMAStarting AND @RMAEnding
     AND rma.whse BETWEEN @WhseStarting AND @WhseEnding
     AND rma.cust_num BETWEEN @CustomerStarting AND @CustomerEnding
     AND (@RMADateStarting is null or rma.rma_date >= @RMADateStarting)
     AND (@RMADateEnding is null or rma.rma_date <= @RMADateStarting)

Create new IDO as extension of existing IDO

Use the new IDO Wizard to create a new IDO, which is extend of a existing Syteline IDO

Add Table

Once the IDO created, we need to add table DocProfileCustomer to the IDO

The links for the table should be CustNum and CustSeq，link to rma.cust_num and rma.cust_seq.

Add New Property

Every output field from the SP navProfileRMAVerificationSp need to be mapped to a IDO property.  So we need to create those property upfront. 

Bound property

Field like rma.rma_num, rma.cust_num and rma.cust_seq are bound to table fields

Derrvied property

For fields that no directly link to table fields

Add Method

Finally, we will add the CLM (Custom Load Method)

Then map the output fields of store procedure to IDO properties

Now this new CLM should be ready to use in our form.