Possible bug in Microsoft system stored procedure

Question

Possible bug in Microsoft system stored procedure

haiao2000

Hall of Fame

Points: 3734
More actions
April 5, 2013 at 8:26 am

#394532

Our application runs into an issue when executing sys.sp_fulltext_catalogs, it throws an error stating that "System.Data.SqlClient.SqlException: Arithmetic overflow error for data type smallint, value = 235139." as I traced it out, the error occurs at line 56
where @ftcatid variable being declared as smallint, while the fulltext_catalog_id is defined as integer in the fulltext catalogs table
declare @ftcatid smallint
select @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @ftcat
Could some experts take a quick look and confirm?
-Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

I would tend to agree.

Do you have the option of creating a copy of the stored procedure with the variable declared as an int, and running that instead? (Do you specify the schema 'sys.' when you call the stored procedure? If not you may be able to create the copy with the same name but under the dbo. schema, so that it gets run first.)

Lowell SSC Guru Points: 323504 More actions · Answer 2

i would disagree.

if you look at the definition for 'sys.fulltext_catalogs' via sp_help, the datatype is clearly int for the column fulltext_catalog_id you are selecting

/*

Column_name Type Computed Length

fulltext_catalog_id int no 4

name sysname no 256

path nvarchar no 520

is_default bit no 1

is_accent_sensitivity_on bit no 1

data_space_id int no 4

file_id int no 4

principal_id int no 4

is_importing bit no 1

*/

the error seems to be the fact that you declared a smallint, and are trying to stuff an int value in it.

declare @ftcatid smallint
select @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @ftcat

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David McKinney SSChampion Points: 10472 More actions · Answer 3

We didn't declare a smallint, Bill Gates and his pals did.

Lowell SSC Guru Points: 323504 More actions · Answer 4

i'm still confused and looking in the wrong place, clearly.

i did sp_helptext 'sys.fulltext_catalogs', and it's a system view on my 2008 instance, i'm not seeing the same code you posted;

can you show me how to reproduce the error?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David McKinney SSChampion Points: 10472 More actions · Answer 5

Your confusion is understandable...it's actually called [sp_fulltext_catalog] (without the s).

Lowell SSC Guru Points: 323504 More actions · Answer 6

David McKinney (4/5/2013)
Your confusion is understandable...it's actually called [sp_fulltext_catalog] (without the s).

doh! now i see it!

i just peeked at my other instances, and the same code is in 2008R2 and 2012 as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

David McKinney (4/5/2013)
We didn't declare a smallint, Bill Gates and his pals did.

First, I would not blame Bill Gates for this.

Looking at the code for sys.sp_fulltext_catalog I can definately see where @ftcatid is declared as smallint. I would submit a connect item on this and include a copy/paste of the code for the procedure plus your code making the call to this procedure.

David McKinney SSChampion Points: 10472 More actions · Answer 8

You're quite right, Lynn, I understand he's a very busy man, and it's only natural that he delegates from time to time.

haiao2000 Hall of Fame Points: 3734 More actions · Answer 9

David McKinney (4/5/2013)
I would tend to agree.
Do you have the option of creating a copy of the stored procedure with the variable declared as an int, and running that instead? (Do you specify the schema 'sys.' when you call the stored procedure? If not you may be able to create the copy with the same name but under the dbo. schema, so that it gets run first.)

David,

Yes, I created a another version of this procedure for out application to use for now, and it seems working fine now.

Thanks!

haiao2000 Hall of Fame Points: 3734 More actions · Answer 10

David McKinney (4/5/2013)
You're quite right, Lynn, I understand he's a very busy man, and it's only natural that he delegates from time to time.

I tend to agree which what you guys said. In the normal case, we wouldn't see this type of error, but my guess our application is a bit of irregular one, it does alot of drop/create fulltext catalog, so after a number of years pass, the newly generated category id start with 6-digits, although there are only about 3000 fulltext category ids in the table. That is why we start seeing these errors popping up. I hope that explains why this error occurs.

Thanks again for looking into it.

David McKinney SSChampion Points: 10472 More actions · Answer 11

haiao2000 (4/5/2013)
David McKinney (4/5/2013)
I would tend to agree.
Do you have the option of creating a copy of the stored procedure with the variable declared as an int, and running that instead? (Do you specify the schema 'sys.' when you call the stored procedure? If not you may be able to create the copy with the same name but under the dbo. schema, so that it gets run first.)
David,
Yes, I created a another version of this procedure for out application to use for now, and it seems working fine now.
Thanks!

Glad I was able to help.