... invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Ok, so the first major stumbling block I've run into seems to be a common problem. I'm making this post title the error message in case someone else is looking for the answer.

Basically - any time you want to get a list of columns from a table, but you want to group the results because your using sum() or max() or the like - SQL Server will not let you do this unless your grouping on every column - which doesn't really help because that would give you all the rows!

This problem arises often where you have a table that records things and has datestamps signifying when the event occured. Lets say you want to query a table and see only the MOST RECENT occurence of an event, well max(datestamp_col) would work great - now lets say you want to join this resultrow with another table to get some more meaningful info about the event - BAM - group isn't allowed

There are actually two solutions to the problem - the first is to get the max value from a subquery and return the value as a column - this is most elegant, but not exactly novice friendly or intuitive.

---- below is an example ----

SELECT rs.LastLogon, rs.[Name], lo.DC, lo.Location
FROM (
SELECT MAX(LastLogon) AS LastLogon, [Name] AS [Name]
FROM logons
GROUP BY [NAME]) rs
INNER JOIN logons lo
ON rs.[Name] = lo.[name]
AND rs.LastLogon = lo.LastLogon
ORDER BY rs.LastLogon DESC

------------------------------


The second solution works only for things like max() - don't aggregate and use "top 1" (yes this is like limit 1 in mySQL) instead and order by the date column or whatever instead. Hardly a universal solution though - so you're stuck with the first one in most cases.

Anyway, for those who have written extensive queries including columns not in the aggregation - have fun rewriting these!!

My First Rant - Importing Tables

So the server arrived...finally. I log into the Microsoft Sql Server Management Studio, by now having gotten some experience with it through use in Microsoft Sql Server Express.

Oh by the way - if you're not needing to do a bunch of db admin stuff - using the Express is a great way to get familiarized with the basic layout of the Management studio - half the battle here folks.

I'm not expecting the world so I start to look into porting over the tables from mySQL to Microsoft. I create the new database and right click on the little drum - "Tasks" allows me to import a table. This is pretty cool. I've got my MyODBC driver loaded - a nice slow but reliable connector - I select the ODBC option and it asks me for a DSN or connection string.

At this point - you could add the string, or just create a user DSN which is much easier to change later if needed. Plus, you can test the connection properties and get a response for those people not speaking hexcommando language if it fails.

Once I get this set up, its pretty nice - choose select * from whatever_table and you've got your import - make sure to check the column mappings though to make sure no silly truncating through the translation.

Next - if that step went right - it will ask you whether to execute now or create an SSIS package. Trust me - unless your project is small - choose to create an SSIS package - it allows you to not have to enter all the bs before and allows you to re-use the package for implementing db mirrors just by changing the DSN above.

Oh yeah - save it to the filesystem.

So lets see if the process works. If all goes well - it works. If not - you get a lot of nonsensical and totally useless errors which spew from the first totally useless error telling you something went wrong and giving you a hexidecimal error code. Thank god for google - get ready to start examining field values in your original table for something slightly out of the "acceptable" range for MSSQL.

Things I've found it not to like mostly include dates and longer text columns which could be nvchar or ntext depending on which side of the brain is currently controlling you.

In the worst case - SQL2005 is complaining about a constraint on a column definition you gave it - kind of ironic don't you think? It lets you put in an invalid value for a constraint to prevent users from entering an invalid value!