[thelist] SQL Server 7: CONVERT function

[Judah McAuley]

Evening, all.  I've started working on a CF/SQL Server 7 system and I'm 
experiencing some weird behavior that I was hoping ya'all could shed some 
light upon.

There is an existing system (hosted) running CF 4.5.1 and SQL Server 7 
(AFAIK) that I replicated on a new W2K box that I just set up.   CF files 
were ftp'd and the SQL db was transferred using the DTS Export tool (create 
new database, transfer all objects).

There is a survey system running (funny how surveys seem to be coming up 
all the time recently) that allow people to input some responses as 
integers and some entries as free text.  The person who created the system 
thus far (whom I've never met) created the following stored procedure to 
run some stats on the answer pool:

CREATE PROCEDURE Behavior  @usergroupid int, @formid int, @q1 int, @q2 int
AS
SELECT COUNT(responses.response) AS Num,
MIN(CONVERT(int,responses.response)) AS Lowest,
MAX(CONVERT(int,responses.response)) AS Highest,
CONVERT(float,SUM(CONVERT(int,responses.response))) / 
COUNT(responses.response) AS Mean,
SQRT(
(SUM(POWER(CONVERT(float,responses.response),2)) - 
(SUM(CONVERT(float,responses.response)) * 
SUM(CONVERT(float,responses.response)))/ COUNT(responses.response)) / 
(COUNT(responses.response) - 1)
) AS StdDev, questions.reporttext,questions.questionnumber
FROM responses
INNER JOIN form_lookup
INNER JOIN questions
ON form_lookup.questionid = questions.questionid
ON responses.questionid = form_lookup.questionid
WHERE userid IN (SELECT userid FROM usergroup_users WHERE usergroupid = 
@usergroupid)
AND form_lookup.formid = @formid
AND (questions.questionnumber BETWEEN @q1 AND @q2 AND questions.status = 1)
GROUP BY responses.questionid, questions.reporttext,questions.questionnumber
ORDER BY Mean

Most of the JOINs are unimportant to my particular problem, which is 
this:  When this procedure is run on the production system it works 
fine.  When I run the same procedure against the same data on the new 
development system, I get an error when it tries to convert character data 
to an integer.  Example:

Error Diagnostic Information:

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the 
varchar value 'laghdf'lhg' to a column of data type int.
SQL = "Behavior"
Data Source = "trustinworkplace"
The error occurred while processing an element with a general identifier of 
(CFSTOREDPROC), occupying document position (7:1) to (7:73) in the template 
file... (removed to protect the guilty)

So Big Question:  Why would it be that the CONVERT function (which should 
return 0 if an input is not convertable to an int if I recall) work on one 
machine and not on another?  Thoughts?  Ideas?

Thanks,

Judah