Questions and Answers on any aspect of .NET. Now closed.
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...
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.)
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.
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!
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.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz