by diligent

Server-side synchronization between SuiteCRM and Microsoft Exchange

Cancel at any time!
Free Trial

#1596 - problem installation with sql server

Closed Installation created by GianlucaSedoc 10 months ago

Hi i'm having a big trouble installing with my sql server. here the log file:

Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:DROP FUNCTION IF EXISTS `IsContactVisibleOutLookSyncFlag`;::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '`'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:CREATE FUNCTION IsContactVisibleOutLookSyncFlag(userId varchar(256), contactId varchar(256))
                                RETURNS int
                                RETURN exists (
                                    select 1 from contacts_users 
                                    where user_id = userId and contact_id = contactId and deleted = 0
                                )::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'userId'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:CREATE FUNCTION IsUserAllowedForExchangeSynch_c (assignedUserId varchar(256), userId nvarchar(256), MyCase varchar(20))
                                RETURNS int
                                RETURN exists (
                                    select 1 from
                                    exchg_exchangegroupmodule eegm
                                    join suoam_sweetersync_uoam ucstm on ucstm.assigned_user_id = userId and
                                    case MyCase
                                        when N'AccountGroup' then (ucstm.eg_account_id_c IS NULL OR ucstm.eg_account_id_c = '')
                                        when N'ContactGroup' then (ucstm.eg_contact_id_c IS NULL OR ucstm.eg_contact_id_c = '')
                                        when N'CallGroup' then (ucstm.eg_call_id_c IS NULL OR ucstm.eg_call_id_c = '')
                                        when N'CaseGroup' then (ucstm.eg_case_id_c IS NULL OR ucstm.eg_case_id_c = '')
                                        when N'MeetingGroup' then (ucstm.eg_meeting_id_c IS NULL OR ucstm.eg_meeting_id_c = '')
                                        when N'ProjectGroup' then (ucstm.eg_project_id_c IS NULL OR ucstm.eg_project_id_c = '')
                                        when N'OpportunityGroup' then (ucstm.eg_opportunity_id_c IS NULL OR ucstm.eg_opportunity_id_c = '')
                                        when N'TaskGroup' then (ucstm.eg_task_id_c IS NULL OR ucstm.eg_task_id_c = '')
                                        else 1 = 1
                                    end
                                    where eegm.isactive = 1
                                        and exists (select 1 from exchgroup_users eu where eegm.id = eu.exchgroup_id and eu.user_id = assignedUserId AND eu.deleted = 0)
                                        and exists (select 1 from exchgroup_users eu where eegm.id = eu.exchgroup_id and eu.user_id = userId AND eu.deleted = 0)
                                )::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'assignedUserId'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IS'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'and'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:DROP FUNCTION IF EXISTS `IsModuleVisible_c`;::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '`'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:CREATE FUNCTION IsModuleVisible_c (assignedUserId varchar(255), userId nvarchar(256), MyCase varchar(20))
                                RETURNS int
                                RETURN exists (
                                    select 1 from
                                    exchg_exchangegroupmodule eegm
                                    join suoam_sweetersync_uoam ucstm on ucstm.assigned_user_id = userId and ucstm.deleted = 0 and 
                                    case MyCase
                                        when N'AccountGroup' then ucstm.eg_account_id_c = eegm.id
                                        when N'ContactGroup' then ucstm.eg_contact_id_c = eegm.id
                                        when N'OpportunityGroup' then ucstm.eg_opportunity_id_c = eegm.id
                                        when N'CallGroup' then ucstm.eg_call_id_c = eegm.id
                                        when N'CaseGroup' then ucstm.eg_case_id_c = eegm.id
                                        when N'MeetingGroup' then ucstm.eg_meeting_id_c = eegm.id
                                        when N'ProjectGroup' then ucstm.eg_project_id_c = eegm.id
                                        when N'TaskGroup' then ucstm.eg_task_id_c = eegm.id
                                    end
                                    where
                                        eegm.isactive = 1 AND 
                                        exists (select 1 from exchgroup_users eu where eegm.id = eu.exchgroup_id and eu.user_id = assignedUserId AND eu.deleted = 0)
                                )::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'assignedUserId'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '='.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ')'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:CREATE TABLE IF NOT EXISTS `itemsynchelper` (
                              `id` varchar(36) NOT NULL,
                              `module_name` varchar(100) NOT NULL,
                              `suite_item_id` varchar(36) NOT NULL,
                              `user_id` varchar(36) NOT NULL,
                              `last_sync_datetime` datetime NOT NULL,
                              `deleted` tinyint(1) DEFAULT '0',
                              PRIMARY KEY (`id`),
                              KEY `idx_sweeter_user_id` (`user_id`),
                              KEY `idx_sweeter_item_id` (`suite_item_id`),
                              KEY `idx_sweeter_module_name` (`module_name`)
                            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IF'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '`'.
Tue Nov 27 10:57:36 2018 [5972][1][FATAL]  Query Failed:CREATE TABLE IF NOT EXISTS `syncinstances` (
                              `id` varchar(36) NOT NULL,
                              `number` int(11) NOT NULL,
                              `knownnumber` int(11) NOT NULL,
                              `lastrun` datetime DEFAULT NULL,
                              PRIMARY KEY (`id`)
                            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IF'.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '`'.
Tue Nov 27 10:57:42 2018 [5972][1][FATAL] Error altering column(s) isactive on table: exchg_exchangegroupmodule: Query Failed: ALTER TABLE exchg_exchangegroupmodule DROP CONSTRAINT DF__exchg_exc__isact__23A93AC7 ALTER TABLE exchg_exchangegroupmodule ALTER COLUMN [isactive] bit NULL 
 ALTER TABLE exchg_exchangegroupmodule ADD   DEFAULT '0' FOR isactive ::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]'DF__exchg_exc__isact__23A93AC7' is not a constraint.
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Could not drop constraint. See previous errors.
Tue Nov 27 10:57:42 2018 [5972][1][FATAL] Exception handling in C:\SITI\GammaCRMv2\include\MVC\Controller\SugarController.php:400
Tue Nov 27 10:57:42 2018 [5972][1][FATAL] Exception in Controller: Errore nel Database. Per dettagli si prega di fare riferimento al registro errori di SuiteCRM.
Tue Nov 27 10:57:42 2018 [5972][1][FATAL] backtrace:
#0 C:\SITI\GammaCRMv2\include\database\DBManager.php(353): sugar_die('Errore nel Data...')
#1 C:\SITI\GammaCRMv2\include\database\DBManager.php(328): DBManager->registerError('Error altering ...', 'Error altering ...', true)
#2 C:\SITI\GammaCRMv2\include\database\SqlsrvManager.php(235): DBManager->checkError('Error altering ...', true)
#3 C:\SITI\GammaCRMv2\include\database\DBManager.php(1267): SqlsrvManager->query(' ALTER TABLE ex...', true, 'Error altering ...')
#4 C:\SITI\GammaCRMv2\include\database\DBManager.php(912): DBManager->alterColumn('exchg_exchangeg...', Array, false)
#5 C:\SITI\GammaCRMv2\include\database\DBManager.php(764): DBManager->repairTableParams('exchg_exchangeg...', Array, Array, true, NULL)
#6 C:\SITI\GammaCRMv2\modules\Administration\QuickRepairAndRebuild.php(177): DBManager->repairTable(Object(exchg_ExchangeGroupModule), true)
#7 C:\SITI\GammaCRMv2\modules\Administration\QuickRepairAndRebuild.php(72): RepairAndClear->repairDatabaseSelectModules()
#8 C:\SITI\GammaCRMv2\ModuleInstall\ModuleInstaller.php(222): RepairAndClear->repairAndClearAll(Array, Array, true, false)
#9 C:\SITI\GammaCRMv2\modules\Administration\UpgradeWizard_commit.php(340): ModuleInstaller->install('cache/upgrades/...')
#10 C:\SITI\GammaCRMv2\include\MVC\View\SugarView.php(818): include_once('C:\\SITI\\GammaCR...')
#11 C:\SITI\GammaCRMv2\include\MVC\View\views\view.classic.php(72): SugarView->includeClassicFile('cache/upgrades/...')
#12 C:\SITI\GammaCRMv2\include\MVC\View\SugarView.php(211): ViewClassic->display()
#13 C:\SITI\GammaCRMv2\include\MVC\Controller\SugarController.php(435): SugarView->process()
#14 C:\SITI\GammaCRMv2\include\MVC\Controller\SugarController.php(375): SugarController->processView()
#15 C:\SITI\GammaCRMv2\include\MVC\SugarApplication.php(113): SugarController->execute()
#16 C:\SITI\GammaCRMv2\index.php(52): SugarApplication->execute()
#17 {main}
Tue Nov 27 11:01:26 2018 [5892][1][FATAL] log call at: modules/Administration/index.tpl:53 - MY_FRAME is not set
Tue Nov 27 11:01:52 2018 [5972][1][FATAL] Undefined index: module
Tue Nov 27 11:01:52 2018 [5972][1][FATAL] Invalid or missing SubPanelDefinition property: subpanel_name
Tue Nov 27 11:01:52 2018 [5972][1][FATAL] Failed to load original or custom subpanel data for eapm in 

actually i've edited a lot of query and the only 2 i can't still fix are:

CREATE FUNCTION IsUserAllowedForExchangeSynch_c (assignedUserId varchar(256), userId nvarchar(256), MyCase varchar(20))
                                RETURNS int
                                RETURN exists (
                                    select 1 from
                                    exchg_exchangegroupmodule eegm
                                    join suoam_sweetersync_uoam ucstm on ucstm.assigned_user_id = userId and
                                    case MyCase
                                        when N'AccountGroup' then (ucstm.eg_account_id_c IS NULL OR ucstm.eg_account_id_c = '')
                                        when N'ContactGroup' then (ucstm.eg_contact_id_c IS NULL OR ucstm.eg_contact_id_c = '')
                                        when N'CallGroup' then (ucstm.eg_call_id_c IS NULL OR ucstm.eg_call_id_c = '')
                                        when N'CaseGroup' then (ucstm.eg_case_id_c IS NULL OR ucstm.eg_case_id_c = '')
                                        when N'MeetingGroup' then (ucstm.eg_meeting_id_c IS NULL OR ucstm.eg_meeting_id_c = '')
                                        when N'ProjectGroup' then (ucstm.eg_project_id_c IS NULL OR ucstm.eg_project_id_c = '')
                                        when N'OpportunityGroup' then (ucstm.eg_opportunity_id_c IS NULL OR ucstm.eg_opportunity_id_c = '')
                                        when N'TaskGroup' then (ucstm.eg_task_id_c IS NULL OR ucstm.eg_task_id_c = '')
                                        else 1 = 1
                                    end
                                    where eegm.isactive = 1
                                        and exists (select 1 from exchgroup_users eu where eegm.id = eu.exchgroup_id and eu.user_id = assignedUserId AND eu.deleted = 0)
                                        and exists (select 1 from exchgroup_users eu where eegm.id = eu.exchgroup_id and eu.user_id = userId AND eu.deleted = 0)

and the query:

CREATE TABLE IF NOT EXISTS `syncinstances` (
                              `id` varchar(36) NOT NULL,
                              `number` int(11) NOT NULL,
                              `knownnumber` int(11) NOT NULL,
                              `lastrun` datetime DEFAULT NULL,
                              PRIMARY KEY (`id`)
                            ) ENGINE=MyISAM DEFAULT CHARSET=utf8

there is a way to convert this query on a mssql server?

thanks

  1. diligent member avatar

    diligent Provider Affiliate

    10 months ago

    This question has been answered through a private channel, as the content of the answer contains information that we would like to keep undisclosed.
    Thank you,

    the diligent Team

  2. GianlucaSedoc member avatar

    GianlucaSedoc

    10 months ago

    thanks for the response, a question, so there are some plugin with exchange that can run on a mssql without create trouble or bug? because i really need it and i can't switch the db

  3. diligent member avatar

    diligent Provider Affiliate

    10 months ago

    Hi Gianluca,
    we would suggest looking on the SuiteCRM shop. SuiteCRM official outlook plugin for example should work with mssql. However, that is not server related, but client side.
    Hope this helps!
    Thanks,

    the diligent Team

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...