SQL Server Error: Cannot schema bind function because table name is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Today for one of my application, i was trying to create a schema bound function for performance optimization. Before we go into the detail you should check this link to know that how the query optimizer can use it to make better performance decisions. Alright, now the function i wanted to create is used to check that particular role id have access to menu link or not. Simple right! Here is the code of my function.

CREATE FUNCTION [dbo].[udf_IsAuthorisedForMenuLink]
(
	@RoleID TINYINT,
	@MenuID INT
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
	
	DECLARE @IsAuthorised BIT = 0

	IF EXISTS(SELECT 1 FROM tblRoleMenu WHERE RoleId = @RoleID AND MenuID = @MenuID)
	BEGIN
		SET @IsAuthorised = 1
	END
	
	RETURN @IsAuthorised
	
END

But when i try to create this function, i get the following error.

Msg 4512, Level 16, State 3, Procedure udf_IsAuthorisedForMenuLink, Line 23 
Cannot schema bind function ‘dbo.udf_IsAuthorisedForMenuLink’ because name ‘tblRoleMenu’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So why i got this error? What is the resolution of this error? Well, the resolution itself is in the error message. As it said – “Names must be in two-part format” i.e. [Schema].[ObjectName] – in our case its dbo.tblRoleMenu. Actually i forgot to add schema in the select statement in my above function code. Once i add it and press “F5” and boom, function created successfully without any error.

Did you guys ever face this type of issue in your application? Write in the comment. Hope you like this post.

Advertisements

Tip of the day: Get the first element from the xml using LINQ to XML

Today, one of my colleague in my office asked me a question that how to get the first element from the XML string. In my previous organization, i had worked with XML but using old methodology. So i thought this is good opportunity to learn and explore LINQ to XML. After some googling i told him that using XElement class of LINQ to XML you can full fill your requirement.

So what exactly the requirement is? Well, In one of my application module, we have save some configuration information in XML format in the database. We need to display the first element of the XML in one label in another web page. So let’s see the XElement class of LINQ to XML in action.

What is XElement?

XElement is one of the fundamental class of LINQ to XML. You can use this class to parse the XML string, create the new element, change the content of the element etc. In this post we’ll use this class to parse the XML string. Well, by not going into much detail let’s see this practically.

Consider i have XML in below format.


<ApplicationName>
  <ModuleConfig AccountNo="Test123" Type="M56X35" Source="XML" PNKNumber="12345" Final="False" />
  <LibraryList>
    <Library1 FolderName="Vendor" />
    <Library12 FolderName="Dealer" />
    <Library123 FolderName="Dealer" />
  </LibraryList>
</ApplicationName>

Now his requirement is –
1). Read the first element from the “LibraryList” tag which is “Library1”.
2). Read the value of attribute “PNKNumber” from “ModuleConfig” tag.

Let’s create the new console application in visual studio.

Here is the solution for the first requirement.


using System.Linq;
using System.Xml.Linq;
using static System.Console;

class Program
{
	static void Main(string[] args)
	{
		string xml = @"<ApplicationName>
						  <ModuleConfig AccountNo=""Test123"" Type=""M56X35"" Source=""XML"" PNKNumber=""12345"" Final=""False"" />
						  <LibraryList>
							<Library1 FolderName=""Vendor"" />
							<Library12 FolderName=""Dealer"" />
						  </LibraryList>
					  </ApplicationName>";

		// First let us parse the xml string using XElement
		var parsedXML = XElement.Parse(xml);

		// Now get the first element from the <LibraryList>  tag.
		string firstElement = parsedXML.Element("LibraryList").Descendants()
                                     .FirstOrDefault().Name.LocalName;
		WriteLine("First Element is - {0}", firstElement);
		ReadLine();
	}
}

Output

First Element is – Library1

Same way to get the 2nd or upto nth tag, you can use the ElementAtOrDefault() method by passing tag index like this –


string elementAt = parsedXML.Element("LibraryList").Descendants()
                  .ElementAtOrDefault(1).Name.LocalName;
WriteLine("Element at Index 1 - {0}", elementAt);
// Output 
// Element at Index 1 - Library12
		
elementAt = parsedXML.Element("LibraryList").Descendants()
            .ElementAtOrDefault(2).Name.LocalName;
WriteLine("Element at Index 2 - {0}", elementAt);
// Output 
// Element at Index 1 - Library123

Here is the solution for the second requirement – Read the value of attribut “PNKNumber” from “ModuleConfig” tag.


using System.Linq;
using System.Xml.Linq;
using static System.Console;

class Program
{
	static void Main(string[] args)
	{
		string xml = @"<ApplicationName>
						  <ModuleConfig AccountNo=""Test123"" Type=""M56X35"" Source=""XML"" PNKNumber=""12345"" Final=""False"" />
						  <LibraryList>
							<Library1 FolderName=""Vendor"" />
							<Library12 FolderName=""Dealer"" />
                            <Library123 FolderName=""Vendor"" />
						  </LibraryList>
					  </ApplicationName>";

		// First let us parse the xml string using XElement
		var parsedXML = XElement.Parse(xml);

		// Read the value of attribute "LNKNumber" from "ModuleConfig" tag.
		// First we check whether "LNKNumber" attribute exists within this "ModuleConfig" tag or not.
		if (parsedXML.Element("ModuleConfig").Attributes().Any(x => x.Name == "PNKNumber"))
		{
			// Now we read the value of attribute "LNKNumber"
			var attrValue = parsedXML.Element("ModuleConfig").Attribute("PNKNumber").Value;
			WriteLine("Attribute value is - {0}", attrValue);
		}
		else
			WriteLine("Attribute PNKNumber does not exists in the xml.");
		
		ReadLine();
	}
}

Output

Attribute value is – 12345

Same way you can check for other attribute also. LINQ to XML really reduce the number of lines of code in your application for complex requirement. My above example is simple, so you can not find some major difference. Any ways that’s it for now. Hope you like this post.