.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

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

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :ASP.Net


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

View Complete Post

More Related Resource Links

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!

Calling a user defined function without Schema Prefix

Calling a user defined function without Schema


 Hi ,

I created a LOGIN User U1 and CREATED a database user DU1 and linked him with the Login User and I created a schema say S1 and set S1 as the default schema for DU1 .

I have a stored Procedure  and a userdefined function inside S1 . I logged in as the Login user, I can execute that procedure directly  without any schema prefix  but I cannot call a user defined function F1 directly (without schema prefix) . it throws an exception

"function name is not a recognized built-in function name"

How can i call a UDF directly with out a schema prefix


--Login User Creation
--Database User Creation and assign to default schema
--Giving User Privilege
EXEC sp_addrolemember db_owner,'S11'

Using a User Defined Function in a case statement


I created a function and am trying to access it through a case statement, but I am getting the error:

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

The case statement is below:

CASE WHEN c.program_id = 5 and CAST(minutes) As float

ODBC API to retrieve User-Defined DataType of a Column in a ResultSet ?



ODBC APIs such as SQLDescribeCol(), SQLColAttribute() could be used to retrieve SQLDataType of a particular column in a ResultSet.

Similarly, Is there any ODBC API which could be used to retrieve User-Defined DataType of a particular column in a ResultSet?



SQL Server user-defined aggregate returns an error if aggregating 0 rows

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:

Execution of User Defined Function fails with JDBC, when selectMethod=cursor is enabled



When I try to execute  a function using CallableStatement after setting selectMethod=cursor, it throws error com.microsoft.sqlserver.jdbc.SQLServerException: The request for procedure 'MYFUNCTION' failed because 'MYFUNCTION' is a function object. ErrorCode:2809 SQLState:S0001


If I remove selectMethod=cursor, the function is executing properly

Client details

JDBC driver : JDBC driver 3.0(sqljdbc4.jar)

Tested with JDBC driver for SQL server 2005 SP2 al;so.

OS : Windows XP Professiional


java version "1.6.0_22"

Java(TM) SE Runtime Environment (build 1.6.0_22-b04)

Java HotSpot(TM) Client VM (build 17.1-b03, mixed mode)


Server details

SQL Server 2005(Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3) )


Did somebody face this? How to solve it?

We need selectMethod=cursor because of issue mentioned here(http://connect.microsoft.com/SQLServer/feedback/details/295907/resultsetmetadata-gettablename-returns-null-or-inconsistent-results) and the solution as suggested.



"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"

Need function to find difference between two dates


Hi all

I need a function written in vb to find the exact month difference between two dates.

For example

    Start Date : 01/01/2010

    End Date   : 15/02/2010

 then the difference value should be like this 1.5  i.e i need the difference in decimal value.

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

User Defined Data Type - using SMO *** NEED HELP ! ***

Hi,Is it possible to script out the User Defined Data Types via SMO in .NET and specifically VB.Net?  I have not been able to find some examples on how to do this.  If you have some experience with this, pleasepost some examples or provide links where I can find this information.Thank you in advance.-Sydney

I have 3 triggers defined on an sqlserver2008 table and I can't find them with c#

I've got the code below and when I run it, it always says there are 0 triggers in table.Triggers.  I added some debugging code checkin the table.HasDeleteTrigger, etc. and from that, I get the exception: "To accomplish this action, set property HasDeleteTrigger." when I try to set it to bool hasDeleteTrigger. I can get stored procedures find, just not triggers. Below is my problem code: foreach (Database database in databaseCollection) { if (database.Name.Equals(catalogName)) { // first we need to delete triggers associated with all tables that begin // with our prefix. we can skip our the framework tables because those // don't have triggers on them // grab all tables, filter for non prefixed ones in foreach loop so we can delete triggers from non prefixed ones results.AddRange(from Table table in database.Tables select table.Name); foreach (var tableName in results) { var table = new Table(database, tableName, CrSyncDbSchemaName); table.Refresh(); bool hasDeleteTrigger = table.HasDeleteTrigger; bool hasAfterTrigger = table.HasAfterTrigger; bool hasInsertTrigger = table.HasInsertTrigger;

Hide data for specific column from a user

Greetings, I have a problem. I would like to restrict showing specific users (or roles or schemas) data in a column. First I thought DENY option will do it but figured out that it won't. DENY denies the column being in the SELECT query. I would still like to call the column but if access to the data in that column is restricted it would show only null values and not the real ones. So... I would like call the next query from user TestUser: SELECT ID,Name,Address FROM Employee But if access to Address is restricted for TestUser I would get results like: ID Name Address 1 John Smith NULL 2 Rick Ross NULL ... Is it possible to do something like that in SQL Server 2005/2008? I was thinking of storing that kind of information in a table (for which tables, which columns for which users access is restricted) and then manipulate it on the application site but my programmers tell me that they use components which always call the full table (like SELECT *).  Thank you for your answer.

how to include a user defined table type as input for stored procedure

Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

Access denied when user clicks refresh BDC column button

Access denied occurs with any non Site Collection Administrator regardless of rights.  It only occurs in this site collections, others they are able to refresh.  In this particular collection they can add a BDC column to a list, edit an item and place/resolve a value in the bdc col; but when they refresh they are given an acess denied.  Any thoughts on what they need read access to?  This goes away if I give site admin privs (dont want to do that) or give them read only access on the web application (dont want to do that either).
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