Feed: cfsilence - SQL - AggScore: 82.9

Aqua Data Studio 7.0 Released

---

I just got an email from AquaFold announcing the final release of version 7.0 of their popular Database query tool Aqua Data Studio. I've used ADS for a while on and off (thanks to a program that offers free licenses to Open Source developers) and I have to say it is a pretty good application. Here is a list of features for 7.0 - which includes full support for Apache Derby databases. Download Page

MySQL Error Code 17?

---

I don't usually like to post asking for help, but over the last few weeks I've seen some variation of the following error on my server: Can't create/write to file '#sql_5c90_0.MYD' (Errcode: 17) The file name is usually different, but the Errcode (17) is always the same. A quick google search shows that MySQL error code 17 means that the file exists (so it can't be overwritten), but nothing I've found tells me a.) what the heck the file is or b.) why it already exists or c.) how to make sure it doesn't exist the next time the process tries to write to it. Any MySQL gurus out there have an idea? If I do the good old 'reboot' the problem goes away (for a few days/hours anyways).

SlideSix Preso Of The Day

---

Are you tired of me blogging about SlideSix yet? Today's preso of the day comes from my buddy Robert Froehling (Fro). Fro did a quick preso about SQL String Concatentation to the DFW CFUG a few days ago and has posted the preso up to the site. Thanks to his co-operation I did some more tweaks to the conversion engine, and this preso is the best looking yet (I think only one slide came in a bit weird where the bullet points were all concatenated to one line). Notice the heavy use of colored/commented code in his preso that used to come in with each formatted bit on it's own line. Thanks again Fro! See more at slidesix.com (Yes, it is the *only* preso of the day so far, but who's counting)...

MySQL UDF Repository

---

Just found a MySQL UDF Repository with a few nice libraries. Not quite what I'm looking for, but thought I'd blog it anyways. Anyone know of a good library for MySQL date UDFs? I have to admit SQL Server seems to have a much larger community for things like UDFs.

TSQL - UDF To Round In Increments

---

After searching Google for a bit with no luck I decided to whip together a quick TSQL UDF to round values in incremements. Use cases are rather common so I'm surprised I couldn't find anything. [More]

Quick Tip O The Day

---

I found this in a blog comment about a month ago - though I can't remember where and I'm not 100% sure someone didn't actually post it too - nonetheless - check out http://www.sqlinform.com/. It is a very cool utility for "prettying up" your SQL. There are some config options too (tabs or spaces for indent, etc). Basically it's a good way to make sense of nasty looking queries.

SQL Server Date Sorting

---

Just had a quick issue that was driving me a bit nuts. I have a function that inserts site stats into a table. I was querying the table in Query Analyzer and was wondering why SQL server was incorrectly sorting my results. My query looked something like this: select top 10 foo, dateAdded from tbl order by dateAdded desc Which I fully expected to give me the 10 most recent records. In fact it was working fine before lunch today. Well after lunch it started getting weird on me. It was returning noon, then 11 am, then 10 am and then 1 pm! What? 1pm should be first! I took another look at my insert query and found this: Aha! In my timeformat() I was using the 12 hour clock. SQL server wants the 24 hour clock! So a quick change to this (note HH): And the sorting was back to normal. Update: Of cource I could have also just passed now() like so:

TSQL - Deleting Duplicate Rows From A Table

---

Here's a quick and dirty method for deleting duplicate rows from a table without an identity column. I know there are many versions of this query available via Google but I'm being selfish and blogging it so I always know where to find it. Thanks Chad! --add identity row to table ALTER TABLE myTable add rowid int identity(1,1) ---Delete Dups From myTable delete from myTable where rowid not in (select min(rowid) from myTable group by something) --remove identity row from table ALTER TABLE myTable DROP COLUMN rowid

TSQL - Last Day Of Month UDF

---

Here's a UDF I threw together to determine the last day of a given month. SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE function lastDayOfMonth(@d datetime ) returns datetime as BEGIN return dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@d)+1, 0)) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO So: print dbo.lastDayOfMonth(getDate()) --returns Mar 31 2007 11:59PM

TSQL - Don't Forget sp_help!

---

Such a simple thing, but since I always forget that this little beauty exists I thought I'd blog for anyone who also forgets (or doesn't know about) sp_help. Check the docs for specifics, but essentially sp_help will give you metadata about an object (table, UDF, etc). Column names, datatypes, etc. Quick and dirty, just the way I like it.

Using cfqueryparam With An 'IN' Clause

---

I was just working on a query that was acting up a bit and thanks to the ever brilliant Raymond Camden who bailed me out in about 2 seconds with the solution. I figured I ought to blog the issue for those who may come across this. Essentially I needed to pass a potential list of values into my query as an 'in' list. My function looked something like this (disclaimer: this is sql server syntax - your db may be different) select alpha from charlie where whiskey in My first issue was the parenthesis within the value of the cfqueryparam. Ray quickly noted that it should be something like this: where whiskey in () Note the parenthesis outside of the cfqueryparam and the addition of the list="true" attribute and the removal of preserveSingleQuotes. However this still wasn't working for me. The fix was simple however - it seems that the list attribute automatically qualifies the list for you - so the listQualify was not necessary. The end result - which worked perfectly - looked something like this: select alpha from charlie where whiskey in ()

To AutoNumber Or Not To AutoNumber

---

I'm in the midst of building my database structure for 'Project Learn' (see this post if you're not sure what that's about) and have come across the age old question: should I use auto numbered integers for my primary keys or should I create the id using createUUID() in CF and pass it in? I seem to go back and forth on this topic so I'll put my pro's and con's out here and leave it up to my readers for further discussion. Pros to Auto Number: 1. Easy. Inserts will always handle the creation of the auto numbered id field. 2. Integers are more easily indexed by SQL server (performance related). Cons to Auto Number: 1. Less control over data. I know this is kinda a 'control freak' thing, but I like to have control over the creation of the primary key so that I can pass that key back to my caller (or another function when cascading inserts such as a one to many type insert). 2. Related to Con #1 - have to rely on 'select @@identity' to return the most recently created id - which can be unreliable in a high traffic db. This topic has been debated many times and will likely continue to be. There are other ways to retrieve the id I believe - but I don't know much on this topic (anyone?). Pros to createUUID(): 1. As I stated above, I control the creation of the id and have no question that it is unique and the correct key. Cons to createUUID(): 1. No built in datatype in SQL for CF's UUID. Rob Gonda recently posted a way to create such a datatype so that you can validate this type so there's a workaround available for this con. 2. Non-integers are less easily indexed by SQL server. So that's my dilemma. I'm also considering a mix of auto numbered id's and UUID pk's (using the UUID's where I need the granular control over the id number and the auto number where that's less important). I'd like to hear everyones opinion on this one. I'm really hoping that this series will be interactive and that as many people as possible feel comfortable joining in the learning and discussions. Feel free to tell me your thoughts.

TSQL - Exclude Nulls From Grouping

---

Have you ever written a TSQL query that including a GROUP BY clause but wished that you could exclude null values from being included in that grouping? The SQL books state:

If the grouping column contains a null value, that row becomes a group in the results. If the grouping column contains more than one null value, the null values are put into a single group. This behavior is defined in the SQL-92 standard.

Usually that works out just fine. However sometimes you may not want this behavior. Consider the following query: select colA, colB, sum(colC) as sumColC from tblA group by colA, colB Simple enough. But what if there were null values in colB? The results would be distinct groupings for colA and colB and a seperate, aggregate grouping for the null values. So how can you maintain a seperate line (or avoid grouping) the null values? Consider the following: select colA, min(colB), sum(colC) as sumColC from tblA group by colA, isNull(colB, cast(newId() as char(36))) Looks a little weird but let me explain whats going on here. First of all, every column in the select list must appear in the group by - unless that column is part of an aggregate. But the inverse is not true - every item in the group by does not have to be contained in the select list. So by including the varchar colB in an aggregate (min) we can avoid using that column in the group by. The other trick here is to now evaluate colB in the group by clause with the isNull() function. isNull takes two arguments, the column to evaluate and the result to present should that column evaulate as null. In this case we evaluate colB and if the result is null we group by the unique newId() - but we must cast the newId as char to avoid an exception. The result will be a nicely grouped query that excludes grouping on the null columns. As I've stated many times before I'm not an SQL expert - but some of my readers are. So any potential drawbacks to this method?

Funny Post - 10 Things I Hate About SQL Server

---

I just read a hilariously sarcastic post titled TOP 10 THINGS I HATE ABOUT SQL SERVER. Very funny stuff.

SQL Server 2005 Express - DTS Confirmed

---

As a quick follow up to yesterdays post, I have confirmed that the DTS Wizard that us SQL Server 2000 users know and love is in fact available for use with SQL Server 2005 Express. Go here and download the Microsoft SQL Server 2005 Express Edition Toolkit SP1. Install the tools and hit C:Program FilesMicrosoft SQL Server90DTSBinnDTSWizard.exe and you'll get the good old Import/Export Wizard. You can also associate the wizard as a 'Tool' within SQL Server Management Studio Express which saves you a little time when you need to import/export. Unfortunately you still can not directly select an object and import/export but the fact that you can still use the tool makes me happy.