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.

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

SSIS Conditional Logic Based On Stored Procedure Result

I was looking to implement some conditional logic in and SSIS package. In my case, I wanted the package to halt if some validation rules were not met. The logic for these validation rules are found in a stored procedure and I needed a way to return a flag to the SSIS package the success r failure of the validation. I found I could do this with the Result Set tab on an Execute SQL Task. Here is what I did to set this up.

First, I created a stored procedure with the validation rules. For simplicity, I have created a stored procedure that returns a value. You will notice I have named the returning result ‘ReturnResult’.

CREATE PROCEDURE mysp_ReturnAValue
AS
BEGIN
    DECLARE @ReturnVariable SMALLINT
    SET @ReturnVariable = 1

    SELECT @ReturnVariable AS ReturnResult
END

ConditionalLogic_ProcessOverview

Second, I created an SSIS package with a variable of type Int32 named ProcedureResults and an Execute SQL Task component. After establishing the connection on the SQL Task, I changed the ResultSet to Single Row.ConditionalLogic_ExecSQLTask I click on the Result Set ‘tab’ on the left and link the returning value from my stored procedure to the variable in the package.

ConditionalLogic_ResultSet

Third, I added two data flow tasks and linked them with a precedent constraint so the Execute SQL Task would be the first item executed. I only want a data flow tasks to execute based on the results of the stored procedure.

Fourth, I modified the Evaluation Operation of the constraint to be of type Expression and I added and expression it should use when the package runs. In the success constraint the value was @ProcedureResults > 0. ConditionalLogic_ResultSet

The failure contraint? You guess it, @ProcedureResults <= 0 .

**Interesting sidenote.  My stored procedure returns a SMALLINT.  When I created this on a client system the stored procedure returned a SMALLINT data type, just like my example above; however, the Execute SQL task was unable to process the result set.  What happened was no matter what I returned in the stored procedure, the package would stop at the Execute SQL Task and the package would succeed.  It was not until I changed my SMALLINT to an INT that it was able to process the result and continue in the package.  When I created the package on my local machine, I was unable to recreate the issue.  I haven’t been able to put my finger on the difference between the environments, but it seems to have gotten fixed somewhere.  :)

Free Space In Database Files

File growth can cause system outages as SQL Server will have to wait for the file to grow before it can resume operations.  Of course, this depends on the size of the database, the growth rate, etc; however, the size of your databases is something you will want to keep an eye on.  The below script gives you this information and so you can better monitor these rates and grow your database during the maintenance window.

This query will only grab the information for the database you are connected to.

SELECT  s.name AS [Name]
<pre>, s.physical_name AS [FileName]
, s.size * CONVERT(FLOAT, 8) AS [Size] /*File size on Disk*/
, CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT)
* CONVERT(FLOAT, 8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8))
/ 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(FLOAT, 8)
- CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)))
/ 1024 AS 'Available Free Space In MB' /*Matches SSMS file report*/
, (s.size * CONVERT(FLOAT, 8)
- CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8))
/ (s.size * CONVERT(FLOAT, 8)) * 100 AS 'Percent Free'
, growth
, max_size
, state_desc
FROM    sys.master_files AS s
WHERE   s.database_id = DB_ID()

Getting a little fancy, we can grab the information for each database on the instance, but you would have to insert this into a table to do any ordering.

EXEC dbo.sp_MSforeachdb ' Use ? SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size], /*File size on Disk*/
CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)) / 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8))) / 1024 AS ''Available Free Space In MB'' /*Matches SSMS file report*/
, (s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)) / (s.size * CONVERT(float,8)) * 100 AS ''Percent Free''
, growth, max_size, state_desc
FROM
sys.master_files AS s
WHERE s.database_id = db_id()'
**I was delayed in getting this post out and I no longer remember where I got the original code from. I am pretty sure it was from a trace file looking at what SSMS did do pull this information. If this is your script (or started as your script), I am sorry I did not note it above.**

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.

This setting can be found by clicking on Properties/[Package Name] properties . . . Where [Package Name] is the name of the package you are working with.

SSISPropertyConfig_64Bit

SQL Saturday 173 Washington DC

Saturday December 9th found me in Washington DC for SQL Saturday 173.  As we are planning an event in Richmond, I opted to volunteer more than I have in the past so I could be closer to the logistics and see what the organizers had done. #SQLSat173 was organized by Chris Bell, a first time organizer, with a little assistance from his wife.  :)

I knew putting on a SQL Saturday took a lot of work and the DC team put it all together nicely.  While the content of the sessions would make most attendees happy, the DC team provided a few extras to give the attendees lots of reasons to enjoy themselves–there was food all day long, a Microsoft Kinect in the lobby, a hot lunch from Maggiano’s, and they had a Kindle Fire raffle for those that printed their speedpass.  The DC team had wonderful sponsors that helped most of that possible and I hope to the RVA team can replicate some of that–with our own twist of course.

The DC team provided vests as a thank you to the speakers and volunteers.  Here I am sporting mine.  :) VestOh course, I did make it to several sessions and I had a great time.  My dad actually tagged along and enjoyed himself.

The one other treat was meeting with Karla Kay and chatting with her about some of the ideas we had for our event.  As always, the SQL community is wonderful and I hope to see you out on the trail!

Roanoke SQL Server Users Group

October 18th, I spoke at the Roanoke SQL Server users group.  While not a big group, there was a good comradery and I enjoyed meeting the people there.  I spent the night in Roanoke with the family and we enjoyed seeing the town.  If anyone in Roanoke is looking for a DBA . . . I may know someone.  :)

I spoke on Performance tuning with a focus on indexes.  This was my first presentation for this subject and I had some trouble with the demo so I have some work to do there; however, here are the scripts I used for the presentation.

If anyone from the group has some feedback, drop me a line.  I would like to hear it.

SSMS default projects folder

I have a client that wants to ability to see the ad-hoc queries/scripts I write for them.  These aren’t things that go into production or a source control environment.  While it is only a couple of clicks, it became tediuos to change my directory folder every time I wanted to save or open an script.  The default location for saving your files in SSMS can be changed by editing the My Documents\SQL Server Management Studio\Settings\CurrentSettings-[Date].vssettings file.

As always–give yourself a way to get back and take a backup before you edit the file.

You are looking for the part of the file that says <PropertyValue name=”ProjectsLocation”>%vsspv_user_documents%\My Projects</PropertyValue>.  Mine was on the first line way to the right.  I had SSMS closed when I edited the file and changed the location to a network share.  I opened SSMS again and it opened right to network location.  If for some reason it doesn’t do that, you can click on the ‘My Projects’ folder on the left and it should take you there.

Going on a SQL Cruise!

I have had a pretty good life on the D list as a DBA here in RVA and I am looking to bump it up a notch–you know, take it to a new level.  I have found so many different SQL Bloggers and community folk that really know their stuff.  Along the way, I came across a group that offers classes on week-long cruise!  SQL Cruise invites a wide variety of talented folks on a cruise and while at sea, they have classes and then use some of the social time to continue discussions, network, and address other questions.

I am going on a Cruise!

I was somewhat hesitant about it–I have never been on a cruise and wasn’t sure it was the right work/play combo; however, after talking to fellow Richmonder (Well, metro Richmond–way metro) Wayne Sheffield, who had gone on a cruise this year and thought it was fantastic, I decided it was worth pursuing.  I had to convince the Mrs, and now we are both headed to a cruise leaving from Miami in January.  SQL Cruise is run by Tim Ford, and while I won’t actually meet him until January, he seems like a decent fellow and he has had some pretty good talent on deck.  This Miami cruise has some folks I am less familiar with, but I am looking forward to meeting them and I have been pursuing their blogs to see what might be in store.

I am sure I will have more to say on this topic as we get a little closer, but if you are interested, check out the FAQ page and wish me well, cause I am going cruising and maybe I’ll see you onboard!

Not all operating sytem builds are equal

I was trying to build out a virtual machine for my SQL Azure presentation to the Richmond SQL Server Users Group and I realized something I had taken for granted–ALL OS downloads from Microsoft’s MSDN are not created equally.

I was using Oracle’s VM Virtualbox software and wanted to install SQL 2012 to use with my demo.  It was actually my first time installing SQL 2012 and while I had given a quick glace at the install reqs, I wasn’t too worried because you know–it was for a demo and I have lots of experience installing 2005 and 2008.  For the OS, I was using the en_windows_server_2008_r2_standard_enterprise_datacenter_and_web_with _sp1_debug_checked_build_x64_dvd_619600  edition/version for the OS.  No problem I thought–it is SP1_debug_checked.  Must be good.  This was a little too optimistic.

The installation of the OS went fine and I was able to install items from the server role console and I updated with all the latest OS patches, etc.  When I went to install SQL Server, everything seemed fine until it tried to install .NET framework 4.  Everything went to pieces.  It gave me the following error: .Net framework is unable to install.  That was it–and searching through the logs didn’t give me hints(that I could decifer) either.

I searched the web and tried all the following options

  • Installing the .net framework before I installed SQL 2012
  • downloading the .net framework again
  • Modifing the registry to add entries to ‘trick’ the .net install
  • Installing fewer items in SQL Server, but found the engine itself needs it.
  • Installing a different version of SQL Server (enterprise, standard and development) en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294
  • Installing a different build/version of SQL Server en_sql_server_2012_standard_edition_x86_x64_dvd_813403
  • Hopping on one foot while the install was going on, which was way harder than it sounds.  At least it left me breathless, which was probably good cause I was ready to hurl a few undesirable words out.

Nothing worked.  I ran into one almost side comment that there was an issue with some OS builds that may cause this problem.  I thought no way–this has been out long enough.  Anyway, I tried everything I could think of and was about to give up when I thought–what the heck, lets try another OS version.

I downloaded the OS version en_windows_server_2008_r2_with_sp1_x64_dvd_617601 and installed with en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546 and had NO problem whatsoever.  It was a piece of cake.
And that is the rest of the story.  :)  As, for the demo–it went well.  I actually submitted to present at the SQL Saturday in DC in December.