2

I recently installed WordPress on IIS 7 and wired it up to SQL Server 2008R2. The installation went fine.

Right off the bat, I can see on post is present (just in the count) but none are listed; I added a few posts.

In the admin menu, under posts, I see that there are six published posts "Published (6)". However the list below this is empty. Also trying to view these on the site, by selecting the category returns

Nothing Found

Apologies, but no results were found for the requested archive. Perhaps searching will help find a related post.

I have re-installed the database, overwrote the PHP/SQL files and re-installed WP. Problem persists.

I tried the Settings/Permalinks suggestions here and. No change.

The suggestions here relate to not being able to write to the .htaccess file. Not an issue for me, and I am not seeing the error of not being able to save. (To verify, I saved, exited, navigated to the same Permalinks page and my settings were saved).

I even set the Permalinks to to default and removed the Web.config rewrite rules:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <rewrite>
      <rules>
        <rule name="wordpress" patternSyntax="Wildcard">
            <match url="*"/>
                <conditions>
                    <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true"/>
                    <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true"/>
                </conditions>
            <action type="Rewrite" url="index.php"/>
        </rule>
     </rules>
    </rewrite>
  </system.webServer>
</configuration>

No change. (Added them back in).

I set SAVEQUERIES=TRUE in wp-config.php and I can see the contents of some of the posts in the log file.

ie. Hi there! I'm a bike messenger by day, aspiring actor by night, and this is my blog. I live in Los Angeles, have a great dog named Jack...

Enabled the php_sqlsrv_53_ts_vc9.dll extension and disabled phpsqlsrv.dll

I have seen other posts that refer to changing some security settings as the system may be detecting a potential SQL injection attack. However those posts are related to an Apache-related installation, and cannot locate any references in my version.

I retried this with WP 3.21 - same issue.

I can click on the month link (March) then see that the post count beside Uncategorized. Once I click on the link, I see the Nothing Found message again.

Retried with 3.31, and noticed the following in Windows/Temp/php53_errors.log:

[07-Mar-2012 21:00:51 UTC] WordPress database error 42000 : [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'wp_users'. for query SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''wp_users'' made by display_setup_form, pdo_wpdb->query, pdo_wpdb->_post_query, pdo_wpdb->print_error

Upon installation, and tracked it down to wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php.

In this case, adding extra single quotes around the table name was causing the error, so I commented out the line:

    // SHOW TABLES
    if ( strtolower($query) === 'show tables;' ) {
        $query = str_ireplace('show tables',"select name from SYSOBJECTS where TYPE = 'U' order by NAME",$query);
    }
    if ( stripos($query, 'show tables like ') === 0 ) {
        $end_pos = strlen($query);
        $param = substr($query, 17, $end_pos - 17);
        // quoted with double quotes instead of single?
        $param = trim($param, '"');
        if($param[0] !== "'") {
            //$param = "'$param'";          <----------- commented out
        }
        $query = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ' . $param;
    }

Now, the SQL statement reads

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'wp_users'

instead of

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''wp_users''

Retried the installation - problem persists, but this time no SQL error message.

-- Update --

Forgot to mention that I am using this with the WP DB Abstraction (implied by ...

wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php

... above. Is there another way to get this to work with SQL Server?

5
  • Is this just the stock WordPress? Are you running this with it: wordpress.org/extend/plugins/wordpress-database-abstraction
    – Kev
    Mar 8, 2012 at 0:53
  • @Kev - yes, with db abstraction. Still can't get around this issue.
    – ElHaix
    Mar 12, 2012 at 16:20
  • I also facing same issue. Did you also got same problems for the media library?
    – user14108
    Mar 13, 2012 at 18:53
  • Interesting as I too am seeing the same results. I have even updated the SQL PHP drivers from 2.0 to 3.0 from our good friends at MS... Ron
    – user14110
    Mar 13, 2012 at 20:38
  • There are a few problems listed. Surprisingly one is directly right from the database setup process, and I'm curious as to why there are no solutions/responses to this yet. We have somewhat abandoned this effort as our requirements have changed, however I'm still looking forward to a solution for. As I don't have the cycles to resolve, the starting point would be the sprocs that retrieve the data. My suspicion is that the count sprocs are different than the data retrieval sprocs/functions. Check for a common PHP module (and trace it back), as the same problem is seen on the admin side.
    – ElHaix
    Mar 13, 2012 at 21:29

1 Answer 1

1

I had the same problem. From my log file I can see its breaking on the query that returns the posts.

I managed to get this working by going to "wp-includes\query.php", and then removing the $limits variable from the query.

It seems that this is breaking on sql server.

Find this line:

$this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";

change to:

$this->request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby";

Let me know if this worked for you.

7
  • That’s probably the worst solution: Now you have to re-edit the files after each update. Use a filter on 'query' instead.
    – fuxia
    Mar 15, 2012 at 8:11
  • You might be right, but my answer is to get the site working. There are a few other things I found that can cause problems too, so still working on these myself.
    – edwardl
    Mar 15, 2012 at 11:20
  • @edwardl - Can you share what other things are causing issues and provide the solutions for them? What was the value of $limits in the first query when you ran it? Can you post the actual queries?
    – ElHaix
    Mar 15, 2012 at 20:17
  • @toscho - I found an error in the setup code that, unless it's changed, will require a code change after each update. See my comments above. So this being a bug would warrant that, don't you think? I'm removing the -1.
    – ElHaix
    Mar 15, 2012 at 20:37
  • @ElHaix If it is a bug in WordPress open a Trac ticket. If it is a plugin bug, fix the plugin and contact its author. Anyway, I would rather filter the SQL request on the 'query' hook than changing the core files.
    – fuxia
    Mar 16, 2012 at 4:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.