.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL CLR Aggregate with 2 arguments

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server

I am trying to build a user defined aggregate that will use the Linear Regression formula to give me the slope of a trend line.  This means that my aggregate will need two input parameters rather than one.

My starting point was http://blog.effiproz.com/2010/06/multi-parameter-clr-user-defined.html

I must have hit every possible error message that it is possible to hit in trying to write an aggregate.  Everytime I think I have taken a step forward I get either a new error message or go back to one I had before.

I've had the UDAGG error messages, the IBinarySerializer messages, you name it.


I am currently getting the UDAGG message and a message for the CREATE AGGREGATE statement that is telling me that my CLR types don't match my T-SQL types.

I am using Visual Studio 2008 and SQL2008 Developer Edition.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

    Format.Native, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = false, //optimizer property
    IsInvariantToDuplicates = true, //optimizer property
    IsInvariantToOrder = false //optimizer pro

View Complete Post

More Related Resource Links

Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

More Versioning Fun With Optional Arguments

In my last blog post , I covered some challenges with versioning methods that differ only by optional parameters. If you haven't read it, go read it. If I do say so myself, it's kind of interesting. ;) In this post, I want to cover another very subtle versioning issue with using optional parameters. At the very end of that last post, I made the following comment. By the way, you can add overloads that have additional required parameters. So in this way, you are in the same boat as before. However, this can lead to subtle bugs. Let's walk through a scenario. Imagine that some class library has the following method in version 1.0. public static void Foo( string s1, string s2, string s3 = "v1" ) { Console.WriteLine( "version 1"...(read more)

Versioning Issues With Optional Arguments

One nice new feature introduced in C# 4 is support for named and optional arguments . While these two features are often discussed together, they really are orthogonal concepts. Let's look at a quick example of these two concepts at work. Suppose we have a class with one method having the following signature. // v1 public static void Redirect( string url, string protocol = "http" ); This hypothetical library contains a single method that takes in two parameters, a required string url and an optional string protocol . The following shows the six possible ways this method can be called. HttpHelpers.Redirect( "http://haacked.com/" ); HttpHelpers.Redirect(url: "http://haacked.com/" ); HttpHelpers.Redirect( "http...(read more)

How to Post Code To Your Blog and other Religious Arguments

If you've got a programming blog, chances are you'll want to post some code snippets. Posting code sounds easy but it's surprisingly tricky if you consider all the ways that people will be reading your blog. There's a number of ways. Here's a few and their pros and cons . Copy Paste from your IDE (like Visual Studio, for example) If I copy paste directly from VS into my editor of choice, Windows Live Writer, I'll get a <pre> section. using System; namespace WindowsGame1 { #if WINDOWS || XBOX     static class Program     {         /// <summary>         /// The main entry point for the application.        ...(read more)

Creating .NET Assemblies That Aggregate Data from Multiple External Systems for Business Connectivit

This article describes a quick, four-step process for creating a .NET assembly that BCS can use to retrieve external data for SharePoint Server 2010 by using Visual Studio 2010.

Process.Start - arguments length restriction to 2080 characters

Hi, I'm wondering why is there a limit to "The sum of the length of the arguments and the length of the full path to the process " to 2080? I see that the documentation says that a Win32Exception is thrown if this exceeds 2080 characters. Can someone please explain to me this limit? I'm trying to start a process and I have a lot of arguments to pass, and of course ... I exceed the limit of 2080.   Thanks, Ionut.

Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

About the Aggregate Function MIN

Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

The best overloaded method match for... has some invalid arguments

I have a compilation error generated by this scenario: i have : 1. DataAccessLayses 2. Entity 3. PresentationLayer(web application)4. BankServices(web service) inside the webservice i have one web method with argument of my pre-defined class library it name is CustomerEL and the class in PresentationLayer calling the webservice method ,CustomerEL cusE = new CustomerEL();objsv.addCustomer(cusE);(addCustomer(<argument>) is method in BankServices)  and here the eror occuredcan you help me !Regards

Enumerating Variables & Arguments in Rehosted Designer

I'm trying to enumerate the variables and arguments of a workflow after I load it into my rehosted designer.  I see that I can use ActivityMetadata.GetArgumentsWithReflection() and ActivityMetadata.GetVariablesWithReflection() but those require that I have ActivityMetadata to start with.  So the real question is, how do I get the ActivityMetadata once I've done MyWorkflowDesigner.Load(XamlPath);?Thanks in advance.ChuckChuck Berg | Consulting Engineer | Corporate IT - Station Casinos, Inc.

Aggregate only filtered members

I’m creating a measure in a cube as lookup from another cube: CREATE MEMBER CURRENTCUBE.[MEASURES].[Test]  AS     lookupcube("[Cube1]",     "([Measures].[PublicPrice],     [Cube1 DimDate].[TIME Hierarchy].[" + [Cube2 DimDate].[TIME Hierarchy].currentmember.name + "]     )")     If I select in filter only January 2010 and February 2010 in Cube1, for Quarter 1  I obtain 100; in Cube2  I obtain 150. Even if March 2010 is not selected in Cube2 the aggregation level (the Quarter) considers it. Could someone help me? Thank you very much, Giada

How to persist workflow with non-serializable arguments

I have a workflow that has some arguments for underlying components (DAL implementation, email sender etc). It also has a  WCF data contract and other serializable arguments. I need to persist and unload this workflow and then resume later on. It is throwing an exception because it is trying to serialize and persist all the workflow arguments. Is there a way to get the serializer to persist only those arguments that can be serialized?

What is best way to aggregate tasks in Intranet?

A lot of Intranet users are challenged to keep track of tasks across multiple team sites. Anyone find a technique that works? It seems like it would make sense to live in a user's My Site on SP2010.  Anyone doing it that way?      

Examining the context/arguments for an idle workflow

Short version: How can I get the values of arguments for an activity that's been idled?  That is, is there some way to load the activity up (without resuming it) so that I can retrieve the values of the arguments? Long version: I have an activity.  It takes in a large amount of information via its arguments, emails a user that they've got work to do, and then creates a bookmark and idles.  That part works fine. The user clicks the link in the email and goes to a page that presents to them information about the work they need to do.  This information comes from the arguments passed into the activity.  I'm missing the magic link that'll get me that information.  I could have the activity persist all of its arguments to the database, but this seems redundant (as the instance is already persisted to the database) and inefficient (I only need this information while the activity is idle).   What am I missing?

The values provided for the root activity's arguments did not satisfy the root activity's requirem

I have flowchart as implementation  of activitybuilder and added two dynamicactivityproperty to it. The flowchart is having flowdecision and 3 other customized activities with same defined arguments. But when i invoke the workflow and pass dictionary, it gives the error: The values provided for the root activity's arguments did not satisfy the root activity's requirements: 'Sample Workflow': The following keys from the input dictionary do not map to arguments and must be removed: FolderName, FolderSize.  Please note that argument names are case sensitive. Parameter name: rootArgumentValues Flowchart       fc = new Flowchart();  protected override void OnInitialized(EventArgs e) { wd.Load(new ActivityBuilder { Implementation =  fc});             ModelTreeManager mtm = wd.Context.Services.GetService<ModelTreeManager>();             ModelItem ab = mtm.Root;             ModelItemCollection argsAndProperties = ab.Properties["Properties"].Collection;                     InArgument<string> foldername = new InArgument<string>();    &n

arguments null in onSuccess method

HiI'm trying to validate user input into a textbox via a web service.I'm using a Dynamic Data site, with a custom Dynamic Data field to show this particular value. In the dynamic data field I've added a custom validator - <asp:CustomValidator id="CustomValidator1" runat="server"        ErrorMessage="Invalid Item ID!"      ControlToValidate="TextBox1"      OnServerValidate="ServerValidate"      ClientValidationFunction="validateItem"     /><asp:CustomValidator id="CustomValidator1" runat="server" ErrorMessage="Invalid Item ID!" ControlToValidate="TextBox1" OnServerValidate="ServerValidate" ClientValidationFunction="validateItem" />In a custom page, I have the following Javascript - <script type="text/javascript"> <!-- function validateItem(source, arguments) { InventTableService.exists(arguments.Value, onComplete, onError); } function onComplete(source, arguments) { alert(source); arguments.IsValid = source; }

Aggregate using update

Is this possible?  It seems trivial, but I have learned that doesn't mean there is a trivial fix.  I have a table that has an index, quantity, and total.  I want the total fields to be the sum of quantity per distinct index. (And no, I can't redesign the database from scratch, or change the way our organization stores data, etc.  These are the parameters I have to work within.) pIx    pValue    pTotalIX 1    2    0 (should be 9) 1    4    0 (9) 1    3    0 (9) 2    1    0 (7) 2    2    0 (7) 2    4    0 (7) 32    11    0 (74) 32    22    0 (74) 32    41    0 (74) Can I update the pTotalIX column without resorting to record-by-record processing, etc? Caveat - this is a table variable.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend