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

  • Jeffrey, here is a sample of the be_xref.oa table;

    base_equip_addressoffice_app_addressx_pbaseregionportpollxprimarypath_typedesignator
    22009121220169126912111691201110NULL
    2201003120016003600310160030110NULL
    2201108222016108610820161081110NULL
    2201108322016108610830161082110NULL
    2201169122016169616910161690110NULL
    2201172222016172617220161721110NULL
    2201173222016173617320161731110NULL

    Here is a sample of the atcs_group table;

    Group_AddressSubregionLine_Addressleft_neighbor_groupright_neighbor_groupATCS_GroupNameStateDescriptionMilepostLongitudeLatitude
    220030010000NULL220030000000NULLNULL1Hauser B House West MainIDNULLNULLNULLNULL
    220030020000NULL220030000000NULLNULL2Hauser B House West BlueIDNULLNULLNULLNULL
    220030030000NULL220030000000NULLNULL3Downing West MainIDNULLNULLNULLNULL
    220030040000NULL220030000000NULLNULL4Downing West BlueIDNULLNULLNULLNULL
    220030050000NULL220030000000NULLNULL5Downing East MainIDNULLNULLNULLNULL
    220030060000NULL220030000000NULLNULL6Downing East BlueIDNULLNULLNULLNULL
    220030070000NULL220030000000NULLNULL7Hauser East MainIDNULLNULLNULLNULL
    220030080000NULL220030000000NULLNULL8Hauser East BlueIDNULLNULLNULLNULL
    220040100000NULL220040000000NULLNULL10Dalhart NorthTXNULLNULLNULLNULL
    220040110000NULL220040000000NULLNULL11Dalhart UPRRXNULLNULLNULLNULLNULL
    220040120000NULL220040000000NULLNULL12CP 4154TXNULLNULLNULLNULL
    220590010000NULL220590000000NULLNULL1Rhea WestTXNULLNULLNULLNULL
    220590020000NULL220590000000NULLNULL2Rhea EastTXNULLNULLNULLNULL
    220590030000NULL220590000000NULLNULL3Dickworsham WestTXNULLNULLNULLNULL
    220590040000NULL220590000000NULLNULL4Dickworsham EastTXNULLNULLNULLNULL

    What you are saying makes sense when you put it into context.

     

    The eight character string consists of that base id value, i.e. 601.1.01

    Here is the sample from the Base_Equipment

    Base_Equip_AddressSubregionEquipment_TypeNodeDeviceOffice_App_AddressPort_NumberPoll_AddressNameStateDescriptionHW_VersionSW_VersionDate_In_ServiceLongitudeLatitudecov_region
    22009121NULL2150912199999999901Browning West IPMTCNA1007-1.2.3 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
    22010031NULL21513199999999901Sig Eng Gen Primary IPKSCNA1000 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
    22011082NULL2151108299999999901Hazen East MP 63.54 IPNDCNA1009-1.1.0 IP x.x.x.xNULLNULL2016-06-13 00:00:00.000NULLNULL0
    22011083NULL2151108399999999901West Tower MP 552.61 IPTXCNA1007-1.2.3 IP x.x.x.xNULLNULL2018-08-27 00:00:00.000NULLNULL0
    22011691NULL2151169199999999901Oakland North IPNECNA1007-1.2.3 IP x.x.x.xNULLNULL2012-01-05 00:00:00.000NULLNULL0
    22011722NULL2151172299999999901Garretson South IPMNCNA1007-1.2.3 IP x.x.x.xNULLNULL2012-04-24 00:00:00.000NULLNULL0
    22011732NULL2151173299999999901Merrill North IPMNCNA1007-1.2.3 IP x.x.x.xNULLNULL2012-01-31 00:00:00.000NULLNULL0
    22011733NULL2181173399999999901106.82 RCPS IPMNCNA1000 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
    22011771NULL2151177199999999901Hauser B House West IPIDCNA1007-1.2.3 IP x.x.x.xNULLNULL2017-08-24 00:00:00.000NULLNULL0
    22011891NULL2151189199999999901MP 219.31 IPIACNA1009-1.1.0 IP x.x.x.xNULLNULL2015-12-14 00:00:00.000NULLNULL0

    Let me  know if you need something else.

  • Shifting gears back to the function that was used, I've added a smidgen of additional bullet-proofing.

     CREATE OR ALTER FUNCTION dbo.ufnStringToPbase
    (@String CHAR(8))
    /*******************************************************************
    Purpose:
    Given an 8 character string of precisely 8 digits 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

    To summarize, positions 6 and 4 of the string are not used and the
    string is prepended with a Leader/Offset of "6".

    Programmer Notes:
    1. If any of the characters in the original string are non-numeric
    digits, the function will return a NULL.
    ---------------------------------------------------------------------
    Usage Examples:
    --===== Basic syntax
    SELECT dbo.ufnStringToPbase(@String)
    ;
    --===== Usage with table
    SELECT 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.
    Rev 02 - 11 Jun 2021 - Jeff Moden
    - Modify the code to return a NULL if any non-numeric digits
    are present, which also forces a NULL to be returned for
    "short" values of less than 8 digits because the input
    parameter is a CHAR(8). The COLLATE is necessary because
    some collations recognize superscripted digits and some
    fractional representations as being the same as the 0-9
    digits. The COLLATE will also make it a bit faster.
    ********************************************************************/
    RETURNS INT AS
    BEGIN
    RETURN (
    SELECT 6000000
    + CONVERT(INT,STUFF(STUFF(@string,6,1,''),4,1,''))
    WHERE @String NOT LIKE '%[^0-9]%' COLLATE Latin1_General_BIN
    );
    END
    ;
    GO

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

  • the fourth and sixth digit is a decimal symbol ".", would that affect this?

  • If you could put that data into temp tables - with create statement and insert statements - it would help.  We could then take that sample data and use it to generate a working solution.  This also helps because it tells us what data types are used for each column - for example, what is the data type of the Group_Address column in the atcs_group table?

    For this - we really only need the columns that are related to the issue - we don't need every column in the table.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • You also need to provide samples of the [base1], [base2] and [base3] values.  Those are the values that are passed into the function - to return the base equipment address.

    The lookup is based on the region, port and poll - and identifies a potential problem.  Can there be the same combination of region|port|poll for separate base addresses, or is that a unique combination?  If unique combination - is it enforced by a constraint?

    Is the base value always formatted as {region}.{port}.{poll} - with region = 4 digits, port = 1 digit and poll = 2 digits?

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Here is the ATCS_Group Table;

    USE [nms_cfg4]
    GO

    /****** Object: Table [dbo].[ATCS_Group] Script Date: 06/11/2021 12:23:56 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[ATCS_Group](
    [Group_Address] [float] NOT NULL,
    [Subregion] [int] NULL,
    [Line_Address] [float] NOT NULL,
    [left_neighbor_group] [float] NULL,
    [right_neighbor_group] [float] NULL,
    [ATCS_Group] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [State] [varchar](3) NULL,
    [Description] [varchar](255) NULL,
    [Milepost] [varchar](12) NULL,
    [Longitude] [varchar](20) NULL,
    [Latitude] [varchar](20) NULL,
    CONSTRAINT [ATCS_Group_PK] PRIMARY KEY CLUSTERED
    (
    [Group_Address] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[ATCS_Group] WITH CHECK ADD CONSTRAINT [ATCS_Line_ATCS_Group_FK1] FOREIGN KEY([Line_Address])
    REFERENCES [dbo].[ATCS_Line] ([Line_Address])
    GO

    ALTER TABLE [dbo].[ATCS_Group] CHECK CONSTRAINT [ATCS_Line_ATCS_Group_FK1]
    GO

    ALTER TABLE [dbo].[ATCS_Group] WITH CHECK ADD CONSTRAINT [Subregion_ATCS_Group_FK1] FOREIGN KEY([Subregion])
    REFERENCES [dbo].[Subregion] ([Subregion])
    GO

    ALTER TABLE [dbo].[ATCS_Group] CHECK CONSTRAINT [Subregion_ATCS_Group_FK1]
    GO


    Here is the be_xref.oa;

    USE [nms_cfg4]
    GO

    /****** Object: Table [dbo].[be_xref_oa] Script Date: 06/11/2021 12:25:15 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[be_xref_oa](
    [base_equip_address] [float] NOT NULL,
    [office_app_address] [float] NOT NULL,
    [x_pbase] [int] NOT NULL,
    [region] [int] NOT NULL,
    [port] [tinyint] NOT NULL,
    [poll] [tinyint] NOT NULL,
    [xprimary] [bit] NOT NULL,
    [path_type] [tinyint] NOT NULL,
    [designator] [nvarchar](24) NULL,
    CONSTRAINT [PK_be_xref_oa] PRIMARY KEY CLUSTERED
    (
    [base_equip_address] ASC,
    [office_app_address] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[be_xref_oa] WITH CHECK ADD CONSTRAINT [FK_be_xref_oa_Base_Equipment] FOREIGN KEY([base_equip_address])
    REFERENCES [dbo].[Base_Equipment] ([Base_Equip_Address])
    GO

    ALTER TABLE [dbo].[be_xref_oa] CHECK CONSTRAINT [FK_be_xref_oa_Base_Equipment]
    GO

    ALTER TABLE [dbo].[be_xref_oa] WITH CHECK ADD CONSTRAINT [FK_be_xref_oa_Office_Applications] FOREIGN KEY([office_app_address])
    REFERENCES [dbo].[Office_Applications] ([Off_App_Address])
    GO

    ALTER TABLE [dbo].[be_xref_oa] CHECK CONSTRAINT [FK_be_xref_oa_Office_Applications]
    GO


     

  • Is the base value always formatted as {region}.{port}.{poll} - with region = 4 digits, port = 1 digit and poll = 2 digits?

    Yes, the first digit of the Region is always a 6, so 6xxx.x.xx

     

    the sample is in the be_xref.oa earlier in the chain.

  • So if the base value is 601.1.01 - the lookup will be for region 6601, port 1 poll 01?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Correct.

  • Okay - here is something for you to review:

    Declare @xref Table (base_equip_address int, office_app_address int, x_pbase int, region int, port int, poll int);
    Insert Into @xref (base_equip_address, office_app_address, x_pbase, region, port, poll)
    Values (22009121, 22016912, 6912111, 6912, 0, 11)
    , (22010031, 20016003, 6003101, 6003, 0, 1)
    , (22011082, 22016108, 6108201, 6108, 1, 1)
    , (22011083, 22016108, 6108301, 6108, 2, 1)
    , (22011691, 22016169, 6169101, 6169, 0, 1)
    , (22011722, 22016172, 6172201, 6172, 1, 1)
    , (22011732, 22016173, 6173201, 6173, 1, 1);


    Declare @base_equipment Table (base_equip_address int, Name varchar(100), State char(2));
    Insert Into @base_equipment (base_equip_address, Name, [State])
    Values (22009121, 'Browning West IP', 'MT')
    , (22010031, 'Sig Eng Gen Primary IP', 'KS')
    , (22011082, 'Hazen East MP 63.54 IP', 'ND')
    , (22011083, 'West Tower MP 552.61 IP', 'TX')
    , (22011691, 'Oakland North IP', 'NE')
    , (22011722, 'Garretson South IP', 'MN')
    , (22011732, 'Merrill North IP', 'MN')
    , (22011733, 'RCPS IP', 'MN')
    , (22011771, 'Hauser B House West IP', 'ID')
    , (22011891, 'MP 219.31 IP', 'IA');

    Declare @base_examples Table (base1 char(8), base2 char(8), base3 char(8));
    Insert Into @base_examples (base1, base2, base3)
    Values ('912.0.11', '003.0.01', '108.1.01')
    , ('108.2.01', '169.0.01', '172.1.01')
    , ('173.1.01', Null, Null);

    Select *
    From @base_examples ex

    Outer Apply (Select BasePrimary = concat(be.Name, ', ', be.[State], ' (', ex.base1, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.region = concat('6', substring(ex.base1, 1, 3))
    And xr.port = substring(ex.base1, 5, 1)
    And xr.poll = substring(ex.base1, 7, 2)
    ) b1

    Outer Apply (Select BaseSecondary = concat(be.Name, ', ', be.[State], ' (', ex.base2, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.region = concat('6', substring(ex.base2, 1, 3))
    And xr.port = substring(ex.base2, 5, 1)
    And xr.poll = substring(ex.base2, 7, 2)
    ) b2

    Outer Apply (Select BaseTertiary = concat(be.Name, ', ', be.[State], ' (', ex.base3, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.region = concat('6', substring(ex.base3, 1, 3))
    And xr.port = substring(ex.base3, 5, 1)
    And xr.poll = substring(ex.base3, 7, 2)
    ) b3
    ;

    You could create a function to accomplish this - but there really isn't any need.  If you will always have values for all 3 base values you can change to CROSS APPLY instead of OUTER APPLY.

    With that said - looking at the sample data you provided I am seeing invalid values.  The x_pbase value does not match the region, port, and poll values.  I would expect the x_pbase value to be 6912011 for region 6912 port 0 poll 11 but you have that value as 6912111.

    The lookup you have is looking for a x_pbase = {function} - but that function isn't returning the correct value unless your base id values are different.  If your base id values are actually something like 6912.1.11 for region 6912 port 0 poll 11 - then it would be even easier:

     Select *
    From @base_examples ex

    Outer Apply (Select BasePrimary = concat(be.Name, ', ', be.[State], ' (', ex.base1, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.x_pbase = concat('6', replace(ex.base1, '.', ''))
    ) b1

    Outer Apply (Select BaseSecondary = concat(be.Name, ', ', be.[State], ' (', ex.base2, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.x_pbase = concat('6', replace(ex.base2, '.', ''))
    ) b2

    Outer Apply (Select BaseTertiary = concat(be.Name, ', ', be.[State], ' (', ex.base3, ')')
    From @base_equipment be
    Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
    Where xr.x_pbase = concat('6', replace(ex.base3, '.', ''))
    ) b3
    ;

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeff,

    You are correct in what you say below here. the leading 6 is dropped from the final product as it identifies that as a base. So the resulting values of 912.1.11 is the ID for the base and how the lookup comes back if you are searching the db that way.

    The function is designed to take the numeric value and put a plain text on it so that "joe user" knows what it is without having access to the database, and is built into a report for them to see what the historic values of the bases Signal Strength over a given period of days. The Stored Procedure pulls all that data, and the function converts the base information.

    I think your first suggestion in this last message will work. Thank you for you help!

    With that said - looking at the sample data you provided I am seeing invalid values.  The x_pbase value does not match the region, port, and poll values.  I would expect the x_pbase value to be 6912011 for region 6912 port 0 poll 11 but you have that value as 6912111.

    The lookup you have is looking for a x_pbase = {function} - but that function isn't returning the correct value unless your base id values are different.  If your base id values are actually something like 6912.1.11 for region 6912 port 0 poll 11 - then it would be even easier:

Viewing 11 posts - 16 through 26 (of 26 total)

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