I think Brett has a point in terms of the long term development of a stadium - although I would take it a little differently.
A temporary stadium is all fine and good to gage demand/need and see if there would be appetite for a team and gage whether there would be corporate sponsorship interest (and yes, SMU/DAL could use it for university football as well).
Corporate sponsorship is where many of these teams get a lot of their money and there are a few developers, corporate citizens that I'm sure could be persuaded to donate money. Ken Rowe (owner of IMP) is a pretty generous guy when it comes to sponsorship; or at least he seemed to be; there is one example. The family that owns eastlink could be approached, the Piercey family, Sobeys...and so on.
Once the temporary field is established - the other thing to consider is that the place could be used for bigger/better things too such as concerts, but that may not work with a temporary field.
When the temporary field is established and say a team comes here; or it's use is justified - then you can move onto securing land a permanent structure. But in considering it's design - it should be designed to be able to support a multitude of sporting and non-sporting events. For example - it should be able to have a football field, but could also have a hockey rink (with the surface for football play that could be put on top - is that possible??), or baseball - then it could also hold concerts and other events.
While I've said before that I think a downtown location would be great (as it would be an addition to the skyline) - I'm not necessairily opposed to a suburban location either (in the right context). McMahon stadium here in Calgary is not in the downtown - it's just outside in the NW (by the university) - but it is easily connected to downtown through the LRT system - the Banff Trail station (adjacent to Motel village) is well positioned. Speaking of motel village; I would highly suggest that if a sub-urban location is considered and the building of this stadium were to be conditioned with getting a team - that hotel/motels be readily accessible from the location. It should also be close to transit (future high speed ferries, rail or potential LRT as well as bus).
But back to my thought on downtown - I think downtown would be better to achieve having it close to hotels/motels and other tourist type venues - so it could be a great addition to that mix. But; sub-urban can work with the right context. Perhaps a location like the Brightwood Golf course could work if it was re-developed - but I'm not sure on that one. Another location to consider would be here:
Akerley Blvd. This is a location that could provide a high speed ferry location with bus access into Burnside Park and there is land adjacent where hotels could be built. The stadium itself could be built on fill - although being so close to the Bedford Munitions depot causes me some concern.
One other site might be as part of the Bedford Waterfront development - say the highway commercial section or as an additional area of land fill.
But I don't think anyone here is trying to say a stadium isn't something to shoot for - I believe people are trying to be realistic in what money HRM has now and in the future. Where I think people under estimate things is corporate sponsorship; because I'm sure many businesses like Sobey's would be right on board.