SQL Server Agent Scheduler Has No AM/PM Selector

 Articles  1 Response »
Sep 042009
 

I recently came across a situation at a client’s site where I was unable to choose whether a SQL Agent schedule would be AM or PM.  Here’s a screenshot:

image

There is no AM/PM option, and clicking in the area of the control where it should be does nothing.  The first part – the hour – only goes from 1 to 12.  This means it’s only possible to schedule the job to run between midnight and 11:59:59am.

The cause of the problem was in the Standard Operating Environment settings for the organisation, where their default regional settings have the time set to h:mm:ss.  This displays times in 12-hour (instead of 24 hour), and does not display an AM/PM symbol.  This can be solved by changing the regional settings (via the Regional Settings Control Panel) time format on the computer to H:mm:ss (24 hour, no AM/PM) or h:mm:ss tt (12 hour, with AM/PM).  After this is done, reopen the schedule dialog box, and all is well. 

image

This is an issue with both Management Studio 2005 and 2008, running on Windows XP, Vista and Server 2003, but I think it’s rare that the regional settings will be configured in such a way.

Quick Tip #5: Get Column Names

 QuickTip  5 Responses »
Sep 022009
 

(Updated due to noeldr’s comment being a better option)

We all know that SELECT * is frowned upon when writing queries, but some tables have lots of columns that you don’t want to type out all the column names for.  What’s the fastest way of getting a column list?

I used to script out the CREATE statement for the table, and then remove any extra square brackets and types.  This had a lot of editing associated with it, which is relieved with Management Studio 2008 which provides the “SELECT TOP 1000 Rows” when right clicking on the table.  This still provides square brackets, however.  Other options were to put the results into text mode, and copy the header line.  However, the fastest method I’ve found is using the Query Designer, which I’ve never had use for. Another option is to use the Query Designer, which I still have no use for.

To get a list of columns for a table, create a SELECT * statement for it:

USE AdventureWorks
GO
SELECT * FROM Production.Product

Next, highlight the SELECT statement, and press Control-Shift-Q or use the menu item “Design Query in Editor…” under the “Query” menu to bring up the Query Designer:

image

Select or deselect the columns you don’t want, and copy the list to the clipboard.  That’s it!

Noeldr’s option, from the comments below is even better, and works in either Management Studio or Query Analyzer.  Simply open the Object Explorer up to the table, expand the table so that the “Columns” node is visible, and drag this item from the Object Explorer into your query window.  I love how you can work with a product for years and still find little tips like this!

Are there any other easy options I’ve missed, or should I always use square brackets when using column names?  Let me know!