access violation on sql server 7

an error has been returned by a long query shown below
SqlDumpExceptionHandler: Process 7 generated fatal
exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.
we are running on sqlserver 7 sp4
if one field is removed the query will run
also if the top 1 is removed it will work
the script will also work on sql server 2000
the query is shown below:-
SELECT TOP 1 ISNULL(CONVERT(CHAR,SEC_ID),'0') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0039),PORTIA_DESCRIPTION_1), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0039),PORTIA_DESCRIPTION_2), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY_TYPE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_CASH_BALANCE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0012),PORTIA_CUSIP_ISIN), ' ') + '\17'
+ ISNULL(CONVERT(CHAR(0015),PORTIA_PRICE_SYMBOL), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_STATE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_COUNTRY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_EXCHANGE), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_CURRENCY), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_MOODY_RATING), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_SP_RATING), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_OTHER_RATING), ' ')
+ '\17' + ISNULL(CONVERT(CHAR,PORTIA_COUPON_RATE),'0')
+ '\17' +
ISNULL(CONVERT(CHAR,PORTIA_MATURITY_DATE,120),' ') + '\17'
+ ISNULL(CONVERT(CHAR,PORTIA_MATURITY_PRICE),'0') + '\17'
+
ISNULL(CONVERT(CHAR,PORTIA_ISSUE_PRICE),'0') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_TAX_TYPE), ' ') + '\17' +
ISNULL(CONVERT(CHAR,PORTIA_DATED_DATE,120),' ') + '\17' +
ISNULL(CONVERT(CHAR,PORTIA_ODD_FIRST_CPN,120),' ') + '\17'
+
ISNULL(CONVERT(CHAR,PORTIA_ODD_LAST_CPN,120),' ') + '\17'
+ ISNULL(CONVERT(CHAR(0008),PORTIA_POOL_NUMBER), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_SEC_IDENTIFIER), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0015),PORTIA_DST_PMT_FREQ), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PATH), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PRICING_SOURCE), ' ')
+ '\17' +
ISNULL(CONVERT(CHAR(0040),PORTIA_DTC_ELIGIBLE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR
(0040),PORTIA_INTL_TAXSTATUS), ' ') + '\17' +
ISNULL(CONVERT(CHAR(0040),PORTIA_INTL_INCOMETYPE), ' ')
+ '\17' + ISNULL(CONVERT(CHAR,PORTIA_PAYMENT_DELAY),'0')
+ '\17'
FROM PORTIA_BLOOMBERG_SECURITIES
the create script for the table is shown below :-
it may look a bit odd but its an execute script to deal
with different databases
PRINT 'EXECUTING: Portia_bloomberg_SECURITIES.sql'
GO
if exists (select * from dbo.sysobjects where id = Object_id('dbo.PORTIA_BLOOMBERG_SECURITIES') and type in
('U','S'))
begin
drop table dbo.PORTIA_BLOOMBERG_SECURITIES
end
go
DECLARE @.LockClause VARCHAR(14)
IF CHARINDEX('Microsoft', @.@.VERSION) = 0 -- Sybase
SELECT @.LockClause = ' LOCK DATAROWS'
ELSE -- SQL Server
SELECT @.LockClause = ''
EXECUTE( 'CREATE TABLE dbo.PORTIA_BLOOMBERG_SECURITIES ('
+ 'SEC_ID NUMERIC(8,0) IDENTITY NOT NULL,'
+ 'RECEIVE_DATE DATETIME default getdate() NOT NULL,'
+ 'SEC_IDENTIFIER NVARCHAR(12) NOT NULL,'
+ 'SEC_IDENTIFIER_FLAG NVARCHAR(2) NOT NULL,'
+ 'SEC_LAST_UPDATE_DATE DATETIME NULL,'
+ 'SEC_TICKER NVARCHAR(8) NULL,'
+ 'SEC_COUPON_FI FLOAT NULL,'
+ 'SEC_COUPON FLOAT NULL,'
+ 'SEC_MATURITY DATETIME NULL,'
+ 'SEC_ISSUER NVARCHAR(80) NULL,'
+ 'SEC_ISSUE_AMOUNT NUMERIC(20,2) NULL,'
+ 'SEC_OUTSTANDING_AMOUNT FLOAT NULL,'
+ 'SEC_COUNTRY_CODE FLOAT NULL,'
+ 'SEC_CURRENCY_CODE FLOAT NULL,'
+ 'SEC_ISO_CURRENCY VARCHAR(3),'
+ 'SEC_ISSUE_PRICE FLOAT NULL,'
+ 'SEC_DATED_DATE DATETIME NULL,'
+ 'SEC_FIRST_COUPON_DATE DATETIME NULL,'
+ 'SEC_PENULTIMATE_COUPON_DATE DATETIME NULL,'
+ 'SEC_COUPON_FREQUENCY NUMERIC(9, 2) NULL,'
+ 'SEC_NEXT_REFIX_DATE DATETIME NULL,'
+ 'SEC_NEXT_COUPON_DATE DATETIME NULL,'
+ 'SEC_DAY_TYPE FLOAT NULL,'
+ 'SEC_SP_RATING NVARCHAR(8) NULL,'
+ 'SEC_MOODY_RATING NVARCHAR(8) NULL,'
+ 'SEC_FITCH_RATING NVARCHAR(8) NULL,'
+ 'SEC_DUFF_AND_PHELPS NVARCHAR(8) NULL,'
+ 'SEC_BB_COMPOSITE_RATING NVARCHAR(8) NULL,'
+ 'SEC_PRODUCT_GROUP FLOAT NULL,'
+ 'SEC_INDUSTRY_TYPE FLOAT NULL,'
+ 'SEC_CALCULATION_TYPE FLOAT NULL,'
+ 'SEC_PAYMENT_FREQUENCY FLOAT NULL,'
+ 'SEC_INTEREST_ACCRUAL_DATE DATETIME NULL,'
+ 'SEC_PRPL_FLAG NVARCHAR(1) NULL,'
+ 'SEC_FLOATER_FLAG NVARCHAR(1) NULL,'
+ 'SEC_BLOOMBERG_SECURITY NVARCHAR(12) NULL,'
+ 'SEC_POOL_NUMBER NVARCHAR(8) NULL,'
+ 'SEC_TRANCHE NVARCHAR(4) NULL,'
+ 'SEC_FACTOR FLOAT NULL,'
+ 'SEC_FACTOR_DATE DATETIME NULL,'
+ 'SEC_PAYMENT_DELAY FLOAT NULL,'
+ 'SEC_COLLATERAL NVARCHAR(8) NULL,'
+ 'SEC_CAP FLOAT NULL,'
+ 'SEC_FLOOR FLOAT NULL,'
+ 'SEC_WAC_CURRENT FLOAT NULL,'
+ 'SEC_WAC_ORIGINAL FLOAT NULL,'
+ 'SEC_WAM_REMAINING_MONTHS FLOAT NULL,'
+ 'SEC_WAM_ORIGINAL_MONTHS FLOAT NULL,'
+ 'SEC_WAL_MONTHS FLOAT NULL,'
+ 'SEC_PAC_LOWER_COLLAR FLOAT NULL,'
+ 'SEC_PAC_UPPER_COLLAR FLOAT NULL,'
+ 'SEC_PRE_PAYMENT_TYPE FLOAT NULL,'
+ 'SEC_PRE_PAYMENT_SPEED NUMERIC(8,3) NULL,'
+ 'SEC_PRIOR_MONTH_FACTOR FLOAT NULL,'
+ 'SEC_PRIOR_FACTOR_DATE DATETIME NULL,'
+ 'SEC_PAYMENT_ACCRUAL FLOAT NULL,'
+ 'SEC_NOTIONAL_PRINCIPLE_FLAG NVARCHAR(1) NULL,'
+ 'SEC_MORTGAGE_TYPE NVARCHAR(14) NULL,'
+ 'SEC_SERIES NVARCHAR(4) NULL,'
+ 'SEC_NEXT_CALL_DATE DATETIME NULL,'
+ 'SEC_NEXT_CALL_PRICE FLOAT NULL,'
+ 'SEC_PAR_CALL_DATE DATETIME NULL,'
+ 'SEC_NEXT_PUT_DATE DATETIME NULL,'
+ 'SEC_NEXT_PUT_PRICE FLOAT NULL,'
+ 'SEC_PAR_PUT_DATE DATETIME NULL,'
+ 'SEC_WORKOUT_DATE DATETIME NULL,'
+ 'SEC_WORKOUT_PRICE FLOAT NULL,'
+ 'SEC_STEP_UP_COUPON FLOAT NULL,'
+ 'SEC_STEP_UP_DATE DATETIME NULL,'
+ 'SEC_TREASURY_INDEX_FACTOR FLOAT NULL,'
+ 'SEC_TAX_STATUS NVARCHAR(1) NULL,'
+ 'SEC_CREDIT_ENHANCEMENTS NVARCHAR(28) NULL,'
+ 'SEC_STATE_CODE VARCHAR(15) NULL,'
+ 'SEC_PRE_REFUNDED_DATE DATETIME NULL,'
+ 'SEC_PRE_REFUNDED_PRICE FLOAT NULL,'
+ 'SEC_TAX_DESC NVARCHAR(18) NULL,'
+ 'SEC_STRIKE_PRICE_FI FLOAT NULL,'
+ 'SEC_STRIKE_PRICE FLOAT NULL,'
+ 'SEC_UNDERLYING_CUSIP NVARCHAR(9) NULL,'
+ 'SEC_EXPIRATION_DATE DATETIME NULL,'
+ 'SEC_PUT_CALL_IND NVARCHAR(1) NULL,'
+ 'SEC_CONTRACT_SIZE FLOAT NULL,'
+ 'SEC_DIVIDEND_FREQUENCY FLOAT NULL,'
+ 'SEC_LAST_DIVI_PER_SHARE NUMERIC(8,5) NULL,'
+ 'SEC_EX_DIVIDEND_DATE DATETIME NULL,'
+ 'SEC_DIVIDEND_PAY_DATE DATETIME NULL,'
+ 'SEC_DIVIDEND_REC_DATE DATETIME NULL,'
+ 'SEC_SPLIT_DATE DATETIME NULL,'
+ 'SEC_IMPLIED_VOLATILITY FLOAT NULL,'
+ 'SEC_DELTA FLOAT NULL,'
+ 'SEC_PAY_SPREAD FLOAT NULL,'
+ 'SEC_RECV_SPREAD FLOAT NULL,'
+ 'SEC_RECV_COUPON_FI FLOAT NULL,'
+ 'SEC_RECV_COUPON FLOAT NULL,'
+ 'SEC_RECV_COUNTRY FLOAT NULL,'
+ 'SEC_RECV_CURRENCY FLOAT NULL,'
+ 'SEC_RECV_FIRST_COUPON_DATE DATETIME NULL,'
+ 'SEC_RECV_COUPON_FREQ FLOAT NULL,'
+ 'SEC_RECV_NEXT_REFIX_DATE DATETIME NULL,'
+ 'SEC_RECV_NEXT_COUPON_DATE DATETIME NULL,'
+ 'SEC_RECV_DAY_TYPE FLOAT NULL,'
+ 'SEC_RECV_PAYMENT_FREQ FLOAT NULL,'
+ 'SEC_PAY_FIXED_RATE NVARCHAR(17) NULL,'
+ 'SEC_RECV_FIXED_RATE NVARCHAR(17) NULL,'
+ 'SEC_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'SEC_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'SEC_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'SEC_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'SEC_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'SEC_SHARES_PER_WARRANT FLOAT NULL,'
+ 'SEC_IS_SINKABLE VARCHAR(1) NULL,'
+ 'SEC_IS_CONVERTIBLE VARCHAR(1) NULL,'
+ 'SEC_INDUSTRY_SUBGROUP VARCHAR(24) NULL,'
+ 'SEC_RESET_INDEX VARCHAR(10) NULL,'
+ 'SEC_LAST_REFIX_DATE DATETIME NULL,'
+ 'SEC_FIRST_RATE_RESET_DATE DATETIME NULL,'
+ 'SEC_FIRST_PAYMENT_RESET_DATE DATETIME NULL,'
+ 'SEC_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'SEC_ISIN_NUMBER VARCHAR(12) NULL,'
+ 'SEC_SEDOL1_NUMBER VARCHAR(8) NULL,'
+ 'SEC_SEDOL2_NUMBER VARCHAR(8) NULL,'
+ 'SEC_CUSIP_NUMBER VARCHAR(9) NULL,'
+ 'SEC_CINS_NUMBER VARCHAR(8) NULL,'
+ 'SEC_COMMON_STK_ISIN_NUMBER VARCHAR(12) NULL,'
+ 'SEC_IS_144A_ELIGIBLE VARCHAR(4) NULL,'
+ 'SEC_IS_ZERO_COUPON VARCHAR(2) NULL,'
+ 'SEC_ROUND_LOT_SIZE NUMERIC(8,2) NULL,'
+ 'SEC_QUOTE_UNITS VARCHAR(12) NULL,'
+ 'SEC_IS_QUOTED_AS_A_PERC_OF_PAR VARCHAR(4) NULL,'
+ 'SEC_MINIMUM_PIECE NUMERIC(8,2) NULL,'
+ 'SEC_IS_OID_BOND VARCHAR(8) NULL,'
+ 'SEC_M_MKT_GUARANTOR VARCHAR(14) NULL,'
+ 'SEC_GUARANTOR VARCHAR(30) NULL,'
+ 'SEC_MUNI_REMARKETING_AGENT VARCHAR(8) NULL,'
+ 'SEC_MUNI_ALT_MIN_TAX VARCHAR(8) NULL,'
+ 'SEC_PUT_NOTIFICATION_MIN_DAYS NUMERIC(8,2) NULL,'
+ 'SEC_DAY_COUNT_DESC VARCHAR(20) NULL,'
+ 'SEC_CALCULATION_TYPE_DESC VARCHAR(8) NULL,'
+ 'SEC_MM_PROGRAM_TYPE VARCHAR(8) NULL,'
+ 'SEC_COLLATERAL_TYPE VARCHAR(28) NULL,'
+ 'SEC_BID_PRICE_DEC NUMERIC(8,2) NULL,'
+ 'SEC_LAST_UPDATE_DATETIME VARCHAR(10) NULL,'
+ 'SEC_INDUSTRY_SECTOR VARCHAR(24) NULL,'
+ 'SEC_INDUSTRY_GROUP VARCHAR(30) NULL,'
+ 'SEC_GICS_INDUSTRY_GROUP NUMERIC(7,2) NULL,'
+ 'SEC_GICS_INDUSTRY_GROUP_NAME VARCHAR(30) NULL,'
+ 'SEC_BLOOMBERG_SUBFLAG NUMERIC(4,0) NULL,'
+ 'SEC_COUNTRY_ISO_CODE VARCHAR(3) NULL,'
+ 'SEC_MAIN_ISO VARCHAR(3) NULL,'
+ 'SEC_QUOTE_LOT_SIZE NUMERIC(11,2) NULL,'
+ 'SEC_SECURITY_DESCRIPTION VARCHAR(15) NULL,'
+ 'SEC_SECURITY_TYPE_2 VARCHAR(28) NULL,'
+ 'SEC_SECTOR VARCHAR(10) NULL,'
+ 'SEC_SHORT_NAME VARCHAR(18) NULL,'
+ 'SEC_SIC_CODE VARCHAR(4) NULL,'
+ 'SEC_MTGE_IS_AGENCY_BACKED VARCHAR(4) NULL,'
+ 'SEC_DTC_ELIGIBLE VARCHAR(4) NULL,'
+ 'SEC_REDEMPTION_VALUE NUMERIC(16,6) NULL,'
+ 'SEC_IS_DEFAULTED VARCHAR(4) NULL,'
+ 'SEC_MTGE_PAYMENT_DELAY VARCHAR(8) NULL,'
+ 'SEC_MTGE_ORIGINAL_AMOUNT VARCHAR(17)NULL,'
+ 'SEC_CONVERSION_PRICE NUMERIC(16,6) NULL,'
+ 'SEC_CONVERSION_RATIO NUMERIC(16,4) NULL,'
+ 'SEC_CONVERTIBLE_START_DATE DATETIME NULL,'
+ 'SEC_CONVERTIBLE_UNTIL DATETIME NULL,'
+ 'SEC_FIXD_EX_RTE_CONVERTIBLES NUMERIC(12,6) NULL,'
+ 'SEC_MARKET_SECTOR_DESCRIPTION VARCHAR(6) NULL,'
+ 'SEC_SECURITY_TYPE VARCHAR(28) NULL,'
+ 'SEC_SIC_NAME VARCHAR(14) NULL,'
+ 'SEC_MTY_OR_REFUND_TYPE VARCHAR(18) NULL,'
+ 'SEC_MID_YLD2WORST_CONVENTION NUMERIC(14,6) NULL,'
+ 'SEC_MTGE_WAL_IN_YEARS_TO_CALL NUMERIC(8,4) NULL,'
+ 'SEC_MID_OAS_EFFECTIVE_DURATION NUMERIC(8,4) NULL,'
+ 'SEC_MID_OAS_CONVEXITY NUMERIC(8,4) NULL,'
+ 'SEC_MID_MODIFIED_DURATION NUMERIC(8,4) NULL,'
+ 'SEC_ISSUE_DATE DATETIME NULL,'
+ 'SEC_PREPAYMENT_TYPE NUMERIC(2, 0) NULL,'
+ 'SEC_PREPAYMENT_SPEED NUMERIC(8, 3) NULL,'
+ 'SEC_MTGE_GENERIC_TICKER VARCHAR(8) NULL,'
+ 'PORTIA_SECURITY VARCHAR(15) NULL,'
+ 'PORTIA_DESCRIPTION_1 VARCHAR(39) NULL,'
+ 'PORTIA_DESCRIPTION_2 VARCHAR(39) NULL,'
+ 'PORTIA_SECURITY_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_CASH_BALANCE VARCHAR(15) NULL,'
+ 'PORTIA_CUSIP_ISIN VARCHAR(12) NULL,'
+ 'PORTIA_PRICE_SYMBOL VARCHAR(15) NULL,'
+ 'PORTIA_STATE VARCHAR(15) NULL,'
+ 'PORTIA_COUNTRY VARCHAR(15) NULL,'
+ 'PORTIA_EXCHANGE VARCHAR(15) NULL,'
+ 'PORTIA_CURRENCY VARCHAR(15) NULL,'
+ 'PORTIA_MOODY_RATING VARCHAR(15) NULL,'
+ 'PORTIA_SP_RATING VARCHAR(15) NULL,'
+ 'PORTIA_OTHER_RATING VARCHAR(15) NULL,'
+ 'PORTIA_COUPON_RATE FLOAT NULL,'
+ 'PORTIA_MATURITY_DATE DATETIME NULL,'
+ 'PORTIA_MATURITY_PRICE FLOAT NULL,'
+ 'PORTIA_ISSUE_DATE DATETIME NULL,'
+ 'PORTIA_ISSUE_PRICE FLOAT NULL,'
+ 'PORTIA_TAX_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_DATED_DATE DATETIME NULL,'
+ 'PORTIA_ODD_FIRST_CPN DATETIME NULL,'
+ 'PORTIA_ODD_LAST_CPN DATETIME NULL,'
+ 'PORTIA_POOL_NUMBER VARCHAR(8) NULL,'
+ 'PORTIA_SEC_IDENTIFIER VARCHAR(15) NULL,'
+ 'PORTIA_TRADE_STATUS VARCHAR(10) default ''Received''
NULL,'
+ 'PORTIA_TRADE_STATUS_MESSAGE VARCHAR(255) NULL,'
+ 'PORTIA_DST_PMT_FREQ VARCHAR(15) NULL,'
+ 'PORTIA_DST_PATH VARCHAR(15) NULL,'
+ 'PORTIA_DST_PRICING_SOURCE VARCHAR(15) NULL,'
+ 'PORTIA_DTC_ELIGIBLE VARCHAR(40) NULL,'
+ 'PORTIA_INTL_TAXSTATUS VARCHAR(40) NULL,'
+ 'PORTIA_INTL_INCOMETYPE VARCHAR(40) NULL,'
+ 'PORTIA_SOURCE_CATEGORY VARCHAR(15) NULL,'
+ 'PORTIA_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
+ 'PORTIA_SETTLE_LOCATION VARCHAR(15) NULL,'
+ 'PORTIA_SHARES_OUTSTANDING FLOAT NULL,'
+ 'PORTIA_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
+ 'PORTIA_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'PORTIA_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'PORTIA_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'PORTIA_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'PORTIA_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'PORTIA_SHARES_PER_WARRANT FLOAT NULL,'
+ 'PORTIA_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'PORTIA_ST_SP_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ST_MOODY_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ST_OTHER_RATING VARCHAR(15) NULL,'
+ 'PORTIA_ISSUER VARCHAR(15) NULL,'
+ 'PORTIA_DATA_SOURCE VARCHAR(15) NULL,'
+ 'PORTIA_SIC_CODE VARCHAR(5) NULL,'
+ 'PORTIA_INDUSTRY VARCHAR(15) NULL,'
+ 'PORTIA_PREPAY_TABLE VARCHAR(15) NULL,'
+ 'PORTIA_PREPAY_RATE NUMERIC(8, 3) NULL,' --
FLOAT in Portia
+ 'PORTIA_CASH_FLOW_SRC VARCHAR(15) NULL,'
+ 'PORTIA_CONV_SECURITY VARCHAR(15) NULL,'
+ 'PORTIA_CONV_PRICE NUMERIC(16,6)
NULL,' -- FLOAT in Portia
+ 'PORTIA_CONV_RATIO NUMERIC(16,4)
NULL,' -- FLOAT in Portia
+ 'PORTIA_CONV_START_DATE DATETIME NULL,'
+ 'PORTIA_CONV_END_DATE DATETIME NULL,'
+ 'PORTIA_CONV_EXER_RATE NUMERIC(12,6)
NULL,' -- FLOAT in Portia
+ 'PORTIA_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' --
FLOAT in Portia
+ 'PORTIA_USR_DEF_STRING_01 VARCHAR(15) NULL,'
+ 'PORTIA_USR_DEF_DATE_02 DATETIME NULL,'
+ 'NEW_SECURITY_FLAG VARCHAR(1) default ''Y'' NOT NULL,'
+ 'FRACT_IND_RUN_STATUS VARCHAR(1) NULL,'
+ 'MAPPING_RUN_STATUS VARCHAR(1) NULL,'
+ 'LINE_NUMBER int NULL,'
+ 'P2_SECURITY VARCHAR(15) NULL,'
+ 'P2_DESCRIPTION_1 VARCHAR(39) NULL,'
+ 'P2_DESCRIPTION_2 VARCHAR(39) NULL,'
+ 'P2_SECURITY_TYPE VARCHAR(15) NULL,'
+ 'P2_CASH_BALANCE VARCHAR(15) NULL,'
+ 'P2_CUSIP_ISIN VARCHAR(12) NULL,'
+ 'P2_PRICE_SYMBOL VARCHAR(15) NULL,'
+ 'P2_STATE VARCHAR(15) NULL,'
+ 'P2_COUNTRY VARCHAR(15) NULL,'
+ 'P2_EXCHANGE VARCHAR(15) NULL,'
+ 'P2_CURRENCY VARCHAR(15) NULL,'
+ 'P2_MOODY_RATING VARCHAR(15) NULL,'
+ 'P2_SP_RATING VARCHAR(15) NULL,'
+ 'P2_OTHER_RATING VARCHAR(15) NULL,'
+ 'P2_COUPON_RATE FLOAT NULL,'
+ 'P2_MATURITY_DATE DATETIME NULL,'
+ 'P2_MATURITY_PRICE FLOAT NULL,'
+ 'P2_ISSUE_DATE DATETIME NULL,'
+ 'P2_ISSUE_PRICE FLOAT NULL,'
+ 'P2_TAX_TYPE VARCHAR(15) NULL,'
+ 'P2_DATED_DATE DATETIME NULL,'
+ 'P2_ODD_FIRST_CPN DATETIME NULL,'
+ 'P2_ODD_LAST_CPN DATETIME NULL,'
+ 'P2_POOL_NUMBER VARCHAR(8) NULL,'
+ 'P2_SEC_IDENTIFIER VARCHAR(15) NULL,'
+ 'P2_DST_PMT_FREQ VARCHAR(15) NULL,'
+ 'P2_DST_PATH VARCHAR(15) NULL,'
+ 'P2_DST_PRICING_SOURCE VARCHAR(15) NULL,'
+ 'P2_DTC_ELIGIBLE VARCHAR(40) NULL,'
+ 'P2_INTL_TAXSTATUS VARCHAR(40) NULL,'
+ 'P2_INTL_INCOMETYPE VARCHAR(40) NULL,'
+ 'P2_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
+ 'P2_SETTLE_LOCATION VARCHAR(15) NULL,'
+ 'P2_SHARES_OUTSTANDING FLOAT NULL,'
+ 'P2_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
+ 'P2_WARRANT_EXPIRE_DATE DATETIME NULL,'
+ 'P2_WARRANT_UNDERLY VARCHAR(10) NULL,'
+ 'P2_WARRANT_EXERCISE_PRICE FLOAT NULL,'
+ 'P2_WARRANT_EXERCISE_DATE DATETIME NULL,'
+ 'P2_WARRANT_ISSUE_DATE DATETIME NULL,'
+ 'P2_SHARES_PER_WARRANT FLOAT NULL,'
+ 'P2_MUNI_PURPOSE VARCHAR(24) NULL,'
+ 'P2_ST_SP_RATING VARCHAR(15) NULL,'
+ 'P2_ST_MOODY_RATING VARCHAR(15) NULL,'
+ 'P2_ST_OTHER_RATING VARCHAR(15) NULL,'
+ 'P2_ISSUER VARCHAR(15) NULL,'
+ 'P2_DATA_SOURCE VARCHAR(15) NULL,'
+ 'P2_SIC_CODE VARCHAR(5) NULL,'
+ 'P2_INDUSTRY VARCHAR(15) NULL,'
+ 'P2_PREPAY_TABLE VARCHAR(15) NULL,'
+ 'P2_PREPAY_RATE NUMERIC(8, 3) NULL,' --
FLOAT in Portia
+ 'P2_CASH_FLOW_SRC VARCHAR(15) NULL,'
+ 'P2_CONV_SECURITY VARCHAR(15) NULL,'
+ 'P2_CONV_PRICE NUMERIC(16,6)
NULL,' -- FLOAT in Portia
+ 'P2_CONV_RATIO NUMERIC(16,4)
NULL,' -- FLOAT in Portia
+ 'P2_CONV_START_DATE DATETIME NULL,'
+ 'P2_CONV_END_DATE DATETIME NULL,'
+ 'P2_CONV_EXER_RATE NUMERIC(12,6) NULL,' --
FLOAT in Portia
+ 'P2_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' -- FLOAT in
Portia
+ 'P2_USR_DEF_STRING_01 VARCHAR(15) NULL,'
+ 'P2_USR_DEF_DATE_02 DATETIME NULL,'
+ 'CONSTRAINT pk_sec_id PRIMARY KEY NONCLUSTERED
(SEC_ID))' + @.LockClause
)
GO
CREATE NONCLUSTERED INDEX idx_new_sec_flag ON
dbo.PORTIA_BLOOMBERG_SECURITIES(NEW_SECURITY_FLAG)
GO
any help would be very much appreciated
thanksThese types of error are most often errors in SQL Server. Assuming that you have already searched KB
and are current on service pack, I suggest you open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Mark Percival" <anonymous@.discussions.microsoft.com> wrote in message
news:092601c39714$a1454f40$a501280a@.phx.gbl...
> an error has been returned by a long query shown below
> SqlDumpExceptionHandler: Process 7 generated fatal
> exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
> process.
> we are running on sqlserver 7 sp4
> if one field is removed the query will run
> also if the top 1 is removed it will work
> the script will also work on sql server 2000
> the query is shown below:-
>
> SELECT TOP 1 ISNULL(CONVERT(CHAR,SEC_ID),'0') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0039),PORTIA_DESCRIPTION_1), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0039),PORTIA_DESCRIPTION_2), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SECURITY_TYPE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_CASH_BALANCE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0012),PORTIA_CUSIP_ISIN), ' ') + '\17'
> + ISNULL(CONVERT(CHAR(0015),PORTIA_PRICE_SYMBOL), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_STATE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_COUNTRY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_EXCHANGE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_CURRENCY), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_MOODY_RATING), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_SP_RATING), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_OTHER_RATING), ' ')
> + '\17' + ISNULL(CONVERT(CHAR,PORTIA_COUPON_RATE),'0')
> + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_MATURITY_DATE,120),' ') + '\17'
> + ISNULL(CONVERT(CHAR,PORTIA_MATURITY_PRICE),'0') + '\17'
> +
> ISNULL(CONVERT(CHAR,PORTIA_ISSUE_PRICE),'0') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_TAX_TYPE), ' ') + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_DATED_DATE,120),' ') + '\17' +
> ISNULL(CONVERT(CHAR,PORTIA_ODD_FIRST_CPN,120),' ') + '\17'
> +
> ISNULL(CONVERT(CHAR,PORTIA_ODD_LAST_CPN,120),' ') + '\17'
> + ISNULL(CONVERT(CHAR(0008),PORTIA_POOL_NUMBER), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_SEC_IDENTIFIER), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0015),PORTIA_DST_PMT_FREQ), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PATH), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0015),PORTIA_DST_PRICING_SOURCE), ' ')
> + '\17' +
> ISNULL(CONVERT(CHAR(0040),PORTIA_DTC_ELIGIBLE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR
> (0040),PORTIA_INTL_TAXSTATUS), ' ') + '\17' +
> ISNULL(CONVERT(CHAR(0040),PORTIA_INTL_INCOMETYPE), ' ')
> + '\17' + ISNULL(CONVERT(CHAR,PORTIA_PAYMENT_DELAY),'0')
> + '\17'
> FROM PORTIA_BLOOMBERG_SECURITIES
> the create script for the table is shown below :-
> it may look a bit odd but its an execute script to deal
> with different databases
>
> PRINT 'EXECUTING: Portia_bloomberg_SECURITIES.sql'
> GO
>
> if exists (select * from dbo.sysobjects where id => Object_id('dbo.PORTIA_BLOOMBERG_SECURITIES') and type in
> ('U','S'))
> begin
> drop table dbo.PORTIA_BLOOMBERG_SECURITIES
> end
> go
>
> DECLARE @.LockClause VARCHAR(14)
> IF CHARINDEX('Microsoft', @.@.VERSION) = 0 -- Sybase
> SELECT @.LockClause = ' LOCK DATAROWS'
> ELSE -- SQL Server
> SELECT @.LockClause = ''
> EXECUTE( 'CREATE TABLE dbo.PORTIA_BLOOMBERG_SECURITIES ('
> + 'SEC_ID NUMERIC(8,0) IDENTITY NOT NULL,'
> + 'RECEIVE_DATE DATETIME default getdate() NOT NULL,'
> + 'SEC_IDENTIFIER NVARCHAR(12) NOT NULL,'
> + 'SEC_IDENTIFIER_FLAG NVARCHAR(2) NOT NULL,'
> + 'SEC_LAST_UPDATE_DATE DATETIME NULL,'
> + 'SEC_TICKER NVARCHAR(8) NULL,'
> + 'SEC_COUPON_FI FLOAT NULL,'
> + 'SEC_COUPON FLOAT NULL,'
> + 'SEC_MATURITY DATETIME NULL,'
> + 'SEC_ISSUER NVARCHAR(80) NULL,'
> + 'SEC_ISSUE_AMOUNT NUMERIC(20,2) NULL,'
> + 'SEC_OUTSTANDING_AMOUNT FLOAT NULL,'
> + 'SEC_COUNTRY_CODE FLOAT NULL,'
> + 'SEC_CURRENCY_CODE FLOAT NULL,'
> + 'SEC_ISO_CURRENCY VARCHAR(3),'
> + 'SEC_ISSUE_PRICE FLOAT NULL,'
> + 'SEC_DATED_DATE DATETIME NULL,'
> + 'SEC_FIRST_COUPON_DATE DATETIME NULL,'
> + 'SEC_PENULTIMATE_COUPON_DATE DATETIME NULL,'
> + 'SEC_COUPON_FREQUENCY NUMERIC(9, 2) NULL,'
> + 'SEC_NEXT_REFIX_DATE DATETIME NULL,'
> + 'SEC_NEXT_COUPON_DATE DATETIME NULL,'
> + 'SEC_DAY_TYPE FLOAT NULL,'
> + 'SEC_SP_RATING NVARCHAR(8) NULL,'
> + 'SEC_MOODY_RATING NVARCHAR(8) NULL,'
> + 'SEC_FITCH_RATING NVARCHAR(8) NULL,'
> + 'SEC_DUFF_AND_PHELPS NVARCHAR(8) NULL,'
> + 'SEC_BB_COMPOSITE_RATING NVARCHAR(8) NULL,'
> + 'SEC_PRODUCT_GROUP FLOAT NULL,'
> + 'SEC_INDUSTRY_TYPE FLOAT NULL,'
> + 'SEC_CALCULATION_TYPE FLOAT NULL,'
> + 'SEC_PAYMENT_FREQUENCY FLOAT NULL,'
> + 'SEC_INTEREST_ACCRUAL_DATE DATETIME NULL,'
> + 'SEC_PRPL_FLAG NVARCHAR(1) NULL,'
> + 'SEC_FLOATER_FLAG NVARCHAR(1) NULL,'
> + 'SEC_BLOOMBERG_SECURITY NVARCHAR(12) NULL,'
> + 'SEC_POOL_NUMBER NVARCHAR(8) NULL,'
> + 'SEC_TRANCHE NVARCHAR(4) NULL,'
> + 'SEC_FACTOR FLOAT NULL,'
> + 'SEC_FACTOR_DATE DATETIME NULL,'
> + 'SEC_PAYMENT_DELAY FLOAT NULL,'
> + 'SEC_COLLATERAL NVARCHAR(8) NULL,'
> + 'SEC_CAP FLOAT NULL,'
> + 'SEC_FLOOR FLOAT NULL,'
> + 'SEC_WAC_CURRENT FLOAT NULL,'
> + 'SEC_WAC_ORIGINAL FLOAT NULL,'
> + 'SEC_WAM_REMAINING_MONTHS FLOAT NULL,'
> + 'SEC_WAM_ORIGINAL_MONTHS FLOAT NULL,'
> + 'SEC_WAL_MONTHS FLOAT NULL,'
> + 'SEC_PAC_LOWER_COLLAR FLOAT NULL,'
> + 'SEC_PAC_UPPER_COLLAR FLOAT NULL,'
> + 'SEC_PRE_PAYMENT_TYPE FLOAT NULL,'
> + 'SEC_PRE_PAYMENT_SPEED NUMERIC(8,3) NULL,'
> + 'SEC_PRIOR_MONTH_FACTOR FLOAT NULL,'
> + 'SEC_PRIOR_FACTOR_DATE DATETIME NULL,'
> + 'SEC_PAYMENT_ACCRUAL FLOAT NULL,'
> + 'SEC_NOTIONAL_PRINCIPLE_FLAG NVARCHAR(1) NULL,'
> + 'SEC_MORTGAGE_TYPE NVARCHAR(14) NULL,'
> + 'SEC_SERIES NVARCHAR(4) NULL,'
> + 'SEC_NEXT_CALL_DATE DATETIME NULL,'
> + 'SEC_NEXT_CALL_PRICE FLOAT NULL,'
> + 'SEC_PAR_CALL_DATE DATETIME NULL,'
> + 'SEC_NEXT_PUT_DATE DATETIME NULL,'
> + 'SEC_NEXT_PUT_PRICE FLOAT NULL,'
> + 'SEC_PAR_PUT_DATE DATETIME NULL,'
> + 'SEC_WORKOUT_DATE DATETIME NULL,'
> + 'SEC_WORKOUT_PRICE FLOAT NULL,'
> + 'SEC_STEP_UP_COUPON FLOAT NULL,'
> + 'SEC_STEP_UP_DATE DATETIME NULL,'
> + 'SEC_TREASURY_INDEX_FACTOR FLOAT NULL,'
> + 'SEC_TAX_STATUS NVARCHAR(1) NULL,'
> + 'SEC_CREDIT_ENHANCEMENTS NVARCHAR(28) NULL,'
> + 'SEC_STATE_CODE VARCHAR(15) NULL,'
> + 'SEC_PRE_REFUNDED_DATE DATETIME NULL,'
> + 'SEC_PRE_REFUNDED_PRICE FLOAT NULL,'
> + 'SEC_TAX_DESC NVARCHAR(18) NULL,'
> + 'SEC_STRIKE_PRICE_FI FLOAT NULL,'
> + 'SEC_STRIKE_PRICE FLOAT NULL,'
> + 'SEC_UNDERLYING_CUSIP NVARCHAR(9) NULL,'
> + 'SEC_EXPIRATION_DATE DATETIME NULL,'
> + 'SEC_PUT_CALL_IND NVARCHAR(1) NULL,'
> + 'SEC_CONTRACT_SIZE FLOAT NULL,'
> + 'SEC_DIVIDEND_FREQUENCY FLOAT NULL,'
> + 'SEC_LAST_DIVI_PER_SHARE NUMERIC(8,5) NULL,'
> + 'SEC_EX_DIVIDEND_DATE DATETIME NULL,'
> + 'SEC_DIVIDEND_PAY_DATE DATETIME NULL,'
> + 'SEC_DIVIDEND_REC_DATE DATETIME NULL,'
> + 'SEC_SPLIT_DATE DATETIME NULL,'
> + 'SEC_IMPLIED_VOLATILITY FLOAT NULL,'
> + 'SEC_DELTA FLOAT NULL,'
> + 'SEC_PAY_SPREAD FLOAT NULL,'
> + 'SEC_RECV_SPREAD FLOAT NULL,'
> + 'SEC_RECV_COUPON_FI FLOAT NULL,'
> + 'SEC_RECV_COUPON FLOAT NULL,'
> + 'SEC_RECV_COUNTRY FLOAT NULL,'
> + 'SEC_RECV_CURRENCY FLOAT NULL,'
> + 'SEC_RECV_FIRST_COUPON_DATE DATETIME NULL,'
> + 'SEC_RECV_COUPON_FREQ FLOAT NULL,'
> + 'SEC_RECV_NEXT_REFIX_DATE DATETIME NULL,'
> + 'SEC_RECV_NEXT_COUPON_DATE DATETIME NULL,'
> + 'SEC_RECV_DAY_TYPE FLOAT NULL,'
> + 'SEC_RECV_PAYMENT_FREQ FLOAT NULL,'
> + 'SEC_PAY_FIXED_RATE NVARCHAR(17) NULL,'
> + 'SEC_RECV_FIXED_RATE NVARCHAR(17) NULL,'
> + 'SEC_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'SEC_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'SEC_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'SEC_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'SEC_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'SEC_SHARES_PER_WARRANT FLOAT NULL,'
> + 'SEC_IS_SINKABLE VARCHAR(1) NULL,'
> + 'SEC_IS_CONVERTIBLE VARCHAR(1) NULL,'
> + 'SEC_INDUSTRY_SUBGROUP VARCHAR(24) NULL,'
> + 'SEC_RESET_INDEX VARCHAR(10) NULL,'
> + 'SEC_LAST_REFIX_DATE DATETIME NULL,'
> + 'SEC_FIRST_RATE_RESET_DATE DATETIME NULL,'
> + 'SEC_FIRST_PAYMENT_RESET_DATE DATETIME NULL,'
> + 'SEC_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'SEC_ISIN_NUMBER VARCHAR(12) NULL,'
> + 'SEC_SEDOL1_NUMBER VARCHAR(8) NULL,'
> + 'SEC_SEDOL2_NUMBER VARCHAR(8) NULL,'
> + 'SEC_CUSIP_NUMBER VARCHAR(9) NULL,'
> + 'SEC_CINS_NUMBER VARCHAR(8) NULL,'
> + 'SEC_COMMON_STK_ISIN_NUMBER VARCHAR(12) NULL,'
> + 'SEC_IS_144A_ELIGIBLE VARCHAR(4) NULL,'
> + 'SEC_IS_ZERO_COUPON VARCHAR(2) NULL,'
> + 'SEC_ROUND_LOT_SIZE NUMERIC(8,2) NULL,'
> + 'SEC_QUOTE_UNITS VARCHAR(12) NULL,'
> + 'SEC_IS_QUOTED_AS_A_PERC_OF_PAR VARCHAR(4) NULL,'
> + 'SEC_MINIMUM_PIECE NUMERIC(8,2) NULL,'
> + 'SEC_IS_OID_BOND VARCHAR(8) NULL,'
> + 'SEC_M_MKT_GUARANTOR VARCHAR(14) NULL,'
> + 'SEC_GUARANTOR VARCHAR(30) NULL,'
> + 'SEC_MUNI_REMARKETING_AGENT VARCHAR(8) NULL,'
> + 'SEC_MUNI_ALT_MIN_TAX VARCHAR(8) NULL,'
> + 'SEC_PUT_NOTIFICATION_MIN_DAYS NUMERIC(8,2) NULL,'
> + 'SEC_DAY_COUNT_DESC VARCHAR(20) NULL,'
> + 'SEC_CALCULATION_TYPE_DESC VARCHAR(8) NULL,'
> + 'SEC_MM_PROGRAM_TYPE VARCHAR(8) NULL,'
> + 'SEC_COLLATERAL_TYPE VARCHAR(28) NULL,'
> + 'SEC_BID_PRICE_DEC NUMERIC(8,2) NULL,'
> + 'SEC_LAST_UPDATE_DATETIME VARCHAR(10) NULL,'
> + 'SEC_INDUSTRY_SECTOR VARCHAR(24) NULL,'
> + 'SEC_INDUSTRY_GROUP VARCHAR(30) NULL,'
> + 'SEC_GICS_INDUSTRY_GROUP NUMERIC(7,2) NULL,'
> + 'SEC_GICS_INDUSTRY_GROUP_NAME VARCHAR(30) NULL,'
> + 'SEC_BLOOMBERG_SUBFLAG NUMERIC(4,0) NULL,'
> + 'SEC_COUNTRY_ISO_CODE VARCHAR(3) NULL,'
> + 'SEC_MAIN_ISO VARCHAR(3) NULL,'
> + 'SEC_QUOTE_LOT_SIZE NUMERIC(11,2) NULL,'
> + 'SEC_SECURITY_DESCRIPTION VARCHAR(15) NULL,'
> + 'SEC_SECURITY_TYPE_2 VARCHAR(28) NULL,'
> + 'SEC_SECTOR VARCHAR(10) NULL,'
> + 'SEC_SHORT_NAME VARCHAR(18) NULL,'
> + 'SEC_SIC_CODE VARCHAR(4) NULL,'
> + 'SEC_MTGE_IS_AGENCY_BACKED VARCHAR(4) NULL,'
> + 'SEC_DTC_ELIGIBLE VARCHAR(4) NULL,'
> + 'SEC_REDEMPTION_VALUE NUMERIC(16,6) NULL,'
> + 'SEC_IS_DEFAULTED VARCHAR(4) NULL,'
> + 'SEC_MTGE_PAYMENT_DELAY VARCHAR(8) NULL,'
> + 'SEC_MTGE_ORIGINAL_AMOUNT VARCHAR(17)NULL,'
> + 'SEC_CONVERSION_PRICE NUMERIC(16,6) NULL,'
> + 'SEC_CONVERSION_RATIO NUMERIC(16,4) NULL,'
> + 'SEC_CONVERTIBLE_START_DATE DATETIME NULL,'
> + 'SEC_CONVERTIBLE_UNTIL DATETIME NULL,'
> + 'SEC_FIXD_EX_RTE_CONVERTIBLES NUMERIC(12,6) NULL,'
> + 'SEC_MARKET_SECTOR_DESCRIPTION VARCHAR(6) NULL,'
> + 'SEC_SECURITY_TYPE VARCHAR(28) NULL,'
> + 'SEC_SIC_NAME VARCHAR(14) NULL,'
> + 'SEC_MTY_OR_REFUND_TYPE VARCHAR(18) NULL,'
> + 'SEC_MID_YLD2WORST_CONVENTION NUMERIC(14,6) NULL,'
> + 'SEC_MTGE_WAL_IN_YEARS_TO_CALL NUMERIC(8,4) NULL,'
> + 'SEC_MID_OAS_EFFECTIVE_DURATION NUMERIC(8,4) NULL,'
> + 'SEC_MID_OAS_CONVEXITY NUMERIC(8,4) NULL,'
> + 'SEC_MID_MODIFIED_DURATION NUMERIC(8,4) NULL,'
> + 'SEC_ISSUE_DATE DATETIME NULL,'
> + 'SEC_PREPAYMENT_TYPE NUMERIC(2, 0) NULL,'
> + 'SEC_PREPAYMENT_SPEED NUMERIC(8, 3) NULL,'
> + 'SEC_MTGE_GENERIC_TICKER VARCHAR(8) NULL,'
> + 'PORTIA_SECURITY VARCHAR(15) NULL,'
> + 'PORTIA_DESCRIPTION_1 VARCHAR(39) NULL,'
> + 'PORTIA_DESCRIPTION_2 VARCHAR(39) NULL,'
> + 'PORTIA_SECURITY_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_CASH_BALANCE VARCHAR(15) NULL,'
> + 'PORTIA_CUSIP_ISIN VARCHAR(12) NULL,'
> + 'PORTIA_PRICE_SYMBOL VARCHAR(15) NULL,'
> + 'PORTIA_STATE VARCHAR(15) NULL,'
> + 'PORTIA_COUNTRY VARCHAR(15) NULL,'
> + 'PORTIA_EXCHANGE VARCHAR(15) NULL,'
> + 'PORTIA_CURRENCY VARCHAR(15) NULL,'
> + 'PORTIA_MOODY_RATING VARCHAR(15) NULL,'
> + 'PORTIA_SP_RATING VARCHAR(15) NULL,'
> + 'PORTIA_OTHER_RATING VARCHAR(15) NULL,'
> + 'PORTIA_COUPON_RATE FLOAT NULL,'
> + 'PORTIA_MATURITY_DATE DATETIME NULL,'
> + 'PORTIA_MATURITY_PRICE FLOAT NULL,'
> + 'PORTIA_ISSUE_DATE DATETIME NULL,'
> + 'PORTIA_ISSUE_PRICE FLOAT NULL,'
> + 'PORTIA_TAX_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_DATED_DATE DATETIME NULL,'
> + 'PORTIA_ODD_FIRST_CPN DATETIME NULL,'
> + 'PORTIA_ODD_LAST_CPN DATETIME NULL,'
> + 'PORTIA_POOL_NUMBER VARCHAR(8) NULL,'
> + 'PORTIA_SEC_IDENTIFIER VARCHAR(15) NULL,'
> + 'PORTIA_TRADE_STATUS VARCHAR(10) default ''Received''
> NULL,'
> + 'PORTIA_TRADE_STATUS_MESSAGE VARCHAR(255) NULL,'
> + 'PORTIA_DST_PMT_FREQ VARCHAR(15) NULL,'
> + 'PORTIA_DST_PATH VARCHAR(15) NULL,'
> + 'PORTIA_DST_PRICING_SOURCE VARCHAR(15) NULL,'
> + 'PORTIA_DTC_ELIGIBLE VARCHAR(40) NULL,'
> + 'PORTIA_INTL_TAXSTATUS VARCHAR(40) NULL,'
> + 'PORTIA_INTL_INCOMETYPE VARCHAR(40) NULL,'
> + 'PORTIA_SOURCE_CATEGORY VARCHAR(15) NULL,'
> + 'PORTIA_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
> + 'PORTIA_SETTLE_LOCATION VARCHAR(15) NULL,'
> + 'PORTIA_SHARES_OUTSTANDING FLOAT NULL,'
> + 'PORTIA_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
> + 'PORTIA_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'PORTIA_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'PORTIA_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'PORTIA_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'PORTIA_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'PORTIA_SHARES_PER_WARRANT FLOAT NULL,'
> + 'PORTIA_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'PORTIA_ST_SP_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ST_MOODY_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ST_OTHER_RATING VARCHAR(15) NULL,'
> + 'PORTIA_ISSUER VARCHAR(15) NULL,'
> + 'PORTIA_DATA_SOURCE VARCHAR(15) NULL,'
> + 'PORTIA_SIC_CODE VARCHAR(5) NULL,'
> + 'PORTIA_INDUSTRY VARCHAR(15) NULL,'
> + 'PORTIA_PREPAY_TABLE VARCHAR(15) NULL,'
> + 'PORTIA_PREPAY_RATE NUMERIC(8, 3) NULL,' --
> FLOAT in Portia
> + 'PORTIA_CASH_FLOW_SRC VARCHAR(15) NULL,'
> + 'PORTIA_CONV_SECURITY VARCHAR(15) NULL,'
> + 'PORTIA_CONV_PRICE NUMERIC(16,6)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_CONV_RATIO NUMERIC(16,4)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_CONV_START_DATE DATETIME NULL,'
> + 'PORTIA_CONV_END_DATE DATETIME NULL,'
> + 'PORTIA_CONV_EXER_RATE NUMERIC(12,6)
> NULL,' -- FLOAT in Portia
> + 'PORTIA_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' --
> FLOAT in Portia
> + 'PORTIA_USR_DEF_STRING_01 VARCHAR(15) NULL,'
> + 'PORTIA_USR_DEF_DATE_02 DATETIME NULL,'
> + 'NEW_SECURITY_FLAG VARCHAR(1) default ''Y'' NOT NULL,'
> + 'FRACT_IND_RUN_STATUS VARCHAR(1) NULL,'
> + 'MAPPING_RUN_STATUS VARCHAR(1) NULL,'
> + 'LINE_NUMBER int NULL,'
> + 'P2_SECURITY VARCHAR(15) NULL,'
> + 'P2_DESCRIPTION_1 VARCHAR(39) NULL,'
> + 'P2_DESCRIPTION_2 VARCHAR(39) NULL,'
> + 'P2_SECURITY_TYPE VARCHAR(15) NULL,'
> + 'P2_CASH_BALANCE VARCHAR(15) NULL,'
> + 'P2_CUSIP_ISIN VARCHAR(12) NULL,'
> + 'P2_PRICE_SYMBOL VARCHAR(15) NULL,'
> + 'P2_STATE VARCHAR(15) NULL,'
> + 'P2_COUNTRY VARCHAR(15) NULL,'
> + 'P2_EXCHANGE VARCHAR(15) NULL,'
> + 'P2_CURRENCY VARCHAR(15) NULL,'
> + 'P2_MOODY_RATING VARCHAR(15) NULL,'
> + 'P2_SP_RATING VARCHAR(15) NULL,'
> + 'P2_OTHER_RATING VARCHAR(15) NULL,'
> + 'P2_COUPON_RATE FLOAT NULL,'
> + 'P2_MATURITY_DATE DATETIME NULL,'
> + 'P2_MATURITY_PRICE FLOAT NULL,'
> + 'P2_ISSUE_DATE DATETIME NULL,'
> + 'P2_ISSUE_PRICE FLOAT NULL,'
> + 'P2_TAX_TYPE VARCHAR(15) NULL,'
> + 'P2_DATED_DATE DATETIME NULL,'
> + 'P2_ODD_FIRST_CPN DATETIME NULL,'
> + 'P2_ODD_LAST_CPN DATETIME NULL,'
> + 'P2_POOL_NUMBER VARCHAR(8) NULL,'
> + 'P2_SEC_IDENTIFIER VARCHAR(15) NULL,'
> + 'P2_DST_PMT_FREQ VARCHAR(15) NULL,'
> + 'P2_DST_PATH VARCHAR(15) NULL,'
> + 'P2_DST_PRICING_SOURCE VARCHAR(15) NULL,'
> + 'P2_DTC_ELIGIBLE VARCHAR(40) NULL,'
> + 'P2_INTL_TAXSTATUS VARCHAR(40) NULL,'
> + 'P2_INTL_INCOMETYPE VARCHAR(40) NULL,'
> + 'P2_PAYMENT_DELAY NUMERIC(3, 0) NULL,'
> + 'P2_SETTLE_LOCATION VARCHAR(15) NULL,'
> + 'P2_SHARES_OUTSTANDING FLOAT NULL,'
> + 'P2_SWIFT_SEC_TYPE VARCHAR(15) NULL,'
> + 'P2_WARRANT_EXPIRE_DATE DATETIME NULL,'
> + 'P2_WARRANT_UNDERLY VARCHAR(10) NULL,'
> + 'P2_WARRANT_EXERCISE_PRICE FLOAT NULL,'
> + 'P2_WARRANT_EXERCISE_DATE DATETIME NULL,'
> + 'P2_WARRANT_ISSUE_DATE DATETIME NULL,'
> + 'P2_SHARES_PER_WARRANT FLOAT NULL,'
> + 'P2_MUNI_PURPOSE VARCHAR(24) NULL,'
> + 'P2_ST_SP_RATING VARCHAR(15) NULL,'
> + 'P2_ST_MOODY_RATING VARCHAR(15) NULL,'
> + 'P2_ST_OTHER_RATING VARCHAR(15) NULL,'
> + 'P2_ISSUER VARCHAR(15) NULL,'
> + 'P2_DATA_SOURCE VARCHAR(15) NULL,'
> + 'P2_SIC_CODE VARCHAR(5) NULL,'
> + 'P2_INDUSTRY VARCHAR(15) NULL,'
> + 'P2_PREPAY_TABLE VARCHAR(15) NULL,'
> + 'P2_PREPAY_RATE NUMERIC(8, 3) NULL,' --
> FLOAT in Portia
> + 'P2_CASH_FLOW_SRC VARCHAR(15) NULL,'
> + 'P2_CONV_SECURITY VARCHAR(15) NULL,'
> + 'P2_CONV_PRICE NUMERIC(16,6)
> NULL,' -- FLOAT in Portia
> + 'P2_CONV_RATIO NUMERIC(16,4)
> NULL,' -- FLOAT in Portia
> + 'P2_CONV_START_DATE DATETIME NULL,'
> + 'P2_CONV_END_DATE DATETIME NULL,'
> + 'P2_CONV_EXER_RATE NUMERIC(12,6) NULL,' --
> FLOAT in Portia
> + 'P2_USR_DEF_TABLE_01 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_04 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_06 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_08 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_09 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_10 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_TABLE_12 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_NUMBER_05 NUMERIC(14,6) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_06 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_07 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_08 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_10 NUMERIC(20,2) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_11 NUMERIC(8,4) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_NUMBER_12 NUMERIC(8,0) NULL,' -- FLOAT in
> Portia
> + 'P2_USR_DEF_STRING_01 VARCHAR(15) NULL,'
> + 'P2_USR_DEF_DATE_02 DATETIME NULL,'
> + 'CONSTRAINT pk_sec_id PRIMARY KEY NONCLUSTERED
> (SEC_ID))' + @.LockClause
> )
> GO
>
> CREATE NONCLUSTERED INDEX idx_new_sec_flag ON
> dbo.PORTIA_BLOOMBERG_SECURITIES(NEW_SECURITY_FLAG)
> GO
> any help would be very much appreciated
> thanks
>