DH Unlimited
Portfolio Blog Login
X

About
DH Unlimited is comprised solely of me, Dave. I've been doing freelance web development since 2010, mainly for the purpose of expanding my technical skill set.


Site Updated
01/06/2016

I'm Still Here

Friday, December 21, 2012 3:37 pm

The world didn’t end (yet).  It’s 12 – 21 – 2012!



Tag(s): Life 

SQL Server Maximum Key Length

Tuesday, December 11, 2012 11:18 am

Warning! The maximum key length is 900 bytes. The index ‘PK_BATCH_ORACLE_SHIPMENTS’ has maximum length of 919 bytes. For some combination of large values, the insert/update operation will fail.


This message bothered me until I found a way around it (kind of sorta).  Instead of using all 360 characters of a particular field to use as a key I chose to use only 320 (it’s a customer name).  I used a calculated column with keyword PERSISTED.


So…instead of using  


[customer] [nvarchar](360) not null


as my primary key I use part of it instead 


[customer_truncated] as left ([customer], 330) persisted


I hope this helps anyone interested in overcoming this issue (although I hope you can find better columns to build a primary key…).



Tag(s): SQL Server  Oracle  Work  Windows 

Divide by Zero

Monday, December 3, 2012 10:37 am

Instead of using a CASE statement inside a SELECT statement in SQL Server, I have found a better, cleaner method is to implement NULLIF which takes two parameters and compares them.  If they are equal then NULL is returned.   This forces the divisor to become NULL instead of zero.  And dividing by NULL makes the entire equation NULL.


For my project this morning I need to return 0 instead of NULL so I will also implement a COALESCE function which compares the output. 


So rather than use this logic below:


case
when isNull(divisor, 0) = 0
then 0
else numerator/divisor
end as value


I use: 


select coalesce( numerator / nullif( divisor, 0 ) , 0)



Tag(s): SQL Server 

SQL Server Date Conversions from Excel

Thursday, November 22, 2012 11:44 am

Going from Excel data to SQL Server data shouldn’t be difficult.  A common problem when doing this conversion is how to go from an Excel date like ’6/4/2011 11:34’ to a SQL Server date that is identical, but with a datetime data type.  The first thing to do in SQL Server is to convert the data from NVARCHAR to Float.  From there convert the float to datetime.  It really is almost too easy.



select cast(
	    	cast([column name] as float)
	    	-2 as datetime
            )
  from [table]



Tag(s): Oracle  Windows  SQL Server 

Error Running .exe files in Server 20008

Wednesday, October 31, 2012 2:43 pm

“Windows cannot access the specified device, path, or file. You may not have the appropriate permissions to access the item.”
Changing Group Policy did not change the error nor did changing file and folder permissions. One solution to execute the file is to:

1. Open a command prompt as Administrator
2. Navigate to the file location and run



Tag(s): Windows 
Back to the Top