Contracting, while sometimes challenging, can be so rewarding.
I find myself celebrating with a beer at North Parade Street Festival in Bradford at the end of a contract with HC-One Ltd and thus looking for pastures new.
If anyone needs a SQL developer immediately, pass it on (use the form below)!
My contract was extended until April 2016 so I must have been doing doing something right.
The nagging thought at the back of my mind – was I settling into a rut?
I had not done any training for ages – so it was time to fix that and do some Microsoft Accreditations in SQL 2012.
All I needed to do was set up 4 virtual servers under Hyper-V and while I was at it I decided to upgrade 2 physical servers to Windows Server 2012 and 5 clients to Windows 10.
Then I needed to set up SQL Server 2012 and yes, of course, all the security patches needed updating network wide and as luck would have it I needed some new AV software for the servers and then my backup software needed upgrading and coincidentally the Windows Firewall was blocking ports and just to cap it all my printer wasn’t supported in Windows 10.
No problem, really – but this did take up about 2 weeks in the evenings.
Then on Wednesday Virgin broadband died for a day and Crin (@crinber – my lovely wife) was not best pleased that she had no Internet and could not play on her new phone.
So I got to work.
Virgin engineers fixed their problem on Thursday and I refreshed the hub and my router.
However, on rebooting, my secondary DNS, DHCP and Domain Controller server had the wrong IP address (which caused me to scratch my head as it was a static IP reserved in DHCP) so I had still lost the network and Internet access.
No worries – I would fix it that night and bring the network back online for the weekend so I have remote access through LogMeIn.
On Friday and after much buggeration it turned out that the Virtual Switch had swapped NIC’s on the server running Hyper-V thus disabling both cards (a bug I think, Microsoft).
Switching them back solved the entire issue and now I have a shiny new network.
Now I could start training in my “spare” time!
Blessed relief.. at last I am enjoying a week off after working as part of a team on a data migration and rollout of Microsoft Dynamics AX for Insight UK.
I personally clocked up ninety two hours of overtime during two weeks of being “on call” as the only DBA in UK due to the rollout falling on my boss’s prebooked (and well deserved) holiday.
For over two years I have been spending three hours a day commuting on Yorkshire’s congested motorways with all the attendant stress and escalating fuel costs.
However, from next week I start working locally for the Mitchell Farrar Group as “Lead SQL Developer/Administrator and Business Intelligence Analyst”.
Whilst I know this will be challenging I am very much looking forward to it – just after the break, that’s all!
There’s a little surprise booked for my wife: dinner, wine, bed and breakfast in the “Chocolate Room” at The Wensleydale Heifer which is a fine boutique hotel and restaurant in the Yorkshire Dales.
Succumbed to a Galaxy s 2 smartphone. However did I cope without one?
This script assists with the creation of the Conditional Split “Changed Rows” conditional expression for an incremental load in SSIS packages.
Simply copy and paste the output.
I must admit that I unashamedly copied the original script from David R Buckingham (many thanks) on SQLBlog.com and here’s the link to the original).
However, here I modified it to deal with multiple datatypes since editing and debugging in the “Conditional Split Transformation Editor” needs much improvement.
/*
This script assists with the creation of the Conditional Split “Changed Rows” condition
— be sure your results aren’t being truncated when you have a table with many columns
*/
— BEGIN SCRIPT —
USE master
GO
DECLARE @Filter varchar(max)
SET @Filter = ”
SELECT @Filter = @Filter + ‘((ISNULL(‘ + c.[name] + ‘)?’+
CASE WHEN c.system_type_id IN (35,104,167,175,231,239,241) THEN ‘””‘
WHEN c.system_type_id IN (58,61) THEN ‘(DT_DBTIMESTAMP)”1900-01-01″‘
ELSE ‘0’ END
+ ‘:’ + c.[name] + ‘)!=(ISNULL(Dest_’ + c.[name] + ‘)?’ +
CASE WHEN c.system_type_id IN (35,104,167,175,231,239,241) THEN ‘””‘
WHEN c.system_type_id IN (58,61) THEN ‘(DT_DBTIMESTAMP)”1900-01-01″‘
ELSE ‘0’ END
+’:Dest_’ + c.[name] + ‘)) || ‘
FROM sys.tables t
INNER JOIN sys.columns c
ON t.[object_id] = c.[object_id]
WHERE SCHEMA_NAME( t.[schema_id] ) = ‘dbo’ — enter tour schema name here
AND t.[name] = ‘MyTable’ — enter your table name here
AND c.[is_identity] = 0
AND c.[is_rowguidcol] = 0
ORDER BY
c.[column_id]
SET @Filter = LEFT(@Filter, (LEN(@Filter) – 2))
SELECT @Filter
–SELECT
— c.*
–FROM
— sys.tables t
–JOIN
— sys.columns c
— ON t.[object_id] = c.[object_id]
–WHERE
— SCHEMA_NAME( t.[schema_id] ) = ‘dbo’
–AND t.[name] = ‘DimUPRTable’
–AND c.[is_identity] = 0
–AND c.[is_rowguidcol] = 0
–ORDER BY
–c.[column_id]
–SELECT
— schemas.name AS [Schema]
— ,tables.name AS [Table]
— ,columns.name AS [Column]
— ,CASE WHEN columns.system_type_id = 34
— THEN ‘byte[]’
— WHEN columns.system_type_id = 35
— THEN ‘string’
— WHEN columns.system_type_id = 36
— THEN ‘System.Guid’
— WHEN columns.system_type_id = 48
— THEN ‘byte’
— WHEN columns.system_type_id = 52
— THEN ‘short’
— WHEN columns.system_type_id = 56
— THEN ‘int’
— WHEN columns.system_type_id = 58
— THEN ‘System.DateTime’
— WHEN columns.system_type_id = 59
— THEN ‘float’
— WHEN columns.system_type_id = 60
— THEN ‘decimal’
— WHEN columns.system_type_id = 61
— THEN ‘System.DateTime’
— WHEN columns.system_type_id = 62
— THEN ‘double’
— WHEN columns.system_type_id = 98
— THEN ‘object’
— WHEN columns.system_type_id = 99
— THEN ‘string’
— WHEN columns.system_type_id = 104
— THEN ‘bool’
— WHEN columns.system_type_id = 106
— THEN ‘decimal’
— WHEN columns.system_type_id = 108
— THEN ‘decimal’
— WHEN columns.system_type_id = 122
— THEN ‘decimal’
— WHEN columns.system_type_id = 127
— THEN ‘long’
— WHEN columns.system_type_id = 165
— THEN ‘byte[]’
— WHEN columns.system_type_id = 167
— THEN ‘string’
— WHEN columns.system_type_id = 173
— THEN ‘byte[]’
— WHEN columns.system_type_id = 175
— THEN ‘string’
— WHEN columns.system_type_id = 189
— THEN ‘long’
— WHEN columns.system_type_id = 231
— THEN ‘string’
— WHEN columns.system_type_id = 239
— THEN ‘string’
— WHEN columns.system_type_id = 241
— THEN ‘string’
— WHEN columns.system_type_id = 241
— THEN ‘string’
— END AS [Type]
— ,columns.is_nullable AS [Nullable]
–FROM
— sys.tables tables
–INNER JOIN
— sys.schemas schemas
–ON (tables.schema_id = schemas.schema_id )
–INNER JOIN
— sys.columns columns
–ON (columns.object_id = tables.object_id)
–WHERE
— tables.name <> ‘sysdiagrams’
— AND tables.name <> ‘dtproperties’
–ORDER BY
— [Schema]
— ,[Table]
— ,[Column]
— ,[Type]
This is a bloke with a wry look on life, see http://ennui-cooper.blogspot.com/2010/06/weve-only-got-rice.html
Happy days!
I am so very much enjoying my new role as a SQL DBA at Insight UK http://uk.insight.com
This company is really switched on and very responsive.
Immediately I am involved with SQL 200o, 2005, 2008 and a Business Intelligence project!
Plus I have all the software and system resources I need to do my job and all my connections use a single logon.
What a refreshing change!
Here’s a link to a goldarnit, new fangled Virtual Business Card
Here’s a nice gotcha – someone had limited the maxsize of the tempdb and it had filled up the available space so everyone was receiving the following error:
And here’s the quick and dirty fix:
ALTER DATABASE TempDB
MODIFY FILE (NAME=tempdev_02, MAXSIZE = UNLIMITED)
This at least allowed us to continue working – ensure you have a lot of disk space so it doesn’t fill up entirely!
This breaks the connection to the Internet somehow. I have seen this on two separate machines. Simply uninstalled it and put it in hidden updates. Comments most welcome.
Microsoft Update and KB960082 generally fails for Windows Server 2003 boxes and mine were no exception.
MS have a few “solutions” which entail manually running the fix in a command prompt, however they are full of inaccuracies and caused me endless hours of fun.
I think MS is a joke for issuing an Automatic Update which doesn’t , erm, automatically update…
Here’s the link to the MS update with its “known issues”:
http://support.microsoft.com/kb/960082/
At last I fixed the thing!
Add a comment or email JohnnyReaction@rickatson.com for the corrected method.
Ouch!
Never use dynamic disks on a server if you will need to resize the partitions later as most software (at least the stuff I can afford) will not do it. To convert back to basic disks the Microsoft way is a real pain so this link is a life saver:
http://mypkb.wordpress.com/2007/03/28/how-to-non-destructively-convert-dynamic-disks-to-basic-disks/
Hurrah!
I wasted all weekend reinstalling Reporting Servers 2005 on Windows Server 2003 R2 Enterprise with no luck – then this morning I did something in ten minutes to make it all work again.
Great! I guess I should attribute this to the power of REM sleep according to http://news.bbc.co.uk/1/hi/health/8090730.stm
Now I can go back to studying for the exam I have been postponing (70-445 MS SQL Server 2005 Business Intelligence-Implementation & Maintenance).
I have just upgraded my Active Directory Domain from Windows 2000 to Windows 2003 servers , hurrah!
One of the servers is running SQL 2005 Reporting Services and now I find that the Report Server is unavailable to my browser clients.
This is apparently something to do with the way IIS 6.0 uses Application Pools, so now I have to go and research that.
This should have been pointed out on the Windows Server 2003 Upgrade Advisor Tool.
Thanks, once again, Microsoft for your sterling efforts.
JohnnyReaction's Blog 