There's nothing especially difficult about nesting CASE statements in SQL, but I guess it could be confusing sometimes.

Here's an example:

Ran into a problem today. Two SQL DBs, using transactional replication. I had some triggers created on the subscriber end that worked off insert, delete, and update operations.

The triggers worked great when inserting/updating/deleting data locally on the subscriber DB. But when a table was modified via replication, no triggers fired.

I thought replication just treated updates as a series of deletes and inserts, but the delete and insert triggers weren't firing, either.

If you're having a similar problem, first make sure your triggers don't have a "not for replication" setting. Sadly, mine don't. That's sad because a solution took a very long time to find.

The solution was a rather cryptic setting that tells replication to send updates *as* updates, rather than a delete/insert.

Read about the details and caveats here. I don't think this is the best solution, but nothing seemed to work, and nobody seems to know why this would occur.

Say you've got a numeric field (SQL Server DB) which returns numbers to three decimal places, e.g. 1.000, 2.125, 3.250, etc.

How do you return those numbers minus the trailing zeros? I can't believe this is so difficult. Surely there's an easy way to do it. Here's the hack I used:

Ick.

Okay, say you encounter a SQL Server Database that's got a HUGE transaction log, and you need to shrink it quickly. MS has several KB articles such as this one.

But if you really want to shrink the log file fast, and you don't need to keep the transaction log, here's a short cut:

Obviously, you'll need to stop using the database for a few minutes. All SQL processes that access the Database need to be closed.

The first step is to detach the Database. Do not skip this step; it's rather important for the health of your DB:

The next step is to delete the transaction log file (the .ldf file, NOT the .mdf). Note you are DELETING the transaction log. Obviously, it will no longer be available.

Finally, reattach the Database:

Say you want to do something like this:

update table set ntextThing = ntextThing + '!' where id = 1

That's not going to work. This does, though:

Every now and then you get a little job that seems simple and straight forward, but is anything but. I just encountered one of those.

I recently inherited an old CF application, written in CF 4.5 or 5.0.

The cfml templates have Japanese characters simply pasted in the file, with a meta tag in the HEAD section of the HTML that sets the character set to x-sjis (shift japanese).

[More]

If you've got a scheduled DTS Package that won't run, it's probably a simple fix.

The error we got was "reason: the system cannot find the file specified."

The job would run just fine when manually started, but scheduled jobs would fail.

The reason is irritating -- SQL Server doesn't include the path to "DTSRun.exe"

So you'll need to add that path to your PATH environmental variable, or edit each job you create individually.

To edit the job, go the the STEPS tab, double click the Step, and manually edit the command that calles DTSRun.

You'll find DTSRun.exe in tools/binn.

I saw this super odd error message from SQL Server today:

Firehose mode? What's that?

[More]