SQL Server – How to convert varchar to decimal

— This SQL reads in a varchar(10) field called work_hours that could have anything in it. It should be numeric represented as decimal.

— The CASE statement will produce either 0.00, or (if it is numeric) whatever is in the work_hours field.

— The is_numeric column is for analytical purposes and shows that the value of 1 means it was numeric and 0 when it was not numeric.

SELECT column1, column2,
--ISNUMERIC(work_hours) As is_numeric,
CASE
WHEN ISNUMERIC(work_hours) = 0 THEN 0
WHEN work_hours LIKE '%[^-+ 0-9]%' THEN 0
WHEN isnumeric(CAST(CAST(work_hours as varchar(10)) as decimal(10,2))) = 1 then CAST(CAST(work_hours as varchar(10)) as decimal(10,2))
WHEN CAST(work_hours AS NUMERIC(38, 0))
NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE
1
END AS work_hours

FROM table
WHERE column1 = 'doe'

If I had six rows of data where the work_hours had the following values:

image_01

Then this SQL will produce the results as follows:

image_02

 

 

Microsoft Visual Studio – Fixing a Broken Toolbox (in Visual Studio 2010 SP1)

When starting Visual Studio 2010, it would hang for about 30 seconds trying to load the toolbox. I found this solution online and it worked for me:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

http://michaelcrump.net/fixing-a-broken-toolbox-in-visual-studio-2010-sp1

I was recently running into a situation where every time I opened Visual Studio 2010 SP1, the following message would appear for about 60 seconds or so:

“Loading toolbox content from package Microsoft.VisualStudio.IDE.Toolbox.ControlInstaller.ToolboxInstallerPackage
‘{2C98B35-07DA-45F1-96A3-BE55D91C8D7A}'”

After finally get fed up with the issue, I started researching it and decided that I’d share the steps that I took to resolve it below:

  • I first made a complete backup of my registry.
  • I then removed the following key: [HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftVisualStudio
    10.0Packages{2c298b35-07da-45f1-96a3-be55d91c8d7a}]
  • I went to the following directory: C:UsersYour Name HereAppDataLocalMicrosoftVisualStudio10.0 and created a folder called bk and moved the .tbd files to that folder (they are hidden so you will have to show all files). I then removed the .tbd files in the root directory.

SNAGHTMLcaac6ca

  • I then launched Visual Studio 2010 SP1 again and it recreated those files and the problem was gone.

Anyways, I hope this helps someone with a similar problem. I created this blog partially for myself but it is always nice to help my fellow developer.

Thanks for reading.

SQL Server Management Studio – Error message when you try to save a table in SQL Server: “Saving changes is not permitted”.

This problem occurs when “Prevent saving changes that require table re-creation” option is enabled.

Solution:

Go into Tools -> Options -> Designers-> Uncheck “Prevent saving changes that require table re-creation”.