I'm Still Here
The world didn’t end (yet). It’s 12 – 21 – 2012!
SQL Server Maximum Key Length
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…).
Divide by Zero
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:
when isNull(divisor, 0) = 0
end as value
select coalesce( numerator / nullif( divisor, 0 ) , 0)
SQL Server Date Conversions from Excel
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.
cast([column name] as float)
-2 as datetime
) from [table]
Error Running .exe files in Server 20008
“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