Sorry to lower the tone to work but do you know SQL? - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

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

tlck9 Avatar
9y, 3m agoPosted 9 years, 3 months ago
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:
tlck9 Avatar
9y, 3m agoPosted 9 years, 3 months ago
Options

All Comments

(14) Jump to unreadPost a comment
Comments/page:
#1
http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx

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.
#2
Tatey
http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx

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.


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
#3
I'd say pass to the IT guy!

SQL is very technical!, lol!
#4
I know a little sql - if you post the full code snippet I might be able to help
#5
Yep, post the SQL please ..
#6
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 < 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;
}
------------------------------------------------------------------------

Hopefully someone will now be able to point us in the right direction
#7
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.
#8
tlck9
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.
#9
richcf14
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.
#10
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
[admin]#11
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 :)
#12
Bit late here :) - but have you tried running SQL profiler to see what the sql statment that's fired at the engine looks like ?
#13
Any luck getting it to work? If not I'll offer my knowledge....
#14
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

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!