|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Friday, July 06, 2007 3:11 AM
Points: 145,
Visits: 1
|
|
Robert,
My functions are not used in production yet, so I wouldn't really know about this behaviour. Thanks for the warning, I will keep an eye on memory leaks and overall performance and keep the TSQL version standby.
Real life experience is important for technology like this!
thanks,
Jan
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, November 16, 2011 4:46 PM
Points: 1,290,
Visits: 137
|
|
I also found it handy to write a CLR procedure to a SQL Broker Queue process. The CLR procedure formatted an XML document based on parameters passed in message body, and FTP the document. It was much easier created the XML document and using the .Net framework for FTP client processing than to try to do the same in TSQL.
Mark
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, January 11, 2012 9:54 AM
Points: 470,
Visits: 588
|
|
another reason is code reuse. In our org, we use CLR functions to expose to our procs in SQL a library of useful functions that are shared with other managed components. For example, in our system we need to be able to convert from UTC to local time correctly for every county in the US, as well as every country in Europe. This is non-trivial because DST rules change in both time and space. I won't go into it except to say that Indiana is truly a hellish state as far as telling the time is concerned! It was very convenient to put this code on one class, and then expose it both to other managed components as well as SQL through CLR udfs.
---------------------------------------
elsasoft.org
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 1,244,
Visits: 859
|
|
This sounds a lot like the CLR code that was killing our system. If you want, I can send you a solution in nothing but TSQL.
My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Program Manager: SQL Server Master Certification Program
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, January 11, 2012 9:54 AM
Points: 470,
Visits: 588
|
|
Our need is to be able to convert an abitrary UTC time to local time (and vice versa) for an arbitrary US county or country in Europe. what I mean by arbitrary time: any date back to a cutoff of 2001/01/01 -- not just "today" or "right now". that's what made it messy: the rules for when DST turns on and off have changed through the years, and they don't change uniformly in space. Indiana is a case of this, and worse: some counties there even changed timezones in Apr 2006! To implement this logic twice, and maintain two codebases for it, was a cost I was not willing to pay. We haven't experienced any problems with our solution. we didn't download it off the web though - built it in house. maybe that's where your problem was. you get what you pay for.  i'm curious to see what you did in t-sql though.
---------------------------------------
elsasoft.org
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
Code reuse in this circumstance sounds really useful. I think I might take a look around. I'm sure we have something along these lines that might, possibly benefit. I'm curious to see the TSQL solution as well.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, July 06, 2007 3:11 AM
Points: 145,
Visits: 1
|
|
I am considering using CLR for something that does involve data access and would value your opinions on that. We have a lot of stored procedures to analyze financial data. The result is displayed in SSRS on our intranet. But we also need to feed several other applications with exactly the same data but formatted in XML. So I am basically left with the choice to duplicate all stored procedures (and maintain them 2 times) once FOR XML and once as a recordset; or I need to convert everything to dynamic sql and conditionally paste “FOR XML” or not at the end. I like neither of these options for several reasons. So I was thinking that maybe I could write a wrapper in CLR that builds a resultset by executing the name of the stored procedure passed as a parameter; and then selects FOR XML out of that resultset.
What do you think of that? Or are there better suggestions?
Thanks,
Jan
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
Most of our procs are converted to XML out on the client (app server actually). No real need to output to XML, in most cases, in the procedure. From your database, I don't think I'd change a thing. Just put an application layer between your db & the other apps.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, July 06, 2007 3:11 AM
Points: 145,
Visits: 1
|
|
Of course Grant, you are right, this would be the better way.
Suppose I was too much thinking database.
thanks,
Jan
|
|
|
|
