Skip to main content


Showing posts from July, 2010

The week that was Friday 23 July 2010

Its been a busy week this week. From troubleshooting several production issues to writing some incremental load SSIS scripts, the week has flown by. I Still haven’t managed to get my replication post out the door, and after re-reading and re-working again I think it will be a few weeks yet before it makes it on to the blog. If it doesn’t get dramatically better soon I may just ‘can’ it altogether.I intend to write some posts on the incremental data loads I have been working on this week, they will be written and published in the coming weeks, I have a few other ideas for technical posts and they will be written in the near future.On the home front, Mrs Ellis and I were hoping to make it to an outdoor theatre production this week bit the weather has been terrible with heavy rain so we haven’t made it.  The puppies on the other hand love the rain they love swimming in the lake that is close by, they love rolling in the mud and getting dirty. Trouble is they now stink so I think it may b…

Raining Tuesday – Cricket Cancelled – Replication Stinks - SQL BITS VOTE NOW!

Well this was going to be replication post, but I have read it, re-written it, read it, re-written and it still stinks. When I got fed-up of reading and re-reading my stinky replication post I turned on twitter and read a few tweets by active SQL Server community members in the UK, people like Simon Sabin and Neil Hambly and Gavin Payne. They were talking about the SQL BITS conference being held this autumn in York. SQL BITS is a great conference we don’t get many of them in the UK but this is really well run, well organised and well worth your support if you are a database/sql server professional here in the UK.All the speakers have submitted their sessions, and now its up to us, the attendees to vote on what we want, there is over 90 sessions to chose you simply have to register and you get to vote for ten sessions, I think  the sessions with the most number of votes when voting closes gets picked, democracy rules, think of it as the SQL X-Factor (or SQL Idol for my American friends…

The week that was: Friday 15th July 2010

This is just a short post for a Friday, I have no technical content ready to publish today but lots and lots of ideas and a couple of drafts. This week I have looked a little at replication, I have a few posts lined up next week on that.  I have also been doing some initial prep work on migrating quite a large SQL Server 2000 database to SQL Server 2008 and I intend to write some posts around that too.
I had a TSQL2sday post go live on Tuesday all about learning, you can find my post here. I have read some of the posts already, not all of them yet, I like to wait for the round up post from the host before reading them all. Robert said on twitter that there were over 30 submissions so I guess that post may take a while to come out.
This weekend I have a whole bunch of DIY to do…I hate DYI but it needs doing. I’m playing cricket this Saturday for Senghenydd Cricket Club,  my two Golden Retriever puppies (They are dogs really but will always be puppies to me) will want to do something n…

TSQL2sday #008 – Gettin’ Schooled

This months TSQL2sday is being hosted by Robert Davies of MCM and Microsoft fame, you can find his post here. This month’s topic is titled ‘Gettin’ Schooled’ and for those participating in the blog party this month we will be looking at how we learn and how we teach.This fits in quite nicely with my post from last Thursday where I wrote a short note on the SQLBITs conference being held in the UK this Autumn. How do I learn?I have been working with SQL Server since 2002, for most of that time as a DBA. This post will focus on the learning that I have undertaken since becoming a DBA. I know we all went school, some of us went to university, some of us may have even taken higher degrees in the form of Masters or PhD's etc. There has actually been quite a lot of debate and quite a lot written about whether you need college/university degree to be a DBA and I don’t want to go down that route and discuss that topic here, instead I want to look at the type of learning I think most DBAs …

SSIS - Enabling Logging

Have you ever had a scheduled run of one of your SSIS packages fail? Have you ever wished you had a little more information available to you when troubleshooting? SSIS has a neat little feature that you can enable called 'Logging' and it does pretty much what it says on the tin. It logs the execution of your SSIS packages, you have a variety of logging options to choose from including logging to a SQL Server database itself, and if the worst happens and the execution fails you can gain some useful information regarding the reasons behind the failure.Setting Up LoggingYou can enable logging within your package. Open the SSIS package in BIDS (Business Intelligence Design Studio)My SSIS package has two control flow tasks 1. Execute SQL Task which is used to truncate a staging table and 2. A Data Flow task which copies some data from the Sales Header table in the Adventure works database to a staging database.Right click on  some open space on the control flow tab and select <L…

SQL Server Agent – Proxy error

I was setting up some SQL Server agent jobs in a test environment. I needed one of our testers to be able to run the job on demand. I made the testers account the job owner and I created a proxy for the the job to run under. I granted  the proxy appropriate rights for SSIS. The SSIS package needed permission on the share I added the testers account to the “SQLAgentUserRole” and asked the tester to try the jobs.The job failed with the following error:“Proxy (11) is not allowed for subsystem "SSIS" and user "xxx\xxxxxx". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.
The 'sp_sqlagent_is_member' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (Microsoft SQL Server, Error: 14516)”
This failed because the tester’s account did not have permission to use the proxy. To fix this I run the following script.
USE msdb ;

EXEC dbo.sp_grant_login_to_proxy

SQL Bits 7 – The 7 wonders of SQL

I have been procrastinating this evening, I have several blog posts that I want to write but the football is on TV and my wife is writing up an assignment for her Master’s degree  and she wants me to proof read her work. So while I’m waiting for the next instalment of her assignment I thought I would do my bit to promote the  SQLBITS conference. I have another post all about learning that I intend to write this weekend for TSQL2sday but this is simply a blatant plug for the SQLBITS conference. This is lifted from the website:“We are pleased to announce SQLBits - The 7 Wonders of SQL conference, which will be taking place from September 30th to October 2nd at York University. SQLBits is the largest SQL Server conference in Europe and the previous six conferences have established it as a must-attend event for DBAs, developers and BI professionals who work with Microsoft SQL Server in the UK and Europe. We are making this the biggest event ever with capacity for over 500 SQL…