0

I try to use the following SQL-query to retrieve some financial information from a Dynamics NAV database to a PHP-based web appalication.

SELECT SUM([SUM$Amount]) AS total 
FROM [Company Name$G_L Entry$VSIFT$1] WITH(NOEXPAND) 
WHERE [Posting Date]>='2014-01-01' AND 
      [Posting Date]<='2014-11-26' AND 
      [G_L Account No_]='3020'

The query works fine when used directly in SQL Server Management Studio, but it fails when I use it in the web application though PHP PDO. I get the following error:

Array (
  [0] => HY000
  [1] => 1934
  [2] => General SQL Server error: Check messages from the SQL Server [1934] (severity 16) [SELECT SUM([SUM$Amount]) AS total FROM [Company Name$VSIFT$1] WITH(NOEXPAND) WHERE [Posting Date]>='2014-01-01' AND [Posting Date]<='2014-11-26' AND [G_L Account No_]='3020']
  [3] => -1
  [4] => 16 
)

I find no further error information in the server logs.

The problem is with the "WITH(NOEXPAND)"-part of the query. If I remove that, the query runs also when called from PHP. But the query then becomes very much slower as it doesn't utilize the indexed view on the table.

Does anyone know what is the problem here?

Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
Rune K M
  • 1
  • 2
  • seen this? -> http://stackoverflow.com/questions/16054217/mssql-error-1934-occurs-on-insert-to-table-with-computed-column-php-pdo – Marco Mura Nov 26 '14 at 14:44

1 Answers1

0

Are you sure the object you are querying is an indexed view? I believe that hint only applies to indexed views. And I believe that hint is only available on the Enterprise Edition of SQL Server, but I'm not positive

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • Yes it is an indexed view. Setting the SET parameters as suggested in the post that Marco linked to, solved the problem. – Rune K M Nov 26 '14 at 14:58