A colleague of mine and I were recently talking about some scripts that they had seen containing the WITH NOLOCK hint. They asked me what it was and my basic answer is this…Using WITH NOLOCK will return records as they currently are stored regardless of if someone is updating them at that point in time. Updating tables causes a lock to be held. If you don’t use WITH NOLOCK (the WITH is optional by the way), when someone is updating the table, you will not get results until the update has complet...
Read More
Sometimes you’ll want to manage reporting services functionality through SQL Server Management Studio (SSMS). This would be the case if you want to manage Jobs (subscriptions), Security or Shared Schedules. To connect to your reporting services instance, fire up SSMS and then choose Reporting Services as the Server type as shown in figure 1. Figure 1: Connecting to a Reporting Services Instance Then enter the url to your Report Server as shown above, verify the authentication method (in my case ...
Brian K. McDonald SQLBIGeek Twitter: @briankmcdonald Welcome to the first of my “SQLBIGeek’s Function Friday” blog series. I understand that I am posting it on Sunday for this first post, rather than Friday. However, going forward I am going to post a new function each Friday. Whether it be cleaning up an old function that I have come across (written by others or myself J ) or it is just something that I have written lately. Either way, I hope that you enjoy the series and that you can get some ...
A while ago, I posted a blog on how one could perform running totals using Reporting Services and a user recently asked how he could do this within groups of data. So, I thought why not re-address this with another blog. Business Intelligence Design Studio (BIDS) allows us to create running totals for entire data sets and within groups of data as well. With that being said, I am going to show you how you can create this running tally effect within each group. If you want to start a new report fr...
Here is another one of those strange but true things that I come across every so often which I thought I should point out. Its primary focus is when you create parameters in SQL Server Reporting Services. When creating available values for parameters, you can specify a label and a value. You can access the value and the label properties of the Parameter collection something like this: =Parameters!ParameterName.Value Or =Parameters!ParameterName.Label Normally, I set both the label and the value ...
This past weekend, my wife and I travelled down to Orlando for SQL Saturday 49. I wasn't planning on presenting, but Andy Warren said that he had some mini session slots still available when talking with him at the South Florida SQL Saturday Event. So, I said what the heck... At any rate, I presented a 15 minute crammed pack session on Delivery Mechanisms using SQL Server Reporting Services. Details are below: On demand reporting is the most common type of report request using SQL Server Reporti...
While doing some performance testing recently and utilizing some of the DMV’s I figured, I better find out when the SQL Server Service was restarted. The reason being, the DMV’s get cleared out when SQL Server is restarted! Otherwise, the server I was doing performance testing on could have been restarted so recently, that the data stored so far may not provide a true representation of the performance of the server. To that end, I had to find out when the TEMPDB was created (which also occurs on...
I posted a blog recently about returning a listing of all indexes for database , which is good if you want to see how many are in a database. However, you may want to return the indexes for one particular table. Script 1 shown below will do just that! Just modify the table that you are looking for (and USE statement like above) and execute the script. See figure 1 for sample results of executing this script against the AdventureWorksDW2008R2 database available on Codeplex . Script 1: List Indexe...
I typically don’t blog about other people blogs, because quite frankly I don’t like giving summations of the work someone else has provided. I just don’t think it is a very good practice, but every now and then a quick post about something I have seen or found, warrants a brief blog about it. Needless to say though, one must give the original creator their proper credit. At any rate, today I thought that I would share something that I have seen recently. One that I never really thought about try...
A while ago, I showed you how to find out what indexes were on a table using the GUI . Today, I am going to show you a quick query to determine the indexes for ALL tables in a database. The results will also show you whether it is a CLUSTERED or NONCLUSTERED index. Script 1 below will return ALL indexes in the AdventureWorksDW2008R2 database (available on Codeplex ). If you want to see what indexes are in your environment, just modify the USE statement at the top of the script. Figure 1 shows sa...
Have you ever queried the msdb tables to get duration details about a particular job? I’ve had to do this many times in the past, but never blogged about it and when I needed it recently, I couldn’t find my scripts. I could have done a quick search for what others have done, but I thought I would deep into my brain housing group and see what I can dig up! At any rate, the duration columns often are returned as integer values. The values are not in milliseconds or even seconds and it is not very ...
Presenting is a huge fear for many and it is no different for me. I have been presenting at SQL Saturdays, Code Camps, User Groups and internal developer training sessions for the past few years. But for some reason, the initial knots in my stomach cause nearly all of the blood in my body to rush to the tip of my increasingly balding head. L And the bald head thing I can deal with just fine. It is the stress that I put on myself that causes this blood rush! Like everything else, I try to make it...
Recently, I came across a forum post from a user trying to determine if a directory existed in the file system. The user tried several ways to get a valid response from the system and came up short. I have done something similar in the past (years ago though) and thought I would write a quick blog to help him out. Below is a script that can be used to test if a directory exists. Script 1: Determine if Directory Exists DECLARE @FullDirectoryPathStatement VARCHAR ( 255 ) --Test... This one does NO...
While working on a project recently, I came across a conversion that was trying to convert a date to an integer value. Much like the value one would see in a Date_SK column contained within the DimDate table in a data warehouse. The conversion was actually quite strange and I was thinking to myself that this could be much easier. Below I have a script that shows you the way that I found as strange and what I believe to be an easier way to convert it. Script 1: Strange Way and My Way Comparison D...
It's official! I am presenting a mini session for SQL Saturday in Orlando. My topic and some details are below: Delivery Mechanisms using SQL Server Reporting Services On demand reporting is the most common type of report request using SQL Server Reporting Services, but did you know that there are other options available to you? In this mini session, Brian is going to go over all of the delivery mechanisms built into SQL Server Reporting Services that will allow you to take full advantage of Mic...
Over the many years that I’ve been dealing with SQL Server, I’ve always been told and read about how using dynamic SQL statements can’t be reused by the query optimizer. So, this morning, I thought about testing it out and finding out if dynamic SQL statements get stored in cache. Below I have a query that sets the statistics IO and Time on so that we can see more details regarding our execution. Before running the query, I also wanted to restart the instance of SQL Server that I was developing ...
In a previous post, I showed you how one could Restart, Stop and Start the services for an instance of SQL Server using the SSMS GUI. Again, please do not execute this on your PRODUCTION server, but here I am going to show you how to create a batch script that could be used to stop and start your SQL Server service. Create a new text file using notepad and enter the below statements into your file. Remove, any unneeded statements depending on your instance. If it is a default instance, you can u...
The way Reporting Services creates subscriptions is through generating a SQL Server Agent job. This is apparent when you look at the jobs on the database server that Reporting Services is configured to use. At any rate, when the jobs are created, they are named with a very hard to determine GUID. Here is a quick sample query how you might get some of the “Report Names” to match the “jobs”. SELECT c . Name AS ReportName , rs . ScheduleID AS JOB_NAME , s . [Description] , s . LastStatus , s . Last...
Sometimes you may need to restart your instance of SQL Server. You should rarely do this on Production boxes, but in development or on your local desktop instance I restart my SQL Server instance fairly often. Here is the easiest way for those who like to use the GUI (graphical user interface). NOTE: Do not test with this on your PRODUCTION server. J What you’ll need to do is RIGHT CLICK on the instance of SQL Server. If you want to do the stop and start all together, you can choose the RESTART ...
First and foremost, I want to thank everyone who attended my presentation today. It got off to a late start due to the prior presentation running over and a little setup issues set me back a little bit more, but I was able to cram in all of the content that I wanted to cover. I hope those who attended liked the details and demos. As promised in the presentation, here is a zip file containing all of the reports that I created and the windows application that used the ReportViewer control to integ...
I heard a little birdy cherping and it looks like there is going to be a great give away tomorrow night. The meetings normally start around 6PM at the Bank Of America building on Southside Blvd. Brandie Tarvin is presenting some of the intricate details of backing up and restoring. It should be pretty informative. For more information go to the http://jacksonville.sqlpass.org/ website. Oh... and don't forget there is normally about an hour of networking after the meeting, so don't miss it!
While getting prepared for an upcoming presentation, I remembered that I installed the November CTP trial on the machine that I use for presentations. And because of that, I began to panic... I thought "man... this is going to be bad if my 180 day trial expires the morning of the presentation". So, I had to figure out when I installed it. Of course one could go into Add/Remove Programs and look for Microsoft SQL Server, but I wanted to do it the hard way... I wanted to query it. :) So, that is w...
Scott Gleason just posted a blog yesterday that showed an awesome new tool that people can create little animated videos. When I clicked on his link, I was instantly surprised... I think this is a great way to get something across. My first stab only took me about 30 minutes or so to get up to speed and switch some wording around, but it is just a small snippet of things that one can do with the tool. Check it out here: http://www.xtranormal.com/watch/6933387/ Although Scott's video is pretty in...
How many of you keep falling for this one…You open up Report Manager for a user that you know you have given access to view reports. Only to see this annoying error! It is basically stating that the user BIGMAC has not been given the appropriate permissions to browse to that object. In this case it is the Home directory. Figure 1: No Permissions For BIGMAC So, you go back to report manager as an admin and can see plain as day that BIGMAC is a browser for the folder that you were navigating to. W...
In this quick blog, I want to show you one way of how to find out what indexes you have on a table using SQL Server Management Studio (SSMS). When you have SSMS running and are connected to the database engine, navigate down the database hierarchy to the Tables folder. You should see the tables that you have access to within that particular database. If you expand on a table you should see a subfolder called Indexes. Expand Indexes and there you will find your indexes for that table. For this bl...