Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
 Posted - 2004-06-14 : 11:37:03
|
I'm getting a GPF with EM when I create a certain kind of DTS package and I was wondering if someone could give it a try and see if they can duplicate what I'm getting.First, create the following table:CREATE TABLE [dbo].[T1] ( [LOAN_NO] [int] NOT NULL , [PCR_DATE] [char] (10) NULL , [SERV_ID] [varchar] (1) NOT NULL , [INV_ID] [varchar] (1) NOT NULL , [SUBSID_NO] [varchar] (1) NOT NULL , [BILL_ADDR1] [varchar] (1) NOT NULL , [BILL_ADDR2] [varchar] (1) NOT NULL , [BILL_CITY] [varchar] (1) NOT NULL , [BILL_STATE] [varchar] (1) NOT NULL , [BILL_CNTY] [varchar] (1) NOT NULL , [BILL_ZIP] [varchar] (1) NOT NULL , [PROP_ADDR1] [varchar] (40) NULL , [PROP_ADDR2] [varchar] (20) NULL , [PROP_CITY] [varchar] (1) NOT NULL , [PROP_STATE] [varchar] (255) NULL , [PROP_CNTY] [varchar] (20) NULL , [PROP_ZIP] [varchar] (10) NULL , [PROP_TYPE] [varchar] (4) NULL , [NUM_UNITS] [varchar] (1) NOT NULL , [OCCUPANCY] [varchar] (25) NULL , [SALE_PRICE] [varchar] (30) NULL , [APP_VALUE] [varchar] (30) NULL , [ORIG_DATE] [varchar] (1) NOT NULL , [MATURITY] [char] (10) NULL , [FIRST_PMT] [char] (10) NULL , [ORIG_AMT] [varchar] (30) NULL , [LTV] [varchar] (30) NULL , [PROD_TYPE] [varchar] (4) NULL , [AMORT_TERM] [varchar] (30) NULL , [LOAN_TERM] [varchar] (1) NOT NULL , [INIT_RATE] [varchar] (30) NULL , [UW_RATIO2] [varchar] (1) NOT NULL , [UW_RATIO1] [varchar] (1) NOT NULL , [LOAN_TYPE] [varchar] (4) NULL , [PURPOSE] [varchar] (4) NULL , [LIEN_TYPE] [varchar] (1) NOT NULL , [PMT_FREQ] [varchar] (1) NOT NULL , [LOAN_SRC] [varchar] (1) NOT NULL , [BROKER_CD] [varchar] (1) NOT NULL , [BUYDOWN] [varchar] (1) NOT NULL , [DOCUMENT] [varchar] (1) NOT NULL , [PMIINSCODE] [varchar] (3) NULL , [CONVERT_CD] [varchar] (1) NOT NULL , [CONVERT_DT] [varchar] (1) NOT NULL , [RECOURSE] [varchar] (1) NOT NULL , [PP_PENALTY] [varchar] (1) NOT NULL , [PP_PEN_TERM] [varchar] (1) NOT NULL , [ORIG_SCORE] [varchar] (10) NULL , [INT_ONLY] [varchar] (1) NOT NULL , [ALT_A_FLAG] [varchar] (1) NOT NULL , [NEGAM_FLAG] [varchar] (1) NOT NULL , [MAX_NEGAM] [varchar] (1) NOT NULL , [MARGIN] [varchar] (1) NOT NULL , [P_RATE_CAP] [varchar] (1) NOT NULL , [P_RATE_FLR] [varchar] (1) NOT NULL , [P_PAY_CAP] [varchar] (1) NOT NULL , [P_PAY_FLR] [varchar] (1) NOT NULL , [L_RATE_CAP] [varchar] (1) NOT NULL , [L_RATE_FLR] [varchar] (1) NOT NULL , [RATE_RESET] [varchar] (1) NOT NULL , [PAY_RESET] [varchar] (1) NOT NULL , [FIRST_RATE] [varchar] (1) NOT NULL , [FIRST_PAY] [varchar] (1) NOT NULL , [LAST_INT_P] [char] (10) NULL , [PRIN_BAL] [varchar] (30) NULL , [CURR_RATE] [varchar] (30) NULL , [TOT_PMT] [varchar] (1) NOT NULL , [SCH_PRIN] [varchar] (1) NOT NULL , [SCH_PI] [varchar] (1) NOT NULL , [DAYS_DLQ] [varchar] (1) NOT NULL , [EXCEPTION] [varchar] (1) NOT NULL , [FC_START_DT] [varchar] (1) NOT NULL , [FC_END_DT] [varchar] (1) NOT NULL , [FC_END_TYPE] [varchar] (1) NOT NULL , [REO_START_DT] [varchar] (1) NOT NULL , [REO_END_DT] [varchar] (1) NOT NULL , [BK_START_DT] [varchar] (1) NOT NULL , [BK_END_DT] [varchar] (1) NOT NULL , [BK_TYPE] [varchar] (1) NOT NULL , [FOREBEARANCE] [varchar] (1) NOT NULL , [PAYOFF_DT] [varchar] (2) NOT NULL , [PAYOFF_RSN] [varchar] (1) NOT NULL )GO Now create a new DTS package. Create a SQL Server connection and point it the database where the table was created. Create a flat file destination and accept all the default properties. Create a transformation from SQL Server to the flat file. In the transformation source, type SELECT * FROM T1. When you click on the destination tab, a list of columns will pop up. Click Execute to create the "columns" in the destination text file. At this point I don't actually see any columns listed in the destination tab. If I click on Define Columns, Enterprise Manager GPF's. Can someone reproduce this for me and tell me I'm not crazy? I'm on SQL 2000 SP3 without any hotfixes.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-14 : 11:43:45
|
| I did as you suggested, but everything seemed Ok. SQL2000 SP3.Raymond |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-06-14 : 11:44:56
|
| Did you see a list of columns in the Destination tab?===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-14 : 11:45:55
|
| Yep, and could manipulate the transformations as well. I could even execute the package.Raymond |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-14 : 12:02:44
|
| I reproduced the error. 2000/Dev edition/sp3/named instance running on xp.Incidentally, going back in and pressing "define columns" caused the dts program to hourglass/white screen.-------Moo. :) |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-06-14 : 14:54:45
|
| Hmmm. I'm on XP & DEV edition too and using a named instance. Raymond, I'm curious what edition you were using and whether you were using a named instance.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-14 : 15:02:06
|
| Nope no problems here win2k sql 2k sp3Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-14 : 15:04:02
|
quote:
Originally posted by mr_mist
Incidentally, going back in and pressing "define columns" caused the dts program to hourglass/white screen.
ANOTHER undocumented feature!Look at all the bonus stuff you get...that's what I call value addedBrett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 15:57:39
|
| I get the same - it happens with the table as the source too rather than a query.I'm using the default instance.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 16:14:57
|
| I have a feeling that this is ok but it fails if you add the commented out columnand it's the number of columns not the size.CREATE TABLE [dbo].[T2] ( [LOAN_NO] [int] NOT NULL , [PCR_DATE] [char] (10) NULL , [SERV_ID] [varchar] (1) NOT NULL , [INV_ID] [varchar] (1) NOT NULL , [SUBSID_NO] [varchar] (1) NOT NULL , [BILL_ADDR1] [varchar] (1) NOT NULL , [BILL_ADDR2] [varchar] (1) NOT NULL , [BILL_CITY] [varchar] (1) NOT NULL , [BILL_STATE] [varchar] (1) NOT NULL , [BILL_CNTY] [varchar] (1) NOT NULL , [BILL_ZIP] [varchar] (1) NOT NULL , [PROP_ADDR1] [varchar] (40) NULL , [PROP_ADDR2] [varchar] (20) NULL , [PROP_CITY] [varchar] (1) NOT NULL , [PROP_STATE] [varchar] (255) NULL , [PROP_CNTY] [varchar] (20) NULL , [PROP_ZIP] [varchar] (10) NULL , [PROP_TYPE] [varchar] (4) NULL , [NUM_UNITS] [varchar] (1) NOT NULL , [OCCUPANCY] [varchar] (25) NULL , [SALE_PRICE] [varchar] (30) NULL , [APP_VALUE] [varchar] (30) NULL , [ORIG_DATE] [varchar] (1) NOT NULL , [MATURITY] [char] (10) NULL , [FIRST_PMT] [char] (10) NULL ,-- [ORIG_AMT] [varchar] (30) NULL , [PAYOFF_RSN] [varchar] (1) NOT NULL )GO==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 16:21:32
|
| My word.It's ORIG_AMT - it doesn't like the name.If you change it to ORIGAMT it's fine.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-06-14 : 16:52:23
|
| Nope. I removed the ORIG_AMT column and it still generated an error. My only guess was that it's something to do with the number of columns in the result set. Or the total number of characters in the names of the columns. Weird.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 17:16:15
|
| Yep [FIRST_PMT] [char] (10) NULL , [ORIGAMT] [varchar] (30) NULL , [PAYOFF_RSN] [varchar] (1) NOT NULL works but if you change ORIGAMT to ORIG_AMT or ORIG1AMT it fails.Can't be bothered to try more permutations - it is quite a small table if you omit the other columns so I'm a bit surprised.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-06-14 : 19:08:20
|
| I think you may have some environmental issue. I have tried this on 2 environments here both default instances one Windows 2k, one Windows XP both on SP3 and no problems. One is personal edition and the other standard edition. Maybe it has something to do with the dev edition. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-14 : 19:32:29
|
| Yep I'm xp pro and dev think sp3a.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 16:22:12
|
| Awesome! I've been having the same problem in production. I'd almost given up modifying a DTS package. I was ready to put a client machine out there that had sp2 installed. I've got to see when on the schedule this patch is going to get installed.Tara |
|
|
|
|
|
|
|
|
Problem with DTS