SQL Error : 'ROW_NUMBER' is not a recognized function name - Using SQL 2005!

[tlck9]

Losing the will and the SQL error is getting longer.


The version of SQL we are using is SQL Server 2005, on a PC with Windows XP sp2

Version 4.5.1d (Build 1273)

Screen shot can be founf here: http://www.imagestation.com/3472924/3900659245


It is accessed via a Web browser off site.

cant seem to post an image on here but i've included a link to the screenshot above


At the moment I get the message when I try and click on any hperlink, say if I click accounts and the list comes up, I click on one of the contacts there, I will get this error

[eggsurplus]

Wow, this is another case of this error happening on 2005. Usually it was 2000. I wonder if there is a mode in 2005 that lets you emulate 2000. Do you have 2000 installed on the same server as 2005? Make sure the connection string is to 2005. You can also do a SELECT @@VERSION to see which version it really is. Also turn logging on to see what errors are spitting out. Maybe it's a bad sql statement beside the row_number error that's causing it. I doubt it though.


(P.S. Rank can be used instead of row_number for MSSQL 2000)

[Deuce]

Make sure you didn't set the compatibility mode to 70 (sql 7) or 80 (sql 2000), it should be set to 90.
How to check: http://blog.biztalk-info.com/archive...celerator.aspx

[tlck9]

I found another thread which suggested:, so I have posted what I think it was asking for

Default *Re: MsSQL Error*
> ------------------------------------------------------------------------
> The row function is used whenever a limit query is done and is most
> common during pagination. This function is new to SQL Server 2005, so my
> first suggestion would have been to check what version of sql server you
> are using. Since you are using sql server 2005, then I would look at the
> limit query getting generated.
>
> Can you print the query before and after the limit function is called?
> To do this:
>
> 1. Go to MSSQLManager.php and look for "limitQuery" function.
> 2. Print out the incoming "$sql" parameter somewhere at the beginning of
> the function.
> 3. Next go to the end of the function and print the generated "$newSQL"
> variable just before the query is run.
>
> If you print out these two sql strings and post them we should be able
> to get a better idea as to why you are seeing these errors.



: will this answer any questions


function limitQuery($sql,$start,$count, $dieOnError=false, $msg=''){
$newSQL = $sql;
if (strpos($sql, "UNION"))
{
$newSQL = $this->handleUnionLimitQuery($sql,$start,$count);
}else{
if ($start < 0) $start=0;
$GLOBALS['log']->debug(print_r(func_get_args(),true));
$this->lastsql = $sql;
preg_match("/^(\s*SELECT )(.*?FROM.*WHERE)(.*)$/is",$sql, $matches);
if(!empty($matches[3])){
if($start == 0){
$match_two = strtolower($matches[2]);
if(!strpos($match_two, "distinct")> 0 && strpos($match_two, "distinct") !==0){
//proceed as normal
$newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
}else{

$distinct_o = strpos($match_two, "distinct");
$up_to_distinct_str = substr($match_two, 0, $distinct_o);
//check to see if the distinct is within a function, if so, then proceed as normal
if(strpos($up_to_distinct_str,"(")){
//proceed as normal
$newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];

}else{
//if distinct is not within a function, then parse

//string contains distinct clause, "TOP needs to come after Distinct"
//get position of distinct
$match_zero = strtolower($matches[0]);
$distinct_pos = strpos($match_zero , "distinct");
//get position of where
$where_pos = strpos($match_zero, "where");
//parse through string
$beg = substr($matches[0], 0, $distinct_pos+9 );
$mid = substr($matches[0], strlen($beg), ($where_pos+5) - (strlen($beg)));
$end = substr($matches[0], strlen($beg) + strlen($mid) );
//repopulate matches array
$matches[1] = $beg; $matches[2] = $mid; $matches[3] = $end;

$newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
}
}
}
else{
preg_match("/^(.*)(ORDER BY)(.*)$/is",$matches[3], $orderByMatch);

//if there is a distinct clause, parse sql string as we will have to insert the rownumber
//for paging, AFTER the distinct clause
$hasDistinct = strpos($matches[0], "distinct");
if($hasDistinct){
$matches_sql = strtolower($matches[0]);
$distinct_pos = strpos($matches_sql , "distinct");
$distinct_com_pos = strpos($matches_sql , ",",$distinct_pos );
$where_pos = strpos($matches_sql, "where");
//split the sql into a string before and after the distinct clause
if ($distinct_pos>0 && $distinct_com_pos>0){
$distinctSQLARRAY[0] = substr($matches_sql,0, $distinct_com_pos+1);
$distinctSQLARRAY[1] = substr($matches_sql,$distinct_com_pos+1);
//get position of order by (if it exists) so we can strip it from the string
$ob_pos = strpos($distinctSQLARRAY[1], "order by");
if($ob_pos){
$distinctSQLARRAY[1] = substr($distinctSQLARRAY[1],0,$ob_pos);

}

}

}

if(!empty($orderByMatch[3])){
//if there is a distinct clause, form query with rownumber after distinct
if($hasDistinct){
$newSQL ="SELECT TOP $count * FROM
(
" . $distinctSQLARRAY[0] . " ROW_NUMBER() OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number, " . $distinctSQLARRAY[1]. "
) AS a
WHERE row_number > $start";

}else{
$newSQL ="SELECT TOP $count * FROM
(
" . $matches[1] . " ROW_NUMBER() OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number, " . $matches[2] . $orderByMatch[1]. "
) AS a
WHERE row_number > $start";

}

}else{
//if there is a distinct clause, form query with rownumber after distinct
if($hasDistinct){
$newSQL ="SELECT TOP $count * FROM
(
" . $distinctSQLARRAY[0] . " ROW_NUMBER() OVER (ORDER BY ".$this->getTableNameFromModuleName($_REQUEST['module'],$sql).".id) AS row_number, " . $distinctSQLARRAY[1] . "
) AS a
WHERE row_number > $start";

}else{
$newSQL ="SELECT TOP $count * FROM
(
" . $matches[1] . " ROW_NUMBER() OVER (ORDER BY ".$this->getTableNameFromModuleName($_REQUEST['module'],$sql).".id) AS row_number, " . $matches[2] . $matches[3]. "
) AS a
WHERE row_number > $start";
}
}

}
}

}
$GLOBALS['log']->debug('Limit Query: ' . $newSQL);
$result = $this->query($newSQL, $dieOnError, $msg);
$this->dump_slow_queries($newSQL);
return $result;
}



What do your think????

[tlck9]

have checked this post and my settign are correct, so what else could be causing this


Did you try what Deuce posted at http://www.sugarcrm.com/forums/show...257&postcount=3? That looked really promising.


I am getting quite fustrated as the SQL error is now Growing in length!!!!!

SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.SQL Error : 'ROW_NUMBER' is not a recognized function name.




Apprecaite any help,

[tlck9]

Now I have logged in as administrator and so far it only give me this error when I try and click on a link, eg

go to accounts, search with nothing in field to bring up all acounts and then click on one of the hyperlinks, I get the following message;
SQL Error : 'ROW_NUMBER' is not a recognized function name.

I login via Firefox browser and have tried Safari as I use a Mac,

My SQL server is 2005 and I have check the settings which are correct, I am losing the will to live with this as without further inout I may have to scrap the whole system

I access the system via the companies web hosting, eg www.companyname.com/sugarcrm

Could this cause problems? and what do I need to check further to ensure no furth problems

I have logged in via a windows pc, using IE and get exactly the same message when logging in via my own name, I've now tried loggin in under admin and guess what no error other than the hyperlink, this could mean that both of my user ID's are becoming corrupted but I do not know how this could be happening

[mjprimeaux]

The compatibility level in SQL 2005 for a specific database won't affect the syntactic validity of the ROW_NUMBER() function. Factors such as default GROUP BY sorting and approximately 40 other system-level behaviors (aspects of replication, ARITHABORT, etc.) are controlled by the compatibility level so I don't suspect this to be the issue.

As others, I'd be interested in seeing the result of "SELECT @@VERSION".

Regards,
Michael

[Deuce]

The compatibility level in SQL 2005 for a specific database won't affect the syntactic validity of the ROW_NUMBER() function.

I didn't know that - I never actually tested it Thanks for the info.
The only other thing I can think of then is maybe there is an instance of SQL server 2000 or older running on the same machine as well, and sugar is connected to that one instead - "'ROW_NUMBER' is not a recognized function name" is the exact error you'll get on sql 2000.