Showing posts with label exception. Show all posts
Showing posts with label exception. Show all posts

Monday, March 19, 2012

Access Violation When Selecting Data

Hi,

I have suddenly developed a problem when trying to select from a table. Even a standard select * from generates a SQL Dump exception handler. The table is below:

CREATE TABLE [Stagetb_GIDeptGroup] (
[f_GIImportID] [int] IDENTITY (1, 1) NOT NULL ,
[f_GIAction] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_GIAction] DEFAULT (0),
[f_ImportBatchID] [int] NULL ,
[f_VisualDeptGroupID] [int] NULL ,
[f_DeptGroupName] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[f_DeptGroupTarget] [d_Percentage] NULL ,
[f_ExternalDeptGroupID] [int] NULL ,
[f_StatusID] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_StatusID] DEFAULT (0),
[f_StatusDetailID] [int] NOT NULL CONSTRAINT [DF_stagetb_GIDeptGroup_f_StatusDetailID] DEFAULT (0),
CONSTRAINT [PK_stagetb_GIDeptGroup] PRIMARY KEY CLUSTERED
(
[f_GIImportID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

When i run a dbcc checkdb it finds nothing, if i try and connect from a remote connection i get a general network error and when connecting from the local machine i get this Dump exception error:

2005-12-06 08:40:55.28 spid53 Error: 0, Severity: 19, State: 0
2005-12-06 08:40:55.28 spid53 language_exec: Process 53 generated an access violation. SQL Server is terminating this process..
2005-12-06 08:40:57.76 spid53 Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQL00010.dmp
2005-12-06 08:40:57.76 spid53 Error: 0, Severity: 19, State: 0
2005-12-06 08:40:57.76 spid53 SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 12/06/05 08:40:57 spid 53
*
* Exception Address = 00425E61
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 70 bytes -
* select * from stagetb_GIDeptGroup
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF 0071a000
* ntdll 77F50000 77FF6FFF 000a7000
* kernel32 77E60000 77F45FFF 000e6000
* ADVAPI32 77DD0000 77E5CFFF 0008d000
* RPCRT4 78000000 78085FFF 00086000
* USER32 77D40000 77DC5FFF 00086000
* GDI32 7E090000 7E0D0FFF 00041000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77C10000 77C62FFF 00053000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 002A0000 002AFFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 71C20000 71C6DFFF 0004e000
* wmi 76D30000 76D33FFF 00004000
* SSNETLIB 42CF0000 42D05FFF 00016000
* WSOCK32 71AD0000 71AD7FFF 00008000
* WS2_32 71AB0000 71AC4FFF 00015000
* WS2HELP 71AA0000 71AA7FFF 00008000
* SSNMPN70 410D0000 410D5FFF 00006000
* security 71F80000 71F83FFF 00004000
* SECUR32 76F90000 76F9FFFF 00010000
* crypt32 762C0000 76347FFF 00088000
* MSASN1 762A0000 762AFFFF 00010000
* VERSION 77C00000 77C06FFF 00007000
* userenv 75A70000 75B14FFF 000a5000
* mswsock 71A50000 71A8AFFF 0003b000
* DNSAPI 76F20000 76F44FFF 00025000
* winrnr 76FB0000 76FB6FFF 00007000
* WLDAP32 76F60000 76F8BFFF 0002c000
* rasadhlp 76FC0000 76FC4FFF 00005000
* wshtcpip 71A90000 71A97FFF 00008000
* SSmsLPCn 42CD0000 42CD6FFF 00007000
* ntdsapi 767A0000 767B2FFF 00013000
* msv1_0 76D10000 76D2CFFF 0001d000
* OLE32 771B0000 772D0FFF 00121000
* CLBCATQ 76FD0000 77047FFF 00078000
* OLEAUT32 77120000 771AAFFF 0008b000
* COMRes 77050000 77114FFF 000c5000
* sqloledb 01D80000 01DF2FFF 00073000
* MSDART 74C50000 74C6EFFF 0001f000
* COMCTL32 77340000 773CAFFF 0008b000
* comdlg32 763B0000 763F4FFF 00045000
* SHLWAPI 70A70000 70AD4FFF 00065000
* SHELL32 773D0000 77BC1FFF 007f2000
* MSDATL3 60E30000 60E44FFF 00015000
* comctl32 71950000 71A33FFF 000e4000
* oledb32 02020000 02084FFF 00065000
* OLEDB32R 01E00000 01E0FFFF 00010000
* xpsqlbot 42F00000 42F05FFF 00006000
* xpstar 410F0000 41133FFF 00044000
* SQLUNIRL 5C1B0000 5C1DCFFF 0002d000
* WINSPOOL 73000000 73022FFF 00023000
* SQLRESLD 42AC0000 42AC6FFF 00007000
* SQLSVC 42C40000 42C56FFF 00017000
* ODBC32 02150000 02181FFF 00032000
* odbcbcp 02190000 02195FFF 00006000
* W95SCM 41140000 4114BFFF 0000c000
* NDDEAPI 75940000 75946FFF 00007000
* odbcint 022B0000 022C5FFF 00016000
* clusapi 55560000 55570FFF 00011000
* resutils 750B0000 750C0FFF 00011000
* SQLSVC 43970000 43975FFF 00006000
* xpstar 439E0000 439EBFFF 0000c000
* DBGHELP 02420000 02432FFF 00013000
* msdbi 6BE90000 6BEABFFF 0001c000
* sqlimage 4A400000 4A40CFFF 0000d000
*
* Edi: 1ADA13F4: 00000000 1ADA1758 00000000 1ADA1400 00000001 00000000
* Esi: 00000000:
* Eax: 1ADA13E0: 009802E4 00000032 00000000 00000000 00000000 00000000
* Ebx: 1ADA13E0: 009802E4 00000032 00000000 00000000 00000000 00000000
* Ecx: 00000000:
* Edx: 1ADA1410: 00000000 6E006100 00000000 00000000 00000000 1ADA0030
* Eip: 00425E61: 7589018B 8B10FFFC F44D8BF0 A5A5A5A5 5EC38B5F 0D89645B
* Ebp: 0213DFE8: 0213E01C 004C4EB7 1ADA0020 00000000 1ADA1168 1ADC84D0
* SegCs: 0000001B:
* EFlags: 00010282: 0050004F 00300048 00300033 00560030 004F0042 004E0000
* Esp: 0213DFCC: 1ADA07F0 1ADA0020 00A0A4A0 1ADA13E0 0213E010 0092D7B0
* SegSs: 00000023:
************************************************** *****************************
------------------------
Short Stack Dump
00425E61 Module(sqlservr+00025E61)
004C4EB7 Module(sqlservr+000C4EB7)
0049DCD9 Module(sqlservr+0009DCD9)
0049DAF9 Module(sqlservr+0009DAF9)
00447078 Module(sqlservr+00047078)
004510FE Module(sqlservr+000510FE)
00415080 Module(sqlservr+00015080)
004592CE Module(sqlservr+000592CE)
005A7208 Module(sqlservr+001A7208) (SQLExit(unsigned long)+000523F6)
005A7099 Module(sqlservr+001A7099) (SQLExit(unsigned long)+00052287)
00446A83 Module(sqlservr+00046A83)
004510FE Module(sqlservr+000510FE)
00415080 Module(sqlservr+00015080)
004592CE Module(sqlservr+000592CE)
0041534F Module(sqlservr+0001534F)
00459A54 Module(sqlservr+00059A54)
004175D8 Module(sqlservr+000175D8)
410735D0 Module(UMS+000035D0) (UmsSystemUserContext::UmsSystemUserContext(class UmsScheduler *,struct UMS_SYSPARAMS *)+00000434)
4107382C Module(UMS+0000382C) (UmsScheduler::FiberEnabled(void)+000001D2)
77C37E53 Module(MSVCRT+00027E53) (_endthread+000000AA)
77E7D33B Module(kernel32+0001D33B) (RegisterWaitForInputIdle+00000043)

I am running Windows XP Professional & MSDE Service Pack 3. Any ideas anyone?Has anyone ever experienced this before with any tables? Unable to select, update, insert or delete from the table as it throws a dump exception error. But DBCC CHECKDB returns no error?

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
>

Tuesday, March 6, 2012

Access To Dts.Variables Causes Exception

Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks.

I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile

Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following:

Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User:Tongue TiedtrDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString()

Everytime this script executes I get the following errors:

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25

I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that.

Expresion property set as false also.

Any ideas? Thanks.

Right Click the script task, press F4, this will show the properties of the script task, there you will have properties called ReadOnlyVariables, ReadWriteVariables. You can set the variables name in this based on your requirement.
These variables will be available in the script, else they dont have visibility inside the script task and an error will be thrown at run time.

Thanks

Monday, February 13, 2012

Access is denied exception when loading an assembly from custom data processing extension

Hello,
I have been banging my head against this for the better part of today.
Here's the scenario. I have a custom data processing extension which
tries to make calls into another .NET assembly. As soon as this second
assembly is loaded, an "Access is denied: name_of_the_assembly"
System.Exception is thrown. I can't even catch this error as it seems
to occur on the assembly load and not on the first function call. Both
my data processing extension and second assembly have strong names and
are signed with the same key.
The rssvrpolicy.config is setup to allow FullTrust to my data
processing extension and second assembly. I have tried both URL
Membership Condtion as well as StrongName. Note that my custom data
processing extension works just fine if it doesn't call the second
assembly. Rolling everything back into a single assembly is not really
an option though.
OS: Windows Server 2003
Reporting Services SP1
IIS AppDomain Identity: NetworkService
Any ideas? Help please.
~JeffOk, I'm an idiot. It turns out that my data processing extension was
NOT signed but my second assembly was meaning that I had an untrusted
caller trying to call a trusted assembly and bomb! I went ahead and
signed everything and gave the proper StrongName membership in the
policy config and I'm good now.
I'm glad it turned out to be my problem because I couldn't figure out
for the life of me what I was doing wrong in regards to RS.
Hope this helps somebody else.
~Jeff

Thursday, February 9, 2012

Access denied on deployment

Just deployed my web app at an ISP. The very first time I hit my database, I get an exception

'SQL Server does not exist or access denied'

I'm trying to connect using an SQL account with user id and password (which work fine if I go in using SQL Query Analyser).

The connection string contains the initial catalog, user id and password.

Can anybody help please?

Thanks,
BernieI assume you checked the remote data source address too.

Did you try connecting to the remote database using the Server Explorer inside of VS.Net? If you can connect to it through there, then just drag a table from the remote data source onto some test page. Look in the VS.Net generated code in the code behind and find where it creates the connection. Copy and paste this connection string info to where you need it.|||Are you sure you're using the correct server in the connection string?

When I first started doing this, in SQL Client Network Utitlity, I gave my server an Alias - - locally, that's what I was using, and when I uploaded the pages to the server, I'd forget to change the alias, and of course, it wouldn't work, since the host has no idea what that alias is.|||Thanks for the replies guys. I did as McMurdoStation suggested.
I could 'see' the server database and all of its tables from the VS.Net SQL server , so proceeded to create an SQL Adaptor/SQL Connection datagrid etc... on a test page.

When it tried to connect, the login failed, so I assume this is the same problem.

The connection string looks like:-
value="initial catalog=ClubNoticeBoard; user id=ClubNoticeBoard;pwd=*****;packet size=4096"

The one in the test page I created looks like
this.sqlConnection1.ConnectionString = "workstation id=BERNIE;packet size=4096;user id=ClubNoticeBoard;data source=\"URL here\";persist security info=False;initial catalog=ClubNoticeBoard";

I've just read somewhere that the ASPNET account must have access, that sounds reasobale as I can access the database fine except through the aspx pages. How do I go about setting up an ASPNET account using the SQL Server Enterprise Manager.

Cheers,
Bernie|||under SQL Server explorer, expand on the name of your database, right click on 'Users' and click on 'Add New Database user'. then add ASPNET user.

HTH.|||Ok, bearing in mind that this is a shared server at an ISP, when i did what you suggested, I get the New User box up requesting a Login name and User name. The Login name is a drop down list of other logins that already exist, plus BUILTINS\Administrators.

What do I select in that login box, I can type in ASPNET but it then gives me an error saying 'login doesn't exist'.

Assuming I can eventually get a Login of ASPNET what would I put in the User name.

Cheers,
Bernie|||I think the database user is a red herring, as my local development machine does not have an ASPNET user.

Although there is an ASPNET account for the machine itself, and so I suspect that this is what I need. I have asked the ISP to set one up.

Cheers,
bernie|||you have to select <new> from the list and then it will let you add a new user.|||I tried that but it said I had to be an adminsitrator to complete the task.

Can somebody tell me what exactly needs to be setup on the box?

1. Is it a new user account i.e machinename/ASPNET?
2. Is it a new SQL Server log with name ASPNET?

If no. 1, what happens about the password, how can asp.net use it if doesn't know it?
If no. 2, would it use WIndows Authentication, or an SQL account?

I must say this all seems pretty standard stuff, I can't understand why nobody has come up with a definitive answer. Maybe the ISP i'm using don't know their ASP.NET stuff and they havn't set things up correctly.

Cheers,
Bernie|||when you install vs.net it creates a ASP.NET user account on your system. this is for internet users to login to your comp. but for the aspx pages to access your databases, you still need to add the machinename\ASPNET account inside the SQL Server.

when you select <new> user from the list, another window will come up to fill in the details for the new user.

there would be a button with "..." on it which will bring up all the user accounts on that computer. click on it and you can choose the ASPNET account from the list.
and you can use windows authentication. and dont forget to choose the database.

we usually need not set a password. you can use integrated security in your connection string. something like this :

"server=local;database=Northwind;Integrated Security=SSPI "

and i guess you need to be an admin to create a user account in SQL Server.

HTH|||Thanks, the problem was I couldn't see ASPNET in the drop down list.

All sorted now and working (up to a point), I've some other permission issues to sort out.

Thanks everybody for your help.

Bernie