|
PIEBALDconsult wrote: Description is intended only as documentation for developers looking at the table
That kind of info I am storing in a separate data dictionary, which has not yet moved beyond an Excel doc, but I am seriously considering writing something to use SQL Server's Extended Properties for data dictionary data.
PIEBALDconsult wrote: What matters more is using names like "Name" and "Description" which are or may be reserved words -- you should probably avoid that, and by being in the habit of using more descriptive names you may never need to worry about that.
Yes, and even names that aren't reserved but are prickly, like having a table called ReferenceType with Id and Type columns. I have avoided all of those things.
|
|
|
|
|
Brady Kelly wrote: SQL Server's Extended Properties
Can those be applied to individual rows? Even if so, would a developer look there when perusing the table?
|
|
|
|
|
Duh, no.  I had crossed channels in my mind when I write that. 
|
|
|
|
|
This week I very quickly put together a simply database of some fifty tables and about three hundred columns all told, with every table having at least one foreign key. Then I noticed some spelling mistakes and or typos in some table and column names, and this inspired me to develop a tool that can perform a spelling check on all identifiers used in all database objects.
This is no mean feat. My rought draft strategy is to
1. Build a list of all identifiers in the database. To start with, I will limit this to table, column, view, and key and index names.
2. Use patterns and heuristics to split identifiers into word parts and ignore parts such as 'FK_' etc.
3. Check spellings of split identifiers, and those not split, and mark possible errors.
4. Where an error is e.g. found and corrected in a column name, correct all other occurrences of that column name in the DB.
I know none of these is simple, but I think this could make an interesting hobby project and an article or two, if I start simple and incrementally make the app more intelligent.
What warnings, criticisms, suggestions, or general comments do you have?
|
|
|
|
|
You are taking all the fun out of debugging. The hours of pure joy spent trying to understand a bug just because my typing skills were dyslexic are boundless. And you want to take this away!
Serious note:  good idea and an article would be a good read for sure. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I would be very happy to read such an article.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Are you going to look into only key fields or all field names, for us dyslexics who can spell ModifiedBy in multiple ways.
All fields would actually be simpler, build a distinct from sysobjects, then do some matching and soundex comparisons might be just as useful.
It is going to totally fai on some naming conventions but for us who use sensible naming it could be good.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm going to do all columns. My naming conventions nearlly always use full names, e.g.
SecondaryEducation ExamNumber
SecondaryEducation HighestLevelId
SecondaryEducation HighestLevelOther
SecondaryEducation SchoolName
SecondaryEducation EnglishProficiencyLevelId
SecondaryEducation EnglishProficiencyInstitution
SecondaryEducationLevel Id
SecondaryEducationLevel Description
I'll be using a set of algorithms to split names in various ways, hopefully into separate English words, as my example above would do quite nicely. Then I'll have a configurable set of standard non- or semi-English tokens that can be checked for deviation from their definitions, and good old ignore lists.
|
|
|
|
|
I have some vb code around somewhere that split words fairly reliably, used to make DGV headers readable before MS built it into their DGV.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Maybe you should article it.
There, how's that for a really dirty verbing? 
|
|
|
|
|
That entire phrase belongs in the nasty buzzword forum.
It would barely make a tip IIRC. I'll try and dig it up if you feel you have a need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I've found quite a list of splitting algorithms, so no need for you to go digging for it now, but thanks anyway.
|
|
|
|
|
Brady Kelly wrote: into separate English words
I would check for consistency first -- if a token is repeated many times, then it is probably what you meant and a token with a small Levenshtein Distance from it is probably misspelled.
On one project I decided that (by golly) I was going to spell "threshold" with three Hs (threshhold), as it's pronounced! I later changed it back, but before doing so I'd want the dozen "threshhold"s to outweigh an outlying "threshold".
Likewise, an incorrectly named item could be a valid word.
|
|
|
|
|
PIEBALDconsult wrote: I would check for consistency first -- if a token is repeated many times, then it is probably what you meant and a token with a small Levenshtein Distance from it is probably misspelled.
True, but e.g. using the designer to add foreign keys, where a misspelled column or table name is then used in the key name and DDL.
PIEBALDconsult wrote: Likewise, an incorrectly named item could be a valid word.
I plan to present a list of potential misspellings, with options to add to dictionary, ignore, propagate correction, etc. eventually.
|
|
|
|
|
Aside: I just found a table named ReportGalley rather than ReportGallery !  (The team lead was in the Navy, maybe this is something he cooked up, but it certainly is a mess.)
|
|
|
|
|
Groan!
|
|
|
|
|
Hi
Could anyone help me with a PHP or ASP.Net code that retracts an image from a database once a front end user clicks a button.The image is then send as email attachment to the user's email address.
|
|
|
|
|
Member 9467982 wrote: Could anyone help me with a PHP or ASP.Net code
There's tutorials on the NET that show how to fetch data from a database. Also on sending emails with attachments. What part do you need help with?
|
|
|
|
|
I've got a query going out to a Cache database via ODBC.
It almost always works (90% or so).
The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column)
By wrong, I mean the data is from another row.
At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference.
Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway.
I've even cleared the parameters for each loop of the read and it still does it.
I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right?
Anyone ever see anything like this before?
|
|
|
|
|
Usually when I have problems like this, I first try and eliminate something like ODBC. Is there any way to connect natively to the database. Preferably by running something on the server that is hosting the database? If you can do something like that, it will help to eliminate whether the database itself is doing something unusual or unpredictable. As far as ODBC goes, can you try other drivers and duplicate the issue still?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
The server is in Idaho or someplace up thataway, and I'm in Texas, so I won't being doing any hands-on. (Nope. Can't remote.)
There are no native drivers. There is a slight possibility that I could get a .NET driver... If they let me.
And no other ODBC driver will work. Freaking InterSystems Cache.
The DBA up there is goign to check with InterSystems and see if they have any ideas.
|
|
|
|
|
This sounds like a classic No-Win No-Win situation. You better get a good prescription for anti-depressants.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Could you post the query? Be aware that Cache  ::pinch of salt over the shoulder:: isn't SQL-92 compliant and the worst thing (in my opinion) is that it doesn't support operator precedence and that could be a factor here.
When I have used it (against my will) I used an ADO.net connector when I could, but prod used ODBC. I don't recall any differences.
|
|
|
|
|
select ID, DateOfChecksum, Checksum, RoutineCode, RoutineName, Site from CARC.RoutinesOST
But, like I said, it mostly works.
|
|
|
|
|
GenJerDan wrote: it mostly works
I think that's their slogan. 
Is CARC.RoutinesOST a view or something else other than a table?
Can you make a copy in another database (e.g. SQL Server) to test against?
What you describe certainly is a mystery.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
