|
|
Posted by billmiami2 on 05/28/06 14:33
Erland,
> Actually not. You can load assemblies as hexstrings as well. Try
> Script As in Mgmt Studio on an assembly to see what I'm talking about.
>
> What would be very nice is something like:
>
> CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS
>
> and the rest of the batch would be the procedure definition in C#. SQL
> Server would compile it on the server, and create an assembly and all
> that jazz for you.
Hey--that's great! Thanks for pointing that out to me. It will save
me from having to copy the assemblies to the production server. Your
modified CREATE PROCEDURE would be even better but I suppose we'll need
to wait for SQL Server 2010 (?) for that.
Yes, the error trapping is quite a nice addition. Perhaps your
standard error handler can write to your new XML log and send you an
e-mail if the severity level is a certain level.
I'm just now experimenting with all of the new XML capabilities.and I'm
trying to see where I might put them to use. I haven't yet covered the
Service Broker. I have written one SSIS package that is now running in
production, but I must admit that it was far more complicated to use
than DTS, at least for simple things.
One thing that disappointed me was the PIVOT function. It seems that
the column names must be hard coded. I suppose that I can get around
this by using dynamic SQL or by writing a CLR procedure that calculates
the column names, but this is what I was hoping to avoid. If you know
a clever way around this, I'd love to hear it.
There's so much new ground to cover, it's quite amazing.
Bill
Erland Sommarskog wrote:
> (billmiami2@netscape.net) writes:
> > I'm aware that the assemblies are stored in SQL Server upon running
> > CREATE ASSEMBLY so there is no direct dependency. However,the
> > assemblies must initially be made accessible to the SQL Server so that
> > it can bind to them. Also, if an assembly is changed, the revised file
> > must be again made available, so you're still having to manage files
> > that you wouldn't need to manage had you used T-SQL only.
>
> Actually not. You can load assemblies as hexstrings as well. Try
> Script As in Mgmt Studio on an assembly to see what I'm talking about.
>
> What would be very nice is something like:
>
> CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS
>
> and the rest of the batch would be the procedure definition in C#. SQL
> Server would compile it on the server, and create an assembly and all
> that jazz for you.
>
> > Can you foresee using some of the new T-SQL enhancements in your
> > product? I have a couple of projects that involve hierarchies and the
> > recursive Common Table Expressions and Cross/Outer Apply have made for
> > far simpler queries than I could have created in SQL 2000. I haven't
> > used them in production yet, but the ranking functions look very nice
> > too.
>
> The absolutely biggest improvement in SQL 2005 is the error handling.
> I'm considering modifying our load tool so that if there is no BEGIN
> TRY in the code, the load tool would splice in BEGIN TRY in the
> beginning of the procedure (it splices in SET NOCOUNT ON today), and
> then tack on an standard error handler at the end.
>
> We have also discussed improving our auditing (which is not very strong
> today). To this end the xml data type can be very useful. You can write
> standard trigger that transforms the contents of inserted to one XML
> document per row, and the store that in a common log table. We would then
> write a generic log reader on top of this.
>
> I would also expect row_number() to very useful. We don't work that
> much with hierarchies, but certainly there will be one or two CTEs.
>
> I have hardly looked at Service Broker myself, but there are some
> people who developing new stuff that will use Service Broker. (This is
> for a new customer that will run SQL 2005 from day one.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|