7/8/2025 11:34:01 AM
|
| |
| 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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Getting Insufficient or Invalid Join Data Error on SQL Query - VBS in Outlook
Posted: 17 Sep 09 2:56 PM
|
--------------------------------------[Code]-------------------------------------------
Sub subGetMyTicketTimes()
Dim strTickets
'--------------------> Get SLX Data for User
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Set objRS2 = CreateObject("ADODB.Recordset")
objConn.ConnectionString = "Provider=SLXOLEDB.1;Integrated Security=True;Initial Catalog=SLXSUPPORT;Data Source=slx;Extended Properties=PORT=1706;LOG=ON;Location=;Mode=Read"
objConn.Open
strSQL = "SELECT TOP 100 " & _
"T.CREATEDATE , T.TICKETID, T.ALTERNATEKEYPREFIX, T.ALTERNATEKEYSUFFIX, T.Subject, A.Account, C.FirstName, C.LastName, C.Email, C.WORKPHONE " & _
"FROM sysdba.TICKET T " & _
"JOIN sysdba.SECCODE S ON S.SECCODEID = T.ASSIGNEDTOID " & _
"JOIN sysdba.ACCOUNT A ON A.ACCOUNTID = T.ACCOUNTID " & _
"JOIN sysdba.CONTACT C ON C.CONTACTID = T.CONTACTID " & _
"JOIN sysdba.PICKLIST P ON T.STATUSCODE = P.ITEMID " & _
"WHERE S.SECCODEDESC LIKE 'wilson, c%' " & _
"AND NOT P.TEXT = 'Engineering' " & _
"AND NOT P.TEXT = 'Closed' " & _
"AND NOT P.TEXT = 'Closed - No Customer Response' " & _
"ORDER BY TEXT, CREATEDATE"
objRS.Open strSQL, objConn, 3, 3
While Not objRS.EOF
strTickets = strTickets & Trim(objRS("ALTERNATEKEYPREFIX")) & "-" & Trim(objRS("ALTERNATEKEYSUFFIX")) & "|||" & _
Trim(objRS("ACCOUNT")) & "|||" & _
Trim(objRS("SUBJECT")) & "|||" & _
Trim(objRS("FIRSTNAME")) & "|||" & Trim(objRS("LASTNAME")) & "|||" & _
Trim(objRS("EMAIL")) & "|||" & _
Trim(objRS("WORKPHONE")) & "|||" & _
Trim(objRS("CREATEDATE")) & "|||"
strTicketID = Trim(objRS("TICKETID"))
strSQL2 = "SELECT TOP 1 T.ASSIGNEDDATE, U.USERNAME, P.TEXT " & _
"FROM sysdba.TICKETACTIVITY T " & _
"JOIN sysdba.PICKLIST P ON P.ITEMID = T.ACTIVITYTYPECODE " & _
"JOIN sysdba.USERINFO U ON U.USERID = T.USERID " & _
"WHERE TICKETID = '" & strTicketID & "' " & _
"ORDER BY ASSIGNEDDATE DESC"
objRS2.Open strSQL2, objConn, 3, 3
While Not objRS2.EOF
strTickets = strTickets & Trim(objRS2("USERNAME")) & "|||" & _
Trim(objRS2("TEXT")) & "|||" & _
Trim(objRS2("ASSIGNEDDATE"))
objRS2.MoveNext
Wend
objRS2.Close
strTickets = strTickets & vbCrLf
objRS.MoveNext
Wend
objRS.Close
objConn.Close
--------------------------------------[Code]-------------------------------------------
When I debug the script, the error points to:
objRS.Open strSQL, objConn, 3, 3
However, when I copy and past the query and run it from ADO Explorer, the query returns exactly as expected. I've flattened the query to make sure I didn't have any typos same error, same working result in ADO Explorer. Can anyone suggest what might be wrong with the SQL ? TIA!
Edit: Here is the flat SQL:
SELECT TOP 100
T.CREATEDATE, T.TICKETID, T.ALTERNATEKEYPREFIX, T.ALTERNATEKEYSUFFIX, T.SUBJECT, A.ACCOUNT, C.FIRSTNAME, C.LASTNAME, C.EMAIL, C.WORKPHONE
FROM sysdba.TICKET T
JOIN sysdba.SECCODE S ON S.SECCODEID = T.ASSIGNEDTOID
JOIN sysdba.ACCOUNT A ON A.ACCOUNTID = T.ACCOUNTID
JOIN sysdba.CONTACT C ON C.CONTACTID = T.CONTACTID
JOIN sysdba.PICKLIST P ON T.STATUSCODE = P.ITEMID
WHERE S.SECCODEDESC LIKE 'wilson, c%'
AND NOT P.TEXT = 'Engineering'
AND NOT P.TEXT = 'Closed'
AND NOT P.TEXT = 'Closed - No Customer Response'
ORDER BY TEXT, CREATEDATE |
|
|
| |
|
Re: Getting Insufficient or Invalid Join Data Error on SQL Query - VBS in Outlook
Posted: 17 Sep 09 6:04 PM
|
| JOINDATA table has plenty of stuff in it. i removed my JOIN to the SECCODE table and it runs fine so i guess i can't run queries against that info? thanx for the not on slx profiler. i had to do a little research and testing but at least i can see the executed sql statements in the background (i think). |
|
|
|
Re: Getting Insufficient or Invalid Join Data Error on SQL Query - VBS in Outlook
Posted: 10 Jan 10 11:54 PM
|
Hi,
I am getting the same error in profiler after applying my customisations to a staging database. It happens after I create a custom entity record and then try to navigate to the record using: Application.MainViews.AddEx "System:EFIC_CustomTable", 1, True, 1, strrecordid , "". The record is created but it doesn't navigate to the record.
It works fine on the development database. I have checked the join table and it looks fine. Any thoughts? Is it related to bug 1-62539 Insufficient or invalid join data on the fix list? We are on 7.2.1
Thx
Leon
update - Application.MainViews.Add works so I'm a bit confused - although at least I have a work around. |
|
|
|
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!
|
|
|
|
|
|
|
|