.NET Questions (CLOSED)

Questions and Answers on any aspect of .NET. Now closed.

This discussion group is now closed.

Have a question about .NET development? Try stackoverflow.com, a worldwide community of great developers asking and answering questions 24 hours a day.

The archives of .NET Questions contain years of Q&A. Even older .NET Questions are still online, too.

SQL Server 2005 Disappointments

SQL Server 2005 introduces a number of new features, but as far as I can see, *all* of them have turned out to be frustrating at best, and crushing disappointments at worst. However, I'd be delighted to be proved wrong - I've got to use it for the next umpteen years, after all, so...

DataSet Paging
This has been trumpeted from the roof-tops as a major new, developer-focused feature – to only get back the records your application needs to display, using Row_Number(). Except, it is implemented via some dodgy syntax, and involves doing an inner-select, which rules out its use in any dynamic environment, as it introduces the rule that you can't have duplicate column names... which means you can't do a SELECT * from a JOIN (all the tables in our environment contain a field called "LastUpdatedDate"). What was wrong with MySql's "LIMIT" syntax? It would have been trivial to implement and would have done very nicely thank you!

CLR Stored Procs
These are the biggest let-down of all – because you are limited to a single connection*, which *has* to enlist in the parent transaction. This means that you can't to any complex work, even though the only reason for using them would be... to do complex work. To take one example, you can't write to an error / event log, because if your basic execution needs to roll-back... ooops – there goes the error log! So, given the patent obviousness of this need, why is there no method to get an out-of-context connection (using the in-context security settings) to the current database?

This is now "server-only" technology... except if you actually want to run it *on* a server, *from* a client, in which case... there is no way to do this. (And, no, running dtexec via WMI is not acceptable, not least because it only kicks-off the job, so you can't run it synchronously.)

SSIS Programmability
This seems to largely work in terms of execution, although you wouldn't know it from the documentation, which consists of a single example (albeit spread across a number of pages) which doesn't work; and a chunk of obviously-generated object definitions, which all say "this method is for SQL Server internal use only".

However, day-to-day working with it is a nightmare, because there is no longer a way of opening up the packages directly from the server, so you have to pull them back to the client, via a series of unhelpful dialogs, which don't remember your previous settings, then open them up in BIS... and then when it changes you have to do it all over again... This is really, really annoying when you have to do it thirty times in one day. (Yes, I know – if I wrote the code right the first time I wouldn't have to, but given what I've said about the documentation...)

* Unless you want to import it with a security setting less than "safe" – and try telling *that* to the guys that manage the server-farm.
Syd Send private email
Friday, January 19, 2007
Take a look at http://www.developerdotstar.com/community/node/512

It might not relate at all to your problems with SSIS, but it certainly makes it clear to me that I'll have to find a way to augment my brain before we make the move ourselves!
Ron Porter Send private email
Friday, January 19, 2007
A good place to pass these on to might be to SSWUG - they track 2005 uptake, issues, best practices etc.  I get the daily newsletter and it's a great resource.
Cade Roux Send private email
Friday, January 19, 2007
I'm still trying to figure out how to automatically script out a stored proc as a DROP/CREATE in SQL Server Management Studio like I can in Query Analyzer.
Kyralessa Send private email
Friday, January 19, 2007
from a performance standpoint, SQL2k5 has a number of significant internal upgrades with regard to SARG inference and index choices.  Also, CTEs are a great addition to the language.

I agree the CLR extensions are poorly executed and seem more like an afterthought.

With regard to your problem with row_number, you shouldn't be returning identical column names anyway, and select * is poor practice.
Patrick Send private email
Sunday, January 28, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz