Is it possible for a user-defined aggregate to perform basic DML operations through ADO.NET (read, update, insert, delete)?
Why would I want to do that, you might ask? Rather than carry forward an accumulation of data, what I want to do is insert the data into a temporary table and retrieve it at the Terminate method call.
I created an aggregate to do the above using VS2005. My aggregate compiles, and deploys through VS2005, but I get the following error when I attempt to run it in debugger:
Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
All functions in UDA must be compatible with User Defined Functions. But UDFs couldn't consist update/insert/delete DML. So I couldn't use them in context connection. But you could create different connection and run update statements through it.
|||I tried a couple of different options, but it appears that the user-defined aggregates are prohibited from having any kind of connection via SQLCLR in the database.
I have not been able to find any documentation to support or refute this claim, but it appears that UDAs are severely limited in SQL Server 2005.
|||I finally found a reference that specifies an answer to the question of whether user-defined aggregates can perform database access or not. The answer is "No".
I quote from the Microsoft Whitepaper, Using CLR Integration in SQL Server 2005 (Rathakrishnan, et al.):
A "UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used.UDA can perform no data access, nor have side-effects; if either of these are necessary then a stored procedure should be used."
Thus UDAs have some significant limitations in this version of SQL Server. They are:
No data access|||vb_hal,
I have been dealing with the same problems as you. In my case, I wanted to create an UDA for calculating the percentile of a set of numbers (as in the PERCENTILE function in Excel).
What I wished for was to be able to write something like
SELECT dbo.PERCENTILE(some_column, 0.5)
FROM some_table
GROUP BY some_other_column
To work arround the multiple arguments problem, I created an UDT called PercentileParameters and an UDF called PP that acts as a sort of constructor. As a result, the query now looks like this:
SELECT dbo.PERCENTILE( dbo.PP(some_column, 0.5) )
FROM some_table
GROUP BY some_other_column
I also had some trouble with the 8000 bytes limit. To work arround it, I gave my assembly EXTERNAL_ACCESS rights and used them to store data as needed. I know it's not very efficient, and that it's impossible in some settings due to security issues, but it works for me.
So there you go. I just thought I'd share these couple ideas with people facing the same problems as I am (and are looking for a quick and dirty way out of it, just as I was).
--
Carlos
|||I've been trying to find exactly what you seem to have. I've been looking for a function in SQL Server that does the same thing as PERCENTILE in Excel. Oracle has an implementation called PERCENTILE_CONT and I've seen ways to do the calculations in SQL Server 2005 but not as a function. Is there any way you could share your code with me?
From the documentation I've read on UDAs, I'd have to create an assembly in a .Net language to create my own aggregate. Quite a daunting task from my perspective since my background is strictly SQL Server code and Admin. I could get around the multivalued function issue because I use 5 static percentile values (.1,.25,.5,.75,.9). I could just create 5 UDAs.
Any feedback is greatly appreciated.
blackjackIT
|||Jourdan, can you shaer your dbo.Percentile and dbo.PP function if possible. I'm trying to do the same UDA for percentile as you.
Thanks!
