{"action":"created","issue":{"url":"https://api.github.com/repos/ProjectNami/projectnami/issues/194","repository_url":"https://api.github.com/repos/ProjectNami/projectnami","labels_url":"https://api.github.com/repos/ProjectNami/projectnami/issues/194/labels{/name}","comments_url":"https://api.github.com/repos/ProjectNami/projectnami/issues/194/comments","events_url":"https://api.github.com/repos/ProjectNami/projectnami/issues/194/events","html_url":"https://github.com/ProjectNami/projectnami/issues/194","id":212927699,"number":194,"title":"WordPress database error [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHEN'","user":{"login":"DeanoFino","id":17516743,"avatar_url":"https://avatars3.githubusercontent.com/u/17516743?v=3","gravatar_id":"","url":"https://api.github.com/users/DeanoFino","html_url":"https://github.com/DeanoFino","followers_url":"https://api.github.com/users/DeanoFino/followers","following_url":"https://api.github.com/users/DeanoFino/following{/other_user}","gists_url":"https://api.github.com/users/DeanoFino/gists{/gist_id}","starred_url":"https://api.github.com/users/DeanoFino/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DeanoFino/subscriptions","organizations_url":"https://api.github.com/users/DeanoFino/orgs","repos_url":"https://api.github.com/users/DeanoFino/repos","events_url":"https://api.github.com/users/DeanoFino/events{/privacy}","received_events_url":"https://api.github.com/users/DeanoFino/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":13,"created_at":"2017-03-09T03:45:35Z","updated_at":"2017-03-15T22:06:04Z","closed_at":null,"body":"ProjectNami Version 1.5.5\r\n\r\nThe following error occurs when translating a MySQL insert statement...\r\n\r\n`[09-Mar-2017 04:12:46 UTC] WordPress database error [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHEN'. Code - 156 for query MERGE INTO  wp_advads_impressions  WITH (HOLDLOCK) AS target USING (SELECT 2397 as ad_id,1703100806 as timestamp,1 as count) AS source (ad_id, timestamp, count) ON (source.ad_id=target.ad_id AND source.timestamp=target.timestamp AND source.count=target.count) WHEN MATCHED THEN UPDATE SET  WHEN NOT MATCHED THEN INSERT (ad_id, timestamp, count) VALUES(2397, 1703100806, 1); made by do_action('wp_ajax_advanced-ads-tracking-track'), WP_Hook->do_action, WP_Hook->apply_filters, Advanced_Ads_Tracking_Ajax->track, Advanced_Ads_Tracking_Util->track_impression, Advanced_Ads_Tracking_Util->persist`\r\n\r\nIs it possibly the run on between `WHEN MATCHED THEN UPDATE SET  WHEN NOT MATCHED THEN INSERT`?\r\n\r\nInstead of `WHEN MATCHED THEN UPDATE SET`...\r\n\r\nThen\r\n\r\n`WHEN NOT MATCHED THEN INSERT`...\r\n\r\nSee http://stackoverflow.com/questions/32917743/how-to-use-merges-when-not-matched-to-insert-a-new-record-in-same-table"},"comment":{"url":"https://api.github.com/repos/ProjectNami/projectnami/issues/comments/286894668","html_url":"https://github.com/ProjectNami/projectnami/issues/194#issuecomment-286894668","issue_url":"https://api.github.com/repos/ProjectNami/projectnami/issues/194","id":286894668,"user":{"login":"DeanoFino","id":17516743,"avatar_url":"https://avatars3.githubusercontent.com/u/17516743?v=3","gravatar_id":"","url":"https://api.github.com/users/DeanoFino","html_url":"https://github.com/DeanoFino","followers_url":"https://api.github.com/users/DeanoFino/followers","following_url":"https://api.github.com/users/DeanoFino/following{/other_user}","gists_url":"https://api.github.com/users/DeanoFino/gists{/gist_id}","starred_url":"https://api.github.com/users/DeanoFino/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DeanoFino/subscriptions","organizations_url":"https://api.github.com/users/DeanoFino/orgs","repos_url":"https://api.github.com/users/DeanoFino/repos","events_url":"https://api.github.com/users/DeanoFino/events{/privacy}","received_events_url":"https://api.github.com/users/DeanoFino/received_events","type":"User","site_admin":false},"created_at":"2017-03-15T22:06:04Z","updated_at":"2017-03-15T22:06:04Z","body":"OK cool thanks again Patrick. \r\n\r\nIt looks like there are two column candidate keys that together act as a primary key in SQL Server (timestamp and ad_id). This could be the root of the problem as to why the UPDATE statement is failing...Not sure if this is an edge case or not that PN may or may not want to support?\r\n\r\nIt appears I can bypass the UPDATE failing problem by adding an auto increment int column as primary key in SQL Server, and remove the UPDATE statement in the query, forcing all INSERTS.\r\n"}}
{"actor":{"display_login":"DeanoFino"}}