Wordpress, IIS7, SQL Server 2008, not showing posts (displays 'Nothing Found...') and only shows counts in admin - how to fix?

Question

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?

Answer 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.