SchemaBinding Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SchemaBinding Error

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-09-12 : 14:38:08

When I am trying to create a View with SchemaBinding, I am getting the below error.

This is my View
-----------------

CREATE VIEW [dbo].[vwBlogEntryFullText] WITH SCHEMABINDING
AS
SELECT BET.BlogTextID, BLE.blog_entryId, BLE.blogId, dbo.uf_StripHTML(BET.blogText) AS BLOGTEXT
FROM dbo.blog_entry AS BLE INNER JOIN
dbo.blog_text AS BET ON BLE.blog_entryId = BET.blog_entryId AND BLE.entryDisabled = 0

Msg 4513, Level 16, State 2, Procedure vwBlogEntryFullText, Line 3
Cannot schema bind view 'dbo.vwBlogEntryFullText'. 'dbo.uf_StripHTML' is not schema bound.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 14:45:28

Your function "uf_StripHTML" also needs to be created with schemabinding. See the restrictions about in CREATE FUNCTION topic in Books Online.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 14:56:44

Is your intention to use schemabinding so that you can create an index on this view for performance purposes? If so, indexed views have their place (like any tool) but more likely you just need to optimize the underlying query. I'm guessing that StripHTML function is the performance hog.

Be One with the Optimizer
TG

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-09-12 : 17:04:46

yeah am trying to create an index for the view..which forces me to add SCHEMABINDING and i added that.. somehow it errors out for the STRIPHTML function am using.. so i added SCHEMABINDING to it.. and is good to go..

thanks TG for your Fast Response..

Your help is Much Appreciated.

--

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 17:10:08

I will be curious to hear if this solution (indexed view) solves your problem. If you don't mind updating us I'd appreciate it.

Be One with the Optimizer
TG

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-09-12 : 17:55:52

Yes this solved my Problem.. I used SCHEMABINDING here in this function.and it worked for me

/****** Object: UserDefinedFunction [dbo].[uf_StripHTML] Script Date: 09/12/2008 14:54:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* strip HTML Chars from a field */
CREATE FUNCTION [dbo].[uf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) WITH SCHEMABINDING
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 18:02:19

I mean did having the indexed view solve your performance problems? How much of an improvement was there?

Be One with the Optimizer
TG

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-09-12 : 18:14:41

Had a Drastic Improvement when used with FullText Catalogs.
I am using this Indexed view for One of my FullText Catalog, My main purpose is to Strip of HTML from a Field and search on clean Text.

Not sure if i answered your question.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-12 : 19:27:03

>>Not sure if i answered your question.
Yes. Thank you.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources