December SQL Server User Group Meeting
Location: At Lamar Advertising
Wednesday, December 2, 2009
6:00 PM - 8:00 PM
Topic
An Overview of Business Intelligence
Presenter
Brandon McMillon
Brandon McMillon is a Senior Solutions Principal at EMC. He has 10 years of experience at Microsoft, working on the Visual Studio & Windows development teams. He was also a Chief Software Architect for a Microsoft Gold Partner before coming to EMC.
Abstract:
This talk will first examine the current marketplace for Business Intelligence, and how it has gotten there. We’ll look at how EMC is leveraging some of the newer tools and products to lower the costs and effort for implementing BI, in the context of a real-world EMC BI project. We’ll also share best practices & lessons we’ve learned in these projects. Finally, we’ll look at the next generation of BI tools and products, and talk about how they will impact the market and what problems they’re attempting to solve.
Agenda
5:45 pm - 6:00 pm:
General Introduction/Food and Drinks
6:00 pm - 7:30 pm:
Overview of Business Intelligence
7:30 pm - until:
Open forum for questions
Talk to you soon
Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com
A new feature of SQL Server 2008 is Table-valued parameters (TVP). This feature will allow Developers and DBAs to pass tables as parameters to stored procedures. You cannot pass a variable table or temp table, you can only pass a Table Type, which is an alias data type or a user-defined type. So how do you use it? The first step is to create a Table Type. See the following script:
USE
GO
IF
DROP
CREATE
AS
(
CountryRegionCode nvarchar(3)
)
go
The next step is to create a Stored Procedure that will include a variable of the aforementioned Table Type. The following Stored Procedure uses the AdventureWorks2008 database to select Sales by Date and Country Region Code, using the TVP to limit the result to specified Country Region Codes:
@Month varchar(20),
@Year int,
@CountryRegions Sales.CountryCodes readonly
SET
SELECT
cr.Name,
SUM(TotalDue) TotalDue
FROM
INNER
ON sod.TerritoryID = st.TerritoryID
ON st.CountryRegionCode = cr.CountryRegionCode
ON cr.CountryRegionCode = c.CountryRegionCode
WHERE
DATENAME(MONTH,sod.OrderDate) = @Month AND
YEAR(sod.OrderDate) = @Year
GROUP
DATENAME(MONTH,sod.OrderDate),
YEAR(sod.OrderDate)
In the variable declaration of in the above stored procedure, the last variable is declared as the Table Type (Sales.CountryCodes) created in the first script. Then the Table-valued Parameter (TVP) is used in the last JOIN of the query to limit the result to the items or Country Region Codes contained with the TVP. On thing to be aware of is that the Table Type is read only. The contents of the table cannot be modified. Now that all of the formalities are out of the way, how do you use this in T-SQL? The following example is a script of how to call a stored procedure that has a TVP as a parameter:
DECLARE
@Month int,
@CountryRegions Sales.CountryCodes
@Month = 4,
@Year = 2002
INSERT
VALUES
EXEC
@Month,
@Year,
@CountryRegions
As you can see in the above script, you will populate the TVP the same way that any other table is populated. Once it is populated it can be passed to a stored procedure.
Talk to you soon,
Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com
At most large companies one business requirement is that all reports have the same look and feel. This may vary by department, but there is typically some level of standardization amongst the business entities. In most cases there is a header and footer template that needs to be seen on all reports. Often developers I have seen developers start from scratch or copy and paste and existing report. Those days are gone. For all of you still using Reporting Services 2005, don’t worry this method is available to you also. Here are the steps:
1. Create a template report, maybe and .rdl that contains only the header and footer information. These are items that are typically used throughout a company or department.
2. Copy the .rdl file to one of the following directories
(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject (SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
The next time you create a report, right click on the Reports folder in the Solution Explorer of your Reporting Services Project. Then choose Add –> New Item. A dialogue box will open, similar to the one below:
In the above screen shot, my template is named Dashboard Template. Choose that item and click Add. When the report is added to you project it will look exactly like the template you created. Happy Report Writing!
Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com
After several tries I think we finally have it working. If you were unable to attend the last two SQLLunches, go to www.SQLLunch.com and go to the Archived November meetings. They are both available for your viewing. If you have any questions or comments about the SQL Lunch, please send an email to webmaster@sqllunch.com.
Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com
SQLDownSouth
When partitioning was first introduced in SQL Server 2005, I thought what a great idea. However, when I started reading Books Online I thought, this is going to be a big Pain in the you know what. After reading Kimberly’s article, Partitioned Tables and Indexes, I was convinced of how easy it would be to implement Partitioning and how it would help manage and maintain Very Large Databases, specifically warehouses. The article is a little dated, but if you have any questions about Partitioning from a management and maintainability stand point this article is a must read. She discusses topics that include, planning, defining, steps to create, etc… This article should definitely help jump start your design and implementation of partitioning in your SQL Server environment.
To read the article in its entirety click here.
Meeting URL: Join Meeting
Click the above Meeting URL around 11:30 AM CST on 11/16/2009 to join the meeting
Date: 11/16/2009
Time: 11:30 AM
Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=NHHGB9&role=attend
Presenter: Patrick LeBlanc, currently the Sr. Database Administrator for Lamar Advertising. I have worked as a SQL Server DBA for the past 9 years. My experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. I am the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Area SQL Server User Group.
Topic: Integrating Table-Valued Parameters with Reporting Services
SQL Server Reporting Services 2005 introduced Mutli-Select Drop Down List. SQL Server 2008 introduced the Table-Valued Parameters (TVP). Natively Reporting Services does not support TVP. In this session I will provide a demonstration on how to seamlessly integrate to two features.
If you would like to receive email notification about upcoming SQL Lunches go here:
http://www.sqllunch.com/Register.aspx
Patrick LeBlanc, found TSQLScripts.com and SQL Lunch
SQL Down South
This past Tuesday night I had the honor of speaking to a small group of college students who are all members of the Association of Information Technology Professionals (AITP). This was my third opportunity speaking to a group of college students and my second time speaking to an AITP group. The structure and order of the meetings are very similar. I start with a short introduction and continue by discussing some trends in Information Technology and my current career. This meeting began in a very similar fashion; however this time I was asked a question that I had never been asked. One student asked me, “How did you become a DBA?” It’s strange that the question had never been posed before. Nevertheless, I was up to the challenge.
In the mid 1990’s I began my career in corporate America as a mortgage underwriter. This was a very monotonous job. A few variations to the type of income documentation, credit level or appraisal, but overall the same set of information. One primary part of the job was traveling to various branch offices within the United States. During the travels an underwriter was expected to teach the Loan Originators employed at the branch how to build a loan package that contained only the information that was needed for the approval of the loan.
When the underwriter returned to his or her home office, the person was required to send emails to the each branch, listing each loan underwritten in the branch and a list of pended items for each loan. Typically an underwriter could spend a day or two composing the emails for each branch. At the time I was not a very good typist. So for me, this was a very daunting task. During my undergraduate studies I had taken an Introductory Information Technology course. One of the requirements was to develop an Access Database. I remembered how Access stored information that was later used for reporting and making decisions. At that moment I decided that I was going to design an Access database that would store this information and I would later use that information to somehow compose these emails.
The idea was great but there was one problem, I did not own a portable computer, or as we know them today as a LAPTOP. Fortunately my mother-in-law was always purchasing things she did not need, and fortunately during our last visit she had given me a TOSHIBA Satellite PRO (T2400CT). Don’t believe me check this out:
I could not believe that it still worked. Running Windows 95 nonetheless.
Coupling my little laptop with an Access Bible, I developed a nice Access Database, forms and reports included, that I used to collect data when I was traveling. I assigned the Macro to a button, and when the button was clicked an Email message was opened with a Word document attached that only contained data for a specified branch. The only thing left was to add a recipient and click the Send button. This reduced the time I spent composing these emails from 2 or 3 days to a couple of hours. In the end the CEO found out about my little database and decided that it should be used by all Underwriters. I explained to him that my book stated that Access should not be used by no more than 5 or 10 people concurrently. He suggested that we upsize it to SQL Server 7.0 for the backend and keep the front end in Access, which led to the beginning of my obsession with databases. From that project I started developing larger databases and moved on to become a DBA for a small Mortgage Company in Baton Rouge, and the rest is HISTORY!!
If you are in the Baton Rouge Area stop by and join our user group meeting this evening. The details are below:
Topic: Introduction to MDX
Date: Wednesday, November 11, 2009
Speaker:
Barry Ralston
Barry is currently Vice President for Technical Solutions with Birmingham-based ComFrame Software. Since joining ComFrame in 2001, his client successes include Aflac, Honda, and the Children's Hospital of Alabama. In addition to speaking at the Alabama .Net Code Camps 1, 4 and 5, Barry has delivered presentations on Business Intelligence with Microsoft technologies at SQL Saturday 1 and 4.
Location: Lamar Advertising, 5551 Corporate BLVD, Baton Rouge, LA 70808
Time: 5:30 pm
Overview:
Introduction to MDX
Prizes: Wireless Keyboard and Mouse, Books, T-Shirts
Talk to you soon.
Data Compression was introduced in SQL Server 2008. This feature helps compress data inside the database, thus potentially reducing the size of the database. In the article titled, Data Compression: Strategy, Capacity, Planning and Best Practices, the author outlines several pertinent details regarding the implementation of this new feature. Some topics discussed included: What to Compress, Estimating Space Savings, Resource Requirements and the Side Effects of Compressing a Table or Index. The article also includes a couple of scripts that will help you determine what level of compression should be used.
Click Here to Add to Outlook Calendar
Click the above Meeting URL around 11:00 AM CST on 10/12/2009 to join the meeting
Date: 11/9/2009
Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=95MQQM&role=attend
Presenter: Kevin Kline: Kevin is the Technical Strategy Manager for SQL Server Solutions at Quest Software. A Microsoft SQL Server MVP, Kevin is a founding board member of PASS and the author of several books including “SQL in a Nutshell” (O’Reilly & Associates). Kevin is a top-rated speaker at industry trade shows and has been active in the IT industry since 1986.
Topic: Understanding and Preventing SQL Injection
SQL Injection attacks are one of the most common hacker tricks used on the web. Learn what a SQL injection attack is and why you should be concerned about them. Through demonstrations, witness different types of SQL injection attacks, how to find them, and how to block them.
My friend Trevor Barkhouse called me up a couple of nights ago and asked if I could host a Live Meeting Rehearsal of his PASS Presentation. Is that a question, of course I can. So if you have some time this morning join in a for a sneak peek. The meeting details are as follows:
NOTE: The Live Meeting is set up from 9:00 a.m. through 11:30 a.m. (CDT), however the presentation is actually from 9:30 a.m. through 10:45 a.m. Please do not attempt to join the meeting until 2009-10-30T09:30:00.000-05:00!
Speaker: Trevor Barkhouse. Trevor is a DBA on the Database Escalations and Implementations team for Terremark Worldwide, Inc. He is also a volunteer on the board of the NTSSUG and maintains the groups website
Overview: Over the years, Microsoft Customer Service and Support has developed a number of amazing tools for troubleshooting SQL Server. Thankfully many of these tools have been shared with the public. In this session I will demonstrate the configuration and usage of PSSDiag (for SQL Server 2000) and SQLDiag (for SQL Server 2005 and 2008), which collect valuable diagnostic data. We will then analyze the data using RML Utilities as well as a few scripts of my own. Come and see how these tools can save you massive amounts of troubleshooting time!
Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=HD8QCW&role=attend
I was recently contacted by a developer with what I would consider to be a very simple question. How do you search for string of the same length starting and ending with given characters. My response was simple, use the underscore(_) Wildcard Character Match. The person looked very puzzled from my response. As a result, I wrote a quick example to clarify. For some of you this may be second nature, but for many developers and some DBAs that I have spoken to, this was a new concept. Therefore, I am sharing this information.
Scenario: Suppose you have the following table
and you wanted to return all the customers whose name started with P and ended with a K. One last thing, you only wanted customers whose first name contained Seven characters. How would you accomplish this task. If you used the following query:
your results set would return the following rows:
This is because the % wild card character matches any string of characters. If you modified the query to look like this:
then you result would be correct, returning only the rows.
This is because I included 5 underscores as part of the character string. These 5 underscores restricts the search to that number plus any additional characters that are specified within the character string. In my case 2. Therefore, my keyword search was limited to character strings that started with P, ended with K and strings that contained seven characters.
This past weekend I had the pleasure of not only attending, but speaking at SQL Saturday in Orlando, FL. When I arrived in Orlando on Friday I drove over to the SQL Share office and met Andy Warren and Jack Corbett. Two great guys. When I arrived at the office Buck Woody was giving a training session on Performance Tuning. I spent most of the afternoon with Andy and Jack having lunch and picking up a few items for the event. We discussed many topics, including the recent Board of Directors election, which I will leave for another blog posting.
I left Andy and Jack around 4:30 pm and decided to go and check in and answer a few emails before the speaker event. The hotel was nice and centrally located to each event location, but about 36 miles from the airport. When I arrived at the speaker event many of the speakers were already there. I am not going to try and name each speaker, but Andy and Jack had a great list that included MVPs, book authors and columnists. I had a chance to meet all the speakers and have some very interesting conversations throughout the evening. After a long evening of talk I decided to call it a night.
The next morning I arrived at the event around 7:45AM. There were several people already in line. My session was not until 4pm, therefore I had time to do a lot of networking and attend other sessions. I attended Brian Knights sessions on SSAS and MDX. Both sessions provided me with some good information to get started building my own cubes and writing some MDX. I also had an opportunity to spend about an hour chatting with Joe Celko, details of that conversation is forthcoming. I also attended a session on Partitioning given by Elijah Baker, which was one of the best partitioning sessions that I have ever attended. He has a new take on partitioning, at least new to me, that he is writing a white paper on.
I started my session promptly at 4pm. My turnout was very low, but the talk went on. This was my second time presenting this topic, Using the CLR to Monitor Disk Space. The attendees all seemed intrigued by the concept. In this session I provide the attendees scripts, which include CLR Functions, that will allow them to proactively monitor Disk space from a central location. If a drive on the server is running low on disk space an email notification will be sent to the DBA. If you would like a copy of the scripts and the slides please email me at pleblanc@tsqlscripts.com
Overall the event was one of the best that I have attended. My only criticism is that a few of the speakers went over on their time. Everything else was great.
Thanks Andy and Jack
Patrick LeBlanc, founder www.tsqlscripts.com and www.sqlllunch.com
Today’s SQL Lunch was a huge success, even though we experienced a few technical difficulties. Tim Mitchell spoke on Deploying Report Builder 2.0 for Self-Service Reporting, which apparently is a very hot topic. There were 100 people in attendance. When I initially started the SQL Lunch I always hoped to reach as many people as possible. Well it looks like it is paying off. Thanks Tim for such a great presentation. If you have yet to attend a SQL Lunch Meeting our next meeting is on October 26, 2009 at 11:30 AM CST time. Here are the details:
Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=3GBFMJ&role=attend
Topic: Accidental DBA and Performance Data Collector
Presenter: Barry Ralston. Barry is currently Vice President for Technical Solutions with Birmingham-based ComFrame Software. Since joining ComFrame in 2001, his client successes include Aflac, Honda, and the Children's Hospital of Alabama. In addition to speaking at the Alabama .Net Code Camps 1, 4 and 5, Barry has delivered presentations on Business Intelligence with Microsoft technologies at SQL Saturday 1 and 4.
Abstract: A new feature in SQL 2008 may well improve the ability of “accidental DBA’s” to monitor, manage and tune SQL Server. This presentation will outline how to setup Performance Data Collector (PDC) and use
Click here to receive updates about upcoming SQL Lunches.
If you would like to speak at upcoming SQL Lunch events please email me at pleblanc@tsqlscripts.com. In your email include a short Bio, Title and Abstract.
Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch
Date: 10/12/2009
Presenter: Tim Mitchell .Tim is a Microsoft SQL Server consultant, developer, speaker, and trainer. He has been working with SQL Server for over 6 years, working primarily in database development, business administration, data integration, and automation. He has earned a number of industry certifications and holds a Bachelor's Degree in Computer Science from Texas A&M at Commerce.
Tim is principal and senior data engineer for Tyleris Data Solutions, a business intelligence and data management consulting firm. As an active member of the community, Tim has spoken at venues including SQL Saturday and the PASS Business Intelligence SIG, and is an active volunteer and speaker at the North Texas SQL Server User Group in Dallas. Tim is an author and forum contributor on SQLServerCentral.com and has published dozens of SQL Server training videos on JumpstartTV.com.
Topic: Deploying Report Builder 2.0 for Self-Service Reporting
For the DBA, creating and maintaining reports can take up a significant amount of time. Constantly changing requirements, an endless stream of ad-hoc requests, and simple formatting changes all add up to a good deal of maintenance. To lighten the load, Microsoft offers SQL Server Report Buider 2.0, an easy-to-use reporting tool for building published and ad-hoc reports using SQL Server Reporting Services. In this SQL Lunch presentation, we'll look at the capabilities of Report Builder 2.0 and how it can be used with SSRS to allow users to create and maintain many of their own reports.
http://www.tsqlscripts.com/sqllunch.aspx
