SQL Trick:- How to run TSQL from Cherwell to another SQL Server

Having played with linked tables from another Db into Cherwell you will quickly find out that OneSteps currently don't work well if at all, there's a reported bug waiting to be fixed. Even so wouldn't it be nice to have the full power of T-SQL to assail your third party DB. So having just worked on a small SQL Server based app that used triggers to call the Cherwell API's I wondered why we couldn't do that from Cherwell back to the Apps DB. So I created a small Cherwell BO with one record in it, located the records RecID as it should never change. Wrote a nifty trigger to write an Asset history Record back to the third parties DB for a given Asset number, and simply updated the Asset Number in my Charwell BO from a onestep and boom, it works like a charm, history record written. You could use this to send all manner of Cherwell data back to a remote DB. enjoy.

ALTER TRIGGER [dbo].[CherwellHistory] ON [dbo].[HistoryID]
AFTER INSERT,UPDATE
AS
BEGIN
declare @assetnumber varchar(1024),@assetid int

Select @assetnumber = assetnumber From HistoryID

INSERT INTO AssetWin70.DBO.HISTORY (

"HistoryID","TransactionCode","DescriptionID","DataCategory","ProductCategory","AssetNumber","DescriptionCode","Location",

"ConditionCode","Quantity","UnitCost","ScanDate","OldLocation","CostCenter","Field2",

"OldCostCenter","OldField2","ProductInventory","InServiceDate","SalvageValue","UsefulLife",

"GLAccount","Capitalized","AccumulatedDepreciation","YTDDepreciation","RemainingUsefulLife",

"NetBookValue","DisposalDate","Notes","CICO_FLAG","CICO_DATA","CICO_BY","CICO_OUT",

"CICO_TOUT","CICO_DUE","ACTIVE_LEA","CAPITAL_NO","CONTRACT_N","DELETIONS","DLS","EBUY_NO","HYBRID",

"ITEM_NO","PCN","PHYSICAL","RELABELED","SECUR_TAG","SERIAL_NO","SYS_DESIG","TRAIN_WEAP","TRANSFER","TYPE",

"UNLOCKS","UPGRA_ACT","VEHIC_TYP","WARR_EXP","USERDATE2","USERDATE3","USERDATE1","AMOUNT_REC","TRANS_FN","PHONE_NUMB","MFG_DATE",

"TEST","PICKED_UP","DATE_PICK","PRIORITY","OUT_OF_OFF","OOO_RETURN","KIT_NUMBER","IMEI")

(

SELECT

(SELECT MAX("HistoryID")+1 from AssetWin70.dbo.History),'P',

"DescriptionID","DataCategory","ProductCategory","AssetNumber","DescriptionCode","Location",

"ConditionCode","Quantity","UnitCost",CURRENT_TIMESTAMP,"OldLocation","CostCenter","Field2",

"OldCostCenter","OldField2","ProductInventory","InServiceDate","SalvageValue","UsefulLife",

"GLAccount","Capitalized","AccumulatedDepreciation","YTDDepreciation","RemainingUsefulLife",

"NetBookValue","DisposalDate","Notes","CICO_FLAG","CICO_DATA","CICO_BY","CICO_OUT",

"CICO_TOUT","CICO_DUE","ACTIVE_LEA","CAPITAL_NO","CONTRACT_N","DELETIONS","DLS","EBUY_NO","HYBRID",

"ITEM_NO","PCN","PHYSICAL","RELABELED","SECUR_TAG","SERIAL_NO","SYS_DESIG","TRAIN_WEAP","TRANSFER","TYPE",

"UNLOCKS","UPGRA_ACT","VEHIC_TYP","WARR_EXP","USERDATE2","USERDATE3","USERDATE1","AMOUNT_REC","TRANS_FN","PHONE_NUMB","MFG_DATE",

"TEST","PICKED_UP","DATE_PICK","PRIORITY","OUT_OF_OFF","OOO_RETURN","KIT_NUMBER","IMEI"

FROM AssetWin70.dbo.Assets

WHERE "AssetNumber" = @assetnumber)

END