The not authenticated version is fine. When logged in, content pages display the error message below. LENGTH() is not a valid SQL Function it is LEN(), but I do not know where the SQL string is being generated.

Message:
PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SUBSTRING", or the name is ambiguous.: SELECT TOP(50) c.[cid] AS [cid], SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) AS torder FROM {comment} c WHERE ( ([c].[nid] = :db_condition_placeholder_0) ) ORDER BY torder ASC; Array ( [:db_condition_placeholder_0] => 360 ) in PagerDefault->execute() (line 79 of \\10.6.16.43\WEBDATA\Websites\sitename\htdocs\includes\pager.inc).

Comments

omegamonk’s picture

Status: Active » Postponed (maintainer needs more info)

Can you provide some additional information with regard to the environment you are using? I am not seeing this message on content pages, when I am logged in, on my dev site.

clairefurney’s picture

For us this is happening on core code version 7.19 using PHP 5.3.13, IIS 7 and MS SQL Server 2008 R2. Its happening on all content when logged in and occasional content when not logged in (e.g. http://82.198.230.164/WhiteCity/content/qpr-helps-jobseekers-bench). I don't seem to get the error on a fresh standard installation of drupal in the same environment (http://82.198.230.164/drupal/).

UPDATE
I've made a temporary fix by commenting out the else clause in modules\comment\comment.module lines 847 to 854. The content is at least displaying now.

  ////if ($mode === COMMENT_MODE_FLAT) {
    $query->orderBy('c.cid', 'ASC');
  ////}
  ////else {
  ////  // See comment above. Analysis reveals that this doesn't cost too
  ////  // much. It scales much much better than having the whole comment
  ////  // structure.
  ////  $query->addExpression('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))', 'torder');
  ////  $query->orderBy('torder', 'ASC');
  ////}
edwardmonk’s picture

clairefurney,

Our environment is the same.
version 7.19 using PHP 5.3.13, IIS 7 and MS SQL Server 2008 R2.

Thank you for the information. We have confirmed that, at least for the site with the issue, they are not using comments. Unchecking in Modules also stopped the error from displaying. I am pretty sure this is a MS SQL only issue, as our WIMP stack does not have this issue.

edwardmonk’s picture

Version 7.19 using PHP 5.3.13, IIS 7 and MS SQL Server 2008 R2.

edwardmonk’s picture

Status: Postponed (maintainer needs more info) » Active
Damien Tournoud’s picture

Category: bug » support
Status: Active » Fixed

Most likely you lost the user-defined functions. I do not know why or how this happens (a broken backup software, maybe?), but it seems to happen to several people. Just visit /update.php once, and the driver will recreate the missing functions.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

jaypark’s picture

Status: Closed (fixed) » Closed (won't fix)

have this issue (for authenticated users), with latest sqlsrv commits pulled; #2 resolved.

jaypark’s picture

Damien, update.php didn't resolve.

xenphibian’s picture

I have found the underlying problem and both a short term solution as well as a proposed long term solution.

See #1261324: PDOException: Can not find column "dbo" function or user-defined aggregate "dbo.SUBSTRING"..

An update for #2 is to just add 'dbo.' in front of LENGTH( like this:

   $query->addExpression('SUBSTRING(c.thread, 1, (dbo.LENGTH(c.thread) - 1))', 'torder');
                                                  ^^^^

The reason it's only happening sometimes for #2 is shown in the actual shown code snippet that gets around the issue. This code is only encountered when the comment mode is FLAT (probably the default for that user login and for some of the other pages, but I'd have to go look at the comment module to say for sure).

:)

Uncle_Code_Monkey’s picture

@jaypark Make sure you have updated your Drupal SqlSrv module and database driver (by copying the sqlsrv folder to the %drupal_root%/includes/database folder as well) to either the 7.x-1.2 release or dev versions. Once you have the latest Drupal driver installed, visit the install.php page to fix both your LENGTH/LEN and Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SUBSTRING" issues. Doing so worked for me.

@xenphibian If you are using the latest version of the SqlSrv driver, you are incorrect. Please do not do what you suggested. Instead of duplicating my explanation, I'll just link you to it.

openmode’s picture

Project: Drupal driver for SQL Server and SQL Azure » Views (for Drupal 7)
Version: 7.x-1.2 » 7.x-3.7
Component: Select builder » Code
Category: support » bug
Priority: Normal » Major
Status: Closed (won't fix) » Active

My drupal 7.22 report this error only for authenticated users on Linux shared Aruba hosting:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 UPDATE command denied to user &#.....;Sql....&#...;@&#...;..(IP)&#....; for table &#...;drupalmodehistory&#....;: UPDATE {history} SET timestamp=:db_update_placeholder_0 WHERE ( (uid = :db_condition_placeholder_0) AND (nid = :db_condition_placeholder_1) ); Array ( [:db_update_placeholder_0] => ............... [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 15 ) in node_tag_new() (line 320 of /web/htdocs/www............../modules/node/node.module).

BalrajB’s picture

There is nothing wrong with any module or PDO or core drupal, sounds like you don't have the SQL DB function called SUBSTRING, you just need to run this script in your sql database:

USE [YOUR_SQL_DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SUBSTRING](@op1 nvarchar(max), @op2 sql_variant, @op3 sql_variant) RETURNS nvarchar(max) AS
BEGIN
RETURN CAST(SUBSTRING(CAST(@op1 AS nvarchar(max)), CAST(@op2 AS int), CAST(@op3 AS int)) AS nvarchar(max))
END

GO

BalrajB’s picture

Issue summary: View changes

edited text

themic8’s picture

#6, running update.php worked for me.

GMahe’s picture

@Uncle_Code_Monkey
When you said go o install.php, do you mean to rebuild the DB ? Because I've been on this url but If I rebuild I'll lose everything and it's not what I'm looking for. (Drupal already installed)
I've been on update.php which said It was ok.

I don't know which one did the trick but now it's working, but I would like to know what happened

Uncle_Code_Monkey’s picture

@Namari Merely visiting install.php fixed your issue, you need not "do anything" while there, just let it load and it will have fixed the problem (which is why you found it working after visiting install.php).

edwardmonk’s picture

Issue summary: View changes
Status: Active » Closed (fixed)