Tag Archives: sql

Reporting Services (2005) “http://localhost/reports” authentication error

Posted on by
5

I have encountered this issue on two separate occasions now. The first was during a report deployment to a new Report Server Virtual Directory and the second was trying to access the report manager via the URL: http://localhost/Reports. Each time I was presented with the same "Authentication Required" "Enter username and password for "" at http://localhost" screen:

On both occasions I searched high and low for a solution to the problem using a few trusty searches , but could not come up with an answer that worked for me. To cut a long story short the simple solution was to ensure that the URL was a) correct and b) fully qualified.

In the first instance (report deployment) the name of the Report Server Virtual Directory was a Named instance and so trying to deploy to http://localhost/reports was never going to work. Ensuring the right virtual directory was in the URL made all the difference. Go figure.

In the second instance (accessing the Report Manager) I had installed a default configuration of Reporting Services and therefore the setup had created the Virtual Directories required (Reports & ReportServer). However, having typed in the URL http://localhost/Reports I got the Authentication Required screen (as above). I found numerous solutions (supposedly) to this problem, all of which are listed at the end of the post, but I eventually just tried the full path of the home page: http://localhost/Reports/Pages/Folder.aspx and it worked like a dream. Again. Go figure.

Why the full path had to provided I don’t know. On previous occasions I have not have to do so and have been able to connect to the Report Manager using the abbreviated URL (http://localhost/Reports). If anyone does have a clue as to why this is I would gladly welcome the comments.

Things to note:

I am running Windows XP Pro SP2 with SQL Server 2005 Developer Edition.

————————————————————————

Other possible solutions, or at least other areas to check when experiencing this issue:

1. If you are using Firefox and still have problems with Authentication (whilst there are no issues with IE) try this very quick and easy fix.

2. Checking the security settings in Control Panel>Administrator Tools> IIS> Default website and right clicking Reports, choose Properties>Directory Security>Anonymous Access & Authentication control. A number of people suggest using the Anonymous setting only, although I have found that although you can then navigate to http://localhost/reports, no folder structure is available to you. It is therefore suggested that you use the Integrated Windows Authentication only.

3. Ensure that all the settings in Reporting Services Configuration are green. If not, attend to each of the areas in red.

4. Any other ideas? Post a comment that I can include here.

Hopefully, you haven’t had as painful a journey as I have in getting this exceedingly annoying issue (but scarily easy fix) sorted out.

SQLServerPedia Syndication!

Posted on by
4

Today I made a commitment, a commitment to the community. And it starts with SQLServerPedia Syndication. That commitment means that I’ll be writing more, interacting more, sharing more, and if at all possible, entertaining more. (OK, so I’m not sure about the last one, but I sure am gonna try).

I have to say, it’s a little daunting being listed amongst MVP’s, professionals with decades of experience and other big personalities in the SQL Server Community. Thankfully, I thrive in situations like these. Being amongst people who are better than you enables you to up your game – or at least that’s been the story for me. Upping my game means that you, dear reader, get more quality content on the topics I care about. For now, that includes SQL Server’s Reporting Services and Integration Services. But you knew that already.

I would ask one favour (yes, that’s the way we spell it over here…) of you, and that is feedback. Of any sort. Whilst I get it right most of the time (I hope), there may be occasions that I’m talking absolute dribble. SPEAK UP! I’m sharing from my own experience and opinion, if it’s wrong, or at least if you believe it’s ‘wrong’, I want to know about it and what you believe the right answer to be.

A big thank you to Brent (Blog | @BrentO) and everyone else at SQLServerPedia for the opportunity to give something back. I’m thoroughly looking forward to the journey ahead and seeing where it takes me.

SQLBits V – Conference & Session Recap (Part 1)

Posted on by
5
Better late than never

I’ve been a little under the weather since getting back from SQLBits V so this post has been days in the making instead of just s a few hours. But here it is. This post focuses on the conference sessions, I am going to follow up with another post on people, conference feedback and things I might do differently next time.

Even the birds aren’t up this early…

Friday started swimmingly. Up at the crack o’ dawn,  taxi to a mainline station (my local didn’t have an early enough train to get me into London), only to find that the train has been delayed by 18 minutes. Pants. There goes all my hard work ‘being early’. The good news is that I made it on to the Newport Train (from Paddington). With just 2 minutes to spare. And I got a seat with a power outlet. Bonus! All my good intentions at getting a head start on an article have gone out the window as I am a little tired, so I try to catch up on some much needed shut eye.

Keynote by Donald Farmer

I arrived at the Celtic Manor, having shared a taxi, just in time for registration and a quick bacon bap before the welcome started. The keynote was given by Donald Farmer (Blog | Twitter). He gave us a great preview of the simply awesome PowerPivot and a few features in SQL Server 2008 R2. See this recap by Brent Ozar.

Below is a recap of the sessions that I found I got the most out of.

Friday Session 1 – New Development Features in SQL Server 2008 [Simon Sabin]

The first session of the day, that I attended, was Simon Sabin’s (Blog | Twitter) overview of the new development features that can be found in SQL Server 2008. Simon favours the demo approach for sessions and so had relatively few slides. Not a bad thing. Among other features available in SQL Server 2008, the following were discussed/demoed:

  • Variable declaration and initialisation – this can now be done in the same line (Declare @i Int = 1)
  • Named regions - You can create these using BEGIN & END blocks & naming it with a comment. Named regions will enable you to collapse the blocks for readability.
  • Intellisense – as has been available to .NET programmers for years. My only query here is, why not use the syntax that can be found in C# when writing LINQ queries [where you specify the FROM TABLENAME line first and then the SELECT statement). As such, you find yourself writing the FROM line first and then going back up to the SELECT statement….
  • Highlighted Syntax errors - think MS Word red squiggly lines to highlight that something is the matter.
  • Code validation at design time -
  • Date & Time datatypes - new Date datatype allows dates before 01/01/1753, and only takes up 4 bytes of storage. great for when you don’t actually need time precision. Time datatype handles precision down to the nano second, for when you do. The Time datatype also only refers to the time of day and not a time interval.
  • No longer allowed implicit calculations on dates - you now must use Date functions to perform calculations on Date datatypes (i.e. DateAdd(), Not “MyDatetime + 1”).
  • DateTime Offset - Allows you to store the date and time that has time zone awareness (based on the 24-hour clock)
  • Debugging - You can now debug your code! breakpoints, watch lists, view locals, etc
  • MERGE Statement - enables you to perform updates/deletes/inserts all within one statement.

Whilst this list is not conclusive, it certainly highlighted a number of new features that are now available in SQL Server 2008 and incentivises you to at least find out what else is available that could simplify you SQL development.

This was a great session, and if you find out that Simon is doing a presentation near you (UK User Groups), I suggest going.

Friday Session 2 - SQL Server 2008 R2: What’s new in Reporting Services? [Donald Farmer]

Donald Farmer (Twitter | Blog) blew my mind. Not once, but twice. First, in his keynote, he talked about the awesomeness of PowerPivot. Then in this session, he did it again. I loved this session and quickly realised that there’s going to be quite a learning curve and an altering of mindset of how reporting ‘gets done’ in the future. Why? Because the lines between the business & IT are blurring.

With the new Self Service Analysis and Reporting tools, there’s a new approach to finding out and tracking how the business is performing. So, if you are a Reporting Services Developer or Data/MI(S) Analyst. Things are going to change:

  • PowerPivot & Excel 2010 bring a lot more functionality ( & power) to business users. Enabling them to analyse data at length straight from the data source.
  • The arrival of report components in report builder, means that reports can be built quicker using existing parts of reports or report items. Think sub-reports, but on a more granular scale. Each report item can be a reusable component, which can be tweaked by the user for use in their own report.This includes the reuse of datasets, as components. These components are centrally managed by IT (or a reporting team).

So what did we learn? Here’s a bulleted summary:

  • Business users analyse data through tools like PowerPivot & Excel 2010, digging for answers to business questions. This analysis is an iterative process and gives rise to metrics that can be monitored. Enter reporting services. Reports are built to monitor the ‘answers’ discovered by the business analysts.
  • Reports can be created using components. Components are pre-built report objects that reside in a library that anyone can reuse. Think of sub-reports, but in a centrally managed way.
  • Created reports that are saved to the Server are saved as a report in its entirety, as well as individual report components. These components are then available to other users to include/tweak in their own reports.
  • When Publishing reports, you can choose which components of the reports you want to publish.
  • Report Data can be subscribed to via feeds (rss anyone?). This can be used to in PowerPivot to run Excel-based analyses using Pivot tables
  • New features of Reporting Services:
    • Spark lines – think inline charts. No x/y axes. Just used to illustrate a trend.
    • Data bars – no more having to use expressions to calculate cell padding and the applying a background colour. Pick your element & configure.
    • KPI’s – like those in SSAS, you can now add a KPI and configure it’s value, goal, status & trend. KPI’s can be ‘componentised’ and reused in other reports.
    • Lookup functions
    • Aggregates of Aggregates
    • New data sources (Project Madison, SQL Azure)
    • Ability to reset page numbers based on groups
    • Expression-based page breaks
    • Expression-based chart headers
    • Naming Excel Worksheet tabs (on render to excel)
    • Bottom-top text rotation
    • Session variables
  • R2 to be released in first half of 2010 (CTP is already out).
Saturday Session 2 – Jamie Thomson’s Whistle Stop Tour of SSIS Add-ins

In this session Jamie took us through the fact that there are a number of SSIS custom tasks available on the intertubez. Many of them are free and can be found on CodePlex. There were only one or two slides (Jamie’s contact info), as the whole session was taken up demoing each of the tasks he had chosen.

  • XMLify Task
  • Kimball SCD (Slowly Changing Dimensions) Task
  • TwitterTask
  • Term Extraction Task
  • Normaliser Task
  • Rank Transform Task
  • Compression & Decompression Task
  • FileWatcher Task
  • Trace File Task
  • Dynamic Data Flow Task

    • XMLify (http://xmlify.codeplex.com) – takes columns that you specify (from a query/view) and loads them into an xml block. Jamie demoed this for capturing error outputs.

    Kimball SCD (http://kimballscd.codeplex.com) – For slowly changing dimensions. Takes all data in the destination and for each column you can define it’s usage ( type 1 change, type 2 change, business key, surrogate key, etc). Type 1 changes will update the destination data based on the source data. This task is a monster. Loads of options for configurability.

    Twitter task (http://ssistwittersuite.codeplex.com/) – I think this is awesome. You can automate sending your tweets. Careful though, password is in full view in design mode. You can also get all replies/mentions and then data mine them using the Term Extraction task. I can see this being used as a substitute for text (SMS) messaging notifications, for whatever you need to be notified about (if you’re always on Twitter).

    Term Extraction – A data mining task to pick out nouns, verbs, phrases. Although not an add-in (comes with SSIS) this is still a neat task that I had not used previously. Jamie is using it to sift through the Twitter feed to his @ssisdemo Twitter account. Very cool use to see what people are saying (words & phrases), and how often each occurs. Could be useful, I guess.

    Normaliser (http://normaliser.codeplex.com) – Normalises the data from an incoming data stream. Pure. Simple. Awesome. Data must be sorted as a prerequisite. This task only handles one to many relationships.

    Rank Transform (http://ranktransform.codeplex.com) – Ranks a dataset by Partition. Enables you to view a dataset ordered and ranked (by specified column), as well as being able to specify a partition for the ranking (i,e, month). The dataset must be sorted before use. Rank & Dense Rank have their differences, specifically where you have two ranked values that are equal (a ranking ‘tie’). So, if you have two values that are equal and ranked ‘2’, then for the column RANK the next value will be ranked as ‘4’ whereas the column DENSE RANK the next value will be ranked as ‘3’. Still confused? Go here.

    Zip task (http://cozyroc.com/ssis/zip-task)- this task takes a source file and compresses it into a gzip file (.gz). That’s it. Decompression decompresses the file. This will only work with a single file and not for a folder or multiple files. I think I would prefer to use 7zip and an Execute

    FileWatcher (http://www.sqlis.com/post/File-Watcher-Task.aspx) – This task ‘watches’ a directory, when a file is detected it will progress the flow to the next task. Placed in an endless loop this will continually execute, and will pick up files as they arrive. Neat.

    Trace File (http://www.sqlis.com/post/Trace-File-Source-Adapter.aspx) – understands a sql trace file and enables you to handle the trace file contents.

    Data Flow Plus (http://www.cozyroc.com/ssis/data-flow-task) – Enables you to populate two different tables with the same data flow. Neat.

    Trash Destination (http://www.sqlis.com/post/Trash-Destination-Adapter.aspx) – Enables you to send any data flow to a trash destination. No configuration required, so ideal for a number development scenarios.

    This session, along with those by Donald Farmer, was the most enjoyable and one that I probably took the most away from. It opened my eyes to the world of custom SSIS tasks and that fact that so many of them are freely available (with source code so that you can tweak at leisure). Jamie definitely seems to be the go-to guy for SSIS related  info. If you haven’t checked out his site yet, I suggest doing so: http://sqlblog.com/blogs/jamie_thomson/default.aspx

    Other SSIS Add-in Links:

    Go to Part II of the SQLBits recap.

    The DatePart() Function in SSRS

    Posted on by
    20

    Using the DatePart() Function in SSRS can be tricky, even at the best of times. I have pulled my hair out too often trying to remember what syntax should be used, so have resorted to writing down all my findings. This is so that you don’t have to go through the same agony & frustration as I did, as well as for my own benefit (my memory is shot…)

    What it does: Returns an Integer containing the specified part of a given date

    Syntax: DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

    There are two ways to use this function:

    • Using the DateInterval collection
    • Using a string expression
     
    Using the DateInterval Collection

    Let’s start with an example:

    To return the current month: =DatePart(DateInterval.Month, Today())

    Now, in the expression builder (in BIDS), it will look like there’s something the matter with what we have just written:

    You can ignore this, the expression will work. An important point to note when using the DateInterval collection, is that you must use a date that is of type Datetime. If you use a string representation of a date (“2009/11/13”), then you’ll need to convert this to Datetime using CDate(“2009/11/13”) before the expression will work. Thus:

    This will work:  =DatePart(DateInterval.Month, CDate(“2009/11/13”))

    This won’t:  = DatePart(DateInterval.Month, “2009/11/13”)

    When you use the DatePart function to identify the day of the week you can specify what the First Day Of The Week is (defaults to Sunday if this is not specified). You can do this by accessing the FirstDayOfWeek collection. Example – To Return the day of the week, specifying Monday as the first day of the week:

    DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)

    All the possibilities for using the DatePart() function with the DateInterval Collection:

    The following table shows all the possibilities for the DatePart Function using the DateInterval collection. All expressions use a Parameter called MyDate (type = Datetime), which has a value of “2009/11/13 12:34:23”

    Expression Result
    DatePart(DateInterval.Second, Parameters!MyDate.Value) 23
    DatePart(DateInterval.Minute, Parameters!MyDate.Value) 34
    DatePart(DateInterval.Hour, Parameters!MyDate.Value) 12
    DatePart(DateInterval.Weekday, Parameters!MyDate.Value, FirstDayOfWeek.Monday) 5
    DatePart(DateInterval.Day, Parameters!MyDate.Value) 13
    DatePart(DateInterval.DayOfYear, Parameters!MyDate.Value) 317
    DatePart(DateInterval.WeekOfYear, Parameters!MyDate.Value) 46
    DatePart(DateInterval.Month, Parameters!MyDate.Value) 11
    DatePart(DateInterval.Quarter, Parameters!MyDate.Value) 4
    DatePart(DateInterval.Year, Parameters!MyDate.Value) 2009

     

    Using a String Expression

    Again, let’s start with an example:

    To return the current month:  =DatePart(“m”, Today())

    Using a String expression for your interval enables you to use a date that is of datatype String:

    =DatePart(“m”, “2009/11/13”)

    All the possibilities for using the DatePart() function with a String Expression:

    As noted previously, using a string expression for the interval allows you to use a date value that is of the String datatype. You can of course use a Datetime value if you wish to.

    This table shows all the possibilities for the DatePart function using a String Expression for the interval. All expressions use a Parameter called MyDate (type = String), which has a value of “2009/11/13 12:34:23”

    Date Part Expression Result
    Second DatePart(“s”, Parameters!MyDate.Value) 23
    Minute DatePart(“n”, Parameters!MyDate.Value) 34
    Hours DatePart(“h”, Parameters!MyDate.Value) 12
    Day of Week DatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday) 5
    Day of Month DatePart(“d”, Parameters!MyDate.Value) 13
    Day of Year DatePart(“y”, Parameters!MyDate.Value) 317
    Week of Year DatePart(“ww”, Parameters!MyDate.Value) 46
    Month DatePart(“m”, Parameters!MyDate.Value) 11
    Quarter DatePart(“q”, Parameters!MyDate.Value) 4
    Year DatePart(“yyyy”, Parameters!MyDate.Value) 2009

     

    Is there a preferred method?

    Ul
    timately, no. At the end of the day, all the results are the same. What is my preference? I use the DateInterval collection, mainly becuase I like that fact that you can’t get confused about which date part you are using (w/ww, y/yyyy, n….). It’s very clear to anyone who is reading the code that (for example) DateInterval.WeekOfYear refers to the week of the year and nothing else. It also forces you to use a Datetime Value, which safeguards against invalid dates (sort of).

    I hope this clears things up a little!

    SQLBits Agenda is now available

    Posted on by
    4

    The SQLBits agenda is up! And not a moment too soon. I understand that the organisers had a few issues when initially trying to get the schedule on the site, but all is resolved. These guys do great work to provide an amazing service to the community, and all at their own expense. So every credit to them as well as a big thanks from the rest of us!

    You can find the SQLBits agenda here: http://sqlbits.com/information/newagenda.aspx

     
    Lending a hand

    After reading Jeremiah Peschka’s (@peschkaj) article, Volunteering with PASS, I’ve realised the need to help out if I can. So, one of my goals at the conference is to find a few of the current organisers and figure how I can get involved. Is there any better way to rub shoulders with the best & the brightest out there? So, if Simon or James or Chris or Martin or Darren or Allan happen to read this, then consider this my offer to help. By the way, you’re all on my list of people to meet… ;)

     

    My Schedule

    When I originally planned to go to SQLBits I wondered whether or not to go up on the Thursday evening or rush in on the Friday morning. I was never going to make the Thursday pre-con, so it was a toss up between shelling out more ££’s for another night in the hotel or risk missing the start of the Friday program. So I gambled and decided I would travel on the Friday morning. This was done before the agenda was published (train tickets are sooooo much cheaper if you book in advance, see my previous entry), hence the gamble. It seems to have paid off however, as registration is from 08:45 – 09:40 and I should arrive (hopefully) at around 09:30. Just in time.

    Here’s my planned schedule (if you’re remotely interested):

    Friday, 20th November:

    [07:15 - 09:05] Train from London Paddington to Newport, South Wales
    [09:30 - 09:40] Registration
    [09:40 - 11:00] ‘Welcome’ session
    [11:00 - 12:00] SQL 2008 Development Features [Simon Sabin]
    [12:20 - 13:20] What’s New in Reporting Services 2008 R2 [Donald Farmer]
    [13:20 - 13:40] Lunch & networking
    [14:40 - 15:40] Powershell – Old Tricks for a new dog? [Martin Bell]
    [16:00 - 17:00] Virtualising SQL Server [Justin Langford]
    [17:00 - 18:00] SQLBits Shenanigans…
    [18:00 - ??:??] Social

     

    Saturday, 21st November:

    [07:30 - 08:30] Breakfast & networking
    [08:45 - 09:40] Registration
    [09:50 - 10:50] …undecided…
    [11:00 - 12:00] Let’s make SQL Fly! [Ramesh Myyappan]
    [12:20 - 13:20] Using Perfmon & Profiler [Brent Ozar]
    [13:20 - 13:40] Lunch & networking
    [14:40 - 15:40] SSIS in SQL Server 2008 [Allan Mitchell]
    [16:00 - 17:00] Report Builder 3 [Andrew Fryer]
    [17:00 - 18:00] SQLBits Shenanigans…
    [18:00 - ??:??] Social

     

    Sunday, 22nd November:

    [07:30 - 10:00] Long Breakfast & Networking
    [10:32 - 13:48] Train back to London, Paddington
    [15:00] Reunited with my amazing family.

     

    I’m hoping to meet a bunch of folks at the conference, so if you are going, let me know. It’s always easier to start a conversation if you’ve already introduced yourself, even if that is only in a ‘online’ sense. So, either leave a comment here or look me up on twitter (@lukehayler). Oh, and if you’re already on twitter and not using the #sqlbits tag for related posts, you should be.

    I’m hunting for social events next….stay tuned.