SQL Server 2008: Table-valued parameters

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 AdventureWorks2008 GO IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales')) DROP TYPE Sales.CountryCodes GO CREATE TYPE Sales.CountryCodes AS TABLE ( CountryCode nvarchar(3) )

 

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:

USE AdventureWorks2008 GO IF(OBJECT_ID('Sales.GetSalesByDateAndRegion')) IS NOT NULL DROP PROC Sales.GetSalesByDateAndRegion GO CREATE PROC Sales.GetSalesByDateAndRegion @Month varchar(20), @Year int, @CountryRegions Sales.CountryCodes readonly AS SET NOCOUNT OFF SELECT    cr.Name,    SUM(TotalDue) TotalDue FROM Sales.SalesOrderHeader sod INNER JOIN Sales.SalesTerritory st    ON sod.TerritoryID = st.TerritoryID INNER JOIN Person.CountryRegion cr    ON st.CountryRegionCode = cr.CountryRegionCode INNER JOIN @CountryRegions c    ON cr.CountryRegionCode = c.CountryRegionCode WHERE    DATENAME(MONTH,sod.OrderDate) = @Month AND    YEAR(sod.OrderDate) = @Year GROUP BY     cr.Name,     DATENAME(MONTH,sod.OrderDate),     YEAR(sod.OrderDate) SET NOCOUNT ON GO

 

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:

USE AdventureWorks2008 GO DECLARE @StartDate datetime, @EndDate datetime, @CountryRegions Sales.CountryCodes SELECT @StartDate = '4/1/2002', @EndDate = '4/30/2002' INSERT INTO @CountryRegions VALUES('US'), ('CA') EXEC Sales.GetSalesByDateAndRegion @StartDate, @EndDate, @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

Deploying Reports on Windows 7

Recently I tried to deploy a report to a Report Server on my laptop, which is running Windows 7 Ultimate. Unfortunately I received the following error:

The permissions granted to user ‘MachineName\SomeUser ‘ are insufficient for performing this operations.

This confused me a bit since my account was an administrator. After digging a bit I found a very simple solution. Right-click on the Visual Studio icon or the Business Intelligence Development Studio icon and click Properties. Then go to the Compatibility tab. On that tab, in the Privilege Level section select the checkbox next to the item labeled Run this program as administrator. See the below screen shot example:

Once done, restart the application and Deploy your report.

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

How to Create a Reporting Services 2005/2008 Template

 

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

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!

Talk to you soon

Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com

SQL Lunch Recordings Now Available

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.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com

SQLDownSouth

Article of the Week – Kimberly L. Tripp (Partitioned Tables and Indexes)

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.

Talk to you soon

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

Today’s SQL Lunch – Integrating Table Valued Parameters with Reporting Services 2008

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

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.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.sqllunch.com/Register.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South

How did I become a DBA

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!!

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Baton Rouge SQL Server User Group

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.

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com

Article of the Week – Sanjay Mirsha (Data Compression)

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. 

To read the article in its entirety click here.

Talk to you soon

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

New Job with Pragmatic Works

Well if you attended PASS, you already know that I am changing jobs.  I recently accepted a position with Pragmatic Works.  I am honored to have the opportunity to work with Brian Knight and his staff over at Pragmatic Works.  For me this is somewhat of a career change.  Most of my career I primarily focused on the OLTP side of things.  In my new position I will be moving into the OLAP realm.  This is an exciting change for me and I look forward to the many challenges.

Talk to you soon

Patrick LeBlanc, founder, www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Today’s SQL Lunch – Understanding and Preventing SQL Injection with Kevin Kline

Meeting URL: Join Meeting

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

Time: 11:30 AM

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.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.sqllunch.com/Register.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South