Sorry to lower the tone to work but do you know SQL?

Found 8th Aug 2007
Since I have tried looking everywhere and know very little about SQL, apart from this error is driving me nuts, I always know that someone will be at hand to help or tell me where I can go (politely of course

I'm using Sugar CRM, and to be honest have posted a error on their forum to no avail, however it seems to be a SQL error rather than specific to Sugar;

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

My It guy tells me we use SQl Server 2005, with windows XP sp2
with the compatibility set to the correct 90

So why am I still getting this error
when I action various things on the sugar system

I'm sure one of you money saving persons is also a dab hand at SQL - unfortunetly I'm not so easy terminology would be appreciated


Give anyone a BIG CYBER HUG if you can help:oops:

  1. Misc
Groups
  1. Misc
14 Comments

blogs.conchango.com/jam…spx

that should hopefully give you a hand i think. Just google the error message like i just did and see what you come up with. If you scroll down to the bottom of the page there is someone with the same error as you and a solution i think.

Original Poster

Tatey

http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspxthat … http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspxthat should hopefully give you a hand i think. Just google the error message like i just did and see what you come up with. If you scroll down to the bottom of the page there is someone with the same error as you and a solution i think.



Thanks, I've tried google but alot of the responses are fairly technical, so I think I will have to pass the links over to the IT guy for him to check out on my behalf and the googled ones i've found give the error and then ahve failed to show a response

however this does look like it has a response that may help solve the problem, or at leadt start to look in the right place


:thumbsup:

couldnt find the HUG button

Tracey

I'd say pass to the IT guy!

SQL is very technical!, lol!

I know a little sql - if you post the full code snippet I might be able to help

Yep, post the SQL please ..

Original Poster

This is the extract that my IT guy pulled out from one of the requests made on the forum, does this help...

My issue with using the IT guy is that he is assigned to customer roles and I can only use him to set it up, when I use Mysql I dont get the issue, but it needs to be compan wide...anyhow does this mean anything?


I've found the function in the MSSQLManager.php file and there is a number of places the variables exist, so I've copied the whole function and pasted it below...
-----------------------------------------------------------------------------------------------------------------

function limitQuery($sql,$start,$count, $dieOnError=false, $msg=''){
$newSQL = $sql;
if (strpos($sql, "UNION"))
{
$newSQL = $this->handleUnionLimitQuery($sql,$start,$count);
}else{
if ($start $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;
}
------------------------------------------------------------------------

Hopefully someone will now be able to point us in the right direction

Original Poster

this was the initial request, that my IT guy did to give the previous post

Any help much appreciated


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.

tlck9

This is the extract that my IT guy pulled out from one of the requests … This is the extract that my IT guy pulled out from one of the requests made on the forum, does this help...My issue with using the IT guy is that he is assigned to customer roles and I can only use him to set it up, when I use Mysql I dont get the issue, but it needs to be compan wide...anyhow does this mean anything?I've found the function in the MSSQLManager.php file and there is a number of places the variables exist, so I've copied the whole function and pasted it below...-----------------------------------------------------------------------------------------------------------------function limitQuery($sql,$start,$count, $dieOnError=false, $msg=''){$newSQL = $sql;....($newSQL, $dieOnError, $msg);$this->dump_slow_queries($newSQL);return $result;}------------------------------------------------------------------------Hopefully someone will now be able to point us in the right direction




My god thats a hell of a snippet. Been trying to go through it but I'm not a databases guy, Java and C is where all my programmings at. Havent had much luck for you i'm afraid.

Original Poster

richcf14

My god thats a hell of a snippet. Been trying to go through it but I'm … My god thats a hell of a snippet. Been trying to go through it but I'm not a databases guy, Java and C is where all my programmings at. Havent had much luck for you i'm afraid.



To be honest, the whole lot probabily isnt needed, but I dont know which bit the request refers to, so it could mean only a small area,

eg
rint 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.

Original Poster

Out of interest, if I continue to use this with the error happening, what is the likely issue? eg will I lose data, will it blow up and never work again

what does this actually refer to, in the general sense


Thanks again

I'm not an expert in this but are you sure your version supports the ROW_NUMBER function? because really that's what the error is saying pretty clearly... Also - not sure if that function needs to take some parameters? Right now it is just ROW_NUMBER(). Maybe check if the quotations are correct in that code to b/c it could be breaking the sql query...as I said not an expert hehe

The implications are that it just won't work. If there's an error in the code it will fail to run.

Edit: To clarify you do need SQL Server 2005 to use this function - if you are running an earlier SQL Server version it won't work. Also looking at the function I don't think my other points are useful at all

Bit late here - but have you tried running SQL profiler to see what the sql statment that's fired at the engine looks like ?

Any luck getting it to work? If not I'll offer my knowledge....

Original Poster

i will check re the sql profiler, as far as I am aware we are using MS sql server 2005, with Windows xp sp2

Apparently on the sugar site this is their answer that we're running an old version, but I can only take it from the IT Guy that we're running the correct server.
Post a comment
Avatar
@
    Text
    Top Discussions
    1. Difference between these WD 1TB Black HDDs11
    2. Help: NowTV Movie pass66
    3. What is the best weight training app for iOS please?11
    4. Is this expensive?816

    See more discussions