| Author |
Topic |
|
Baquardie
Starting Member
8 Posts |
 Posted - 2006-01-18 : 10:31:29
|
HiI'm developing in ASP and one of my table is two big, but I want to live with it.When I try to execute a query into the query analyser, SQL send me a warning message BUT still execute the query.quote:
Warning: The table 'ProSYNCMarketingNutrition' has been created but its maximum row size (17032) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
When I try to execute the same query via ASP, the warning message is send as an error message and the query IS NOT executed. That cause me problem.Is there any way to ignore warnings in ASP?Thank you,  |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-01-18 : 10:56:48
|
i'd suggest you change the huge varchar fields into text datatype. this error will give you headaches in the future.add on error resume next...your code...on error goto 0Go with the flow & have fun! Else fight the flow  |
 |
|
|
Baquardie
Starting Member
8 Posts |
Posted - 2006-01-18 : 11:13:00
|
Hi Spirit1, Thanks for the answerI don't understand what you want me to do in my code when you type "on error resume next". If ASP send me back a warning, the wery is not executed, therefore i'm in trouble because i need to display data.The probleme is that I do not have the choice to keep all the field in the table, but half of them won't be used. So I know I will never lost data even if in theory the table need too much space. Here is the definition of the tableCREATE TABLE [dbo].[ProSYNCMarketingNutrition] ( [id] [int] IDENTITY (517, 1) NOT , [DateCreation] [datetime] , [DateFin] [datetime] , [Historique] [bit] , [Version] [bigint] , [Publier] [int] , [Valide] [int] , [CommonItemNumber] [nvarchar] (255) , [CalculationSize] [varchar] (8) , [CalculationUOM] [varchar] (2) , [ServingSize] [varchar] (8) , [ServingSizeUOM] [varchar] (2) , [EdiblePercent] [varchar] (8) , [RethermalizationIndicator] [varchar] (1) , [ServingSizeType] [varchar] (30) , [KosherClassification] [varchar] (30) , [Note] [varchar] (256) , [WeightDensity] [varchar] (8) , [CanadianEstablishmentNr] [varchar] (14) , [USAEstablishmentNr] [varchar] (14) , [WaterPercent] [varchar] (8) , [MoistureLossPercent] [varchar] (8) , [Calories] [varchar] (8) , [CaloriesUOM] [varchar] (8) , [CaloriesFromFat] [varchar] (8) , [CaloriesFromFatUOM] [varchar] (2) , [Protein] [varchar] (8) , [ProteinUOM] [varchar] (2) , [ProteinPercentDV_RDI] [varchar] (8) , [Carbohydrates] [varchar] (8) , [CarbohydratesUOM] [varchar] (2) , [CarbohydratesPercentDV_RDI] [varchar] (8) , [OtherCarbohydrates] [varchar] (8) , [OtherCarbohydratesUOM] [varchar] (2) , [OtherCarbohydratesPercentDV_RDI] [varchar] (8) , [TotalDietaryFibre] [varchar] (8) , [TotalDietaryFibreUOM] [varchar] (2) , [TotalDietaryFibrePercentDV_RDI] [varchar] (8) , [SolubleFibre] [varchar] (8) , [SolubleFibreUOM] [varchar] (2) , [SolubleFibrePercentDV_RDI] [varchar] (8) , [InsolubleFibre] [varchar] (8) , [InsolubleFibreUOM] [varchar] (2) , [InsolubleFibrePercentDV_RDI] [varchar] (8) , [TotalSugar] [varchar] (8) , [TotalSugarUOM] [varchar] (2) , [TotalSugarPercentDV_RDI] [varchar] (8) , [Starch] [varchar] (8) , [StarchUOM] [varchar] (2) , [TotalFat] [varchar] (8) , [TotalFatUOM] [varchar] (2) , [TotalFatPercentDV_RDI] [varchar] (8) , [SaturatedFat] [varchar] (8) , [SaturatedFatUOM] [varchar] (2) , [SaturatedFatPercentDV_RDI] [varchar] (8) , [PolyunsaturatedFat] [varchar] (8) , [PolyunsaturatedFatUOM] [varchar] (2) , [PolyunsaturatedFatPercentDV_RDI] [varchar] (8) , [MonounsaturatedFat] [varchar] (8) , [MonounsaturatedFatUOM] [varchar] (2) , [MonounsaturatedFatPercentDV_RDI] [varchar] (8) , [TransfattyAcids] [varchar] (8) , [TransfattyAcidsUOM] [varchar] (2) , [Cholesterol] [varchar] (8) , [CholesterolUOM] [varchar] (2) , [CholesterolPercentDV_RDI] [varchar] (8) , [Water] [varchar] (8) , [WaterUOM] [varchar] (2) , [Ash] [varchar] (8) , [AshUOM] [varchar] (2) , [Energy] [varchar] (8) , [EnergyUOM] [varchar] (2) , [VitaminA(IU)] [varchar] (8) , [VitaminA(IU)UOM] [varchar] (2) , [VitaminA(RE)] [varchar] (8) , [VitaminA(RE)UOM] [varchar] (2) , [VitaminAPercentDV_RDI] [varchar] (8) , [A_BetaCarotene] [varchar] (8) , [A_BetaCaroteneUOM] [varchar] (2) , [Thiamin_B1] [varchar] (8) , [ThiaminUOM] [varchar] (2) , [ThiaminPercentDV_RDI] [varchar] (8) , [Riboflavin_B2] [varchar] (8) , [RiboflavinUOM] [varchar] (2) , [RiboflavinPercentDV_RDI] [varchar] (8) , [Niacin_B3] [varchar] (8) , [NiacinUOM] [varchar] (2) , [NiacinPercentDV_RDI] [varchar] (8) , [NiacinEquiv#] [varchar] (8) , [NiacinEquivUOM] [varchar] (2) , [VitaminB6] [varchar] (8) , [VitaminB6UOM] [varchar] (2) , [VitaminB6PercentDV_RDI] [varchar] (8) , [VitaminB12] [varchar] (8) , [VitaminB12UOM] [varchar] (2) , [VitaminB12PercentDV_RDI] [varchar] (8) , [VitaminC] [varchar] (8) , [VitaminCUOM] [varchar] (2) , [VitaminCPercentDV_RDI] [varchar] (8) , [VitaminD] [varchar] (8) , [VitaminDUOM] [varchar] (2) , [VitaminDPercentDV_RDI] [varchar] (8) , [VitaminE] [varchar] (8) , [VitaminEUOM] [varchar] (2) , [VitaminEPercentDV_RDI] [varchar] (8) , [VitaminK] [varchar] (8) , [VitaminKUOM] [varchar] (2) , [VitaminKPercentDV_RDI] [varchar] (8) , [Folate] [varchar] (8) , [FolateUOM] [varchar] (2) , [FolatePercentDV_RDI] [varchar] (8) , [PantothenicAcid] [varchar] (8) , [PantothenicAcidUOM] [varchar] (2) , [PantothenicAcidPercentDV_RDI] [varchar] (8) , [Calcium] [varchar] (8) , [CalciumUOM] [varchar] (2) , [CalciumPercentDV_RDI] [varchar] (8) , [Iron] [varchar] (8) , [IronUOM] [varchar] (2) , [IronPercentDV_RDI] [varchar] (8) , [Magnesium] [varchar] (8) , [MagnesiumUOM] [varchar] (2) , [MagnesiumPercentDV_RDI] [varchar] (8) , [Phosphorous] [varchar] (8) , [PhosphorousUOM] [varchar] (2) , [PhosphorousPercentDV_RDI] [varchar] (8) , [Potassium] [varchar] (8) , [PotassiumUOM] [varchar] (2) , [PotassiumPercentDV_RDI] [varchar] (8) , [Sodium] [varchar] (8) , [SodiumUOM] [varchar] (2) , [SodiumPercentDV_RDI] [varchar] (8) , [Zinc] [varchar] (8) , [ZincUOM] [varchar] (2) , [ZincPercentDV_RDI] [varchar] (8) , [Iodine] [varchar] (8) , [IodineUOM] [varchar] (2) , [IodinePercentDV_RDI] [varchar] (8) , [Copper] [varchar] (8) , [CopperUOM] [varchar] (2) , [CopperPercentDV_RDI] [varchar] (8) , [Biotin] [varchar] (8) , [BiotinUOM] [varchar] (2) , [BiotinPercentDV_RDI] [varchar] (8) , [Omega3FattyAcids] [varchar] (8) , [Omega3FattyAcidsUOM] [varchar] (2) , [Omega6FattyAcids] [varchar] (8) , [Omega6FattyAcidsUOM] [varchar] (2) , [Alcohol] [varchar] (8) , [AlcoholUOM] [varchar] (2) , [SugarAlcohol] [varchar] (8) , [SugarAlcoholUOM] [varchar] (2) , [SugarAlcoholPercentDV_RDI] [varchar] (8) , [OrganicAcids] [varchar] (8) , [OrganicAcidsUOM] [varchar] (2) , [IngredientsEnglish] [varchar] (512) , [IngredientsFrench] [varchar] (512) , [IngredientsRefID] [varchar] (30) , [OtherInquiredAboutIngredientsEnglish] [varchar] (255) , [OtherInquiredAboutIngredientsFrench] [varchar] (255) , [AllergensOtherEnglish] [varchar] (255) , [AllergensOtherFrench] [varchar] (255) , [AllergenInformationAvailable] [varchar] (80) , [AllergenDisclaimer] [varchar] (80) , [Sensitivities] [varchar] (80) , [AdditionalAttributes] [varchar] (256) , [DiabeticExchangeStarch] [varchar] (8) , [DiabeticExchangeMilk] [varchar] (8) , [DiabeticExchangeFruit_Vegetable] [varchar] (8) , [DiabeticExchangeFats_Oils] [varchar] (8) , [DiabeticExchangeSugars] [varchar] (8) , [DiabeticExchangeExtras] [varchar] (8) , [DiabeticExchangeProtein] [varchar] (8) , [SpecificationsEnglish] [varchar] (255) , [SpecificationsFrench] [varchar] (255) , [OtherIngredientsEnglish] [varchar] (255) , [OtherIngredientsFrench] [varchar] (255) , [PrecautionsEnglish] [varchar] (255) , [PrecautionsFrench] [varchar] (255) , [GeneticallyModifiedIndicator] [varchar] (1) , [GMOIngredientsEnglish] [varchar] (255) , [GMOIngredientsFrench] [varchar] (255) , [KosherCor#] [varchar] (5) , [KosherOrganization] [varchar] (25) , [AnalysisResults] [varchar] (255) , [MicrobiologicalAerobicSpores] [varchar] (255) , [MicrobiologicalAnerobicSpores] [varchar] (255) , [MicrobiologicalTotalPlateCount] [varchar] (255) , [MicrobiologicalSalmonella] [varchar] (255) , [AllergenPeanuts] [varchar] (1) , [AllergenTreeNuts] [varchar] (1) , [AllergenSesame] [varchar] (1) , [AllergenMilk] [varchar] (1) , [AllergenEggs] [varchar] (1) , [AllergenFish] [varchar] (1) , [AllergenCrustacean] [varchar] (1) , [AllergenShellfish] [varchar] (1) , [AllergenSoy] [varchar] (1) , [AllergenWheat] [varchar] (1) , [AllergenSulphites] [varchar] (1) , [GLN] [varchar] (255) , [ChannelOfDistribution] [int] , [SuggestedServingSize] [char] (10) , [SuggestedServingUOM] [char] (512) , [ProductLabelType] [char] (2) , [AverageServingsPerCase] [bigint] , [ProductionFacility] [varchar] (66) , [SpecialOrderIndicator] [char] (512) , [ManufacturerAttributes] [varchar] (80) , [GeneralDescription_English] [varchar] (256) , [GeneralDescription_French] [varchar] (256) , [ProductName_English] [varchar] (80) , [ProductName_French] [varchar] (80) , [Division] [varchar] (30) , [ProductCode] [varchar] (14) , [ProductionType] [char] (512) , [PalletExtra] [char] (10) , [Benefits_English] [varchar] (512) , [Benefits_French] [varchar] (512) , [PreparationCookingSuggestions_English] [ntext] , [PreparationCookingSuggestions_French] [ntext] , [ServingSuggestions_English] [ntext] , [ServingSuggestions_French] [ntext] , [PackageStorage_English] [varchar] (512) , [PackageStorage_French] [varchar] (512) , [AdditionalRegulatoryInformation_English] [varchar] (512) , [AdditionalRegulatoryInformation_French] [varchar] (512) , [CFIARating] [char] (512) , [GeneralPharmaceuticalNumber] [varchar] (30) , [TransportationOfDangerousGoodsClassification] [varchar] (30) , [PCPActRegistrationNumber] [varchar] (30) , [EmergencyHealthRelatedInformation_English] [ntext] , [EmergencyHealthRelatedInformation_French] [ntext] , [GradeQuality_English] [ntext] , [GradeQuality_French] [ntext] , [SimilarProducts_English] [ntext] , [SimilarProducts_French] [ntext] , [ForMoreInformation_English] [ntext] , [ForMoreInformation_French] [ntext] , [AcceptDays] [varchar] (50) , [CaseYield] [varchar] (15) , [FinishedYield] [varchar] (15) , [FreezeDays] [char] (512) , [SCCMarked] [char] (1) , [UPCMarked] [char] (1) , [CodeDateType] [char] (2) , [ConsumerSupportNumber] [varchar] (512) , [Manufacturer] [varchar] (80) , [PeakStorageTime] [varchar] (50) , [ZZZ] [varchar] (255) , [ZZ] [varchar] (255) , [column_A] [varchar] (255) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] That's something ...Thank you |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-01-18 : 11:38:14
|
| now that's denormalized table if i ever saw one... normalize it.put that in your code.it will ignore an error that is returned.Go with the flow & have fun! Else fight the flow |
|
|
|
Baquardie
Starting Member
8 Posts |
Posted - 2006-01-18 : 14:40:22
|
| Hi spirit1, The query I try to execute is to alter the table.If I do On Error Resume Next, I'm still in trouble because the table won't be altered as it need to be.What is frustrating is that, in the query analyser, the table is altered, but not when I execute it from ASI cannot change the database nor table. I'm not the one who created it, I just need to alter that table. I'm creating an ASP page that alter that table. My boss said, make it work... This is why I'm trying to make it work since yesterday and I can't find a way...Thank you, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-18 : 15:54:12
|
| Wow, a lot of those columns could be switched into data.Here is an example:CREATE TABLE Person (PersonID int IDENTITY(1, 1), PersonName varchar(30))CREATE TABLE PersonAttribute(AttributeID int IDENTITY(1, 1), AttributeName varchar(30))CREATE TABLE PersonAttributeVal(AttributeID int, PersonID int, AttributeVal varchar(500))INSERT INTO PersonID(PersonName) VALUES('Tara Kizer')INSERT INTO PersonAttribute(AttributeName) VALUES('EyeColor')INSERT INTO PersonAttributeVal VALUES(1, 1, 'Brown')Tara Kizeraka tduggan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-18 : 15:55:24
|
| You need to use Query Analyzer to make schema changes. This should not be done in your application code. When he said make it work, he probably means make your application work. To do that, you use Query Analyzer to make schema changes.Tara Kizeraka tduggan |
|
|
|
Baquardie
Starting Member
8 Posts |
Posted - 2006-01-19 : 08:01:06
|
Hi Thanks for the replyI need to let the user add columns as for their needs. I finaly made it work. It was a mather of rights. My user in ASP didn't had the rights to alter a table.I know it is not the best way to do things, but believe me I do not have the choice  I am not the one who design, I just program it in ASP. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-01-19 : 08:05:05
|
change jobs!  Go with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
Ignore Warnings