SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '>'. [#1199296]

I am creating a simple events block in Drupal 7 using IIS and SQL Server. I have created a content type of event (title, body, date). I have created a view where I want to display only events that are upcoming. I created a filter that to only display dates that are greater than or equal to today. When I create the filter, I get this error:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '>'.
I have tracked it down, and I see that it is not putting table or column names into part of the where part of the query. Here is the query in the auto query viewer of the view:

SELECT node.[title] AS [node_title], node.[nid] AS [nid], field_data_field_date.[field_date_value] AS [field_data_field_date_field_date_value], 'node' AS field_data_field_date_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = node AND field_data_field_date.deleted = 0)
WHERE ( (( ([node].[status] = '1') AND ([node].[type] IN ('event')) AND ( >= '2011-06-22') )) )
ORDER BY field_data_field_date_field_date_value DESC

It was posted here: http://drupal.org/node/1185114 (the SQL Server Driver) but they say it isn't their code.

Comments

Comment #1

dawehner

German

CreditAttribution: dawehner commented 24 June 2011 at 20:20

Status:

Active

» Postponed (maintainer needs more info)

Did you tryed the same view on mysql?

In general you should read the views issue submission guidelines: http://drupal.org/node/571990
They have a lot of valuable informations if you want some real help, for example you should describe what you did, provide an export etc.

Comment #2

jgiboney CreditAttribution: jgiboney commented 30 June 2011 at 16:45

I have not tried it in mysql. I do not have a mysql database set up to do this. Here is the view:

$view = new view;
$view->name = 'events';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Events';
$view->core = 7;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Recent Events';
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 0;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 0;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Field: Content: Date */
$handler->display->display_options['fields']['field_date']['id'] = 'field_date';
$handler->display->display_options['fields']['field_date']['table'] = 'field_data_field_date';
$handler->display->display_options['fields']['field_date']['field'] = 'field_date';
$handler->display->display_options['fields']['field_date']['label'] = '';
$handler->display->display_options['fields']['field_date']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_date']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_date']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_date']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_date']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_date']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_date']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_date']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_date']['hide_alter_empty'] = 0;
$handler->display->display_options['fields']['field_date']['settings'] = array(
  'format_type' => 'long',
  'fromto' => 'both',
  'multiple_number' => '',
  'multiple_from' => '',
  'multiple_to' => '',
  'show_repeat_rule' => 'show',
);
$handler->display->display_options['fields']['field_date']['field_api_classes'] = 0;
/* Sort criterion: Content: Date (field_date) */
$handler->display->display_options['sorts']['field_date_value']['id'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['table'] = 'field_data_field_date';
$handler->display->display_options['sorts']['field_date_value']['field'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'event' => 'event',
);
/* Filter criterion: Content: Date (field_date) */
$handler->display->display_options['filters']['field_date_value']['id'] = 'field_date_value';
$handler->display->display_options['filters']['field_date_value']['table'] = 'field_data_field_date';
$handler->display->display_options['filters']['field_date_value']['field'] = 'field_date_value';
$handler->display->display_options['filters']['field_date_value']['operator'] = '>=';
$handler->display->display_options['filters']['field_date_value']['granularity'] = 'day';
$handler->display->display_options['filters']['field_date_value']['form_type'] = 'date_select';
$handler->display->display_options['filters']['field_date_value']['default_date'] = 'now';
$handler->display->display_options['filters']['field_date_value']['default_to_date'] = '';
$handler->display->display_options['filters']['field_date_value']['year_range'] = '-3:+3';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'events';

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block');
$handler->display->display_options['defaults']['pager'] = FALSE;
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '5';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['row_plugin'] = 'fields';
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'event' => 'event',
);
/* Filter criterion: Content: Date (field_date) */
$handler->display->display_options['filters']['field_date_value']['id'] = 'field_date_value';
$handler->display->display_options['filters']['field_date_value']['table'] = 'field_data_field_date';
$handler->display->display_options['filters']['field_date_value']['field'] = 'field_date_value';
$handler->display->display_options['filters']['field_date_value']['operator'] = 'empty';
$handler->display->display_options['filters']['field_date_value']['granularity'] = 'day';
$handler->display->display_options['filters']['field_date_value']['form_type'] = 'date_select';
$handler->display->display_options['filters']['field_date_value']['default_date'] = 'now';
$handler->display->display_options['filters']['field_date_value']['default_to_date'] = '';
$handler->display->display_options['filters']['field_date_value']['year_range'] = '-3:+3';

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Upcoming Events';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['row_plugin'] = 'fields';
$handler->display->display_options['row_options']['hide_empty'] = 0;
$handler->display->display_options['row_options']['default_field_elements'] = 1;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['sorts'] = FALSE;
/* Sort criterion: Content: Date (field_date) */
$handler->display->display_options['sorts']['field_date_value']['id'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['table'] = 'field_data_field_date';
$handler->display->display_options['sorts']['field_date_value']['field'] = 'field_date_value';
$handler->display->display_options['sorts']['field_date_value']['order'] = 'DESC';
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'event' => 'event',
);

Comment #3

dawehner

German

CreditAttribution: dawehner commented 30 June 2011 at 17:27

What should be done here ...

You have no mysql server and we don't have a mssql server. Noone can reproduce the bug, that's sad.
Can't you get you one for example with xampp?

Comment #4

Damien Tournoud CreditAttribution: Damien Tournoud commented 30 June 2011 at 18:04

It looks like some bug in the Date module filter. I really doubt this is a SQL Server bug.

Comment #5

jgiboney CreditAttribution: jgiboney commented 30 June 2011 at 18:58

I used wamp to create a test site. I only downloaded the views, date, and ctools modules. It seems to work fine. So their seem to be three possibilities:

1) We messed up something in our configuration (we didn't do any manually coding)
2) The Views module doesn't play nicely with the SQL Server Driver
3) The Views module in combination with the Date module don't play nicely with the SQL Server Driver

Comment #6

merlinofchaos CreditAttribution: merlinofchaos commented 30 June 2011 at 19:03

You can eliminate date from the equation by trying views that do not use date fields.

If those work, and date fields are the trigger, then date.module is responsible. If things continue to break, date.module is at least not responsible for other fields.

Comment #7

jgiboney CreditAttribution: jgiboney commented 30 June 2011 at 19:54

So I added an integer field to my event content type and did a greater than 2 filter. This worked correctly. Disabling all the date modules doesn't do anything. Disabling the date filter makes the view run correctly.

edit: Disabling doesn't do anything because it just removes the problem field

Comment #8

merlinofchaos CreditAttribution: merlinofchaos commented 30 June 2011 at 20:22

Project:	Views (for Drupal 7)	» Date
Version:	7.x-3.0-rc1	» 7.x-2.x-dev
Status:	Postponed (maintainer needs more info)	» Active

Then this is probably a date.module problem, which is not terribly surprising, because all databases handle dates a little bit differently.

Comment #9

dawehner

German

CreditAttribution: dawehner commented 2 July 2011 at 16:40

This seems to be a duplicate of #1138622: Add support for SQL Server :)

Comment #10

jgiboney CreditAttribution: jgiboney commented 8 July 2011 at 16:47

Can anyone tell me the file/function that returns the field names, as this seems to be the only issue?

Comment #11

jgiboney CreditAttribution: jgiboney commented 11 July 2011 at 08:08

If anyone needs a temporary solution, or a starting point to solve this issue, the op_simple function on line 134 of the date_views_filter_handler_simple.inc file in the date > date_views > includes folder is where the where clause is added to the query. I added the following lines after the lines that declare the $field variable:

if ($field == "") {
   $field = "[field_data_field_date].[field_date_value]";
}

Comment #12

KarenS CreditAttribution: KarenS commented 14 November 2011 at 11:28

Component:

Code

» Non-MYSQL Databases

Flagging non-MYSQL database issues.

Comment #13

kimberlydb CreditAttribution: kimberlydb commented 6 August 2013 at 20:38

I used hook_views_query_alter as a work around to this issue.

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '>'.

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Thank you to these Drupal contributors

News items

Our community

Documentation

Drupal code base

Governance of community