UPDATE: See the latest version of this article here.
A characteristic error is a mistake so easy to make it appears you're actively encouraged to make it by the very nature of the software you're using.
Here's the latest entry...
- SQL Anywhere: Forgetting to code AUTOSTOP OFF on a START DATABASE statement, then wondering why the database immediately stopped after starting.
- SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.
- SQL: Seeing too much data because a missing predicate in the WHERE clause effectively turned your INNER JOIN into a CROSS JOIN.
- SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.
- SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.
- SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';
- SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.
- SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).
- SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).
- SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...
- SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...
- SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.
- SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.
- SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.
- SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.
- SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).
- SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.
- Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.
- Sybase Central: Setting it to display objects for owner names other than the one you're interested in.
- Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.
- MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.
- MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.
- OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
SELECT older_sample_set.sample_set_number INTO @20_older_sample_set_number FROM ( SELECT TOP 20 ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number, rroad_sample_set.sample_set_number AS sample_set_number FROM rroad_sample_set WHERE rroad_sample_set.sampling_id = @sampling_id AND rroad_sample_set.sample_set_number < @sample_set_number ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set WHERE older_sample_set.scrolling_row_number = 20; - MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).
- SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).
- HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'
- SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )
- Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF
- Forum: Clicking Reply on the main Question or Answer entry instead of the comment you wanted.
- SQL Anywhere: Forgetting to run dblog to tell the database file where the log is now, after moving the database and log files to a different folder (thank you, Justin Willey).
- SQL Anywhere: Having to look up WAIT in the Help ... every ... single ... time, to be reminded that's it's WAITFOR, not WAIT.
- SQL: Forgetting to check the SELECT against the GROUP BY, resulting in "Function or column reference to ... must also appear in a GROUP BY" (thank you, Glenn Paulley).
- SQL: Coding too much in the GROUP BY (like, say, the primary key) so every group contains but a single row (thank you, Glenn Paulley).
- Design: Forgetting to accomodate or prevent loops in a tree structure, resulting in a tree traversal process that pegs the CPU at 100%... forever (thank you, Ove B).
- MobiLink: Unwittingly using a variety of user ids when running sync*.sql, updating MobiLink scripts and running the MobiLink server, resulting in inexplicable inconsistencies.
- MobiLink: Accidentally creating multiple script versions and then getting them crossed up when updating MobiLink scripts and running the MobiLink client.
- SQL Anywhere: Forgetting to run the 32-bit version of SQL Anywhere when working with Excel proxy tables.
- ODBC Administrator: Running the 64-bit version (huh?) of odbcad32.exe (say what?) when you need 32-bit version at C:\WINDOWS\SysWOW64\odbcad32.exe (oh, fer #*@&!!!)
- ODBC Administrator: Forgetting to click OK ... twice ... to actually save your new ODBC DSN after celebrating your success with Test Connection.
- ODBC Administrator: Setting up an ODBC DSN on the wrong computer: "It goes with the client!" ... but sometimes it's not obvious where the client is actually located.
- Security: Forgetting which Windows user id you're using on which system, then spending too much time with Windows menus, firewall software and Google searches before the "Doh!" moment.
- SQL: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers.
- SQL Anywhere: Getting an exception because a FOR loop variable has a scope conflict with a column name, or worse, NOT getting an exception, just a wrong result.
- SQL: Forgetting the comma between two columns in SELECT list, thus turning the second column name into a profoundly misleading alias name for the first column.
- SQL Anywhere: Coding SET OPTION instead of SET TEMPORARY OPTION, then wondering where the commit came from.
- SQL: Not bothering to check that you have the same number of columns and arguments in an INSERT statement and then wondering why you have an error (thank you, Justin Willey).
- SQL: Forgetting to omit your autoincrement primary key from your INSERT column name list (thank you, Justin Willey).
- Foxhound: Changing the "Default" Monitor option settings, then expecting them to apply to an existing target database.
- Foxhound: Creating two separate Foxhound Monitor sessions for the same actual target database, perhaps one using a DSN and the other a DSN-less connection string.
- SQL Anywhere: Forgetting to code AUTOSTOP OFF on a START DATABASE statement, then wondering why the database immediately stopped after starting.
