Wednesday, July 5, 2017

Time in 00:00:00 text format with the need to convert it to seconds then back

This is just a quick example of converting a text format "time" field back and forth into seconds so that summations and such can be done.

DECLARE @fauxTime VARCHAR(8) = '00:01:00'; -- we have a free form "time" field
DECLARE @fauxTimeInSeconds INT = (SELECT Datediff(second, Cast('00:00:00' AS TIME), Cast(@fauxTime AS TIME))) -- Convert it to seconds\
DECLARE @fauxTimeBackToTimefromSeconds TIME =(SELECT Cast(CONVERT(VARCHAR, Dateadd(ss, @fauxTimeInSeconds, 0), 108) AS TIME))
DECLARE @fauxTimeBackToHHMMSSfromSeconds VARCHAR(8) =(SELECT CONVERT(VARCHAR,Dateadd(ss, @fauxTimeInSeconds, 0), 108)) -- convert back to a character based time field
PRINT @fauxTime

PRINT @fauxTimeInSeconds

PRINT @fauxTimeBackToTimefromSeconds

PRINT @fauxTimeBackToHHMMSSfromSeconds

OUTPUT:
00:01:00 -- Original.
60 -- Converted to seconds.
00:01:00.0000000 -- Converted back into a time field.
00:01:00 - Converted back to a character field.

Friday, June 2, 2017

Error: 8624, Severity: 16, State: 116 -> When things go really wrong

If you are reading about this, it is likely that you just hit the error: Error: 8624, Severity: 16, State: 116

This is an error that means you are having a bad day, because somewhere in your code SQL was generated and that SQL isn't working.

If you haven't already figured out what the SQL is, the pro tip is this: If it the SQL is like 100 lines of joins, then it has some IN statements and in that IN statement is a 100 lines of joins, then that is likely the SQL you are looking for.

If you can't find the SQL with that tip, then go through SQL Server Profiler and figure out where it is.  Remember, somewhere you have a mess that looks something like

SELECT * FROM table join (lots of other stuff()  where mykey in (select mykey from table join (lots of other stuff)) over and over and over.

Now that you have found this code, here are my recommendations:

Figure out how to craft the SQL better so that it isn't using so much logic.

The place that I found this problem was with code that was generated dynamically by other code, and my suggestion is that you fix the underlying problem of having generated unusable code. If you can't do it, you have an option to "just make it run" but this option is going to trade the code working, for very slow code.

You can put an option called "OPTION (FORCE ORDER)" at the end of the code, this will FORCE SQL Server to execute the code with the most naive execution plan possible.  This isn't great, in fact, it is really bad. Any code with that many joins is probably going to be slow even in a perfect world, but it will work.

Here are some good articles I found on this when we hit this problem where I work.

https://blogs.msdn.microsoft.com/sqlserverfaq/2014/07/15/troubleshooting-error-8624-severity-16-state-21-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan/

https://www.simple-talk.com/sql/performance/controlling-execution-plans-with-hints/

Tuesday, March 21, 2017

Count vs Count distinct in SQL

Had a new coder ask me how count works, vs count distinct. So I made a small example:

create table #tmp_example
(
       id int identity(1,1)
       , value varchar(255)
)

insert into #tmp_example
(
       value
)
select '1'
UNION select '2'
UNION select '3'
UNION select NULL


select
count(*)
, count(distinct value)
, count(distinct case when value = 1 then null else value end)
from #tmp_example


In this example it returns 4, 3, 2

4 because there are 4 rows (*) in the system.
3 because there are 3 distinct values where NULL is not counted a value in count distinct.
2 because there are 2 distinct values when 1 is cast as null during the counting process.

Saturday, January 14, 2017

Google Chrome does not work but Internet Explorer does

This is for those of you who have gone through the effort of checking the internet explorer->internet options-> and UNCHECK the proxy server, and it didn't work for you.

Another possible solution is to flush the DNS cache of your computer. Sometimes Google just outright loses the ip it needs to connect to and won't work on getting a new one.

Go to the command prompt by going to the start menu, and in the command box tying "cmd" this will bring up a dos box.

In the dos box type "Ipconfig /flushdns" and see if that works for you.  I have seen people think I am "amazing" for knowing how to do this, but this used to be quite a common problem in older versions of windows, and the trick still works sometimes with common connectivity problems.  Now why it allows one browser to work when the other won't... that still confuses me, but there you go.

Monday, November 21, 2016

Advanced Debugging in SSIS no Script Task Related

http://www.techbrothersit.com/2013/12/ssis-how-to-watch-value-of-variables.html

I wanted to just have this out there.  More of a re-blog because it needs to be pushed up.  This is how you do this properly when you are having an issue and are unsure of the exact value of a record. I also tend to log every variable using a script task as well during run time so that I can review the ssis logs for it.

Thursday, November 3, 2016

In Place Updates of Two Fields To Each Other in SQL

Not really sure how to classify this, but there are cases when you want to do an update of data in your system where you update one field to another field and set the other field to NULL.  Those of us that have had to approach this problem before know what the answer is, but I decided to create an example.

So the situation is this:  I have a table with two columns, and I want the first column to be overridden by the second column, and the second column to be set to NULL, if and ONLY if the second column is NOT null.  



The final output I want to be is this:

As you can see, Four was not overridden by Null, but One and Two in Column UserOne were updated by the values in the field UserTwo.

The reason I thought this was interesting as a problem was because of the question I was asked by a junior developer asking how he would track the update step to make sure that he updated the fields to NULL and I realized that I had never really thought about it because SQL Server handles this situation quite elegantly.  You don't HAVE to track what was updated, when doing the update it will use the values that are available at time of update, it does not do in order updates at all.

In reality the only statement you have to use is provided as a single update in the example below.  Order of the columns being set doesn't matter either.

IF object_id('tempdb..#tmp_testexample') IS NOT NULL
BEGIN
   DROP TABLE #tmp_testexample
END

create table #tmp_testexample
(
       ID int identity(1,1)
       ,UserOne varchar(255)
       ,UserTwo varchar(255)
)

insert into #tmp_testexample
(

       UserOne,UserTwo
)
SELECT
'One','Two'
UNION
Select 'Two','Three'

UNION SELECT 'Four',null

SELECT * FROM #tmp_testexample

select
*
from #tmp_testexample
where UserTwo is not null
       and UserOne <> UserTwo

update tta
set tta.UserOne = tta.UserTwo
, tta.UserTwo = NULL
from #tmp_testexample tta
where UserTwo is not null
       and UserOne <> UserTwo


select
*
from #tmp_testexample

This produces the output:

Original Data Set:

ID
UserOne
UserTwo
1
Four
NULL
2
One
Two
3
Two
Three
Example of what I want to update:

ID
UserOne
UserTwo
2
One
Two
3
Two
Three
Final Result after single update statement:

ID
UserOne
UserTwo
1
Four
NULL
2
Two
NULL
3
Three
NULL