Sheldon Hull

Sheldon Hull

  • _________________________________
  • < Welcome! Thanks for stopping by >
  •  ---------------------------------
  •  \
  •   \
  •      __
  •     /  \
  •     |  |
  •     @  @
  •     |  |
  •     || |/
  •     || ||
  •     |\_/|
  •     \___/

Here you’ll find mostly tech talk and musings, with other topics periodically. If you want to stay in touch, look at the bottom for the curated newsletter I send out.

Cheers! 🙏🏻


Get synonym definitions for all databases in server

If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested. Additionally, support may drop for it in the future.

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values.

Installing SSMS 2012 all by it's lonesome

SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation package and choose to install only this single feature. For developers, SQL Developer edition is a great choice. Here’s some screenshots to give you a guide on installing SSMS by itself when working with the full installer. Updated: 2017-02 This doesn’t apply for future SSMS versions as they began (I believe with 2014) to package SSMS outside of the database engine installer, allowing continual iterations and improvements for SSMS outside of being included as part of SQL server patching (finally!

snippet designate a certain time of the day in getdate()

Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, strip the time out of the date, and concatenate the current time together with it, and then convert back to datetime2. 1 selectconvert(datetime2(0),cast(cast(getdate()asdate)asvarchar(10))+' 09:00 ')

Native SSMS a second class citizen no longer...

Intellisense can be a boon to adding quick development. Quick hints on scope specific variables, syntax suggestions, function descriptions and more provide a valuable tool to productive coding.Coding in SQL Server Management Studio (SSMS) has greatly improved over the version releases, but it still lags behind the power of Visual Studio’s intellisense, template insertions with “fill in the blank” functionality . Additionally, the lack of automatic indentation means that lining up sub-queries and levels of logic can be annoyingly time consuming… especially when not everyone on a team following the exact same coding standards.

dynamic sql and a char crash

Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn’t working. Found out that the remote database I was connecting to had a char date instead of a datetime. I found the comparison of CHARDATE > VARCHARDATE failed to error out, but also failed to give a proper result set. Changing the look-up to ensure both dates were converted to date fixed the issue.