Filtering SQL Agent Jobs

Posted on 18/08/2014 by Jens Vestergaard

I suspect that any developer who has been near a SQL Server for more than just a short while, you will be able to recognize a list of SQL Agent jobs that can no longer can be seen in its full extent without having to scroll.
A tip to make it easier for yourself is that you can filter the list.

Just right-click ‘Jobs’ and select Filter->Filter Settings and you’re off.

Looong SQL Agent Job List

Looong SQL Agent Job List

 Then punch in the filtering you’d like to have

SQL Agent Jobs Filter

SQL Agent Jobs Filter

And voila, your list of jobs are filtere to show only the ones you want:

SQL Agent Jobs Filtered

SQL Agent Jobs Filtered

Posted in Programming | Tagged , , | Leave a comment

Microsoft Visual Studio is unable to load this document

Posted on 12/06/2014 by Jens Vestergaard

This issue has actually been on my TODO list for quite some time. Now I had to go back to an old SSIS package and do some changes, I got reminded of how annoying this bug is.

The scenario is SQL Server Integration Services 2008 R2 combined with loading an Excel file. This should in anyones mind be the perfect match (if any such exists). I mean, both Microsoft products, one tailored to accommodate for the other – what could go wrong?

I turns out, that Business Intelligence Development Studio (BIDS) is unable to open the file, when you have configured a file in a remote localtion. Or so at least is my theory. Below is what fixes the flaw:

Opening the package fails:

SSIS Error Page

View Code to fix error:

SSIS View Code

SSIS View Code

Remove the inner text of the <DTS:PropertyDTS:Name=“ConnectionString”></DTS:Property> in question:

SSIS Code Behind

SSIS Code Behind

This Works, until the next time you save the file. :/

Filed a Microsoft Connect Item: https://connect.microsoft.com/SQLServer/feedback/details/894728/visual-studio-bids-unable-to-load-file-dtsx

Posted in Programming | Tagged , , | Leave a comment

Did someone restart the server over night?

Posted on 26/05/2014 by Jens Vestergaard

Sometimes, when you are running lenghty integration jobs over night, you get in the office the next morning, and may be surprised that nothing has completed. And actually nothing is running anymore.

Today was such a Day for me. Last night I started the execution of a package via remote desktop and DTSExec Util. This morning, my job was gone from the session, and no where to be found in the Task Manager. So, did someone restart the server over night? (could even be a stupid auto Windows update setting rebooting the machine)

I found this piece of Powershell to tell, if the Event Log Service was stopped:

PS C:\get-eventlog system | where-object {$_.eventid -eq 6006} | select -first 10

At least it tells you quickly, if the suspicion has something to it.

m4s0n501
Posted in Programming | Tagged , | Leave a comment

Run your cubes in Warp Speed – Really Really Fast!

Posted on 01/04/2014 by Jens Vestergaard

So, finally Microsoft came up with a new feature for the MOLAP part of Analysis Services. The community has been hungering for updates ever since the new kid Tabular came around.

Now it apears that the SQL Server team has unlocked some underlying functionality in the Windows OS, to enable the MOLAP cubes to operate in Warp Speed.

Run the latest CU (get it here) and check out the newly added check box in the properties.

WarpSpeed for MOLAP Cubes

WarpSpeed for MOLAP Cubes

 

Posted in Programming | Tagged , , | 1 Comment

How to Process Multiple Dimensions via BIDS and SSMS

Posted on 31/03/2014 by Jens Vestergaard

This is one of those tips I need to write down, before I forget…
Sometimes Analysis Services acts up and you need to do a process full on all the dimensions in the database. When that happens I usually turned to Visual Studio Business Intelligence Development Studio [BIDS] or SQL Server Data Tools [SSDT], which allows you to select more than one dimension at a time, and fire up the processing. See figures below:

Via BIDS

Open a connection to a “live” database

Open Connection

Open Connection

Choose which server/database to connect to:

Choose Server/Database

Choose Server/Database

Select all dimensions and right-click:

Select dimensions

Select dimensions and right-click

Finally, choose processing mode:

Processing Mode

Processing Mode

Via SSMS

Until today, I didn’t know you could actually do the same action through SQL Server Management Studio [SSMS]. You do as following:

Connect to the server:

Connect to Server

Connect to Server

Expand till you get to the Dimensions folder:

Find the Dimensions folder

Find the Dimensions folder

Hit F7 to open the Object Explorer Details:

Select All and Right-click

Select All and Right-click

Select Processing Mode:

Select Processing Mode

Select Processing Mode

 

Posted in Programming | Tagged , , | 1 Comment