1

The function is a scalar function that returns nvarchar(1000), and I'm calling it from LINQ to SQL:

var allUserBranchIDs = appContext.DataContext.GetUserBranchIDs(appContext.PersonID, branchDelimiter);

And it gives this error:

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

It runs just fine when I call it from SQL Server Management Studio:

select [dbo].[GetUserBranchIDs](3678, ',')

Which suggests a permissions issue with the SQL user that the DataContext is using - but I'm using the exact same sa user in LINQ to SQL that I am in SSMS!

edit: here is the LINQ to SQL generated method:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.GetUserBranchIDs", IsComposable=true)]
public string GetUserBranchIDs([global::System.Data.Linq.Mapping.ParameterAttribute(Name="UserID", DbType="Int")] System.Nullable<int> userID, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="Delimiter", DbType="NVarChar(10)")] string delimiter)
{
    return ((string)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), userID, delimiter).ReturnValue));
}

edit: function definition:

CREATE FUNCTION [dbo].[GetUserBranchIDs] 
(
    @UserID int,
    @Delimiter nvarchar(10) = '/'
)
RETURNS nvarchar(1000)
AS
BEGIN

    declare @result nvarchar(1000) = null;

    select @result = coalesce(@result, '') + cast(a.BranchID as nvarchar(10)) + @Delimiter
    from (
        select distinct  
            b.BranchID      
        from vw_UserBranchCurrent a
        inner join vw_BranchDataCurrent b
            on a.BranchID = b.BranchID
        where
            ((@UserID is not null) and (a.UserID = @UserID))
    ) a;

    set @result = left(@result, len(@result) - len(@Delimiter));

    return @result;

END
ekolis
  • 6,270
  • 12
  • 50
  • 101
  • 3
    That TSQL query looks like a scalar-valued UDF, but your LINQ call looks like it's expecting a table-valued UDF. – David Browne - Microsoft Jul 30 '19 at 20:41
  • What result do you get when you run your code in SSMS? – Brian Jul 30 '19 at 21:37
  • It's not a permissions issue. Your error message would be much different if that were the case. – BeardOfTriumph Jul 30 '19 at 21:39
  • @DavidBrowne-Microsoft hmm, I just dragged the function from Server Explorer into my DBML file; I've used other scalar valued functions in the past with no problems - how do I need to change the LINQ call for a scalar valued function? – ekolis Jul 31 '19 at 13:07
  • @Brian I get a list of branch IDs separated by my specified delimiter, e.g. "1,3,4,5" – ekolis Jul 31 '19 at 13:07
  • I have no idea, as I haven't used L2S in a very long time. What does the C codegen for the function look like? – David Browne - Microsoft Jul 31 '19 at 14:03
  • @DavidBrowne-Microsoft I added the generated LINQ to SQL method to my question. Thanks! – ekolis Jul 31 '19 at 14:15
  • @ekolis do you get a scalar string value or do you get a table with a single row where that row is a single `VarChar` column? It makes a difference. It might be helpful if you updated your question to include the definition of `GetUserBranchIDs()`. – Brian Jul 31 '19 at 19:56
  • @Brian How would I tell the difference between a single cell table and a scalar result? I'll go ahead and add the definition of the function. – ekolis Jul 31 '19 at 20:20
  • 2
    @ekolis this line in the function definition tells you that it returns a scalar: `RETURNS nvarchar(1000)` – Brian Aug 01 '19 at 14:25
  • @Brian Oh, of course! So do you know what I'd need to change to prevent the error I'm seeing, now that you have the definition of the function? – ekolis Aug 01 '19 at 15:32

2 Answers2

2

I was going to say to check which current DB Catalog your are using in your script vs what catalog you are using in Sql Server Management Studio (SSMS).

select DB_NAME()

Then once you know the current database you could create another script to confirm which user defined functions are available for that catalog using something like this post but with a modification to allow for function name matching:

https://stackoverflow.com/a/15420524/2256278

DECLARE @yourFunctionGuessValue nvarchar(200) = 'someFunctionNamePart'
SELECT name, definition, type_desc 
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.object_id=o.object_id
WHERE type_desc LIKE '%function%'
and name LIKE '%' + @yourFunctionGuessValue + '%'

Hope this helps.

Noah Joseph Anderson
  • 303
  • 1
  • 2
  • 9
1

OK, I am a complete idiot... I thought I was using one database but I was actually using another (we have a separate database per client in each of three environments: dev/test/prod). That's why the function was missing - I had added it to the wrong database!

ekolis
  • 6,270
  • 12
  • 50
  • 101