DavidWimbush

The developers of Management Studio and, before that, Query Analyzer have made a bit of a habit of building killer features and then hiding them in the small print of Books Online. Now you may already know that SQL 2000 introduced a new way to arrange your query and result windows, but I only just found out about it. I think it's great so, in case you blinked and missed it too, here's how it works.

Since the dawn of time you edited your query in a full window and, when you ran it, the results covered the bottom half of the query. You could use Ctrl+R to toggle the visibility of the results. When you think about it, it's actually pretty clumsy. These are the only combinations you can see easily:

• half of the query and half the results
• the whole query
• er, that's it

I was so used to it that I didn't really notice. Until somebody showed me a much better way to do it.

There's an option under Query Results | SQL Server | Results to Grid called Display results in a separate tab. If you tick this and its sub-option the behaviour gets much more usable. When you run the query the results fill the screen. You get a second row of tabs below the row of tabs for the query windows you've got open. This second row has tabs for the query, results and messages and (optionally) things like the query plan. It looks like this:

You can tab backwards and forwards between these tabs using F6 and Shift+F6 respectively. You can quickly toggle between the query and results and you can see both of them properly.

You really should try this. Your muscle memory will protest but stick with it. I think you'll find it was worth it.

It can be done but it's not well documented and it's complicated by the fact that you can't easily get the result of the process. Unless you actively check and manually raise an error if there's been a problem, the job will report sucess no matter how screwed up the cube processing is. That part is all about the XML results that are output by the process.

Here's how I do it (and I'd be very interested if you have any suggestions to improve it):

Connect to the Analysis Services server in SQL Server Management Studio.

Right click on the database and select Process.

Configure all the options and then use the Script button to grab the XML that defines the processing job. It will look something like this:

Create a new SQL Agent job.

Add a step to get rid of any old output file(s). Change the step type to CmdExec and paste in this code, modifying the file name:

Add a step for the processing. Change the step type to SQL Server Analysis Services Command and paste in the XML. Go to the Advanced page and give it an XML output file - with the same name you used in the previous step - to write its results to.

Add a step to check the output XML file to see whether the process was successful. Change the step type to T-SQL and paste in this code:

Modify it to add your own file name(s) into #File at the top. This step will raise an error if the processing failed. If you don't do this, you won't know whether the job has worked.

When you restore from a transaction log backup, SQL Server checks the file against the database and gives you an error if the file is not the next one in the sequence. I wanted to know how this works so I did some digging. Not much in Books Online that I could see so I did some practical investigation with some of my log shipping files.

It looks like it compares the last LSN in the backup file's header with the last LSN in the database's restore history. If the last LSN in the file is less than the last LSN in the restore history, it indicates that the database already contains those transactions. Here's some code that does it:

No longer available for download. Check it out here: http://www.idera.com/Products/Free-Tools/SQL-safe-Freeware-Edition/

Bummer! I'm using this extensively. Now you have to choose between the Enterprise edition and the Lite Edition. The Lite Edition is $295 per instance, which is not quite as attractive a price as free. I suppose it was too good to last. Luckily, I just got the budget approved to upgrade to 2008 R2 Standard, which has native backup compression. Can't wait!

Just found the answer to something that has been irritating me slightly for ages: how to give a user access to a report without giving them access to all the other reports in the folder.

We have folders that are based on the roles people perform in the company. We granted the appropriate domain groups access to their folders in the Browser role. That gives those people access to the reports in those folders because, by default, a report inherits its security from its parent folder. This works great until the Sales Manager says, "Can you give Student Joe access to the Sales Stats report so he can do a special job for me?" This report is in the Management folder which also contains a load of sensitive reports that Student Joe cannot be allowed to see, so you can't give him the Browser role on the folder. You can override the inherited security on the Sales Stats report by putting Student Joe in the Browser role at that level but he still can't get to it in the Report Manager because he has no rights on the Management folder. His only access is via a URL direct to the report.

We thought we were stuck with this and had got used to it. After all, people could get to what they needed so there wasn't much of a business case for sorting it out properly. But today I got another such request and the red mist siezed me. (Perhaps it's the weather?) I couldn't believe this was impossible. Surely they thought of this? And, thanks to the excellent Hitchhiker's Guide to SQL Server Reporting Services, I discovered that they did. They just didn't go out of their way to mention it.

Here are the steps:

• On the Home page, click on Site Settings.
• Click on Configure item-level role definitions.
• Create a new role (say, View Folders Role) and assign it the View folders task.
• Now you can assign a user or group the View Folders Role on a folder and they will be able to navigate to the folder and there they will see any reports you have given them access to.

Easy when you know how!