June 9, 2021 at 2:58 pm
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
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 9, 2021 at 3:35 pm
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.
June 9, 2021 at 6:55 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 10, 2021 at 10:14 am
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,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 10, 2021 at 10:25 am
Here is a sampling of the data table;
WEADate_TimeBase1Base2Base3SSI1SSI2SSI3Lock1Lock2Lock3Pref_basedbualarm
2252807200002021-06-09 12:32:30.673453.2.01 389.3.01 804.1.01 615337000453.2.01 00
7610600100002021-06-09 13:49:46.170158.1.01 160.1.01 365.1.01 222012110158.1.01 10
7615501300002021-06-09 15:02:10.077483.1.01 497.1.01 847.2.01 222159184110483.1.01 10
2252911700002021-06-09 16:38:57.420384.3.01 216.3.01 836.2.03 613815000384.3.01 00
7617700700002021-06-09 17:47:18.583554.1.01 548.1.01 549.3.01 150172150110554.1.01 10
7671704500002021-06-09 19:25:45.860282.3.01 288.2.01 288.1.01 34147000282.3.01 00
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 10, 2021 at 2:38 pm
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 above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 10, 2021 at 3:50 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2021 at 10:50 am
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.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 10:53 am
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',
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 10:56 am
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();
}
}
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 11:04 am
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!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 12:48 pm
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
Change is inevitable... Change for the better is not.
June 11, 2021 at 12:51 pm
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.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 2:15 pm
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
endThanks, 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
Change is inevitable... Change for the better is not.
June 11, 2021 at 2:46 pm
@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:
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply