How To List The Column Names Of A Table On SQL Server

We all know that most database tables in EnterpriseOne have a large number of columns. In fact, they usually push the limits of the RDBMS.

Well sometimes, you need a list of those columns for things like INSERT statements.

Below is how you can get the column names from a table in a SQL Server database:

USE JDE_PY910
SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'F98950')

You can use either type V (views) or U (tables).

Check Windows Service Status And Restart If Necessary

Lately we have been noticing an issue where the Print Spooler service stops for some reason and users are unable to print UBE results through EnterpriseOne.  So far, we have been unable to narrow down a cause for the Spooler service to stop.

So, like any good CNC Admin, I put a band-aid on it.  I found a bat file script that I could use to check the status of the service and then restart the service it it wasn’t running. I then setup a scheduled task to run every 5 minutes.

 
ECHO OFF
for /F "tokens=3 delims=: " %%H in ('sc query "Spooler" ^| findstr "        STATE"') do (
ECHO STATE = %%H  
if /I "%%H" NEQ "RUNNING" (
   REM Put your code you want to execute here
   REM For example, the following line
   net start "Spooler"
   ECHO Spooler has been restarted
  )
)

Do you have any band-aids that you use to mitigate issues?

Unable To Check In OMW Object Or Promote OMW Project

Last week we had a developer that kept getting the following error when trying to check-in an object in OMW.  We also found that this error can happen when trying to promote a project:

saveGBRDataToConsolidated() - It could not be determined 
if the GBRSPEC record, e7426989-11ad-44f3-8697-7e7e89b4fbdd 
/ 1, exists in the repository (type = 2). Cannot insert. 

According to the Oracle support doc this error is caused by:

Corruption exists in the spec record(s) for the object related to the spec key shown in the jde.log.This could be related to an install, uninstall and then attempted reinstall of an ESU resulting in the “Insert to table F98741 failed” errors on the R98700 PDF.

There are 2 solutions given for this issue:

  1. To resolve this issue, back up the F98741 table first.  Then use SQL to remove the 198 record for EVSK:5ef15a1b-e064-11d1-9ac9-00600832fe19 from the F98741 table. Use Update Uninstall to remove all references to the ESU if it has not been applied to other environments.  See Note Document 654262.1 E1: ESU: Remove Software Updates (Update Uninstall vs. Update Clean-up) – for more details on removing ESUs.

    Delete the executable for the ESU.  Then download the ESU again.  Run the executable from the new download and apply the ESU through Software Updates application. This resolved the issue.

  2. To resolve this issue, check for the related records (F98740, F98741) for the GBRSPEC related to the spec key shown in the jde.log. Back up the table(s) where the spec records exist and delete the records to that spec key (in this case, a69a4b2e-1435-40e1-9df1-deeb8e88a605).  Reinstall the ESU to reapply the related object.

Our issue had nothing really to do with an ESU, so we opted to try solution #2.

It didn’t work… so it seemed.

We had to go through the following exercise 8+ times in order to get a successful check-in:

  1. Delete the record
     
    DELETE FROM DV910.F98740 WHERE ELEVSK='e7426989-11ad-44f3-8697-7e7e89b4fbdd'  
    DELETE FROM DV910.F98741 WHERE ESEVSK='e7426989-11ad-44f3-8697-7e7e89b4fbdd' 
    

  2. Try the check-in again
  3. Get a new GUID from the JDE.LOG

It’s not the most elegant solution, but hey, this is CNC!

Safely Delete Files From WINSXS

We started to run out of disk space on one of our production Windows Enterprise servers.  This usually isn’t a huge deal with virtual machines, however this was on the c drive.  Yes, I know those can be expanded too, however I wanted to do a little investigating before I just started dumping disk space to it.

I found that there were over 12 GB taken up by the c:\windows\winsxs directory.  I did some checking around the internet and found a blog post (Clean up Winsxs on Windows 2008 R2 after SP1 install) that provided me with a way to cleanup the WINSXS directory in a way that was supported by Microsoft:

DISM.exe /online /Cleanup-Image /spsuperseded

Now, it didn’t totally clear out the directory, but it did reduced it down to only 5 GB.

How To Find The Physical Size Of SQL Server Tables And Indexes

Inspired by Shannon’s post (give me the size of data and indexes for a file in an oracle database please), I found a SQL statement that finds the size of each table and index in a SQL Server database. I also found that there are a ton of different ways to get this information. Below is the method that I is most beneficial to me:

 
EXEC sp_spaceused
GO

DECLARE @spaceUsed TABLE (
    name varchar(255), 
    rows int, 
    reserved varchar(50), 
    data varchar(50), 
    index_size varchar(50), 
    unused varchar(50))

INSERT INTO @spaceUsed
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT name, rows, left(reserved,len(reserved)-3) as [reserved (KB)], 
	left(data,len(data)-3) as [data (KB)], 
	left(index_size,len(index_size)-3) as [index_size (KB)], 
	left(unused,len(unused)-3) as [unused (KB)]
FROM @spaceUsed
order by rows desc

E1 Batch Approval Post Confusion

There is one very simple EnterpriseOne process that confuses me every stinking time I’m asked to configure it.  It’s setting up Batch Approval Post authority. 

My first frustration is that I think that this is NOT an IT function.  The business process owners know who should be able to approve who’s batches and it doesn’t have anything to do with the E1 security system. 

Ok, I’m done with that.  Now, on to the second frustration… What the heck is with the terminology used in this application?  To me, this is the consequences of early-adopter offshore development.  “Approved by Users”?  How about “Approvers”?  Wouldn’t that make way more sense?  Oh, and what about “Secured Users”?  Aren’t those just “Users” or “Batch Creators”?  Either way, I’ve never been able to setup this authorization correctly without looking at my notes that are 10+ years old.

So, here they are:

Create a new approver:

  1. Fastpath to P00241 (Batch Approval/Post Security Constants)
  2. Select the Form Exit “Approved by Users”
  3. Click the Add button: Add Button
  4. Type the approver’s UserID in the Approved by Users field
  5. Type the batch creator’s UserID in the Secured User grid
    E1 Batch Approval Post

Add a batch creator to the list that an approver can approve:

  1. Fastpath to P00241 (Batch Approval/Post Security Constants)
  2. Select the Form Exit “Approved by Users”
  3. Use the QBE to search for the approver
  4. Select the approver
  5. Add users to the “Secured User” grid

Hopefully, by putting it on here I won’t have to refer to my other set of notes.  At least for this issue.

Do you have anything in E1 that you just can’t get straight? 

I might look at Inclusive/Exclusive Row Security next.  What do you think should be my next post subject?

Oracle Killed JDEREF.COM

jderef.com (JDE Reference) was a great site used as a JDE EnterpriseOne table structure reference.  Unfortunately, the owner of the site received a notice from Oracle’s legal counsel asking him to shutdown the site.

Below is from the site:

Sorry, we’ve been closed down

It is with a heavy heart that I must inform you that jderef.com can no longer exist, having recently been served legal notice from Oracle regarding information copyright infringements relating to their product schemas. This is a bitter blow, not only for me personally, (having invested many hours creating this shared resource) but mostly for the users like you, who have started to depend on the presence of this resource, to use and enhance JDE and it’s satellite products – to further your knowledge and insight into Oracles flagship ERP products.

Personally, I’m of the opinion that this is a rather narrow sighted view adopted by Oracles Product Team given that;

  1. it’s supporting their product
  2. anyone with access to JDE will technically be able to obtain this information anyway, albeit in a far less convenient and expedient fashion
  3. any competitors will have long since already obtained this information

But as a private individual based in the UK – I have no choice but to concede – I don’t fancy going up against charges from Oracle’s legal teams. It was only a matter of time before my little website got the attention of Oracle – but this was not the response I was hoping for.

If you spread the word, make a little noise towards the “Legal Director/Managing Counsel (Law Practice) at Oracle Corporation” (Google it) – they may see how valuable this resource was for their users

Regards, the late jderef.com

Attn: Copyright Agent,  jderef.com

Pursuant to 17 USC 512(c)(3)(A), this communication serves as a statement that:
1.I am the duly authorized representative of Oracle Corporation and its affiliates ("Oracle"), the exclusive rights holder for the JD Edwards software including its schemas;
2.These exclusive rights are being violated by material available upon your site at jderef.com;
3.I have a good faith belief that the use of this material in such a fashion is not authorized by the copyright holder, the copyright holder's agent, or the law;
4.Under penalty of perjury in a United States court of law, I state that the information contained in this notification is accurate, and that I am authorized to act on the behalf of the exclusive rights holder for the material in question;
5.I may be contacted by the following methods:

T*** D***
Managing Counsel
Oracle
500 Oracle Parkway
Redwood Shores, CA 94065
6**-5**-5***
t***.d***@oracle.com

I hereby request that you remove or disable access to this material as it appears on your service in as expedient a fashion as possible. Thank you.

How To Redeploy A Package To A Development Client (Fat Client)

I needed to redeploy a full package and all the update packages associated with the pathcode.  So, I used the SQL below to do the job:

 update sy900.f98825 set upinpkst=20 where upmkey='<DEV CLIENT NAME>' and uppathcd='<PATHCODE>' and upjdepkgnm <> '<PACKAGE NAME>' 

Quicker Cross Reference

The biggest pain in the butt about the cross reference facility is the time that it takes to populate the tables when running the R980011.  Jeff Stevenson has a great article on his blog, Partial Cross Reference Rebuild in EnterpriseOne, that explains how to run the R980011 with data selection to select only the objects that have been modified since a certain date.

The steps below will drastically shorten the runtime of the R980011:

  1. Copy the “All Objects” version of R980011
  2. Add a condition for BC Date – Update (F9861) (UPMJ) is greater than the date you last ran the rebuild

This will cause the R980011 to check the UPMJ field in Object Librarian and only process those records with a date more recent than the date specified.

Allow Firefox & Chrome To Access Restricted Ports

When separating WebLogic E1 JAS instances we usually end up using odd port numbers.  Sometimes, we use ports that have been put on a restricted list by both Firefox and Chrome. 

Below is a list of the ports that are blocked and the service that is the reason for it being blocked:

1 – tcpmux 7 – echo 9 – discard 11 – systat
13 – daytime 15 – netstat 17 – qotd 19 – chargen
20 – ftp data 21 – ftp control 22 – ssh 23 – telnet
25 – smtp 37 – time 42 – name 43 – nicname
53 – domain 77 – priv-rjs 79 – finger 87 – ttylink
95 – supdup 101 – hostriame 102 – iso-tsap 103 – gppitnp
104 – acr-nema 109 – POP2 110 – POP3 111 – sunrpc
113 – auth 115 – sftp 117 – uucp-path 119 – NNTP
123 – NTP 135 – loc-srv / epmap 139 – netbios 143 – IMAP2
179 – BGP 389 – LDAP 465 – SMTP+SSL 512 – print / exec
513 – login 514 – shell 515 – printer 526 – tempo
530 – courier 531 – chat 532 – netnews 540 – uucp
556 – remotefs 563 – NNTP+SSL 587 – submission 601 – syslog
636 – LDAP+SSL 993 – IMAP+SSL 995 – POP3+SSL 2049 – nfs
4045 – lockd 6000 – X11    

For more detail about this you can visit Mozilla’s website.

We ended up using ports 81-89.  As you can see in the table above, port 87 is listed because of a service called “ttylink”.  Below are the steps that you can take to “whitelist” any port you want.  However, I would recommend not using the list of restricted ports.  It is much easier than going through these steps with all of your users or maintaining a Windows Group PolicyWindows Group Policy.

Firefox:

  1. Type the following URL into Firefox: about:config
  2. Create a string setting called: network.security.ports.banned.override
  3. Give your new setting a value of “87”.  You can also include a comma separated list, a range or a combination of both:  87, 150-300, 350, 400, 450-500

Chrome:

  1. Modify your shortcut to Chrome by changing the “Target” field to look something like:
    “C:\Program Files (x86)\Google\Chrome\Application\chrome.exe”
    –explicitly-allowed-ports=87