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
Comment #1
omegamonk CreditAttribution: omegamonk commentedCan 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.
Comment #2
clairefurney CreditAttribution: clairefurney commentedFor 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.
Comment #3
edwardmonk CreditAttribution: edwardmonk commentedclairefurney,
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.
Comment #4
edwardmonk CreditAttribution: edwardmonk commentedVersion 7.19 using PHP 5.3.13, IIS 7 and MS SQL Server 2008 R2.
Comment #5
edwardmonk CreditAttribution: edwardmonk commentedComment #6
Damien Tournoud CreditAttribution: Damien Tournoud commentedMost 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.Comment #8
jaypark CreditAttribution: jaypark commentedhave this issue (for authenticated users), with latest sqlsrv commits pulled; #2 resolved.
Comment #9
jaypark CreditAttribution: jaypark commentedDamien, update.php didn't resolve.
Comment #10
xenphibian CreditAttribution: xenphibian commentedI 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:
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).
:)
Comment #11
Uncle_Code_Monkey CreditAttribution: Uncle_Code_Monkey commented@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 theinstall.php
page to fix both your LENGTH/LEN andCannot 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.
Comment #12
openmode CreditAttribution: openmode commentedMy 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).
Comment #13
BalrajB CreditAttribution: BalrajB commentedThere 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
Comment #13.0
BalrajB CreditAttribution: BalrajB commentededited text
Comment #14
themic8 CreditAttribution: themic8 commented#6, running update.php worked for me.
Comment #15
GMahe CreditAttribution: GMahe commented@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
Comment #16
Uncle_Code_Monkey CreditAttribution: Uncle_Code_Monkey commented@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).
Comment #17
edwardmonk CreditAttribution: edwardmonk commented