Configure Data Adapter Wizard Failing

  • Thread starter Thread starter Jeff Dillon
  • Start date Start date
J

Jeff Dillon

The Configure Data Adapter wizard works on 20 other tables in my SQL2000
database, but fails on the following with an error "There were errors
configuring the data adapter"

Granted it's an oversize table, but what are the limits? Also, can I "view"
the errors configuring the adapter?

I've tried in an VB.NET and ASP.NET app.

I can reproduce by building this table in the Pubs database, creating a SQL
connection, then going through the wizard and using the SQL statement
"Select * from NHTSAPatients". It also fails if I explicitly state each
fieldname in the SQL statement.

CREATE TABLE [dbo].[NHTSAPatients] (
[PatientID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[AID] [int] NULL ,
[UID] [int] NULL ,
[LastName] [varchar] (200) NULL ,
[FirstName] [varchar] (100) NULL ,
[MI] [varchar] (1) NULL ,
[HomeAddress] [varchar] (1000) NULL ,
[HomeCity] [varchar] (100) NULL ,
[HomeState] [varchar] (2) NULL ,
[HomeZip] [varchar] (9) NULL ,
[HomeCountry] [varchar] (10) NULL ,
[SSN] [varchar] (9) NULL ,
[DriversLicenseNumber] [varchar] (50) NULL ,
[DriversLicenseState] [varchar] (2) NULL ,
[Gender] [varchar] (1) NULL ,
[Pregnancy] [varchar] (1) NULL ,
[Race] [varchar] (1) NULL ,
[Subrace] [varchar] (200) NULL ,
[Ethnicity] [varchar] (1) NULL ,
[DOB] [datetime] NULL ,
[Age] [varchar] (3) NULL ,
[AgeUnits] [varchar] (1) NULL ,
[PrimaryPhone] [varchar] (10) NULL ,
[SecondaryPhone] [varchar] (10) NULL ,
[RGLastName] [varchar] (200) NULL ,
[RGFirstName] [varchar] (100) NULL ,
[RGMI] [varchar] (1) NULL ,
[RGAddress] [varchar] (1000) NULL ,
[RGCity] [varchar] (100) NULL ,
[RGState] [varchar] (2) NULL ,
[RGZip] [varchar] (9) NULL ,
[RGPhone] [varchar] (10) NULL ,
[RGRelationship] [varchar] (1) NULL ,
[Employer] [varchar] (200) NULL ,
[EmployerAddress] [varchar] (1000) NULL ,
[EmployerCity] [varchar] (100) NULL ,
[EmployerState] [varchar] (2) NULL ,
[EmployerZip] [varchar] (9) NULL ,
[EmployerPhone] [varchar] (10) NULL ,
[PrimaryMethodOfPayment] [varchar] (1) NULL ,
[CertificateOfMedicalNecessity] [varchar] (1) NULL ,
[ResponseUrgency] [varchar] (1) NULL ,
[WorkRelated] [varchar] (1) NULL ,
[PatientsOccupationalIndustry] [varchar] (2) NULL ,
[PatientsOccupation] [varchar] (2) NULL ,
[MassCasualtyIncident] [bit] NULL ,
[PriorAid] [varchar] (1000) NULL ,
[PriorAidPerformedBy] [varchar] (20) NULL ,
[OutcomeOfPriorAid] [varchar] (1) NULL ,
[ChiefComplaintNarrative] [text] NULL ,
[ComplaintAnatomicLocation] [varchar] (50) NULL ,
[ComplaintOrganAgency] [varchar] (2) NULL ,
[PrimarySymptom] [varchar] (2) NULL ,
[OtherAssociatedSymptoms] [varchar] (100) NULL ,
[ProvidersPrimaryImpression] [varchar] (6) NULL ,
[ProvidersSecondaryImpression] [varchar] (6) NULL ,
[BarriersToPatientCare] [varchar] (50) NULL ,
[PatientsPrimaryPractitioner] [varchar] (200) NULL ,
[AdvancedDirectives] [varchar] (50) NULL ,
[MedicationAllergies] [varchar] (1000) NULL ,
[MedicalHistoryObtainedFrom] [varchar] (1) NULL ,
[AlcoholDrugUseIndicators] [varchar] (50) NULL ,
[EstimatedBodyWeight] [varchar] (53) NULL ,
[CardiacArrest] [bit] NULL ,
[CardiacArrestEtiology] [varchar] (1) NULL ,
[ResuscitationAttempted] [varchar] (50) NULL ,
[ArrestWitnessedBy] [varchar] (1) NULL ,
[FirstMonitoredRhythm] [varchar] (1) NULL ,
[AnyReturnOfSpontaneousCirculation] [varchar] (10) NULL ,
[ETAPriorToEMSArrival] [varchar] (2) NULL ,
[DTResuscitationDiscontinued] [datetime] NULL ,
[ReasonCPRDiscontinued] [varchar] (50) NULL ,
[RhythmOnArrivalAtDestination] [varchar] (100) NULL ,
[TraumaInjuryPresent] [bit] NULL ,
[CauseOfInjury] [varchar] (6) NULL ,
[MechanismOfInjury] [varchar] (10) NULL ,
[PTSSize] [varchar] (53) NULL ,
[PTSAirway] [varchar] (53) NULL ,
[PTSSBP] [varchar] (53) NULL ,
[PTSCNS] [varchar] (53) NULL ,
[PTSSkeletalInjury] [varchar] (53) NULL ,
[PTSOpenWounds] [varchar] (53) NULL ,
[PTScore] [varchar] (53) NULL ,
[GCSEye] [varchar] (53) NULL ,
[GCSVerbal] [varchar] (53) NULL ,
[GCSMotor] [varchar] (53) NULL ,
[GCSQualifier] [varchar] (10) NULL ,
[GCSScore] [float] NULL ,
[RTSRespiratory] [float] NULL ,
[RTSBlood] [float] NULL ,
[RTSNeurological] [float] NULL ,
[RTScore] [float] NULL ,
[VehicularInjuryIndicators] [varchar] (50) NULL ,
[AreaOfVehicleImpactedByCollision] [varchar] (50) NULL ,
[SeatRowLocationOfPatientInVehicle] [varchar] (2) NULL ,
[PositionOfPatientInSeatOfVehicle] [varchar] (2) NULL ,
[UseOfOccupantSafetyEquipment] [varchar] (50) NULL ,
[AirbagDeployment] [varchar] (50) NULL ,
[DestinationTransferredToName] [varchar] (500) NULL ,
[DestinationTransferredToCode] [varchar] (500) NULL ,
[DestinationZipCode] [varchar] (25) NULL ,
[IncidentPatientDisposition] [varchar] (2) NULL ,
[TransportModeFromScene] [varchar] (1) NULL ,
[ReasonForChoosingDestination] [varchar] (2) NULL ,
[TypeOfDestination] [varchar] (2) NULL ,
[EmergencyDepartmentDisposition] [varchar] (1) NULL ,
[HospitalDisposition] [varchar] (1) NULL ,
[DestinationMedicalRecordNumber] [varchar] (100) NULL ,
[LawEnforcementCrashReportNumber] [varchar] (100) NULL ,
[FireIncidentReportNumber] [varchar] (100) NULL ,
[PatientIDBandTagNumber] [varchar] (100) NULL ,
[Narrative] [text] NULL ,
[Complete] [bit] NULL ,
[SessionID] [varchar] (50) NULL ,
[Signature] [varchar] (50) NULL ,
[PickupLocationCode] [varchar] (50) NULL ,
[ResidencyStatusCode] [varchar] (50) NULL ,
[MBIExported] [int] NULL ,
[PreexistingConditions] [varchar] (500) NULL
)
 
I believe there is a limitation on the number of columns the wizard can work
with that are in a single table.
You have 2 options:
1) Split up the columns into 2 tables (that should be enough), where there
is a 1 to 1 between the tables, and put the data into separate datatables in
your app
2) Stop using the wizard since it is a piece of junk anyway and write code
by hand. That doesn't mean duplicating what the wizard does, but write your
logic in a generic manner to generate the correct sql statements.


Jeff Dillon said:
The Configure Data Adapter wizard works on 20 other tables in my SQL2000
database, but fails on the following with an error "There were errors
configuring the data adapter"

Granted it's an oversize table, but what are the limits? Also, can I "view"
the errors configuring the adapter?

I've tried in an VB.NET and ASP.NET app.

I can reproduce by building this table in the Pubs database, creating a SQL
connection, then going through the wizard and using the SQL statement
"Select * from NHTSAPatients". It also fails if I explicitly state each
fieldname in the SQL statement.

CREATE TABLE [dbo].[NHTSAPatients] (
[PatientID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[AID] [int] NULL ,
[UID] [int] NULL ,
[LastName] [varchar] (200) NULL ,
[FirstName] [varchar] (100) NULL ,
[MI] [varchar] (1) NULL ,
[HomeAddress] [varchar] (1000) NULL ,
[HomeCity] [varchar] (100) NULL ,
[HomeState] [varchar] (2) NULL ,
[HomeZip] [varchar] (9) NULL ,
[HomeCountry] [varchar] (10) NULL ,
[SSN] [varchar] (9) NULL ,
[DriversLicenseNumber] [varchar] (50) NULL ,
[DriversLicenseState] [varchar] (2) NULL ,
[Gender] [varchar] (1) NULL ,
[Pregnancy] [varchar] (1) NULL ,
[Race] [varchar] (1) NULL ,
[Subrace] [varchar] (200) NULL ,
[Ethnicity] [varchar] (1) NULL ,
[DOB] [datetime] NULL ,
[Age] [varchar] (3) NULL ,
[AgeUnits] [varchar] (1) NULL ,
[PrimaryPhone] [varchar] (10) NULL ,
[SecondaryPhone] [varchar] (10) NULL ,
[RGLastName] [varchar] (200) NULL ,
[RGFirstName] [varchar] (100) NULL ,
[RGMI] [varchar] (1) NULL ,
[RGAddress] [varchar] (1000) NULL ,
[RGCity] [varchar] (100) NULL ,
[RGState] [varchar] (2) NULL ,
[RGZip] [varchar] (9) NULL ,
[RGPhone] [varchar] (10) NULL ,
[RGRelationship] [varchar] (1) NULL ,
[Employer] [varchar] (200) NULL ,
[EmployerAddress] [varchar] (1000) NULL ,
[EmployerCity] [varchar] (100) NULL ,
[EmployerState] [varchar] (2) NULL ,
[EmployerZip] [varchar] (9) NULL ,
[EmployerPhone] [varchar] (10) NULL ,
[PrimaryMethodOfPayment] [varchar] (1) NULL ,
[CertificateOfMedicalNecessity] [varchar] (1) NULL ,
[ResponseUrgency] [varchar] (1) NULL ,
[WorkRelated] [varchar] (1) NULL ,
[PatientsOccupationalIndustry] [varchar] (2) NULL ,
[PatientsOccupation] [varchar] (2) NULL ,
[MassCasualtyIncident] [bit] NULL ,
[PriorAid] [varchar] (1000) NULL ,
[PriorAidPerformedBy] [varchar] (20) NULL ,
[OutcomeOfPriorAid] [varchar] (1) NULL ,
[ChiefComplaintNarrative] [text] NULL ,
[ComplaintAnatomicLocation] [varchar] (50) NULL ,
[ComplaintOrganAgency] [varchar] (2) NULL ,
[PrimarySymptom] [varchar] (2) NULL ,
[OtherAssociatedSymptoms] [varchar] (100) NULL ,
[ProvidersPrimaryImpression] [varchar] (6) NULL ,
[ProvidersSecondaryImpression] [varchar] (6) NULL ,
[BarriersToPatientCare] [varchar] (50) NULL ,
[PatientsPrimaryPractitioner] [varchar] (200) NULL ,
[AdvancedDirectives] [varchar] (50) NULL ,
[MedicationAllergies] [varchar] (1000) NULL ,
[MedicalHistoryObtainedFrom] [varchar] (1) NULL ,
[AlcoholDrugUseIndicators] [varchar] (50) NULL ,
[EstimatedBodyWeight] [varchar] (53) NULL ,
[CardiacArrest] [bit] NULL ,
[CardiacArrestEtiology] [varchar] (1) NULL ,
[ResuscitationAttempted] [varchar] (50) NULL ,
[ArrestWitnessedBy] [varchar] (1) NULL ,
[FirstMonitoredRhythm] [varchar] (1) NULL ,
[AnyReturnOfSpontaneousCirculation] [varchar] (10) NULL ,
[ETAPriorToEMSArrival] [varchar] (2) NULL ,
[DTResuscitationDiscontinued] [datetime] NULL ,
[ReasonCPRDiscontinued] [varchar] (50) NULL ,
[RhythmOnArrivalAtDestination] [varchar] (100) NULL ,
[TraumaInjuryPresent] [bit] NULL ,
[CauseOfInjury] [varchar] (6) NULL ,
[MechanismOfInjury] [varchar] (10) NULL ,
[PTSSize] [varchar] (53) NULL ,
[PTSAirway] [varchar] (53) NULL ,
[PTSSBP] [varchar] (53) NULL ,
[PTSCNS] [varchar] (53) NULL ,
[PTSSkeletalInjury] [varchar] (53) NULL ,
[PTSOpenWounds] [varchar] (53) NULL ,
[PTScore] [varchar] (53) NULL ,
[GCSEye] [varchar] (53) NULL ,
[GCSVerbal] [varchar] (53) NULL ,
[GCSMotor] [varchar] (53) NULL ,
[GCSQualifier] [varchar] (10) NULL ,
[GCSScore] [float] NULL ,
[RTSRespiratory] [float] NULL ,
[RTSBlood] [float] NULL ,
[RTSNeurological] [float] NULL ,
[RTScore] [float] NULL ,
[VehicularInjuryIndicators] [varchar] (50) NULL ,
[AreaOfVehicleImpactedByCollision] [varchar] (50) NULL ,
[SeatRowLocationOfPatientInVehicle] [varchar] (2) NULL ,
[PositionOfPatientInSeatOfVehicle] [varchar] (2) NULL ,
[UseOfOccupantSafetyEquipment] [varchar] (50) NULL ,
[AirbagDeployment] [varchar] (50) NULL ,
[DestinationTransferredToName] [varchar] (500) NULL ,
[DestinationTransferredToCode] [varchar] (500) NULL ,
[DestinationZipCode] [varchar] (25) NULL ,
[IncidentPatientDisposition] [varchar] (2) NULL ,
[TransportModeFromScene] [varchar] (1) NULL ,
[ReasonForChoosingDestination] [varchar] (2) NULL ,
[TypeOfDestination] [varchar] (2) NULL ,
[EmergencyDepartmentDisposition] [varchar] (1) NULL ,
[HospitalDisposition] [varchar] (1) NULL ,
[DestinationMedicalRecordNumber] [varchar] (100) NULL ,
[LawEnforcementCrashReportNumber] [varchar] (100) NULL ,
[FireIncidentReportNumber] [varchar] (100) NULL ,
[PatientIDBandTagNumber] [varchar] (100) NULL ,
[Narrative] [text] NULL ,
[Complete] [bit] NULL ,
[SessionID] [varchar] (50) NULL ,
[Signature] [varchar] (50) NULL ,
[PickupLocationCode] [varchar] (50) NULL ,
[ResidencyStatusCode] [varchar] (50) NULL ,
[MBIExported] [int] NULL ,
[PreexistingConditions] [varchar] (500) NULL
)
Click to expand...
 
Back
Top