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 |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2003-05-27 : 16:01:20
|
OK, me again whining and complaining about this project I'm going to inherit. As with the last post "Multi-functional sproc"http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=26341I'm seeing things no sane individual should be exposed to.What happens when you have a table with over 70 (count 70) text columns? AND there's not 1 READTEXT/WRITETEXT/UPDATETEXT anywhere!Anyone care to guess? I'm going to start messing around with (oh and yes ALL with an Access front end) and test this out on my own box...but REALLY! Thank god we're only a multi-billion dollar company...ARRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGGGGHHHHCREATE TABLE [dbo].[tblCallCoach] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [ProjectID] [tinyint] NULL , [IonsID] [nvarchar] (7) NULL , [LoginID] [nvarchar] (7) NULL , [DateReviewed] [datetime] NULL , [CallDate] [datetime] NULL , [CallTime] [datetime] NULL , [CallLengthMin] [int] NULL , [CallLengthSec] [int] NULL , [CallCoach] [nvarchar] (25) NULL , [Caller] [nvarchar] (30) NULL , [CallType] [nvarchar] (25) NULL , [StateCode] [nvarchar] (2) NULL , [PolicyNo] [nvarchar] (15) NULL , [ExpLevel] [nvarchar] (2) NULL , [Unit] [nvarchar] (4) NULL , [Team] [nvarchar] (10) NULL , [Q01] [int] NULL , [GreetTot] [int] NULL , [GreetApp] [int] NULL , [Q02] [int] NULL , [Q03] [int] NULL , [Q04] [int] NULL , [Q05] [int] NULL , [HoldTot] [int] NULL , [HoldApp] [int] NULL , [Q06] [int] NULL , [Q07] [int] NULL , [ClosingTot] [int] NULL , [ClosingApp] [int] NULL , [Q08] [int] NULL , [Q09] [int] NULL , [TransferTot] [int] NULL , [TransferApp] [int] NULL , [TechniquesTot] [int] NULL , [TechniquesApp] [int] NULL , [Q10] [int] NULL , [AuthnTot] [int] NULL , [AuthnApp] [int] NULL , [Q11] [int] NULL , [InformTot] [int] NULL , [InformApp] [int] NULL , [Q12] [int] NULL , [Q13] [int] NULL , [KnowTot] [int] NULL , [KnowApp] [int] NULL , [Q14] [int] NULL , [AuthTot] [int] NULL , [AuthApp] [int] NULL , [Q15] [int] NULL , [TransacTot] [int] NULL , [TransacApp] [int] NULL , [Q16] [int] NULL , [NotesTot] [int] NULL , [NotesApp] [int] NULL , [Q17] [int] NULL , [TrkTot] [int] NULL , [TrkApp] [int] NULL , [AccuracyTot] [int] NULL , [AccuracyApp] [int] NULL , [Q18] [int] NULL , [Q19] [int] NULL , [Q20] [int] NULL , [Q21] [int] NULL , [CourtTot] [int] NULL , [CourtApp] [int] NULL , [Q22] [int] NULL , [Q23] [int] NULL , [Q24] [int] NULL , [ResponTot] [int] NULL , [ResponApp] [int] NULL , [Q25] [int] NULL , [Q26] [int] NULL , [Q27] [int] NULL , [ConfidTot] [int] NULL , [ConfidApp] [int] NULL , [ImpressTot] [int] NULL , [ImpressApp] [int] NULL , [Q28] [int] NULL , [Q29] [int] NULL , [Q30] [int] NULL , [Q31] [int] NULL , [UndReqTot] [int] NULL , [UndReqApp] [int] NULL , [Q32] [int] NULL , [Q33] [int] NULL , [Q34] [int] NULL , [Q35] [int] NULL , [GuideTot] [int] NULL , [GuideApp] [int] NULL , [Q36] [int] NULL , [Q37] [int] NULL , [Q38] [int] NULL , [ExplanTot] [int] NULL , [ExplanApp] [int] NULL , [Q39] [int] NULL , [Q40] [int] NULL , [ExpTot] [int] NULL , [ExpApp] [int] NULL , [ControlTot] [int] NULL , [ControlApp] [int] NULL , [Q41] [int] NULL , [Q42] [int] NULL , [Q43] [int] NULL , [MatTot] [int] NULL , [MatApp] [int] NULL , [Q44] [int] NULL , [Q45] [int] NULL , [Q46] [int] NULL , [Q47] [int] NULL , [Q48] [int] NULL , [Q49] [int] NULL , [SysTot] [int] NULL , [SysApp] [int] NULL , [EfficiencyTot] [int] NULL , [EfficiencyApp] [int] NULL , [Q50] [int] NULL , [Q51] [int] NULL , [Q52] [int] NULL , [Q53] [int] NULL , [Q54] [int] NULL , [CallsTot] [int] NULL , [CallsApp] [int] NULL , [Q55] [int] NULL , [Q56] [int] NULL , [Q57] [int] NULL , [DemutTot] [int] NULL , [DemutApp] [int] NULL , [LettersTot] [int] NULL , [LettersApp] [int] NULL , [DecisionLetterComments] [text] NULL , [EfficiencyComments] [text] NULL , [TechniquesComments] [text] NULL , [AccuracyComments] [text] NULL , [ImpressionsComments] [text] NULL , [ControlComments] [text] NULL , [OverallComments] [text] NULL , [Desk] [nvarchar] (4) NULL , [Reference] [nvarchar] (25) NULL , [Resources] [nvarchar] (4) NULL , [Navigation] [nvarchar] (4) NULL , [OfficeVision] [nvarchar] (4) NULL , [SBSComments] [text] NULL , [RefBox] [nvarchar] (20) NULL , [Successes] [text] NULL , [Improvements] [text] NULL , [Goals] [text] NULL , [AddlComments] [text] NULL , [SatScore] [tinyint] NULL , [CreatedBy] [nvarchar] (7) NULL , [CreatedDate] [datetime] NULL , [CreatedTime] [datetime] NULL , [UpdatedBy] [nvarchar] (7) NULL , [UpdatedDate] [datetime] NULL , [UpdatedTime] [datetime] NULL , [Attribute01Comment] [text] NULL , [Attribute02Comment] [text] NULL , [Attribute03Comment] [text] NULL , [Attribute04Comment] [text] NULL , [Attribute05Comment] [text] NULL , [Attribute06Comment] [text] NULL , [Attribute07Comment] [text] NULL , [Attribute08Comment] [text] NULL , [Attribute09Comment] [text] NULL , [Attribute10Comment] [text] NULL , [Attribute11Comment] [text] NULL , [Attribute12Comment] [text] NULL , [Attribute13Comment] [text] NULL , [Attribute14Comment] [text] NULL , [Attribute15Comment] [text] NULL , [Attribute16Comment] [text] NULL , [Attribute17Comment] [text] NULL , [Attribute18Comment] [text] NULL , [Attribute19Comment] [text] NULL , [Attribute20Comment] [text] NULL , [Attribute21Comment] [text] NULL , [Attribute22Comment] [text] NULL , [Attribute23Comment] [text] NULL , [Attribute24Comment] [text] NULL , [Attribute25Comment] [text] NULL , [Attribute26Comment] [text] NULL , [Attribute27Comment] [text] NULL , [Attribute28Comment] [text] NULL , [Attribute29Comment] [text] NULL , [Attribute30Comment] [text] NULL , [Attribute31Comment] [text] NULL , [Attribute32Comment] [text] NULL , [Attribute33Comment] [text] NULL , [Attribute34Comment] [text] NULL , [Attribute35Comment] [text] NULL , [Attribute36Comment] [text] NULL , [Attribute37Comment] [text] NULL , [Attribute38Comment] [text] NULL , [Attribute39Comment] [text] NULL , [Attribute40Comment] [text] NULL , [Attribute41Comment] [text] NULL , [Attribute42Comment] [text] NULL , [Attribute43Comment] [text] NULL , [Attribute44Comment] [text] NULL , [Attribute45Comment] [text] NULL , [Attribute46Comment] [text] NULL , [Attribute47Comment] [text] NULL , [Attribute48Comment] [text] NULL , [Attribute49Comment] [text] NULL , [Attribute50Comment] [text] NULL , [Attribute51Comment] [text] NULL , [Attribute52Comment] [text] NULL , [Attribute53Comment] [text] NULL , [Attribute54Comment] [text] NULL , [Attribute55Comment] [text] NULL , [Attribute56Comment] [text] NULL , [Attribute57Comment] [text] NULL , [Driver01Total] [int] NULL , [Driver02Total] [int] NULL , [Driver03Total] [int] NULL , [Driver04Total] [int] NULL , [Driver05Total] [int] NULL , [Driver06Total] [int] NULL , [Driver07Total] [int] NULL )GO Brett8-) |
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-05-27 : 16:12:50
|
Have fun with the transaction log! |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-05-27 : 16:30:22
|
LOL  who needs a database when you can stick everything in one table  |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-27 : 16:42:15
|
| What's that word.Normalisation or something I think.Why did they stop at 57 I wonder.And only 7 drivers?==========================================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. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-05-27 : 16:45:58
|
| Brett,I think you need to save yourself some trouble and go find a bridge and jump off :)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-27 : 16:51:51
|
Are you kidding...the rocket scientist has about 50 tables, although all not as gnarly as this one.My best guess is (though all the higher ups on his side say otherwise) is that he's babysitting/handholding this damn thing on an hourly basis.So what a statement like..oh there's no new development and it runs on it's own so you really don't need even a 1/4 fte translates to: You need a staff of 20 and you'll be lucky to be able to re-write it.And here I came for advice (and sympathy) That's ok...I can take the pot shots...Really, I don't know what to do.Maybe I can pump gasoline or something.... Brett8-) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-05-27 : 17:28:55
|
I didn't mean to take pot shots Brett. Sorry man.With those text fields, I'm guessing if they don't go over 8k, then it just sorta "works" but READTEXT etc etc would be the way to go.May the  be with you!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-27 : 17:31:17
|
| I think everyone is is suddenly very happy with their systems.Probably he got a warning that the row was potentially too big when the table was created - but realised that by using text statements it was ok and didn't make any difference to the insert.(Or update if you replace the text field)I suspect he's noticed since that there are things you can do with a varchar that you can't do with a text.==========================================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.Edited by - nr on 05/27/2003 17:37:19 |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-05-27 : 18:09:29
|
| If it's got an access frontend then that might explain all the text fields - did this start life just in Access ? |
|
|
|
dsdeming
479 Posts |
Posted - 2003-05-28 : 07:57:57
|
quote:
Thank god we're only a multi-billion dollar company...
Not for long, with tables like that. At least the system's thoroughly documented, right? Dennis-- Schadenfreude. It's not just for breakfast anymore. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-05-28 : 08:08:45
|
I can see one that looks like this. It's in a database with 200 tables and no primary keys in any of them.CREATE TABLE EXTREGISTERS1 ( RegKey int NULL, ActiveDates varchar (53), RegDur1 int NULL, RegDate1 datetime NULL, StTime1 datetime NULL, FnTime1 datetime NULL, RegDur2 int NULL, RegDate2 datetime NULL, StTime2 datetime NULL, FnTime2 datetime NULL,-- and so on, down to RegDur53 int NULL, RegDate53 datetime NULL, StTime53 datetime NULL, FnTime53 datetime NULL ) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-28 : 09:21:36
|
| Thanks guys/Gals...Documentation...yeah right...I'm going to build the environment on my own dev box...still waiting for the guy to ship me the access front end...have to make my own to futz around inMaybe a career change...."Would you like Fries with that?"Brett8-) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-28 : 09:47:27
|
| There's just SOOO MUCH fun here !I take there's a good reson they're using Nvarchar's ? 1 not null column ...How much data are they putting in this sucker ? What do the accesses look like ? I can just wonder at what indexing and stat's are going to look like...Good luck!PS - you said 50 tables ... what's the cahnces they;'ve heard about RI ... or are they "just going to look after that in the application"?*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-28 : 10:24:57
|
| I feel a bit left out.Someones coming along this week to implement some new stuff.When I last saw it it only had up to datetime4, text35 and numeric35, Flg15.There's been 3 weeks "development" since then so maybe they've have caught up.Weren't interested in discussing the design "It does everything the business requires".==========================================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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-28 : 11:17:01
|
Well, I just hooked up an Access front end to this table (created in Northwind) and created a form with the Id column and 1 (count'em 1) text field. Then I cut and pasted a whole bunch of data until it told me the text field was too long..I guess I hit the upper end access limit....and the clicked on next record to perform the insert..It took 15 seconds....And this is already in "production" in California...and some remote sites nationally....what they get about 10, 20 keystrokes a day?quote:
Weren't interested in discussing the design "It does everything the business requires".
Nigel, What do we do when this falls in our lap...I mean I'm not getting paid by the hour...Brett8-) |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2003-05-28 : 12:04:10
|
| use xml, that'll fix it.~ monkey |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-28 : 16:43:31
|
| Just say that you have doubts about the system and wait to get blamed when it goes wrong.==========================================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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-28 : 17:25:12
|
quote:
Just say that you have doubts about the system and wait to get blamed when it goes wrong.==========================================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.
LOL....but it's not very funny...just that its sooooooooooooooooooooooooooooooooooooooTrueBrett8-) |
|
|
|
dsdeming
479 Posts |
Posted - 2003-05-28 : 22:53:44
|
| I got to looking at the table structure again and noticed you have a text column called [EfficiencyComments]. Seems a bit ironic under the circumstances. At least it's nullable.Dennis |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-30 : 13:05:47
|
quote:
noticed you have a text column called [EfficiencyComments]. Seems a bit ironic under the circumstances. At least it's nullable.
Yes it certainly is...Just found out the client side install is about 400 mg...be afraid, be very afraid...Check this nonsense out (Damn it Jim I'm a DBA, not a miracle worker):Additional items or services required to use certain features: For multimedia and sound: Accelerated video card or MMX processor for improved graphics rendering For speech recognition (available for U.S. English only): Pentium II 400-MHz or higher processor 128 MB of RAM or more of free unused RAMClose-talk microphone and audio output device Microsoft Internet Explorer 5.0 or later MAPI-compliant messaging software required to use e-mail such as Microsoft Exchange, Internet SMTP/POP3, IMAP4, Lotus Notes or other compatible program(s)Microsoft Exchange Server required for certain advanced collaboration functionality in Microsoft Outlook® Collaboration features compatible with Office 97 or later Some Internet functionality may require Internet access and payment of a separate fee to a service provider; local or long-distance charges may apply 14,400 or higher-baud modem (for remote replication when connected to the network via modem)Graphics tablet recommended for handwriting-input features Microsoft Windows Media™ encoder-compatible video camera for broadcasts including video; Microsoft Exchange Chat server to enable chats during live broadcasts; Windows Media Server to enable multicasts of live broadcasts to more than 10 audience members 50 MB of additional hard disk space for each additional language interface installed To use Microsoft IntelliMouse Explorer¹ with the scroll wheel and all the programmable buttons, you also need: Microsoft IntelliPoint version 3.0 software Windows 95, Windows 98, or Windows NT Workstation 4.0 with SP3 or later operating system 30 MB of available hard disk spaceBrett8-) |
|
|
|
GenghisBlonde
Starting Member
2 Posts |
Posted - 2003-05-31 : 16:20:46
|
| >Check this nonsense out (Damn it Jim I'm a DBA, not a miracle worker)You did remember not to wear the red shirt before beaming down on this one didn't you? ;-) |
|
|
|
Next Page
|
|
|
|
|
70 Columns defined as text in 1 table