First SQL Saturday Presentation

sqlsat189_webOn April 13th, I had the opportunity to present at my first SQL Saturday event in beautiful San José, Costa Rica.  I also presented in Spanish, which was a challenge for me as I do not have much experience working with Spanish technical lingo.  I would like to do more in the Spanish community and I thought this was a good way to get started.  My father is also from Costa Rica and I could think of no better reason to go down and visit with him and get some technical training to boot.

My presentation was entitled fair or foul (on side or offside) and used the soccer analogy of being offside to determine our system perform–either you are in or out and if you are out, we have to make a change.  We reviewed the SQL Profiler, Pal Tool, and several DMVs and discussed how we can collect performance information from SQL Server.  I was in a great slot just before lunch, but had what I thought was the toughest lineup of the day–Eduardo Castro, the organizer and holder of a PhD in databases, Andy Leonard, and Kenneth Urena–a local speaker who is one of the more active members of the community all at the same time as my session.

475997_589177391101038_1820429765_oI was pleasantly surprised by the attendance and glad I gave the presentation in Spanish.  I did stumble a few times, but I would simply say the word in English and the entire audience would respond in Spanish.  The biggest soccer rivalry in the country would take place on Sunday so we had a little fun talking about it and fanning the fanatic’s flames.  I had one of my daughters with me (pictured at right) and let me assure you the other attendees appreciated the material more than she did.

903085_589172027768241_440006347_oIn addition to meeting Eduardo, I also got to meet Russell Fustino of Component One and he even convinced me to be on the Russ Cam! I can’t wait to see how it turns out.  It was my first interview on camera and made me realize I need to practice that another time or two.

My goals for going to this SQL Saturday were two fold–first, give a presentation in Spanish and become more familiar with the tool sets in Spanish.  Second, to meet the community members and see how I can become more involved.  I was able to meet both of these goals and I hope to present a webcast in one of Eduardo’s sessions shortly.  !Pura Vida!

Posted in Presentations, SQL Saturday | Leave a comment

Organizing SQL Saturday RVA

SQLsat_richmondCroppedThis is a little late in coming; however, I wanted to document my experience planning and putting on a SQLSaturday event.  While officially put on by the Richmond SQL Server Users Group, ‘my’ event took place March 9th, 2013 at the University of Richmond.  The work was challenging and sometimes painful, but I am already thinking about organizing another–even better event next year.

Start with Vision

It is sometimes amazing to think that Andy Warren and company were sitting around one day and said–You know, we ought to put on a free Saturday training event . . . and it spread like wild-fire.  I had attended a handful of user group meetings when I started attending regularly in December of 2011.  It was there I learned about SQLSaturday, although I wouldn’t attend my first until June of 2012 in Philadelphia.  I was impressed with the content and winning a book didn’t hurt my experience.  I started talking with other members of the Richmond user group and there seemed to be enough interest in putting one on, so we jumped in.There would be two main objectives–grow the pool of local speakers by encouraging them to speak and provide a well-organized day of training for the Richmond Community.  We always focused on the latter as it was the big fish to fry, but we tried to circled back to supporting the user group.

Plan the Work, Work the plan

I am unsure how the early adopters managed to pull it off–I am not sure what I would have done without Karla Landrum.  While she won’t find sponsors or ask people to speak at your event, the invaluable experience of being battle tested is so important.  We would bounce ideas off of her and she directed us in what steps to take at certain times and every time we took her advice it was the right decision.  In fact there was one area with the SpeedPass we did NOT take her advice and it bit us in the butt.

Never to early

While doing these every year will reduce the time an organizer/user group needs to put on an event, I am surprised at how many events go up with only 3-4 months notice.  Our event went ‘live’ about 6 months in advance and I thought we needed all of that time to prepare.  Occasionally, we just needed some time for an idea or concept to marinate until we felt comfortable as a group.  Other times, we need time to find someone to assist us with a task.  Starting early also helped in the coordination with the venue.  UofRContinuingStudiesWe were lucky to have a gracious host in the University of Richmond and the School of Continuing Studies; however, this type of event was new to them and we needed time to work out details and ensure we met the requirements of the University.  As I mentioned, I am already thinking about next year and I think by July we will need to start putting together the team if we hold the event in March.

Help is out there

Team RVAThe best compliment I received about the event was “You had lots of people helping and it showed.”  While others have organized an event outside of a user group, my thoughts are you should take it to the user group (assuming there is one) and get buy in from them.  Sure, not everyone will help; however, the leaders normally help or provide input on organizing an event, getting local sponsors, and speakers as they have to do this on a regular basis for the user group.  Also members that have been to a SQL Saturday are normally easy pickings for at least the medium level tasks.  Local business sponsors can help promote the activity as they can show their involvement in the community, so don’t be afraid to approach them with a request to send out an email to their network and put some verbiage on their website.  After a successful event they are eager to help again and hopefully in an increased capacity.

???????????????????????????????

Volunteer Heroes

I had the privilege of working with a small core team.  Between us we share the load of the largest responsibilities and brought in others for day/week of needs.  Wayne Sheffield b|t and Geoff Johnson b|t were my two biggest supporters.  Many hands make light work and it was no exception with this.  Having a team around you lets you share experiences and increases the number of reviews an idea gets.  While the men represented the core organizing team, I had another team of ladies made up of mostly of spouses that helped with day of needs.  It was impressive to seem them work and immediately resolve issues that came up.

Speaker Dinner and gift

Nothing made me more nervous than the speaker’s dinner and gift.  We had a great line up of A list speakers–and I was scared to death of what they would think.  It didn’t help that someone had volunteered to organize that part and then fell of the face of the planet.  Richmond is a big small city and there are limited spaces for a party of 35 on a Friday evening.  While unintentional, there is also some pressure to come up with an original gift or idea because many of the speakers have been around for a while and each event is publicized about the good and not so good things that happen.  At the end of the day, what makes the SQL community so great is the willingness to help each other and while our dinner had a few issues, everyone enjoyed getting together and talking.  I had heard it before, but the speakers really are there to help out the community and this was my experience.  While they all seemed to enjoy our gift, they truly appreciated the organization of the event, the preparation of the room they were in, and the people in their sessions.

Day of the Event

We did not have a pre-con before the event, so I had taken most of the day Friday to review everything and get the items I needed to bring to the event.  Because we had user group buy in, we had put together all the programs/swag bags the Thursday prior.  Geoff was the volunteer coordinator and had the registration items and Wayne had the speaker items.  I had a van load of kids so it was good that I didn’t have too many items to bring.
We were the first to arrive that day at about 6:30 AM.  The school was such a big help and the tables were already set up so there was minor physical arrangements to make.  We had made assignments beforehand for the coffee pickup, sign posting, and registration needs.  I shouldn’t have been surprised–after all this is the SQL Community, but several people got there soon after me and offered to help.  We soon had people unpacking cars and bringing in needed items.

The rest of the day passed quickly.  We had a few problems with Standby attendees so attending a morning session was out of the question; however, I did take the advice of some other organizers and squeezed in parts of a couple of afternoon sessions.

WIT Forum

WIT Forum

I had my family there and I made my oldest daughter, 10, attend the Women In Technology forum with me during lunch.  It might be a few years before she appreciates it.  :)

It always amazes me how many people stay for the entire day, but I guess that is why we save the prizes for the end.  We had a good assortment of prizes and we are so grateful to the sponsors for helping us to make it happen.

Lessons Learned

SponsorsImageThere are a few areas in which we can improve, but they are dwarfed by the goodwill of the attendees, sponsors, and speakers.  If you are willing to put in the time (and emails) it is a worthy en devour.  Don’t forget about listening to Karla.  Don’t forget to have the coffee there early–even if it means two trips, one of the first rush and one of the rest of the morning.

I am glad I was able to play a small role and I hope to see you on the SQL trail.

Posted in Conferences, SQL Saturday | Leave a comment

Excel Connection Manager

I find myself needing to import files from time to time and usually use SSIS to import the data–normally to a temp table.  I did so the other day and got this error.

SSIS package “MyTest.dtsx” starting.
Information: 0x4004300A at 2008 10K, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC00F9304 at MyTest, Connection manager “Excel Connection Manager”: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at 2008 10K, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.Error: 0xC0047017 at 2008 10K, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at 2008 10K, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at 2008 10K: There were errors during task validation.SSIS package “MyTest.dtsx” finished: Failure.

I normally just run the package in Visual Studio, so I changed the 64 bit option of the package property and that allowed me to execute the package successfully.  Should you need to save the package and run it in a scheduled job, you will need to make this change elsewhere as noted here.

SSISPropertyConfig_64Bit

Posted in Uncategorized | Leave a comment

Dropping a publisher when the distributor is down

Working on a new server, I came across a replication process that had been forgotten about and the pieces were disconnected.  The distributor server/process had been installed on another server and when I went to remove the publisher from the database, I got the below error.

DECLARE @publication AS sysname;
SET @publication = N'SmokeTest';

-- Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication;

OLE DB provider “SQLNCLI10″ for linked server “repl_distributor” returned message “Login timeout expired”.OLE DB provider “SQLNCLI10″ for linked server “repl_distributor” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.

The linked server was pointing to a server that no longer existed and I thought I was stuck.  I tried lots of options, but ultimately came across this blog that demonstrated how to forcefully remove the publisher when I couldn’t connect to the distributor.

Spoiler: The trick is to use the @ignore_distributor=1 parameter and you should be good to go.

DECLARE @publication AS sysname;
SET @publication = N'SmokeTest';

-- Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication<code>
,@ignore_distributor=1</code>;
Posted in I learn something new everyday, Scripting | Leave a comment

You can’t shrink a logfile when . . .

I was asked to look at a database server for the first time.  One of the tasks I was given was to shrink the log file of a database as the previous owner could not.  The DB was only 10 MB, but the log file was 50GB.  No problem, I say to myself–like taking candy from a baby.  This baby; however, was trained in the art of MakeMeCrazy.

Most of the advice out there prompts you to find the root cause of the growth as the log is likely to grow again so shrinking the log file is not a fix, just a band-aid.  I should have pondered this question longer.  This server was running SQL 2008, so the truncate_only option wasn’t available so I changed the recovery model to simple and was then going to shrink the log–only the database was already is simple mode.  I tried shrinking the log and the command executed but did not change the size of the file.

Strange–why would 10 MB DB need a 50 GB log file?  Can you think of a reason?  I admit I was dumbfounded and actually had some strange ideas pop into my head.

 

The reason??  It was setup as a publisher at one point and had been forgotten about.  I removed the publisher and bam–the log shrunk right down.

Posted in I learn something new everyday | Leave a comment

Importing or Exporting to Access

I had a client request to export some data to Access 2010 for their downstream use.  I have done this in the past and thought it a no-brainer; however, there is always a little trick–hence the post.  :)

Because I had already installed Office 2010, I assumed the Microsoft Access Database Engine for 2010 would get me what I needed.  Because I was using a new machine, I had to install the 2007 Office System Driver: Data Connectivity Components.  After I did that, I could see the Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I put in the location of the access db, and I was off and running.AccessEngineProvider

Posted in I learn something new everyday, SSIS | Leave a comment

Upgrading SharePoint List Adapter in SSIS

A server upgrade prompted me to migrate some SSIS packages.  These packages were going from SQL 2008R2 to SQL 2008R2, so there was no change there; however, the new server had a different version of the SharePoint List Adapters, which allow you to easily write to and get data from SharePoint Lists.  When I opened the package on the new server, the list adapter was not recognized and I got the following error.

ListAdapter Error“Error 1 Validation error. %Job Name% The component metadata for “component “SharePoint List Destination” (529)” could not be upgraded to the newer version of the component. The PerformUpgrade method failed.   %Job Name% 0 0″ 

The ‘version’ of the adapter was same; however,SharePointListAdapter I was using an updated release as shown here by the release date for the adapter.  Upgrading was straightforward, once I found this post on codeplex.  My experience was going to the package in Windows Explorer and opening the file in Textpad.  Now I guess I should lament here that I didn’t do it in PowerShell, but one thing at a time here!  I then replaced the PublicTokenID as mentioned in the post.  I saved the text file and had to re-open the SSIS project.  There is a newer version of the file itself and one would need to update the Version and the PublicTokenID if upgrading to the latest version.

The last step was to add a SharePoint Credential to the List Adapter as this is a new feature.  I right clicked in my connection manager and choose new connection.  I was then NewSharePointConnectionable to choose SPCRED and enter the appropriate information.  My package was converted to the new server and worked well.

Posted in I learn something new everyday, SSIS | Leave a comment