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!
Talk to you soon
Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com
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.
Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com
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
Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com
SQL Down South
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
Last night I presented, Monitoring Disk Space using CLR, to the Baton Rouge Area SQL Server User Group. There were approximately 20 people in attendance. I will be giving this presentation again next week at SQL Saturday #21 in Orlando, FL. I was surprised that so many attendees were unfamiliar with the SQL CLR. This really gave me a chance to identify anything that I may have omitted and any mistakes that were in the presentation or demonstrations.
Overall the attendees appeared to follow the presentation and understand the concept. I have added a few slides and rearranged a few things to accommodate a few requests and include some missing items. If you are in the Baton Rouge Area and would like to attend our meeting register at http://batonrouge.sqlpass.org.
Patrick LeBlanc, found TSQLScripts.com and SQL Lunch
Most of you are aware that the Microsoft SQL Server 2000 Desktop Engine (MSDE) limits database size to 2 gigabytes. However, how do you know when it reaches that limit. Well, the database becomes inaccessible. Yesterday one of my clients called and said, “My SQL Server is not running, can you take a look?”. Of course I could. I logged into the server and the SQL Server was running. Hmmmm, I thought. The server did not have any client tools installed, so I had to logon to another machine and connect.
Before I did that I asked the client what was the specific error? He sent me the following screen shot:
While reading the error I realized one thing, the SQL Server was running as a named instance. Typically in our environment, this usually indicates that someone in the department installed an application that’s using a Desktop version of SQL Server. I connected to the instance via Management Studio and ran, SELECT @@version, which returned the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 2).
I quickly noticed that it was an MSDE install. A quick look in the error log validated my suspicions.
2009-10-06 16:54:01.98 spid3 CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database. 2009-10-06 16:54:02.00 spid3 Could not allocate space for object 'StatisticalMemory' in database 'WhatsUp' because the 'DEFAULT' filegroup is full.
The database had reached the 2 gigabyte limit. As a result, I stopped the SQL Server, copied the .mdf and .ldf files to another SQL Server (SQL Server 2005 Standard Edition) and attached it to that server.
Just a little blast from the past for all of you have that have ever experienced this.
Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch
Well we are less than two weeks away from SQL Saturday #21 in Orlando, FL. My bags are packed and I can’t wait to join so many SQL Professionals in a day of learning, sharing and networking. If you are in the area and have not registered please do so. The list of speakers is very impressive, which includes myself, and you don’t want to miss out on this great day of free learning. Click here to register.
Event URL: http://sqlsaturday.com/eventhome.aspx?eventid=32
See you in Orlando
If you are available for lunch today don’t forget to sign in and watch Thomas LeBlanc talk about Historical DMV information. To attend the meeting use this URL: https://www.livemeeting.com/cc/usergroups/join?id=F7DRFD&role=attend. Also, if you would like to receive notifications about upcoming SQL Lunches register here: http://www.tsqlscripts.com/SQLLunch.aspx.
See you at Lunch
I have been installing and configuring SSRS since the initial release. I have always encountered several different caveats when upgrading to a newer version or adding a new server to a scale-out deployment. In some cases there have been consistencies, but in many these differences vary. These variances may have been attributed to the different installations and configurations. One for example is disabling loop back check. I have upgraded several SSRS 2005 instances to SSRS 2008 in the past couple of months and they were all very similar installations and configurations.
My most recent upgrade required me to make a change to the registry. I have performed this task in the past on my 2005 instances, but I had not encountered it in 2008. I do realize that this is a setting at the OS level. If you perform an upgrade and the Report Server URL is accessible, but you receive a 401 when trying to access the Report Manager URL follow the steps in Method 2 of the Solution section in this KB article.
url: http://support.microsoft.com/kb/896861
Talk to you Soon
Patrick LeBlanc, Founder TSQLScripts.com and SQLLunch
Date: 9/28/2009
Time: 11:30 AM
Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=F7DRFD&role=attend
Presenter: Thomas LeBlanc. Thomas is a Database Administrator for Amedisys, Inc in Baton Rouge, LA. He has been in the IT field for 20 years starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and even some .Net(C#). Designing and developing normalized databases has become his passion. Full-time DBA work started about 8 years ago for Thomas while working at a Paper Mill in St. Francisville, LA continuing with IEM, then Amedisys. Current activity at Amedisys involves 4 other DBAs
Topic: Historical DMV Information
DMV are great to find information about performance without running a trace. But there information is lost once the SQL Server service is restarted. After finding some useful scripts online (Thanks SQL Server community!!!) , I decided to schedule a job to run the night before to store Index Usage, Least Used Indexes, SP Usage, Missing Index and Expensive CPU Usage. We will review the scripts and historical data captured.
Click HERE to add to Outlook Calendar
Yesterday morning around 6AM, my Blackberry started going off. The subject line of every email was, “SQL Timeouts”. In the past, this was a direct result of my Index Rebuild or Defrag nightly process. However, since I have implemented a process that is able to recognize when the Index job is blocking, I knew this could not be it. I quickly ran, the following script:
SELECT r.scheduler_id, r.cpu_time, r.session_id, r.command Command, t.text SQL_Statment, r.blocking_session_id Blocking_Session_ID, r.total_elapsed_time/1000 Total_Elapsed_Time_Seconds, r.cpu_time CPU_Time, s.login_name Login_Name, s.[host_name] [Host_Name], s.[program_name] [Program_name], s.memory_usage Memory_Usage, r.status [Status], db_name(r.database_id) Database_Name, r.wait_type Wait_Type, r.wait_time Wait_time, r.wait_resource, r.reads Reads, r.writes Writes, r.logical_reads Logical_Reads FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(sql_handle) t WHERE r.session_id <> @@spid --and blocking_session_id = -4 ORDER BY 2 DESC
As expected, there was a lot of blocking. The strange thing was the –4 session_id. I have experienced –2 session_ids and I understand how to fix this problem. Click here for more information on –2 session_ids. I had never experienced a –4. After a few minutes I noticed (2:1:103) in the wait_resource column of the above query, which represents to DatabaseID, FileNum, and PageNum. I stumbled upon KB328551, which suggest adding a Trace Flag 1118 and Increasing the number of data files in TempDB. Increasing the number of data files was not a problem, however I was skeptical about adding the trace flag since the article applied to SQL Server 2000 and we are currently running SQL Server 2008. Adding the trace flag forces SQL Server to use uniform extent allocations instead of mixed page allocations.
SQL Server uses a special allocation page called a Shared Global Allocation Map (SGAM) to find unallocated pages in mixed extents. When there are several concurrent processes that need to access the SGAM, in our case 4000 instead of 100, the SGAM could become the bottleneck. This is because each process will need to access the SGAM several times.
Since this was my production system I decided to place a call to Microsoft. While I was on hold I found out that a new process that increased calls to the database by a magnitude of 100 was placed into production on the previous evening. When the Microsoft Engineer came on he poked around for about 15 minutes and then he suggested adding, what else, Trace Flag 1118 as a startup parameter. I was still a little skeptical about adding the trace flag because the article was so specific to 2000, but the engineer pointed me to a section of the KB that also suggested to use of the trace flag for SQL 2005 and 2008. Even further, I found a page by Microsoft Customer Server Support (CSS) recommending the usage of the trace flag to assist in TEMPDB scalability. We added the trace flag as a startup parameter for SQL Server and restarted the SQL Service. Before we added the trace flag I had the developer stop the process that was making the thousands of calls to the database. When I restarted the Service everything was up and running. I asked the developer to restart his service. After a few minutes the –4’s returned. They were not producing as much contention as before and all the systems were still available, but it was slightly slower than normal. As a result, we disabled only the part of the process that was causing the contention, reworked it and redeployed. Plans have been made to add additional database files to TEMPDB also.
Explaining why this problem occurred and how the Trace Flag and additional data files helped solve the problem is beyond the scope of a blog posting. However, reading the KB article, the Blog posting from CSS and this article http://technet.microsoft.com/en-us/library/cc966545.aspx will provide good information regarding these issues.
Patrick LeBlanc Founder TSQLScripts.com and SQLLunch
After about 3 weeks of digging through .css files and html code I finally found the problem with the site. The Master page was missing a small tag at the end that stopped the Content Area from expanding to 100% in height. Now that all of that is fixed, I will try my best to provide you all with a weekly script. This weeks script comes from the Latest publication of TechNet. In an article written by Paul S. Randall, he provides a script that tells you how many 8KB pages are in the buffer pool for each database.
Go here to see the script. It has been modified just a little to allow for further calculations. He discuss this more in his blog posting, What’s in the Buffer Pool.
Check both out.
Patrick LeBlanc, founder TSQLScripts.com and SQLLunch
The next SQL Lunch is scheduled for Monday, September 28. See below for details
As always, if you would like to receive notifications about upcoming SQL Lunch Live Meetings please register here: http://www.tsqlscripts.com/sqllunch.aspx. Also, if you would like to speak at a SQL Lunch please email me at pleblanc@tsqlscripts.com. Remember these are short meetings designed to help new speakers improve their skills.
Patrick LeBlanc, founder SQL Lunch and TSQLScripts.com
