what does this error mean? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

what does this error mean?

Author

Topic

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-08-23 : 13:08:57

There is nothing on it on microsoft's site or books online

Server: Msg 292, Level 16, State 2, Procedure ReportGen_SearchesByAffiliate, Line 15
There is insufficient result space to convert a smallmoney value to varchar.

I think its on this part of the query

CASE
WHEN FK_SearchParameterID=1 THEN MAX(Company.COMP_Name)
WHEN FK_SearchParameterID=2 THEN MAX(CompanyType.CompanyTypeName)
WHEN FK_SearchParameterID=3 AND Data='y' THEN 'Yes'
WHEN FK_SearchParameterID=3 AND Data='n' THEN 'No'
WHEN FK_SearchParameterID=3 THEN '---'
WHEN FK_SearchParameterID=4 THEN MAX(JobCategory.JOBCAT_Name)
WHEN FK_SearchParameterID=5 THEN MAX(JobStatus.JobStatusName)
WHEN FK_SearchParameterID=8 AND Data>0 THEN MAX(AL_Name) + ': ' + MAX(Area.AREA_Name)
WHEN FK_SearchParameterID=8 THEN '---'
WHEN FK_SearchParameterID=9 AND Data=0 THEN 'Match Any Word'
WHEN FK_SearchParameterID=9 AND Data=1 THEN 'Match All Words'
WHEN FK_SearchParameterID=9 AND Data=2 THEN 'Match Exact Phrase'
WHEN FK_SearchParameterID=9 AND Data=3 THEN 'FreeText'
WHEN FK_SearchParameterID=9 THEN '---'
WHEN FK_SearchParameterID=13 AND Data>0 THEN CONVERT(VarChar(20), Data,1)
WHEN FK_SearchParameterID=13 THEN '---'
WHEN FK_SearchParameterID=14 AND Data='hourly' THEN 'Hourly'
WHEN FK_SearchParameterID=14 AND Data='salary' THEN 'Salary'
WHEN FK_SearchParameterID=14 THEN '---'
WHEN FK_SearchParameterID=16 AND Data='y' THEN 'Yes'
WHEN FK_SearchParameterID=16 AND Data='n' THEN 'No'
WHEN FK_SearchParameterID=16 THEN '---'
WHEN FK_SearchParameterID=18 AND Data=0 THEN 'Basic Matching'
WHEN FK_SearchParameterID=18 AND Data=1 THEN 'Root Word'
WHEN FK_SearchParameterID=18 AND Data=2 THEN 'Word Tense'
WHEN FK_SearchParameterID=19 AND Data=1 THEN 'Date Ascending'
WHEN FK_SearchParameterID=19 AND Data=2 THEN 'Date Descending'
WHEN FK_SearchParameterID=19 AND Data=3 THEN 'Relevance Ascending'
WHEN FK_SearchParameterID=19 AND Data=4 THEN 'Relevance Descending'
WHEN FK_SearchParameterID=19 THEN '---'
WHEN FK_SearchParameterID=20 AND Data=2 THEN 'Show Job Previews'
WHEN FK_SearchParameterID=20 AND Data=1 THEN 'Hide Job Previews'
WHEN FK_SearchParameterID=20 THEN '---'
ELSE CONVERT(VarChar(1000),ISNULL(Data, '---'))
END AS RenderedData,

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-23 : 13:24:17

Here's a guess

Could it be on this line:

quote:
WHEN FK_SearchParameterID=13 AND Data>0 THEN CONVERT(VarChar(20), Data,1)

Will the money value take more than 20 characters to represent as a varchar?

The reason I think this is the behavior of the following script:

DECLARE @var money
SET @var = 564.23
SELECT CONVERT(VarChar(5),@var,1)

Looks like your error!

If you change the the script to use varchar(6) it works fine.

Try increasing the size of the varchar your converting to and see if it works.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-08-23 : 13:45:10

Still get error when changing the VarChar(20) to Varchar(8000)

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-23 : 13:52:39

Yeh, I should've known that wasn't the problem because the max allowable value of smallmoney is not big enough to use 20 characters. I was thinking money.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-23 : 14:19:48

Well, since the line Joshb refers to is the only CONVERT statement you've provided us that has a significant limit, I'd suggest you start looking at the data that meets that criteria and see if anything sticks out. Maybe even run a SELECT statement that limits the records to just the matching criteria and then drop in your CONVERT statement and see if you can narrow down the erroroneous data.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-08-23 : 18:17:01

I fixed it . NOt sure if its the best fix. I changed to the join that had the sql_Variant 'Data' Field to a derived table, converting it to VarChar(8000)

JOIN (
SELECT FK_SearchID, FK_SearchParameterID, CONVERT(VarChar(8000), Data) AS Data FROM Track_SearchParameterData
) AS Track_SearchParameterData ON Track_Search.PK_SearchID=Track_SearchParameterData.FK_SearchID

macca
Posting Yak Master

146 Posts

Posted - 2007-11-19 : 05:24:27

Thanks guys,
got that working
macca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources