Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
Posts: 10
Registered: ‎01-04-2011
Accepted Solution

Unable to run SQL statement to create a view and sync to remotes

I created a view from SQL server management and enable the view from Architect. This view is used by Crystal report but should available in remote databases; unfortunately, SLX have not transferred this view to remotes, and could not be run under ADMIN since "Failed to Parse SQL" is prompted. I have tried ignoring the message and pushing to remotes with no luck.

is there any workaround?, I would like to create extra views but require a straight forward process to sync with remotes.

 

Help......

CREATE VIEW HST_PlannedActivities
AS 
SELECT  HISTORY.HISTORYID, CASE WHEN LEN(LEADID) > 0 THEN 'Lead' ELSE ISNULL(ACCOUNT.STATUS, '') 
                      END AS Silo, CASE WHEN LEN(LEADID) > 0 THEN ' ' ELSE isnull(account.SUBSTATUS, '') END AS Substatus, HISTORY.USERID, 
                      HISTORY.USERNAME, PICKLIST.TEXT, HISTORY.ACCOUNTNAME, HISTORY.CONTACTNAME AS [Contact  Name], 
                      CASE WHEN LEN(OPPORTUNITYNAME) > 0 THEN 'T' ELSE 'F' END AS Opportunity, HISTORY.CATEGORY, 
                      HISTORY.DESCRIPTION AS Purpose, HISTORY.RESULT, DATEADD(HH, 
                      CASE WHEN userinfo.TIMEZONE = 'MOUNTAIN STANDARD TIME' THEN - 2 ELSE 0 END, HISTORY.COMPLETEDDATE) AS COMPLETEDDATE, 
                      HISTORY.TIMELESS, CASE WHEN ID = '262146' THEN 1 ELSE CASE WHEN ID = '262145' THEN 2 ELSE 3 END END AS TheOrder, 
                      DATEADD(HH, CASE WHEN userinfo.TIMEZONE = 'MOUNTAIN STANDARD TIME' THEN - 2 ELSE 0 END, 
                      HISTORY.COMPLETEDDATE) AS TimeStamp, ISNULL(HST.HstPhnCalls, 0) AS HstPhCalls
FROM         ACCOUNT RIGHT OUTER JOIN
                      HISTORY LEFT OUTER JOIN
                      USERINFO ON HISTORY.USERID = USERINFO.USERID LEFT OUTER JOIN
                      USER_ACTIVITY ON HISTORY.ACTIVITYID = USER_ACTIVITY.ACTIVITYID LEFT OUTER JOIN
                          (SELECT     ACCOUNTID AS AccountID_, LEADID AS LeadID_, COUNT(HISTORYID) AS HstPhnCalls
                            FROM          HISTORY AS HISTORY_1
                            WHERE      (DATEDIFF(dd, COMPLETEDDATE, { fn NOW() }) < 60) AND (TYPE = '262146')
                            GROUP BY ACCOUNTID, LEADID) AS HST ON HISTORY.LEADID = HST.LeadID_ AND HISTORY.ACCOUNTID = HST.AccountID_ ON 
                      ACCOUNT.ACCOUNTID = HISTORY.ACCOUNTID LEFT OUTER JOIN
                      PICKLIST ON HISTORY.TYPE = PICKLIST.ID LEFT OUTER JOIN
                      CONTACT ON HISTORY.CONTACTID = CONTACT.CONTACTID
WHERE     (HISTORY.TYPE IN ('262145', '262146', '262147'))
ORDER BY HISTORY.USERNAME, COMPLETEDDATE, HISTORY.TYPE

 

Francisco Amaya/ IT Manager
Grayling Industries, Inc.
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Unable to run SQL statement to create a view and sync to remotes

I don't think Create View is supported on the SLX OLE DB Provider.

 

You can try EXEC SQL in the ADMIN......

 

Or have a Plugin included script that uses a SQL Server native connection, you could invoke the execution of the view each time the user logged in for example (If NOT( object exists) then run Create View kinds of stuff) from a global script

 

We used to run this using Agents, but haven't done this in a few years....

RJ Samp
Copper Contributor
Posts: 10
Registered: ‎01-04-2011

Re: Unable to run SQL statement to create a view and sync to remotes

Thank you for your prompt reponse. I didn't explained correctly. I tried running Create view statement from Execute SQL available in the SLX Administrator. Is it possible from you to paste a basic sample to create the script?

 

I sincerely thought will be transferred to remotes when "enabling" view, since defaulted options are checked Even if are views, not tables):

 

- Sync this table out to remotes

- Sync this table from remotes into main database

Francisco Amaya/ IT Manager
Grayling Industries, Inc.
Employee
Posts: 79
Registered: ‎06-25-2009

Re: Unable to run SQL statement to create a view and sync to remotes

You can execute this statement as follows in Admin Execute SQL.  EXEC is a Sql Server function that the provider recognizes and passes through without attempting to parse it.  It will get logged as a SQL2 transaction allowing it to be synced to remotes.  I did have to remove your order by clause at the end because Sql Server did not like it.

 

Exec('CREATE VIEW HST_PlannedActivities
AS
SELECT  HISTORY.HISTORYID, CASE WHEN LEN(LEADID) > 0 THEN ''Lead'' ELSE ISNULL(ACCOUNT.STATUS, '''')
                      END AS Silo, CASE WHEN LEN(LEADID) > 0 THEN '' '' ELSE isnull(account.SUBSTATUS, '''') END AS Substatus, HISTORY.USERID,
                      HISTORY.USERNAME, PICKLIST.TEXT, HISTORY.ACCOUNTNAME, HISTORY.CONTACTNAME AS [Contact  Name],
                      CASE WHEN LEN(OPPORTUNITYNAME) > 0 THEN ''T'' ELSE ''F'' END AS Opportunity, HISTORY.CATEGORY,
                      HISTORY.DESCRIPTION AS Purpose, HISTORY.RESULT, DATEADD(HH,
                      CASE WHEN userinfo.TIMEZONE = ''MOUNTAIN STANDARD TIME'' THEN - 2 ELSE 0 END, HISTORY.COMPLETEDDATE) AS COMPLETEDDATE,
                      HISTORY.TIMELESS, CASE WHEN ID = ''262146'' THEN 1 ELSE CASE WHEN ID = ''262145'' THEN 2 ELSE 3 END END AS TheOrder,
                      DATEADD(HH, CASE WHEN userinfo.TIMEZONE = ''MOUNTAIN STANDARD TIME'' THEN - 2 ELSE 0 END,
                      HISTORY.COMPLETEDDATE) AS TimeStamp, ISNULL(HST.HstPhnCalls, 0) AS HstPhCalls
FROM         ACCOUNT RIGHT OUTER JOIN
                      HISTORY LEFT OUTER JOIN
                      USERINFO ON HISTORY.USERID = USERINFO.USERID LEFT OUTER JOIN
                      USER_ACTIVITY ON HISTORY.ACTIVITYID = USER_ACTIVITY.ACTIVITYID LEFT OUTER JOIN
                          (SELECT     ACCOUNTID AS AccountID_, LEADID AS LeadID_, COUNT(HISTORYID) AS HstPhnCalls
                            FROM          HISTORY AS HISTORY_1
                            WHERE      (DATEDIFF(dd, COMPLETEDDATE, { fn NOW() }) < 60) AND (TYPE = ''262146'')
                            GROUP BY ACCOUNTID, LEADID) AS HST ON HISTORY.LEADID = HST.LeadID_ AND HISTORY.ACCOUNTID = HST.AccountID_ ON
                      ACCOUNT.ACCOUNTID = HISTORY.ACCOUNTID LEFT OUTER JOIN
                      PICKLIST ON HISTORY.TYPE = PICKLIST.ID LEFT OUTER JOIN
                      CONTACT ON HISTORY.CONTACTID = CONTACT.CONTACTID
WHERE     (HISTORY.TYPE IN (''262145'', ''262146'', ''262147''))')

 

 

Paul Zeimet
Development Manager
Infor CRM
Copper Contributor
Posts: 10
Registered: ‎01-04-2011

Re: Unable to run SQL statement to create a view and sync to remotes

Dear Paul,

 

I appreciate so much your solution, I was able to create the view in the server, then, I enabled the view from Administrator->Manage->Database. However, the view was not transferred to remotes after synching several times from both sides.

 

Am I missing something?

 

Francisco Amaya

Francisco Amaya/ IT Manager
Grayling Industries, Inc.
Employee
Posts: 79
Registered: ‎06-25-2009

Re: Unable to run SQL statement to create a view and sync to remotes

You will need to create the view using Administrator->Tools->Execute SQL.. using the statement given above.  This should cause it to be logged for synchronization to remotes.  You should be able to run syncserver at that point to send to remotes.

Paul Zeimet
Development Manager
Infor CRM
Copper Contributor
Posts: 10
Registered: ‎01-04-2011

Re: Unable to run SQL statement to create a view and sync to remotes

Paul

 

Thank you again for your empathy and prompt response to this issue, your solution worked very good; it took some time before view was available for the report was requested from but it finally ran as expected.

 

Hats off to Paul.....

 

Francisco Amaya/ Systems Manager

Grayling Industries, Inc.

Francisco Amaya/ IT Manager
Grayling Industries, Inc.
Employee
Posts: 79
Registered: ‎06-25-2009

Re: Unable to run SQL statement to create a view and sync to remotes

Francisco

Your very welcome.  Glad I was able to help.

 

Paul Zeimet
Development Manager
Infor CRM
Bronze Super Contributor
Posts: 146
Registered: ‎04-01-2009

Re: Unable to run SQL statement to create a view and sync to remotes

Sorry to jump on your train, but I can't seem to get this one to parse... Is it the embedded Select statement? If so, is there a way around it.?

Exec('Create view Anr_LastMSRNote_Vu as
SELECT OPPORTUNITYID, CREATEDATE, NOTES
FROM sysdba.HISTORY H
WHERE Description = 'MSR Note'
AND CreateDate = (SELECT MAX(CreateDate) FROM sysdba.HISTORY H1 WHERE H1.OPPORTUNITYID = H.OPPORTUNITYID and Description = 'MSR Note')
')


Thanks for anything you can provide.

Patrick
Copper Contributor
Posts: 10
Registered: ‎01-04-2011

Re: Unable to run SQL statement to create a view and sync to remotes

Patrick,

 

All select statement needs to be quoted like your example, however, I noticed WHERE clause is not using doubled single quoted. Try it again as follows:


Exec('Create view Anr_LastMSRNote_Vu as SELECT OPPORTUNITYID, CREATEDATE, NOTES FROM sysdba.HISTORY H
WHERE Description = ''MSR Note''AND CreateDate = (SELECT MAX(CreateDate) FROM sysdba.HISTORY H1 WHERE H1.OPPORTUNITYID = H.OPPORTUNITYID and Description = ''MSR Note'')')

 

Notice: WHERE clause = ''MSR Note'' use double single quote

 

I hope this work for you.

 

Francisco Amaya

Francisco Amaya/ IT Manager
Grayling Industries, Inc.