Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, January 20, 2020 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to T-SQL & Queries | New ThreadView:  Search:  
 Author  Thread: Enable View Not Working - Access Violation
Chris Burriss
Posts: 120
 
Enable View Not Working - Access ViolationYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 15 Apr 10 3:12 PM
I've created a few views that provide the data structure necessary to build SLX groups for consolidated history and activity data. The version number is 7.5.2.2151. When I attempt to enable the view, I get an Access Violation error. I've profiled the action and that doesn't help. My view is below. Does anyone know why this is not working? Thanks!

SELECT
CASE IsNull(Histories.AccountID, '') WHEN '' THEN Activities.AccountID ELSE Histories.AccountID END AS AccountID,
CASE IsNull(Histories.UserName, '') WHEN '' THEN Activities.UserName ELSE Histories.UserName END AS UserName,
Histories.LastHistoryByUser, Activities.NextActivityByUser,
CASE IsNull(Activities.NextActivityByUser, '') WHEN '' THEN Histories.LastHistoryByUser ELSE Activities.NextActivityByUser END AS NextActivityLastHistoryCombined
FROM
(
--Last History (User)
SELECT h.ACCOUNTID, h.UserName, Max(CompletedDate) AS LastHistoryByUser
FROM sysdba.HISTORY h
INNER JOIN sysdba.C_ACTIVITYTYPE at ON at.CODE = h.TYPE
WHERE at.DESCRIPTION IN ('Phone Call', 'Meeting')
AND IsNull(h.ACCOUNTID, '') <> ''
GROUP by h.ACCOUNTID,h.USERNAME
) Histories

FULL OUTER JOIN (

-- Next Activity (User)
SELECT a.ACCOUNTID,
CASE u.UserID WHEN 'Admin' THEN 'Administrator' ELSE u.LASTNAME + ', ' + u.FIRSTNAME END AS USERNAME,
Min(a.STARTDATE) AS NextActivityByUser
FROM sysdba.ACTIVITY a
INNER JOIN sysdba.C_ACTIVITYTYPE at ON at.CODE = a.TYPE
INNER JOIN sysdba.USERINFO u ON u.USERID = a.USERID
WHERE at.DESCRIPTION IN ('Phone Call', 'Meeting')
AND IsNull(a.ACCOUNTID, '') <> ''
AND ACTIVITYID NOT IN (SELECT IsNull(ACTIVITYID, '') FROM sysdba.HISTORY)
GROUP by a.ACCOUNTID, CASE u.UserID WHEN 'Admin' THEN 'Administrator' ELSE u.LASTNAME + ', ' + u.FIRSTNAME END
) Activities ON Activities.ACCOUNTID = Histories.ACCOUNTID AND ISNULL(Activities.username, '') = ISNULL(Histories.Username, '')
[Reply][Quote]
Brian Mooy
Posts: 25
 
Re: Enable View Not Working - Access ViolationYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Apr 10 11:46 AM
I have the exact same issue on a much simpler view that I built. I stripped out a lot of crap trying to get to the root of the problem but no luck. Same build number.

I was able to get one view enabled but I have three others I need that won't work.


CREATE VIEW [sysdba].[CrewMembers]
AS
SELECT
LastName + ', ' + FirstName as NameLF,
FirstName + ' ' + LastName as Name
FROM sysdba.CONTACT
WHERE (TYPE = 'Crew Member') OR
(TYPE = 'Installer')

I'm working on this right now and will let you know if I figure anything out.

Brian
[Reply][Quote]
Brian Mooy
Posts: 25
 
Re: Enable View Not Working - Access ViolationYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Apr 10 12:16 PM
Ok. Seems like a bug in 7.5.2 with this build.

I gave up on it because I couldn't find an explanation and decided to find a workaround. The one view of the four that I was able to enable in 7.5.2 I created using the SQL function to execute the 'Create View' statement. I think that has a possibility of working but I tried a few more times and it did not work.

I found a workaround - and loaded m 7.5.2 databse in the 7.2 administrator, and enabled the 3 views without a problem.
[Reply][Quote]
Chris Burriss
Posts: 120
 
Re: Enable View Not Working - Access ViolationYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Apr 10 9:19 PM
I've received confirmation from Sage Support that this is a defect (obviously). There is currently a fix in customer validation that addresses this problem plus one with primary keys on views. If you contact tech support, they should be able to send you an early release of the new build.
[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2020 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 1/20/2020 5:10:19 AM