sql server keeps looking for my User Defined Scalar Function in the Master DB

  • I have a Visual Studio project web site that I have been using for years. The move to SQL server 2019, has apparently broken it.

    When I call the dataadapter.Fill function it tries to find my User Defined Scalar Function in the Master Database.

    System.Data.SqlClient.SqlException: 'Cannot find either column "master" or the user-defined function or aggregate "master.dbo.ufnStringToPbase", or the name is ambiguous.'

    It is in the User Database, and not the master DB.

    here is the function;

    USE [nms_rt]
    GO
    /****** Object: UserDefinedFunction [dbo].[ufnStringToPbase] Script Date: 06/09/2021 09:16:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description: <Description, ,>
    -- =============================================
    ALTER FUNCTION dbo.ufnStringToPbase(@string char(8))
    returns int
    as
    begin
    declare @line int, @port int,@poll int,@pbase int
    SET @line = 0
    SET @port = 0
    SET @poll = 0
    set @pbase=0
    set @line =
    (ASCII(substring(@string,1,1)))
    set @line =
    (ASCII(substring(@string,1,1))-48)*100 +
    (ASCII(substring(@string,2,1))-48)*10 +
    (ASCII(substring(@string,3,1))-48)

    set @port =
    (ASCII(substring(@string,5,1))-48)

    set @poll =
    (ASCII(substring(@string,7,1))-48)*10 +
    (ASCII(substring(@string,8,1))-48)

    set @pbase = (@line * 1000)+(@port*100)+(@poll)+6000000
    return @pbase
    end

    Thanks, any suggestions would be helpful

  • issue is not sql but with your code and project - dataadapter.Fill for some reason has been given the wrong database to connect to - that is what you need to look at and address (and not much we can do without the full solution containing that code.

  • Can you provide the code that calls this function?  Is it called directly from your application code - or is it within a stored procedure or other query?

    And do you have some example data that would be used to call the function?  I think the function can be optimized - but if it is called directly then there wouldn't be any benefit.  If it is called within a query it might be worth the time to optimize it.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey, here is the Code I am using in the project;

     

    //using AjaxControlToolKit;
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public partial class Default : System.Web.UI.Page
    {
    static DataTable dt;
    string strConnection = ConfigurationManager.AppSettings["GroupStatus"];

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
    Calendar1.SelectedDate = DateTime.Today;
    Calendar2.SelectedDate = DateTime.Today;
    //RadCalendar1.SelectedDate = DateTime.Today;
    //RadCalendar2.SelectedDate = DateTime.Today;
    }
    }

    private void BindGrid()
    {
    if (dt.Rows.Count > 0)
    {
    grdTrending.DataSource = dt;
    grdTrending.DataBind();
    }
    }

    //protected void ddlWayside_SelectedIndexChanged(object sender, EventArgs e)
    //{
    // txtSearch.Text = ddlWayside.SelectedItem.Text;
    //}

    protected void BtnSearchClick(object sender, EventArgs e)
    {
    dt = GetRecords();
    BindGrid();
    GetRecords();
    }

    protected void GrdSearchPageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    BindGrid();
    grdTrending.PageIndex = e.NewPageIndex;
    grdTrending.DataBind();
    }
    private DataTable GetRecords()
    {
    SqlConnection conn = new SqlConnection(strConnection);
    conn.Open();
    SqlCommand cmd = new SqlCommand("Trending", conn)
    {
    CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.Add("@WaySide", SqlDbType.VarChar).Value = ddlWayside.SelectedItem.Text;
    cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = Calendar1.SelectedDate;
    cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = Calendar2.SelectedDate;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    conn.Close();
    grdTrending.DataSource = ds.Tables[0];
    grdTrending.DataBind();
    return ds.Tables[0];
    }

    protected void IbtnExportToExcelClick(object sender, ImageClickEventArgs e)
    {
    Export_ToExcelSheet(dt, Response);
    }

    private void Export_ToExcelSheet(DataTable dt, System.Web.HttpResponse response)
    {
    DataGrid grdTrending = new DataGrid
    {
    DataSource = dt
    };
    grdTrending.DataBind();
    response.Clear();
    response.AddHeader("content-disposition", "attachment;filename=WaysideTrendingReport.xls");
    response.Charset = "";
    response.Cache.SetCacheability(HttpCacheability.NoCache);
    response.ContentType = "application/vnd.xls";
    StringWriter stringWrite = new StringWriter();
    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    grdTrending.RenderControl(htmlWrite);
    response.Write(stringWrite.ToString());
    response.End();
    }
    }

    Thanks,

  • Here is a sampling of the data table;

    WEA	Date_Time	Base1	Base2	Base3	SSI1	SSI2	SSI3	Lock1	Lock2	Lock3	Pref_base	dbu	alarm
    225280720000 2021-06-09 12:32:30.673 453.2.01 389.3.01 804.1.01 61 53 37 0 0 0 453.2.01 0 0
    761060010000 2021-06-09 13:49:46.170 158.1.01 160.1.01 365.1.01 222 0 12 1 1 0 158.1.01 1 0
    761550130000 2021-06-09 15:02:10.077 483.1.01 497.1.01 847.2.01 222 159 184 1 1 0 483.1.01 1 0
    225291170000 2021-06-09 16:38:57.420 384.3.01 216.3.01 836.2.03 61 38 15 0 0 0 384.3.01 0 0
    761770070000 2021-06-09 17:47:18.583 554.1.01 548.1.01 549.3.01 150 172 150 1 1 0 554.1.01 1 0
    767170450000 2021-06-09 19:25:45.860 282.3.01 288.2.01 288.1.01 34 14 7 0 0 0 282.3.01 0 0

    Thanks,

  • What is the value of the following:

    string strConnection = ConfigurationManager.AppSettings["GroupStatus"];

    ?

    My thoughts are that strConnection is connecting to MASTER or the user specified in the connection string has master as the default database and no default is specified in the connection string.  Alternately, did you change the stored procedure "Trending" (SqlCommand cmd = new SqlCommand("Trending", conn))?  Might not hurt to look at the source behind "Trending" to see if it is calling out to master.

    One more thought here - did you do an in-place upgrade or a migration install?  If you did a migration install, did you confirm that the function "ufnStringToPbase" doesn't in fact exist on master in the old instance?

     

  • The code you supplied does not show the usage of the function - I have to assume it is being called in the stored procedure.  The stored procedure has 3 parameters - none of which seem to apply to the function.

    What values are passed into this function - and what values are returned from this function?  The function contains an 8 character string - and returns an integer.  What is this 8 character string?

    This can be converted to an inline-table valued function - or simplified to something like this:

     Select pbase = (v1.line * 1000) + (v2.port * 100) + (v3.pol1) + 6000000
    From (Values ((ASCII(substring(@string, 1, 1)) - 48) * 100
    + (ASCII(substring(@string, 2, 1)) - 48) * 10
    + (ASCII(substring(@string, 3, 1)) - 48))) As v1(line)
    Cross Apply (Values ((ASCII(substring(@string, 5, 1)) - 48))) As v2(port)
    Cross Apply (Values ((ASCII(substring(@string, 7, 1)) - 48) * 10
    + (ASCII(substring(@string, 8, 1)) - 48))) As v3(pol1);

    This doesn't resolve the issue you are having - but might improve performance a bit depending on how you are calling the function now.

    You should also check for any synonyms in the user database.  There may be a synonym that redirects the call to the master database - which is possible if you have multiple user databases that would need access to this function.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Brian Gale, here is the Connection Strings in my Web.Config;

      <appSettings>
    <add key="GroupStatus" value="Data Source=X.X.X.X;Database=NMS_RT;User ID=username;Password=password" />
    </appSettings>
    <connectionStrings>
    <add name="Trending" connectionString="Data Source=X.X.X.X;Initial Catalog=NMS_RT;Persist Security Info=True;User ID=username;Password=password" providerName="System.Data.SqlClient" />
    <add name="NMS_CFGConnectionString" connectionString="Data Source=X.X.X.X;Initial Catalog=NMS_CFG4;Persist Security Info=True;User ID=username;Password=password" providerName="System.Data.SqlClient" />
    </connectionStrings>

    Keep in mind that this has worked up until we moved to SQL 2019 from SLQ 2016.

  • Jeff, here is the StoredProcedure that gathers the data. the Scalar function converts the numeric values for the bases and puts them into a text readable format.

    USE [nms_rt]
    GO
    /****** Object: StoredProcedure [dbo].[Trending] Script Date: 06/11/2021 04:58:24 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[Trending]
    @WaySide VARCHAR(50),
    @StartDate Date,
    @EndDate Date
    AS

    BEGIN

    SET NOCOUNT ON

    SELECT ( Grp.name + ',' + Grp.state ) AS Wayside,
    ( SELECT 'G'
    + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
    + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
    ) AS 'Group ID',
    ( SELECT [Name] + ',' + [State]
    FROM [nms_cfg4].[dbo].[Base_Equipment]
    WHERE Base_Equip_Address = ( SELECT TOP 1
    Base_Equip_Address
    FROM [nms_cfg4].[dbo].[be_xref_oa]
    WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
    )
    ) + ' ( ' + [base1] + ')' AS 'Primary',
    [SSI1],
    ( SELECT [Name] + ',' + [State]
    FROM [nms_cfg4].[dbo].[Base_Equipment]
    WHERE Base_Equip_Address = ( SELECT TOP 1
    Base_Equip_Address
    FROM [nms_cfg4].[dbo].[be_xref_oa]
    WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
    )
    ) + ' ( ' + [base2] + ')' AS 'Secondary',
    [SSI2],
    ( SELECT [Name] + ',' + [State]
    FROM [nms_cfg4].[dbo].[Base_Equipment]
    WHERE Base_Equip_Address = ( SELECT TOP 1
    Base_Equip_Address
    FROM [nms_cfg4].[dbo].[be_xref_oa]
    WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
    )
    ) + ' ( ' + [base3] + ')' AS 'Tertiary',
    [SSI3],

    Date_Time


    --CAST([Date_Time] AS Date) AS Date

    FROM [NMS_RT].[dbo].[RT_Group_Coverage] AS Cov
    INNER JOIN [nms_cfg4].[dbo].[ATCS_Group] AS Grp
    ON Grp.Group_Address = Cov.[WEA]
    WHERE Grp.name = @WaySide
    AND Date_Time >= @StartDate
    AND Date_Time < DateAdd(day, 1, @EndDate)
    ORDER BY WEA, Date_Time

    END


    -- EXEC Trending 'Redondo East',



  • Frederoco, here is the codebehind for the web page;

    //using AjaxControlToolKit;
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public partial class Default : System.Web.UI.Page
    {
    static DataTable dt;
    readonly string strConnection = ConfigurationManager.AppSettings["GroupStatus"];

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
    Calendar1.SelectedDate = DateTime.Today;
    Calendar2.SelectedDate = DateTime.Today;
    //RadCalendar1.SelectedDate = DateTime.Today;
    //RadCalendar2.SelectedDate = DateTime.Today;
    }
    }

    private void BindGrid()
    {
    if (dt.Rows.Count > 0)
    {
    grdTrending.DataSource = dt;
    grdTrending.DataBind();
    }
    }

    //protected void ddlWayside_SelectedIndexChanged(object sender, EventArgs e)
    //{
    // txtSearch.Text = ddlWayside.SelectedItem.Text;
    //}

    protected void BtnSearchClick(object sender, EventArgs e)
    {
    dt = GetRecords();
    BindGrid();
    GetRecords();
    }

    protected void GrdSearchPageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    BindGrid();
    grdTrending.PageIndex = e.NewPageIndex;
    grdTrending.DataBind();
    }
    private DataTable GetRecords()
    {
    SqlConnection conn = new SqlConnection(strConnection);
    conn.Open();
    SqlCommand cmd = new SqlCommand("Trending", conn)
    {
    CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.Add("@WaySide", SqlDbType.VarChar).Value = ddlWayside.SelectedItem.Text;
    cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = Calendar1.SelectedDate;
    cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = Calendar2.SelectedDate;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    conn.Close();
    grdTrending.DataSource = ds.Tables[0];
    grdTrending.DataBind();
    return ds.Tables[0];
    }

    protected void IbtnExportToExcelClick(object sender, ImageClickEventArgs e)
    {
    Export_ToExcelSheet(dt, Response);
    }

    private void Export_ToExcelSheet(DataTable dt, System.Web.HttpResponse response)
    {
    DataGrid grdTrending = new DataGrid
    {
    DataSource = dt
    };
    grdTrending.DataBind();
    response.Clear();
    response.AddHeader("content-disposition", "attachment;filename=WaysideTrendingReport.xls");
    response.Charset = "";
    response.Cache.SetCacheability(HttpCacheability.NoCache);
    response.ContentType = "application/vnd.xls";
    StringWriter stringWrite = new StringWriter();
    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    grdTrending.RenderControl(htmlWrite);
    response.Write(stringWrite.ToString());
    response.End();
    }
    }
  • Ok! I figured it out. Somewhere along the way the Master database tag was added into a portion of the Stored Procedure;

    SELECT    [Name] + ',' + [State]
    FROM [nms_cfg4].[dbo].[Base_Equipment]
    WHERE Base_Equip_Address = ( SELECT TOP 1
    Base_Equip_Address
    FROM [nms_cfg4].[dbo].[be_xref_oa]
    WHERE x_pbase = master.dbo.ufnStringToPbase([base1])

    Removing the keyword master from the WHERE statement solved the issue.

    Thank you all! Sometimes a different set of eyes helps to root out a simple issue.

     

    Jeff, I will look your code optimization over Thanks again!

  • As you said in your original post...

    The move to SQL server 2019, has apparently broken it.

    The reason the move broke it is now obvious... the function lived in the master database and it didn't get copied for the migration to 2019.  That might be a bigger problem than you first expected...

    If you were not the one that wrote the original code, then removing the "master." may have some serious implications.  It might mean that there is other code that depends on the correct operation of the dbo.ufnStringToPBase() function in the master database and you're in the process of breaking all that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden, yes, I am the original author. I had another SQL database person working on it late last year before moving to SQL 2019. This is the only code that depends on that Scalar Function.

     

    Thank you for the insight though.

  • brian.cook wrote:

    I have a Visual Studio project web site that I have been using for years. The move to SQL server 2019, has apparently broken it.

    When I call the dataadapter.Fill function it tries to find my User Defined Scalar Function in the Master Database.

    System.Data.SqlClient.SqlException: 'Cannot find either column "master" or the user-defined function or aggregate "master.dbo.ufnStringToPbase", or the name is ambiguous.'

    It is in the User Database, and not the master DB.

    here is the function;

    USE [nms_rt]
    GO
    /****** Object: UserDefinedFunction [dbo].[ufnStringToPbase] Script Date: 06/09/2021 09:16:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description: <Description, ,>
    -- =============================================
    ALTER FUNCTION dbo.ufnStringToPbase(@string char(8))
    returns int
    as
    begin
    declare @line int, @port int,@poll int,@pbase int
    SET @line = 0
    SET @port = 0
    SET @poll = 0
    set @pbase=0
    set @line =
    (ASCII(substring(@string,1,1)))
    set @line =
    (ASCII(substring(@string,1,1))-48)*100 +
    (ASCII(substring(@string,2,1))-48)*10 +
    (ASCII(substring(@string,3,1))-48)

    set @port =
    (ASCII(substring(@string,5,1))-48)

    set @poll =
    (ASCII(substring(@string,7,1))-48)*10 +
    (ASCII(substring(@string,8,1))-48)

    set @pbase = (@line * 1000)+(@port*100)+(@poll)+6000000
    return @pbase
    end

    Thanks, any suggestions would be helpful

    Now that you've solved your problem with the master database problem, consider taking the code in the function up a couple of notches.   If it must remain as a Scalar UDF, at least document it and make it a bit simpler to understand.  The original author did understand that "all math" will tend to make things faster but did so to a fault and used many more substrings than were necessary.  With that, I offer the following direct replacement to get you started on the right track as a suggestion for future code, especially when it comes to a helpful flower box.

     CREATE OR ALTER FUNCTION dbo.ufnStringToPbase
    (@String CHAR(8))
    /*******************************************************************
    Purpose:
    Given an 8 character string containing information for the Line,
    Port, Poll, and other values, extract the Line, Port, and Poll
    information and convert to a prepended PBase value using the
    following logic.

    Given: 12345678
    ^^^ ^ ^^
    ||| | ||<--- Poll
    ||| |<------ Port
    |||<-------- Line
    Added: 6<----------- Leader/Offset
    Returns: 6123578 as an INT

    To summarize, positions 6 and 4 of the string are not used (which
    means they can be replaced with an empty string), is converted to an
    INT, and then is prepended with a Leader/Offset of "6".
    ---------------------------------------------------------------------
    Usage Examples:
    --===== Basic syntax
    SELECT PBase = dbo.ufnStringToPbase(@String)
    ;
    --===== Usage with table
    SELECT PBase = dbo.ufnStringToPbase(SomeStringCol)
    FROM dbo.SomeTable
    ;
    ---------------------------------------------------------------------
    Revision History:
    Rev 00 - Unknown - Author Unknown
    Rev 01 - 11 Jun 2021 - Jeff Moden
    - Full redaction, documentation, and unit test of the code.
    ********************************************************************/
    RETURNS INT AS
    BEGIN
    RETURN (
    SELECT 6000000
    + CONVERT(INT,STUFF(STUFF(@string,6,1,''),4,1,''))
    );
    END
    ;
    GO

    You also said that you've moved to 2019.  I've not yet had the opportunity to work with 2019 but... one of the features of 2019 is that some Scalar UDFs will auto-magically be "in-lined" if they can be (which makes for MUCH faster code).  Unless I miss my guess, the version above is much more likely to be in-lined than the original.  If nothing else, it's a hell of a lot easier to read and understand especially with the documentation in the flower box. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @JeffModen - this is why I asked for sample data being passed to the function.  We can make the assumption that the values being passed in are numeric - but that is only an assumption.

    Either way - looking at the code using this function I see an issue:

    1. The code is called in a sub-query using TOP 1 without an ORDER BY.  Possibly could be getting an incorrect value returned if there are more than 1 possible row returned
    2. The sub-query uses the value [base1] or [base2] or [base3] to lookup the state/name.

    With some sample data from the xref table - and sample data from the source table, it might be possible to create the code to perform that lookup in a single query using CROSS/OUTER APPLY to return all 3 values in a single row.  This could eliminate the requirement for a function at all - and improve the performance of the procedure.

    Worse case - the code for the function could be included directly using CROSS/OUTER APPLY for each base, returning the name/state/base in the correct format.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply