.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 Server user-defined aggregate returns an error if aggregating 0 rows

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server
I have this SQL Server user-defined aggregate:

[SqlUserDefinedAggregate(Format.UserDefined, Name="median", IsInvariantToDuplicates=false, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true, MaxByteSize=8000)] 
public class MedianCalculator : IBinarySerialize { 
    private List<double> values; 
    public void Init() { 
        values = new List<double>(); 
    public void Accumulate(SqlDouble value) { 
        if (!value.IsNull) 
    public void Merge(MedianCalculator other) { 
    public SqlDouble Terminate() { 
        if (values == null || values.Count == 0) 
            return SqlDouble.Null; 
        return (values[(int)Math.Floor((values.Count - 1) / 2.0)] + values[(int)Math.Ceiling((values.Count - 1) / 2.0)]) / 2.0; 
    public void Read(BinaryReader r) { 
        int c = r.ReadInt32(); 
        values = new List<double>(c); 
        for (int i = 0; i < c; i++) 
    public void Write(BinaryWriter w) { 
        for (int i = 0; i < values.Count; i++) 
After deploying this aggregate, I try to run this query:

View Complete Post

More Related Resource Links

Best practice for user defined error messages in SQL Server 2005/2008 --Need help

My requirement is to setup a standard practice for our team in handling DB erros and also  user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.  For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000.  Ex: 80001 | Company name already exists. please check. Here is the sample code ********************************************************************* CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]  -- Add the parameters for the stored procedure here  (@company_name VARCHAR(50),  @company_code VARCHAR(3),  @user_id INT  ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  BEGIN TRY -- Begin Try Block      -- Validate company name   IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)    RAISERROR(90021, 16, 1)      INSERT INTO companyprofile (company_name,       company_code,       createdby)   VALUES(@company_name,     @company_code,     @user_id)    END TRY  -- End Try Block &

"A .NET Framework error occurred during execution of user-defined routine or aggregate" followed by


I have some check constraints that call CLR functions.  Functions like 'CheckMailingAddress' and 'CheckPassword' just accept an nvarchar(4000) parameter, run the parameter through a Regex, and return true if it matchs and false if it doesn't.

Some of my functions are working fine.  They used to all work fine, and nothing has really changed, except all my database fields are nvarchar instead of varchar now (I added Unicode support).

I cannot see any reason why the CheckMailingAddress function fails, but the other functions succeed.  When I run CheckMailingAddress in a standalone app, it works just fine.  When it runs inside SQL Server, I get the message 'A .NET Framework error occurred during execution of user-defined routine or aggregate: "CheckMailingAddress": .', and as you can see there is no error message.  Just a space where there should be one, followed by a period.  I cannot explain this.

Compiling in Visual Studio 2010 and running on SQL Server 2008.


I noticed that it's only failing on functions that were built to accept null values (in the case where "allow_null" is true in the code below).  The check functions are auto-generated from custom attributes on the class in the C# code, and the MSIL is slightly different if the attribute indicates that the database

User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

"Type ASP.x is not defined" error for dynamic user control when publishing updateable


I have a user control (ContainerCtrl) which programmatically instantiates another user control (SimpleCtrl) in OnLoad. It builds and runs fine, but when I publish it I get the error "error BC30002: Type 'ASP.SimpleCtrl' is not defined." (If I publish without the "updateable" option there's no error, but I need the site to be updateable.)

I assume this has to do with the particulars of how the assembly(ies) are built for the different compiler options. My question is, how can I get it to work properly for "updateable" publishing?

VS 2008 / .NET 3.5 sp1

The code follows (web.config is the default from Visual Studio).

Many thanks, Dave


<%@ Control Language="VB" ClassName="SimpleCtrl" CodeFile="SimpleCtrl.ascx.vb" Inherits="_SimpleCtrl" %>

    Simple control


Partial Class _SimpleCtrl
    Inherits UserControl

    'Nothing to see here.

End Class


<%@ Control Language="VB" CodeFile="ContainerCtrl.ascx.vb" Inherits="_ContainerCtrl"

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

Linked Server To MS Access using Jet Returns "unspecified error"

I'm have SQL SERVER 2008 on Win2008 Server. I have several linked servers to Oracle (odbc) and have just created Linked Servers to MS access 2003 databases on network shares. I can 'test connection' and select data from the linked ms access servers using 4 part naming no problem. As soon as I do this, the Oracle db links stop working. I reboot the server, test connection to Oracle dbs , all ok there, run test connection to ms access dbs and receive the following;Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink"".OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) Reboot the server, test connection to ms access db's, all ok, then test connection to oracle, and receive the following;A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) (Microsoft SQL Server, Error: 109) Reboot server, perform test again, toggling the order and I get the same results.Have tried opening ms access via openrowset and "execute ('select count(*) from SomeTable') at MYAccessLink" to rule out test connection (sp_testlinkedserver) doing something to crash the links. I have checke

User does not have permission to perform this action( Microsoft SQL Server, Error:15247)



I was able to do my work with ease on my Database server, until sometime Friday evening. Don't exactly know what transpired, only that I can now no longer run queries or even create new databases. Here is a sample of what happens when I try to create a database

Create Database PetitionSample

Msg 262, Level 14, State 1, Line 1

CREATE DATABASE permission denied in database 'master'.

I used to be able to create databases, create new users and so on. My Server is installed on a Vista machine and I am running Developer version and using Management studio 2008 to access it. Anyone have any idea what could be going on?


Login failed for user:Token-based server access validation failed with an infrastructure error


I would appreciate help with the following issue:


I have created a local group in our SQL 2008 server and added two Windows user accounts "DOMAIN\UserName"  I  then added the local group to the database and granted read only access.


The users are trying to link tables using MS Access using and ODBC connection and getting the following error.

Users are not system administrators.


Date                      6/30/2010 1:01:54 PM

Log                         SQ

The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)





I have two databases xxx and yyy.

I added a new user to xxx named as "Admin". It ownes some schemas.

Afterwards I added a new user to yyy named as "Admin". It ownes the same schemas.

Then I pressed ok button, got the error:" The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)".



Could you please give me some advice?



Cannot find either Column "dbo" or the user-defined function or aggregate "dbo.GetCandiateID", or th



Good morning to everybody. I am not understanding why I am getting the following error (also mentioned in the subject line).

I have written the following function in SQL Server 2005:

    declare @candidate_id int
    SELECT @candidate_id = max(c.candidate_id) from dbo.candidates c
    if(@candidate_id is null)
        set @candidate_id = 1001
        set @candidate_id = @candidate_id + 1
    return @candidate_id

The function compiled properly. I have used the above function like below in the query:

select dbo.GetCandidateID()

I am getting the following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetCandidateID", or the name is ambiguous

Accessing response of HttpWebRequest, even when request returns 500 Internal Server Error



Hoping someone can point me to a solution, haven't been able to find one yet.

I'm using the standard way for send HttpWebRequest

		webRequest = (HttpWebRequest)WebRequest.Create(request.URL);
webRequest.Method = "POST";
webRequest.ContentType = "text/xml";


postStream = webRequest.GetRequestStream();
postStream.Write(requestBuffer, 0, requestBuffer.Length);
webResponse = (HttpWebResponse)webRequest.GetResponse();
responseStream = webResponse.GetResponseStream();
responseReader = new StreamReader(responseStream);
string response = responseReader.ReadToEnd();

This is all fine, working correctly, no poblems.

However the actual webservice that i'm calling returns a 500 Internal Server Error code for standard faults

HTTP/1.1 500 Internal Server Error
Content-Type: text/xml; charset=utf-8
Content-Length: 703
Date: Fri, 22 Oct 2010 04:50:48 GMT

<?xml version="1.0" en
coding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.o

Error 15401 - adding AD user to SQL Server




The current setup is as follows:

SQL 2005 64bit v9.0.3054

Windows 2003 SP2


Problem: When trying to add an AD user to SQL, I receive error 15401


Background: The user's last name was recently changed and so all relevant AD fields were appropriately changed:

Lastname, Displayname, E-mail, UserLogonname, UserLogonname (pre-Windows 2000). The user's previous email account has not been changed except for additional email addresses to account for the new name and old name. Also note, the uses Exchange alias is still the previous account name.


When I enter the user's new account name into the Login Name field in SQL I recieve the 15401 error. When I do a search for the user's new account name SQL finds the previous account name and not the new one. I have reviewed KN 324321 and that article does not resolve this error. The user account can be found be only using the original account name and not the new one.


On the same SQL server, I opened the security settings for a folder and added the new user's account name and it was found. I then had it search for the user's old login name and it could not find it.

Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456, Severity: 14, State: 1) When I us


When I use the following setting to connect server, it succeeds
Server type: Database Engine
Server name: Home-PC
Authentication: Windows Authentication

User name: Home-PC\John

However, when I try to use the following setting to connect the same server, it fails as always
Server type: Database Engine
Server name: Home-PC
Authentication: SQL Server Authentication 

The Error Message as follows
TITLE: Connect to Server
Cannot connect to Home-PC.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Then, I enter the server to change some

how to pass a user defined object as an argument to a web server?


I have the following scenario:

1. my web server is in c#, and my web server client is in Java

2. on the java side, I have a user defined object,  (it is implemented as a java bean) and i would like to pass this object, as an argument to one of the web server methods.

3. the problem however is, that on the C# side, i get a compilation error, saying that the type or namespace could not be found.

I read some forum threads saying that it should be serialised, but i have no idea of what to do, to be quite honest. Could someone please help by either explanation or a helpful link with an example?

Many thanks,


CommunicationException was unhandled by user code;The remote server returned an error: NotFound

Hi ,

Getting an err :CommunicationException was unhandled by user code;The remote server returned an error: NotFound.


1. we are sending large amount of data from Silverlight client to wcf svc and storing in DB.
2. when the data size is less its working fine.
3. But when we send large data we get an err like

CommunicationException was unhandled by user code

The remote server returned an error: NotFound

4. we also increased the Buffer Sizes in the binding part in the config files.

most of the google results , forums are saying to increase the Buffer Sizes, but still we face the same proble.

Any clues on how to overcome this err will be a gr8 help!




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