Dear All,

I am facing this peculiar problem in SQL server.

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Some times the process is coming as 80, 65 and 59. If this error comes then
I will have to close the application and restart it again. If Process 52
comes then only that sql statment is not getting executed but all the others
are getting executing.

Details of the error:

1. I am having 2 tables Voucher and Balances (I am using user defined data
types for all the fields in these tables).
2. I am having 1 stored procedue to insert a record to the voucher table.
3. I am having a trigger (in voucher table) in such a way that when ever an
record is inserted into the Vocuher table an record will get
inserted/updated to the balances table.

This error (Process 52) is coming in the trigger. If I comment the insert
and update statement in the trigger then it is working fine. If I uncomment
the insert and update statement then this error is coming.

Can anyone please help me out to resolve this problem. Your help is much
appreciated.

The coding related to this error is given below:

1. Table - Vocuher
2. Table - Balances
3. Trigger - trgUpdateBalancesWhileInsertVoucher
4. User Defined Data Type (Used in the tables and the triggers - Some
unwanted datatypes will be present. You just update them )
5. Defaults (Used in the tables and the triggers - Some unwanted defauts
will be present. You just update them )

Each set is separated by "---------"

----------------------------------------------------------------------------
------------------------------
Voucher Table Struture
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Voucher]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Voucher]
GO

CREATE TABLE [dbo].[Voucher] (
[SiteCode] [datypSiteCode] NOT NULL ,
[VoucherType] [datypVoucherType] NOT NULL ,
[VoucherDate] [datetime] NOT NULL ,
[ValueDate] [datetime] NOT NULL ,
[VoucherNo] [datypVoucherNo] NOT NULL ,
[VoucherReferenceNo] [datypReferenceNo] NOT NULL ,
[AcctCode] [datypAcctCode] NOT NULL ,
[SubLedgerCode] [datypAcctCode] NOT NULL ,
[SecCode] [datypSecCode] NOT NULL ,
[CurrencyCode] [datypCurrencyCode] NOT NULL ,
[BuySell] [datypBuySell] NOT NULL ,
[FCDr] [datypRate] NOT NULL ,
[FCCr] [datypRate] NOT NULL ,
[BCDr] [datypRate] NOT NULL ,
[BCCr] [datypRate] NOT NULL ,
[FXRate] [datypRate] NOT NULL ,
[Narration] [datypNarration] NOT NULL ,
[VoucherUniqueNo] [int] NOT NULL ,
[DealID] [datypDealID] NOT NULL ,
[Status] [datypStatus] NOT NULL ,
[Transactionno] [int] NOT NULL ,
[TransactionOriginNo] [int] NOT NULL ,
[AutoManual] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AnalysisCode1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AnalysisCode2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AnalysisCode3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BankCode] [datypBankCode] NOT NULL ,
[ChequeNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UploadFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreatorName] [datypUserName] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[ModifierName] [datypUserName] NOT NULL ,
[ModifiedDateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[AcctCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[AnalysisCode1]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[AnalysisCode2]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[AnalysisCode3]'
GO

EXEC sp_bindefault N'[dbo].[dfltA]', N'[Voucher].[AutoManual]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[BankCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[BCCr]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[BCDr]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[BuySell]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[ChequeNo]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]',
N'[Voucher].[CreatedDateTime]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[CreatorName]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[CurrencyCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[DealID]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[FCCr]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[FCDr]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[FXRate]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]',
N'[Voucher].[ModifiedDateTime]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[ModifierName]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[Narration]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[SecCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[SiteCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[Status]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[SubLedgerCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[Transactionno]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[TransactionOriginNo]'
GO

EXEC sp_bindefault N'[dbo].[dfltN]', N'[Voucher].[UploadFlag]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]', N'[Voucher].[ValueDate]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]', N'[Voucher].[VoucherDate]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[VoucherNo]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[VoucherReferenceNo]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Voucher].[VoucherType]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Voucher].[VoucherUniqueNo]'
GO

setuser
GO

----------------------------------------------------------------------------
------------------------------

----------------------------------------------------------------------------
------------------------------
Balances Table Struture
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Balances]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Balances]
GO

CREATE TABLE [dbo].[Balances] (
[SiteCode] [datypSiteCode] NOT NULL ,
[AcctCode] [datypAcctCode] NOT NULL ,
[SubLedgerCode] [datypSecCode] NOT NULL ,
[CurrencyCode] [datypCurrencyCode] NOT NULL ,
[OpenBalanceBC] [datypRate] NOT NULL ,
[OpenBalanceFC] [datypRate] NOT NULL ,
[LedgerBalanceBC] [datypRate] NOT NULL ,
[LedgerBalanceFC] [datypRate] NOT NULL ,
[ClosingBalanceBC] [datypRate] NOT NULL ,
[ClosingBalanceFC] [datypRate] NOT NULL ,
[Memo] [datypMemo] NOT NULL ,
[CreatorName] [datypUserName] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[ModifierName] [datypUserName] NOT NULL ,
[ModifiedDateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[AcctCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[ClosingBalanceBC]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[ClosingBalanceFC]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]',
N'[Balances].[CreatedDateTime]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[CreatorName]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[LedgerBalanceBC]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[LedgerBalanceFC]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[Memo]'
GO

EXEC sp_bindefault N'[dbo].[dfltSysDateTime]',
N'[Balances].[ModifiedDateTime]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[ModifierName]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[OpenBalanceBC]'
GO

EXEC sp_bindefault N'[dbo].[dfltZero]', N'[Balances].[OpenBalanceFC]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[SiteCode]'
GO

EXEC sp_bindefault N'[dbo].[dfltBlank]', N'[Balances].[SubLedgerCode]'
GO

setuser
GO

----------------------------------------------------------------------------
------------------------------

----------------------------------------------------------------------------
------------------------------
Voucher Table Trigger to insert a record to balances table
--- trgUpdateBalancesWhileInsertVoucher
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[trgUpdateBalancesWhileInsertVoucher]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[trgUpdateBalancesWhileInsertVoucher]
GO

CREATE TRIGGER trgUpdateBalancesWhileInsertVoucher ON Voucher
FOR INSERT AS

Declare
  @svSiteCode As datypSiteCode,
  @svAcctCode AS datypAcctCode,
  @svSubLedgerCode As datypAcctCode,
  @svCurrencyCode AS datypCurrencyCode,
  @dblAmountInBC AS datypRate,
  @dblAmountInFC AS datypRate,
  @svUserName AS datypUserName,
  @dvDateTime AS DateTime

IF NOT EXISTS(SELECT 1 FROM INSERTED)
  RETURN
DECLARE CurVoucher CURSOR FOR
  SELECT
    SiteCode,
    AcctCode,
    SubLedgerCode,
    CurrencyCode,
    CASE WHEN BCDr > 0 THEN BCDr ELSE -BCCr END,
    CASE WHEN FCDr > 0 THEN FCDr ELSE -FCCr END,
    CreatorName,
    CreatedDateTime
  FROM INSERTED

OPEN CurVoucher
FETCH CurVoucher INTO
     @svSiteCode,
     @svAcctCode,
     @svSubLedgerCode,
     @svCurrencyCode,
     @dblAmountInBC,
     @dblAmountInFC,
     @svUserName,
     @dvDateTime

WHILE @@FETCH_STATUS = 0
BEGIN


  --- If the Records Not exists then insert and update
  IF Not Exists(SELECT 1 FROM BALANCES
   WHERE SiteCode = @svSiteCode AND AcctCode = @svAcctCode
    AND SubLedgerCode = @svSubLedgerCode
    AND CurrencyCode = @svCurrencyCode)
  BEGIN
   INSERT INTO BALANCES(
     SiteCode,
     AcctCode,
     SubLedgerCode,
     CurrencyCode,
     CreatorName,
     CreatedDateTime)
   VALUES(
     @svSiteCode,
     @svAcctCode,
     @svSubLedgerCode,
     @svCurrencyCode,
     @svUserName,
     @dvDateTime)
  END


  SET @dblAmountInBC = ISNull(@dblAmountInBC, 0)
  SET @dblAmountInFC = ISNull(@dblAmountInFC, 0)


  --- Update the Balances
  UPDATE BALANCES SET
    LedgerBalanceBC = ISNull(LedgerBalanceBC,0) + @dblAmountInBC,
    LedgerBalanceFC = ISNull(LedgerBalanceFC,0) + @dblAmountInFC,
    ClosingBalanceBC = ISNull(OpenBalanceBC,0) + (ISNull(LedgerBalanceBC,0)
+ @dblAmountInBC),
    ClosingBalanceFC = ISNull(OpenBalanceFC,0) + (ISNull(LedgerBalanceFC,0)
+ @dblAmountInFC),
    ModifierName = @svUserName,
    ModifiedDateTime = @dvDateTime
  WHERE SiteCode = @svSiteCode AND AcctCode = @svAcctCode
    AND SubLedgerCode = @svSubLedgerCode
    AND CurrencyCode = @svCurrencyCode



  FETCH CurVoucher INTO
      @svSiteCode,
      @svAcctCode,
      @svSubLedgerCode,
      @svCurrencyCode,
      @dblAmountInBC,
      @dblAmountInFC,
      @svUserName,
      @dvDateTime

END

CLOSE CurVoucher
DEALLOCATE CurVoucher

----------------------------------------------------------------------------
------------------------------

----------------------------------------------------------------------------
------------------------------
User Defined Data Type

--- User Defined Data Type datypCurrencyCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCurrencyCode')
EXEC SP_DROPTYPE N'datypCurrencyCode'
GO

EXEC SP_ADDTYPE N'datypCurrencyCode', N'VarChar(15)', N'NOT NULL'
GO
--- User Defined Data Type datypBankCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypBankCode')
EXEC SP_DROPTYPE N'datypBankCode'
GO

EXEC SP_ADDTYPE N'datypBankCode', N'VarChar(15)', N'NOT NULL'
GO

--- User Defined Data Type datypHolidayCalendar
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypHolidayCalendar')
EXEC SP_DROPTYPE N'datypHolidayCalendar'
GO

EXEC SP_ADDTYPE N'datypHolidayCalendar', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypReferenceNo
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypReferenceNo')
EXEC SP_DROPTYPE N'datypReferenceNo'
GO

EXEC SP_ADDTYPE N'datypReferenceNo', N'VarChar(100)', N'NOT NULL'
GO

--- User Defined Data Type datypDealID
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypDealID')
EXEC SP_DROPTYPE N'datypDealID'
GO

EXEC SP_ADDTYPE N'datypDealID', N'VarChar(15)', N'NOT NULL'
GO

--- User Defined Data Type ClearingCounterParty
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCCPCode')
EXEC SP_DROPTYPE N'datypCCPCode'
GO

EXEC SP_ADDTYPE N'datypCCPCode', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypUserName
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypUserName')
EXEC SP_DROPTYPE N'datypUserName'
GO

EXEC SP_ADDTYPE N'datypUserName', N'VarChar(150)', N'NOT NULL'
GO

--- User Defined Data Type datypCustCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCustCode')
EXEC SP_DROPTYPE N'datypCustCode'
GO

EXEC SP_ADDTYPE N'datypCustCode', N'VarChar(15)', N'NOT NULL'
GO

--- User Defined Data Type datypCHCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCHCode')
EXEC SP_DROPTYPE N'datypCHCode'
GO

EXEC SP_ADDTYPE N'datypCHCode', N'VarChar(15)', N'NOT NULL'
GO


--- User Defined Data Type datypCustCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCPCode')
EXEC SP_DROPTYPE N'datypCPCode'
GO

EXEC SP_ADDTYPE N'datypCPCode', N'VarChar(15)', N'NOT NULL'
GO

--- User Defined Data Type datypAssetCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypAssetCode')
EXEC SP_DROPTYPE N'datypAssetCode'
GO

EXEC SP_ADDTYPE N'datypAssetCode', N'VarChar(15)', N'NOT NULL'
GO

--- User Defined Data Type datypAcctCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypAcctCode')
EXEC SP_DROPTYPE N'datypAcctCode'
GO

EXEC SP_ADDTYPE N'datypAcctCode', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypAcctName
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypAcctName')
EXEC SP_DROPTYPE N'datypAcctName'
GO

EXEC SP_ADDTYPE N'datypAcctName', N'VarChar(150)', N'NOT NULL'
GO

--- User Defined Data Type datypVoucherType
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypVoucherType')
EXEC SP_DROPTYPE N'datypVoucherType'
GO

EXEC SP_ADDTYPE N'datypVoucherType', N'VarChar(12)', N'NOT NULL'
GO

--- User Defined Data Type datypVoucherNo
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypVoucherNo')
EXEC SP_DROPTYPE N'datypVoucherNo'
GO

EXEC SP_ADDTYPE N'datypVoucherNo', N'VarChar(10)', N'NOT NULL'
GO

--- User Defined Data Type datypNarration
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypNarration')
EXEC SP_DROPTYPE N'datypNarration'
GO

EXEC SP_ADDTYPE N'datypNarration', N'VarChar(250)', N'NOT NULL'
GO

--- User Defined Data Type datypStatus
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypStatus')
EXEC SP_DROPTYPE N'datypStatus'
GO

EXEC SP_ADDTYPE N'datypStatus', N'VarChar(10)', N'NOT NULL'
GO

--- User Defined Data Type datypCountry
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypCountry')
EXEC SP_DROPTYPE N'datypCountry'
GO

EXEC SP_ADDTYPE N'datypCountry', N'VarChar(25)', N'NOT NULL'
GO

--- User Defined Data Type datypSecCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypSecCode')
EXEC SP_DROPTYPE N'datypSecCode'
GO

EXEC SP_ADDTYPE N'datypSecCode', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypContractCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypContractCode')
EXEC SP_DROPTYPE N'datypContractCode'
GO

EXEC SP_ADDTYPE N'datypContractCode', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypContractName
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypContractName')
EXEC SP_DROPTYPE N'datypContractName'
GO

EXEC SP_ADDTYPE N'datypContractName', N'VarChar(150)', N'NOT NULL'
GO

--- User Defined Data Type datypSecName
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypSecName')
EXEC SP_DROPTYPE N'datypSecName'
GO

EXEC SP_ADDTYPE N'datypSecName', N'VarChar(150)', N'NOT NULL'
GO

--- User Defined Data Type datypRICCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypRICCode')
EXEC SP_DROPTYPE N'datypRICCode'
GO

EXEC SP_ADDTYPE N'datypRICCode', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypSiteCode
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypSiteCode')
EXEC SP_DROPTYPE N'datypSiteCode'
GO

EXEC SP_ADDTYPE N'datypSiteCode', N'VarChar(10)', N'NOT NULL'
GO

--- User Defined Data Type datypMemo
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypMemo')
EXEC SP_DROPTYPE N'datypMemo'
GO

EXEC SP_ADDTYPE N'datypMemo', N'VarChar(250)', N'NOT NULL'
GO

--- User Defined Data Type datypApproveReject
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypApproveReject')
EXEC SP_DROPTYPE N'datypApproveReject'
GO

EXEC SP_ADDTYPE N'datypApproveReject', N'VarChar(1500)', N'NOT NULL'
GO

--- User Defined Data Type datypFXRate
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypFXRate')
EXEC SP_DROPTYPE N'datypFXRate'
GO

EXEC SP_ADDTYPE N'datypFXRate', N'Numeric(26,6)', N'NOT NULL'
GO

--- User Defined Data Type datypRate
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypRate')
EXEC SP_DROPTYPE N'datypRate'
GO

EXEC SP_ADDTYPE N'datypRate', N'Numeric(26,6)', N'NOT NULL'
GO

--- User Defined Data Type datypMktRegion
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypMktRegion')
EXEC SP_DROPTYPE N'datypMktRegion'
GO

EXEC SP_ADDTYPE N'datypMktRegion', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypSector
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypSector')
EXEC SP_DROPTYPE N'datypSector'
GO

EXEC SP_ADDTYPE N'datypSector', N'VarChar(50)', N'NOT NULL'
GO

--- User Defined Data Type datypFaceValue
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypFaceValue')
EXEC SP_DROPTYPE N'datypFaceValue'
GO

EXEC SP_ADDTYPE N'datypFaceValue', N'Numeric(26,6)', N'NOT NULL'
GO

--- User Defined Data Type datypBuySell
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypBuySell')
EXEC SP_DROPTYPE N'datypBuySell'
GO

EXEC SP_ADDTYPE N'datypBuySell', N'VarChar(4)', N'NOT NULL'
GO

--- User Defined Data Type datypDealEntryType
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypDealEntryType')
EXEC SP_DROPTYPE N'datypDealEntryType'
GO

EXEC SP_ADDTYPE N'datypDealEntryType', N'VarChar(25)', N'NOT NULL'
GO

--- User Defined Data Type datypFundMgrID
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypFundMgrID')
EXEC SP_DROPTYPE N'datypFundMgrID'
GO

EXEC SP_ADDTYPE N'datypFundMgrID', N'VarChar(150)', N'NOT NULL'
GO

--- User Defined Data Type datypDeskName
IF EXISTS (SELECT * FROM dbo.systypes WHERE name = N'datypDeskName')
EXEC SP_DROPTYPE N'datypDeskName'
GO

EXEC SP_ADDTYPE N'datypDeskName', N'VarChar(150)', N'NOT NULL'
GO

----------------------------------------------------------------------------
------------------------------

----------------------------------------------------------------------------
------------------------------
Defaults

--- Default dfltSysDate for get the Sytem Date
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSysDate]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltSysDate]
GO

CREATE DEFAULT dfltSysDate AS Convert(DateTime,
Convert(Char,GetDate(),105),103)
GO

--- Default dfltSysDateTime for get the System Date & Time
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSysDateTime]') AND OBJECTPROPERTY(id, N'IsDefault') =
1)
DROP DEFAULT [dbo].[dfltSysDateTime]
GO

CREATE DEFAULT dfltSysDateTime AS GetDate()
GO

--- Default dfltBlank for get the '' value
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltBlank]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltBlank]
GO

CREATE DEFAULT dfltBlank AS ''
GO

--- Default dfltY for get 'Y'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltY]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltY]
GO

CREATE DEFAULT dfltY AS 'Y'
GO

--- Default dfltY for get 'A'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltA]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltA]
GO

CREATE DEFAULT dfltA AS 'A'
GO

--- Default dfltY for get 'M'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltM]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltM]
GO

CREATE DEFAULT dfltM AS 'M'
GO

--- Default dfltP for get 'P'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltP]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltP]
GO

CREATE DEFAULT dfltP AS 'P'
GO
--- Default dfltN for get 'N'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltN]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltN]
GO

CREATE DEFAULT dfltN AS 'N'
GO

--- Default dfltYES to get 'YES'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltYES]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltYES]
GO

CREATE DEFAULT dfltYES AS 'YES'
GO

--- Default dfltNO to get 'N'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltNO]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltNO]
GO

CREATE DEFAULT dfltNO AS 'NO'
GO

--- Default dfltB for get 'B'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltB]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltB]
GO

CREATE DEFAULT dfltB AS 'B'
GO

--- Default dfltS for get 'S'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltS]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltS]
GO

CREATE DEFAULT dfltS AS 'S'
GO

--- Default dfltBUY to get 'BUY'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltBUY]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltBUY]
GO

CREATE DEFAULT dfltBUY AS 'BUY'
GO

--- Default dfltSELL to get 'SELL'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSELL]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltSELL]
GO

CREATE DEFAULT dfltSELL AS 'SELL'
GO

--- Default dfltZero to get 0
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltZero]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltZero]
GO

CREATE DEFAULT dfltZero AS 0
GO

--- Default dflt1 to get 1
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dflt1]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dflt1]
GO

CREATE DEFAULT dflt1 AS 0
GO

--- Default dfltSYSTEM to get 'SYSTEM'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSYSTEM]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltSYSTEM]
GO

CREATE DEFAULT dfltSYSTEM AS 'SYSTEM'
GO

--- Default dfltDr for get 'Dr'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltDr]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltDr]
GO

CREATE DEFAULT dfltDr AS 'B'
GO

--- Default dfltCr for get 'Cr'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltCr]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltCr]
GO

CREATE DEFAULT dfltCr AS 'Cr'
GO

--- Default dfltDebit for get 'Debit'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltDebit]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltDebit]
GO

CREATE DEFAULT dfltDebit AS 'Debit'
GO

--- Default dfltCredit for get 'Credit'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltCredit]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltCredit]
GO

CREATE DEFAULT dfltCredit AS 'Credit'
GO

--- Default dfltIN for get 'IN'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltIN]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltIN]
GO

CREATE DEFAULT dfltIN AS 'IN'
GO

--- Default dfltOUT for get 'OUT'
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltOUT]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltOUT]
GO

CREATE DEFAULT dfltOUT AS 'OUT'
GO

--- Default dfltSR for get 'SR'  (Spot Rate)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSR]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltSR]
GO

CREATE DEFAULT dfltSR AS 'SR'
GO

--- Default dfltOR for get 'OR' (Original Rate)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltOR]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltOR]
GO

CREATE DEFAULT dfltOR AS 'OR'
GO


--- Default dfltPOS for get 'POS'  (Positionwise)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltPOS]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltPOS]
GO

CREATE DEFAULT dfltPOS AS 'POS'
GO

--- Default dfltTRD for get 'TRD' (Tradewise)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltTRD]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltTRD]
GO

CREATE DEFAULT dfltTRD AS 'TRD'
GO


--- Default dfltINTL for get 'INTL'  (Internal)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltINTL]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltINTL]
GO

CREATE DEFAULT dfltINTL AS 'INTL'
GO

--- Default dfltEXTL for get 'EXTL' (External)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltEXTL]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltEXTL]
GO

CREATE DEFAULT dfltEXTL AS 'EXTL'
GO

--- Default dfltCONTRACT for get 'CONTRACT'  (Internal)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltCONTRACT]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltCONTRACT]
GO

CREATE DEFAULT dfltCONTRACT AS 'CONTRACT'
GO

--- Default dfltSTOCK for get 'STOCK' (Stock)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltSTOCK]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltSTOCK]
GO

CREATE DEFAULT dfltSTOCK AS 'STOCK'
GO

--- Default dfltManual for get 'STOCK' (Stock)
IF Exists (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[dfltManual]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
DROP DEFAULT [dbo].[dfltManual]
GO

CREATE DEFAULT dfltManual AS 'MANUAL'
GO

----------------------------------------------------------------------------
------------------------------

Thanks and Regards,

Peri

Hello Peri,

To the best of my knowledge I did not find any issue about the trigger and
the tables. Also, it does not seem to be related to any known issue.

My suggestion is that you drop and recreate clustered index and run dbcc
checkdb to check if there is any corruption there. I wonder if you have
ugpraded SQL 2000 to SP4.  If not, please upgrade to fix some known issues
to test.

In addtion, you may want to create a new database, and export the related
tables to this database to see if the issue persists.

To find out the root cause of this issue we may need to analyze memory
dumps or live debug, this work has to be done by contacting Microsoft
Product Support Services. Therefore, we probably will not be able to
resolve the issue through the newsgroups. I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Please let me know if you have any concerns.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process. - ERROR !!!
Show quoteHide quote(ISNull(LedgerBalanceBC,0)
(ISNull(LedgerBalanceFC,0)
Show quoteHide quote

SP succeeds but returns error
using UDF in 'IN' clause
The uninstall of sql server 2005 beta and visual studio 2005 is a mess
Consecutive values
Random number generation with seed value