Newest Regional Mentor for PASS

This month I am pleased to accept the invitation to serve as a Regional Mentor for the Mid-Atlantic region of the PASS organization. It was not that long ago I stepped into my first chapter meeting in Richmond and discovered the unique mix that is the SQLFamily. Many local meetings, several SQLSaturdays, and the SQLSummit have only added to the experience and I am happy to be able to share some of these experiences with others.

As a regional mentor, I will serve as a resource for the local chapters in providing help as they operate their chapters. I will be looking to increase or strengthen the number of chapters in the region and maybe even add another SQLSaturday to the mix (looking at you Norfolk or Lynchburg). I am looking forward to it and as always–I will see you on the SQL trail.

Finding Active Heap Tables

As part of a review of a system, I look for tables without clustered indexes; however, invariably I will provide the list of tables to a development team and they will say “These are old tables we don’t use anymore”, so I am forced to provide additional statistics about the number of times a heap table is used in the database.  Based on a check found in Brent Ozar’s sp_blitz script, I pull usage information about the heap tables.  If there is no usage, they are not reported.

EXEC dbo.sp_MSforeachdb 'USE [?]; 
      SELECT DB_NAME(), t.name as TableName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_scan
      FROM [?].sys.indexes i 
		INNER JOIN [?].sys.objects o ON i.object_id = o.object_id 
		INNER JOIN [?].sys.tables t ON o.object_id = t.object_id
        INNER JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id 
        INNER JOIN sys.databases sd ON sd.name = ''?'' 
        LEFT OUTER JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id 
      WHERE i.type_desc = ''HEAP'' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL 
      AND sd.name <> ''tempdb'' AND o.is_ms_shipped = 0 AND o.type <> ''S''';  

Happy Hunting

Formatting Date Parameter In SSRS

DefaultValuesParameterMy report runs a stored procedure that accepts a date as a parameter. I want this date to have a default value of the current date–or the date when the report is run. I can do this by creating a parameter to put into my dataset. I then click on the Default Values tab and choose the Specify values option.

DefaultValuesParameterExpression

 

In the value drop down, I click the Fx button and enter the function of =Today(). This will give me a default date with no time. If I wanted the time included, I can use the =Now() function.

Installing SQL 2005 Service Packs

Now hopefully no one out there will need this post–especially because SQL Server discontinued support in 2011; however, because I learn something new everyday I feel compelled to put his post up. I came across a server that had automatic updates on and was trying to install SP2 for SQL Server 2005. It would error out and SQL Server would be shutdown. I was trying to install the update for SQL Server and the Analysis Server services.  The error in the log was
Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.
When I tried installing the service pack manually, this error displayed in the GUI.
Service Pack 4 for SQL Server Database Services 2005 ENU (KB2463332)'
could not be installed. Error code 1603.

The resolution was to delete some registry keys, which is still gives me the Hebe-geebees, and these were the keys.

SQL Server Entries

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\AGTGroup
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\FTSGroup

Analysis Services Entry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\ASGroup

After I did this the installation went as expected. Now I just have to upgrade these bad boys.

Remotely Changing your Password

As I often work remotely from client sites and connect via VPN and Remote Desktop services, I am not in the position to run ctrl + alt + delete and change the password.  In order the change the password via a Remote Desktop session, you enter ctrl + alt + End and this will bring up the change password prompt.

Happy Trails.  :)

Creating a Linked Server to Oracle

I was recently asked to set up a linked server to Oracle and this is something I have done in the past.  No problem I thought.  To connect to Oracle you need the Oracle Data Access components and I has asked the Oracle DBA if they were installed.  He said yes and that everything was configured on his end.  I referenced David Browne’s blog for a quick review and ensured the Oracle directory was in the PATH variable.

I then proceeded to create the linked server got the following error–ORA-12514:TNS:listener does not currently know of service requested in connect descriptor

Oracle_HomeThere ended up being two issues–the first being, the TNSNames.ora file did not contain the entry for the database I was trying to connect to.  I guess the Oracle DBA didn’t think that was part of his setup.  Secondly, for some reason, the Oracle_Home variable was not set–it was blank.  I am not sure how that is possible; however, once I put the correct path in, I was golden and off and running.

SQL Saturday Orlando

September 14, 2013 was a busy SQLSaturday day with 6 events across the globe.  I happened to find myself in Orlando Floria–the home of SQLSaturday events and I admit I wanted to check out what the originators had planned.  With the increased number of SQLSaturday events, there is an increased awareness of what people like and what people don’t like and I figured the Orlando event would be completely rock’in.  With an organization team of Karla Landrum, Andy Warren, and Kendel Van-Dyke–all superstars in their own right, I was anxious to see their handiwork.  I have to admit I have developed somewhat of a complex at the attitude of some of the speakers and participants about what is and what is not at some events and makes me worried about my own RVA event.  I just knew the Orlando event would be THE event.

A last minute cancellation gave me a presentation spot and I was happy to take it.  I gave my fair or foul presentation and I may have to pick a different analogy.  Not too many sports fan in that session.  :)

There was a good turnout for the event, and when you have Mickey Mouse in your backyard, there is a lot of competition for your Saturday hours.  The event was at the Seminole State College and while it was roomy and let lots of light into the building, it was wasn’t ornate.  The vendor area was cozy, but each vendor had a tablecloth and the familiar basket to deposit your SpeedPass.  We used three floors of the school and while the school had a normal layout, it took a little trial and error to find your way to your session.

Lunch was served outside as was the end of day raffle.  All the speakers were friendly and were asked to help serve lunch.  I enjoyed catching up with folks I had seen during the day and serving food provided another opportunity to visit and get to know them.  Andy Warren is one of a kind and always one to speak his mind and he was true to form that day as well.

It occurred to me as we were wrapping up, that I have been thinking a little too much about what everyone will think about my event when I know if I put my effort into providing a nice environment for a free day of training, everyone will enjoy it and the community will be better for it.  I am so happy to have been had the opportunity to be at #SQLSat232 and for the reminder of why we put these events on.

SQLSaturday #254 EL Salvador

IMG_0062

The University banner.

On October 5th, I had the opportunity to go to San Salvador, the capital of El Salvador, to attend SQLSaturday #254 organized by César Oviedo.  This was the first SQLSaturday in that country and the event has hosted by the Universidad Tecnológica de El Salvador, a private university that offers various degrees–not exclusively technology related.  The university welcomed us with open arms and welcomed us like foreign dignitaries making an official visit.  As the technology sector has been weak in El Salvador, the university was amazed that professionals from outside of country would come on their own dime to speak about technology.  The event was in the national paper and a radio interview was arranged with the school and the organizers.  With schools in Nicaragua and Honduras, the university is considering having events at those locations as well.

Everyone was very excited and the excitement was palpable.  There was a formal introduction with the president of the school and the technology director.  There were several people taking pictures and I hope to put some up soon.

We came to understand the university offers classes 7 days a week, so in addition to our being there, we were able to see some of the other classes going on at the same time.  We had three tracks, down one due to some last-minute changes, and each speaker was asked to give two sessions.  As always, those who attended were exposed to new concepts and ideas and there was an earnest desire to learn.

The food was very good--yucca con chicharones

The food was very good–yucca con chicharones

With Wayne Sheffield’s b|t permission, I modified his Temp Tables–What you need to know session to Spanish and gave my Fair or foul presentation as well.  The sessions were well received and students asked some good questions.  I had modified my presentation to include a game of sorts where I asked the audience their thoughts on certain syntax and put a correct or incorrect image on the screen after they answered.  Before I started setting up, they asked me if they could have my presentation as the room had computers for each student.  I said yes, without thinking and it was not until the second question was answered immediately I knew the ‘game’ was up as they each had my presentation.  :)  One of the words I always have saying is stored procedures (procedimientos almacenados) and the students had a chuckle when I got tongue-tied.

The organizer of the event was César Oviedo t, who is from Costa Rica.  I was impressed that he was able to organize the event remotely and the way everything came together.  The SQL community never ceases to amaze me at their willingness to share and help others.  I hope those who attend and speak will continue to appreciate the efforts required to put on these types of events and will be slow to criticize an organizer. (Especially other speakers)  At the event, I met Adrian Miranda t, Ahias Lopez t, and Jose Redondo t for the first time and they are great folks.  If you get a chance to meet them, don’t pass it up.

It is nice to see the LATAM region growing and I hope to be able to participate in more community events.  See you on the SQL trail.

Verizon Fios setting

I recently switched to Verizon FIOS and spent more time than I care to admit getting my printer set up.  The printer is attached to the network and was working previous to my change; however, when I tried to connect to the printer from my laptop I would get a connection failure error.

The resolution was to disable the IGMP Proxy under the advanced settings and ta-da!, I could connect again.

IGMPProxy

SQL Saturday Presentations

I have had the opportunity to attend several SQL Saturdays this year and have enjoyed each one.  My summer was taken up with work, the Boy Scout Jamboree, and family so it has been several months since I have been to one; however, I am super excited to go to Orlando this weekend and be a part of #SQLSATORLANDO.

Originally not on the lineup, I was asked to speak due to a speaker cancellation.  I will be presenting “Your SQL Server–Fair or Foul”.  Florida is the home of the original SQL Saturday events and I will enjoy seeing what they do in their event.

Next month, I will be going to El Salvador to present in their SQL Saturday.  I have been trying to break my way into the Spanish countries as I hope to be able to work in that area at some point.