Job Plans:

Does the view use any query attributes in a default or mandatory criteria? If so, perhaps this is hiding data from the user because it evaluates differently than in the admin client. Also, how are you connecting to the database? If the 'User DB Connection' setting is checked, then perhaps the user does not have permission to view the data in question.

Work Order LD:

What is the value of the ${LDTable} attribute in this case? It looks like one of the two parameters p_table or p_keyvalue is getting truncated, which doesn't make much sense. How are the fields defined in the underlying database?

Whoops - ignore the comments about the Job Plans view. I didn't notice the version you are using, which doesn't support the features I was talking about.

Can you give me some more details about what is happening on the client? Does the view show up, but simply not contain any records? Are there any error messages?

Jeff just sent me copies of the views, so I'll see if I can recreate the problem.

We've discovered another problem, this time with the LABOR view. If the work order status is DEFER (a status code added fairly recently and not used much yet), we can't save a new labor record. The parameter values all look OK to me. If we change to a different status it works fine.

Job Plans are working fine now, but Work Order LD is not.

-------------------------------------------
For the LABOR view, the error message is -
Error inserting record: Before procedure 0 failed
View Name: LABOR Source File: DBProcedure.cpp Source Line: 265 Procedure: proc_dsinsertlabor SQL Statement: {call proc_dsinsertlabor(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} Details: Error executing statement State 1: 37000 Native Error 1: 50000 ODBC Message 1:Invalid WONUM. 0000115475 must have a valid status.
-------------------------------------------
This excerpt from PROC_DSINSERTLABOR is the only place that error message is generated -
-- validate wonum
select
@wo_eqnum = eqnum,
@wo_glaccount = glaccount,
@wo_location = location,
@wo_woeq10 = woeq10,
@wo_woeq11 = woeq11,
@wo_woeq12 = woeq12
from workorder
where wonum = upper(@p_wonum) and
status not in (select value from valuelist
where maxvalue in ('COMP', 'CAN', 'CLOSE'))
if @@ERROR != 0
begin
raiserror('Invalid WONUM. %s must have a valid status.', 16, 1, @p_wonum)
rollback transaction
return
end
-------------------------------------------
Here's the row in the valuelist table -
listname value maxvalue valdesc defaults type rowstamp
WOSTATUS DEFER WAPPR Deferred N 4

Now I see what the LABOR problem is. The procedure I quoted was the one on the CD, but the procedure in use had the where clause changed to:
"where wonum = upper(@p_wonum) and status not in (select value from valuelist where maxvalue in ('WAPPR', 'CAN', 'CLOSE'))"

That would definately explain it. Are you still having problems with the long description view?

Ok, so I looked at the func_ds_setldkey code and have a few ideas. As far as I can tell, the only statement that could modify anything, and thus generate the truncate error, is:

-- update the workorder record with the new ldkey
update workorder set ldkey = @p_ldkey, 
    changedate = getdate(),
    changeby = upper(SYSTEM_USER)
where wonum = upper(@p_keyvalue)

Furthermore, the changeby field is the only text field that might be causing the error. This raises a couple of questions:

• Do we know the value of SYSTEM_USER at this point?
• What is the column definition of changeby in the workorder table?
• Does commenting out the line that updates changeby cause the error to go away?

Hopefully you can just modify the changeby field to be wide enough to accept the user name.

The column definition for changeby is 18, and if I comment out that line the error goes away. The following also works and I’m leaving it this way for now:

changeby = upper(rtrim(substring((SYSTEM_USER), 1, 18 )))

(If I don't put a space between the 18 and the closing parenthesis above, the 8 ) gets converted to an untended smiley: )

SYSTEM_USER is “NT AUTHORITY\SYSTEM”, but I wish it wasn’t. When I was modifying the @p_user parameter for other procedures, if I picked something from the drop-down and the user didn’t enter anything in that field, DataSplice put “NT AUTHORITY\SYSTEM” in that field. On the old server it would have been “MAXIMO”. I assume this is because the ODBC data source on the old server used SQL Server authentication and the new server uses Windows authentication.

With all the copious notes I wrote about setting things up on the new server, I didn’t document why I used the different authentication method. Do you have any recommendations for authentication method? I’m thinking of changing that line to read, “changeby = ‘DATASPLICE’” since that’s more meaningful than either of the SYSTEM_USER values.

Maybe the best solution is to not hardcode the value at all and instead add another parameter to the function. This would allow you to control the value passed in with the admin client.

Then you could set the value to 'DataSplice', or even better yet ${DS_USER}, which will be the name of the user logged in with DataSplice.

Sounds like a good idea. Am I correct in assuming that if I add the parameter to the stored procedure it will appear in DS Admin Client after I click Update?