A huge array of Personal Weather Stations is what makes Weather Underground (aka WUnderground) be so much more precise than any other weather service. In fact, the company’s forecasts are so precise (and cheap?), that “even” Google chose them to be their primary source for searches and other weather related cloud services (i.e. see http://www.google.com/intl/en/help/features_list.html#weather). But while there are “more than 25,000 weather stations across the globe [that] already send data to Weather Underground“, there are still many areas of the world that are not properly covered by this extensive network. In my case the topography of the region is so extreme, that the nearest station was constantly off by up to 15 degrees. Needless to say, even the next day forecasts were all over the place.
Introducing (cheap consumer grade) La Crosse Technology Wireless Weather Station with La Crosse Alerts (aka C84612) being sold at offline Costco stores. The station costs roughly $80 and comes with wireless wind (speed, gust, direction), rain (still not working for me), temperature, humidity, and pressure sensors, as well as a mediocre dashboard panel, and an “ internet gateway” (connects the panel to La Crosse propitiatory website). This station is capable of properly reporting current information in close to real-time onto the dashboard and roughly every 20 minutes to the attractive La Crosse Alerts secured website.
The code below can be reused to connect other types of weather stations, but it was originally designed to utilize undocumented features of the La Crosse Technology Wireless Weather Station with La Crosse Alerts (aka C84612).
Full tutorial is now available as a separate article “(HACKING) PROFESSIONAL WEATHER STATION FOR UNDER $100“.
Temp path moved to a variable instead (thank you, Mara and Ford)
Down alert default changed to 2 hours (La Cross internet update seems to be down just over 1 hour quite often)
Check for station itself being down (outside temperature reported exactly at 32 and dew point of 0)
Running script with ?output at the end would produce an output each time - helps troubleshoot otherwise successful runs
Info on delay in hours since last update always available on errors
Insert..Select is a great command, but sometimes you just need more- sometimes you need to return bits of the information back.
The first thing you will attempt, of course, is to declare the few variables you need. Yes, you will see that you can indeed do that, along with updating columns, all in one call- neat!
[syntax_prettify linenums=”linenums”][email protected] = ID,OldValue = OriginalTable.ValueSELECTValueFROM OriginalTableWHEREOriginalTable.AddDate < GETDATE() - 30[/syntax_prettify]
Unfortunately not only other types of statements (i.e. delete and insert) will fail to do that, but your variable(s) will hold only one value. Yes, you could concatenate it…but are you really going to be doing this crazy 20th century workaround nonsense again? Our old OUTPUT keyword to the rescue- it redirects the processed information back to where you tell it, including a variable table that you might have created just for that purpose.
We’ve already talked about batch processing in SQL - Cursor and Batching, but it’s not just human labor that batching helps us with. One of the best ways to optimize any type of coding is to limit the amount of hits it has to do against storage (physical or otherwise). It could come at an expense of more CPU cycles, so proper compromises need to be made to ensure true performance improvements.
In case of SQL, this is usually achieved by limiting the number of separate calls to the databases/tables and finally limiting the number of requests themselves (select/insert/update/delete), wrapping them into as few as possible… Oh, how many times have I seen very simple procedures with cursors or even just check requests (ie IF EXISTS), followed by possible change requests, followed yet again by closing check or pull requests. Why would you do that if you can easily do all of this at once and allow the engine to properly optimize out of the box?? The harsh cultural weight of our 20th century coding backgrounds, perhaps?
Somewhat surprisingly, batch processing stays as one of the most gaping holes in DB power-users’ knowledgebases… Processing records one by one, searching, fetching, updating- they seem to be fine with all of this no matter the type of the DB, but there is something about batching that many do not seem to grasp. What’s worse, is that they don’t even KNOW that they are really missing out…
As the number of mobile, remote, and out-of-office workers rises, the most popular offering stays the same- conference calls. A long 800 number, followed by a pause while it connects you and then a very long random set of numbers followed by a pound- oh, how much we all hate dialing and redialing it oh so many times a day…… but what if there was another way??? A 3rd party app or service you ask? An alternative to phone? NO! Any alternative would bring with it side effects and unnecessary confusion, so why suffer, when you can just use what we ALL already have, are used to, and most importantly what works great for ALL of us?? Solution is most simple: use a different format in your emails, notes, and appointments and let the phone handle it all. The following format will have your phone, as well all other users’ of the conference, dial everything completely automatically with no additional work what so ever: phoneXextension# (and other formats are supported too, as well as additional pauses (X) and special symbols (#, *, etc)). For example: 1-800-111-1111×1234#
Again, the beauty of it is that everyone on the call will benefit from this, no app/service is needed, nor is it limited to a certain make and model/OS of the phone. Free, simple, and universal.
Previously I have shared an extremely powerful method of finding objects such as procedures, functions, and triggers…but what about tables (or views)? MS SQL is, once again, able to do this quite easily and logically without the need for any expensive and limited 3rd party software:
[syntax_prettify linenums="linenums"]USE MyDataBase;
ON COLUMNS.TABLE_NAME = TABLES.TABLE_NAME
column_name LIKE '%SEARCHNEEDLE%'
AND TABLE_TYPE <> 'VIEW'[/syntax_prettify]
Yet another “new” feature of MS SQL 05 is an often overlooked “error handling”. The Transact-SQL Try…Catch is designed to operate similar to the exception handler in the Visual/.NET languages- if an error occurs inside the TRY block of a query, control is passed to another group of statements that is enclosed in a CATCH block (and on)… Or so it should- the handler will not help with errors of severity 20+, KILLs, and, of course, with various warnings and messages, so be careful and remember to test all of the scenarios right away.
[syntax_prettify linenums=”linenums”]BEGIN TRY
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
SELECT ‘Continue the run’[/syntax_prettify]
Note that this behavior can also be triggered manually, via the RAISERROR command (notice the spelling, with only one E):
[syntax_prettify linenums=”linenums”]RAISERROR(‘Fatal error’,16,1)[/syntax_prettify]
At last, sometimes, when using this functionality, you might need to ensure that you do exit and don’t run the rest of the statements- feel free to add RETURN command in such cases…
MS SQL 2005 and up adds support for the APPLY clause, which, in turn, lets you join a table to any dynamic sets, such as table-valued-functions or even derived tables. While we can argue over the benefits and dangers of the latter (another set of article perhaps?), being able to do things more than one way is certainly always awesome.
The two new functions (CROSS APPLY and OUTER APPLY) are essentially INNER and OUTER JOINs for table-functions (you cannot directly join them like tables). Here is just a simple example of the usage (don’t hesitate to expand on it):
[syntax_prettify linenums=”linenums”]SELECT All.CustomerID
Customer as C
fnSelectAllChildren(C.CustomerID) AS All
WHERE C.Status = 1[/syntax_prettify]
This technique can also be used in more complex queries alongside derived tables, FOR XML, etc., covering the need for things such as inline multi-row CONCAT (multiple rows into a single column):
SELECT TOP 1
ISNULL(LEFT(o.list, LEN(o.list) - 1), ‘Unknown’)
Orders AS ito2
CROSS APPLY (
CONVERT(VARCHAR(12), ServiceType) + ‘,’ AS [text language=”()”][/text]
OrderService AS itos2 (NOLOCK)
OrderServiceType AS itost (NOLOCK)
itost.Id = itos2.OrderServiceType
itos2.OrderId = ito.ID
) o (list)
ito2.ID = ito.ID
) AS AllTypes
Orders (NOLOCK) ito
ito.Status = 0[/syntax_prettify]
IA Summit 2011 was a huge success. The sessions focused on improving skill sets, analytics, cognitive persuasion, holistic experience, and the new world order- helping Information Architects “play nice” and coexist with the rest of the workforce in an agile environment. Each and every world-famous host and attendee worked on helping each other, and me personally, get closer to the acme of perfection. My most humble appreciation goes to Justin Davis, Eric Reiss, Jonathon Colman, Louis Rosenfeld, and the rest of the presenters who also found time for personal attention…
It is hard to summarize the amazing and already succinct presentations, let alone great hallway discussions we had, but I have to try, out of mere respect and, if for nothing else, to persuade you to read more about their respective slides and websites.