What ports are your SQL Server services are running ?

You need to know which ports your SQL Server services are running.  This could be for Kerberos constrained delegation to Data Sources or to allow access through Firewalls.  Either way, the following guide explains how to go about this for your Analysis Services Service, and a similar process can be used to find ports for other SQL Server services.

For Analysis Services, you can see in Task Manager which port your MSMDSRV process is running.  Alternatively, open the SQL Server Configuration manager, select the Analysis Services service, select Properties, then on the Service tab, note the Process ID.

Now go to a command prompt and enter the following to dump the output to a file called netstatoutputfile.txt in the specified directory, in this case c:\tempfolder


netstat /ao -p TCP >> C:\tempfolder\netstatoutputfile.txt

Look for your process id and you will see port specified beside the IP address in the Local Address column.

A complete reference for the Analysis Services service specifically including firewall specific actions are outlined here http://msdn.microsoft.com/en-us/library/ms174937.aspx

Enjoy !

Register for Microsoft Business Analytics Conference 2013 here !

New conference, new format, designed for Business Analytics professionals in the Microsoft space.  Chicago 2013, April 10-12 (Full Conference), with an optional Pre Conference day beforehand for a sum extra.

Register here (Best rate for registration finishes December 7, 2012) :

http://passbaconference.com/Register.aspx

SSAS – object reference not set to an instance of an object error

You attempt to Build your Analysis Services solution but get a “object reference not set to an instance of an object” error when you try the Build function.   This can be any number of things but as a guide you can try checking the following within your SSAS solution :

DSV – Check the DSV and make sure each object returns data without error.

Dimensions – Check each and every dimension in the solution by manually opening and checking for errors.  Alternatively install BIDS helper and activate the Dimension Health Check on each.

Measures – Check the aggregation property and ensure the Aggregation function is correct.  For example if you don’t have a Date Dimension in your solution but have selected Last Non Empty, this will trigger the object reference not set to an instance of an object error.

Note – Restarting Visual Studio is unlikely to resolve.

Happy error hunting.

Where are my Reporting Services 2012 Integrated end points and log files ?

A good article outlining the location of end points in Reporting Services 2012 integrated mode and how the various bits and pieces talk to each other.

http://blogs.msdn.com/b/jtarquino/archive/2012/06/13/sql-server-reporting-services-2012-sharepoint-integrated-mode-endpoints-architecture-or-how-our-services-talk-each-other.aspx

Includes an outline about how to check the Sharepoint Timer job which is used for Reporting Services 2012 Alerting.

http://blogs.msdn.com/b/jtarquino/archive/2012/05/07/the-sql-server-reporting-services-2012-timer-job-in-sharepoint-mode.aspx

If you are looking for Log files, these include Execution logs (Report Server execution log table) and trace logs (file) over here <Drive>:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS and you can read about the different forms of logging here … http://msdn.microsoft.com/en-us/library/ms156500.aspx.

To configure diagnostic logging for Reporting Services and a number of other Business Intelligence items (e.g. Powerpivot) s, go to Central Administration > Monitoring  and select Configure Diagnostic Logging.   Select SQL Server Reporting Services from the list, note the landing point for Sharepoint log files above) and click OK.

SQL Server 2012 Service Pack 1 and SSDT Automatic Update Feature

SQL Server 2012 Data Tools gets an automatic update feature included in Service pack 1 for Microsoft SQL Server 2012 !

This is an excellent feature as most people update the server, but forget to update their workstations with the latest and greatest offerings that come with the general update.

Once Service pack 1 is installed on the client machine where your SQL Server Data Tools is installed, then you will benefit from automatic updates on execution.

Some important links …

You can getthe latest SQL Server 2012 news here …

http://www.bus7.com/news-feeds/43-microsoft-sql-server-news/10-microsoft-sql-server-release-services.html

MSDN forum for SQL Server Data Tools…  Here you can search for previous issues or lodge new questions …

http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads

Link to Download the service pack … http://www.microsoft.com/en-us/download/details.aspx?id=35575

and the announcement blog….

http://blogs.msdn.com/b/sqlreleaseservices/archive/2012/11/07/sql-server-2012-sp1-is-now-available.aspx