E1 Tips

Below is the SQL that can be used to change the database schema of all tables in an SQL Server Database.  We use it when restoring databases from Production to CRP or Development.

USE JDE_CRP_NEW  --TODO: Change this to DB Name

SET NOCOUNT ON
DECLARE @NewSchema nvarchar(256)
DECLARE @OldSchema nvarchar(256)

Set @NewSchema = 'CRPDTA';  --TODO: Change to New Schema 
Set @OldSchema = 'PRODDTA'; --TODO: Change to Old Schema 

--Don’t need to change anything below this
DECLARE @TableName nvarchar(256)
DECLARE TableName CURSOR FOR
SELECT  DISTINCT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @OldSchema

OPEN TableName

FETCH TableName INTO @TableName

WHILE @@Fetch_Status = 0

   BEGIN

   Print 'Modifying ' + @TableName 
   EXEC('ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName)

   FETCH TableName INTO @TableName
   END

CLOSE TableName

DEALLOCATE TableName

EnterpriseOne Multi-Foundation is a great thing.  However, I always seem to get caught building a package for the wrong Tools Release because I forgot to check the serviceNameListen & serviceNameConnect settings in the JDE.INI.

So, since I’ve been doing a lot of Windows PowerShell scripting while wearing my other geeky hat (SharePoint-Geek.com), I put together a PowerShell script to help me out.

You will need to specify the port that you use for your production tools release and the full filename of the image that the script with create. Then, save the file with the .ps1 extension and put a shortcut to it in the All Users startup folder(%ProgramData%\Microsoft\Windows\Start Menu\Programs\Startup).

NOTE: Please make sure that you have PowerShell installed on your server and permissions to write to the image location that you specify.

Add-Type -AssemblyName System.Drawing

$prodPort = '6001' #*** specify the port that you use for your production tools release
$imgFileName = "c:\TRBG.jpg" #*** specify the full filename of the image that you would like the script to create and set to your desktop

#*** DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING ***

$d = get-date
$usingProd = 0
$bgColor = "Green"
$txtTR = "PRODUCTION"

function Get-Port() {
	$IniFile='c:\windows\jde.ini'
	$InputFile = [System.IO.File]::OpenText("$IniFile")

	$config = @{}

	Get-Content $IniFile | foreach {
		if ($_.IndexOf('=') -gt 0) {
			$line = $_.split("=")
			$config.($line[0]) = $line[1]
		}
	}
	$snl = $config.Get_Item("serviceNameListen")
	$snc = $config.Get_Item("serviceNameConnect")
	if ($snl.CompareTo($snc) -eq 0) { # a value of '0' means that they are equal
		return $snl
	} else {
		write-host "serviceNameListen ($snl) and serviceNameConnect ($snc) are NOT equal!" -foregroundcolor 'red'
	}
	$InputFile.close
}

$portUsed = Get-Port

if ($portUsed -eq $prodPort) {
	#using prod
} else {
	#using non-prod
	$bgColor = "Red"
	$txtTR = "NON-PRODUCTION"
}


$bmp = new-object System.Drawing.Bitmap 600,600 
$font = new-object System.Drawing.Font Consolas,60
$brushBg = [System.Drawing.Brushes]::$bgColor 
$brushFg = [System.Drawing.Brushes]::White 
$graphics = [System.Drawing.Graphics]::FromImage($bmp) 
$graphics.FillRectangle($brushBg,0,0,$bmp.Width,$bmp.Height) 
$graphics.DrawString('E1 IS',$font,$brushFg,185,25) 
$graphics.DrawString('USING THE',$font,$brushFg,97,99) 
$brushFg = [System.Drawing.Brushes]::Yellow 
$graphics.DrawString($txtTR,$font,$brushFg,75,246) 
$graphics.DrawString("PORT: $portUsed",$font,$brushFg,75,325) 
$brushFg = [System.Drawing.Brushes]::White 
$graphics.DrawString('TOOLS RELEASE',$font,$brushFg,2,488) 
$graphics.Dispose() 
#$bmp.Save($imgFileName) 
$bmp.Save($imgFileName, [System.Drawing.Imaging.ImageFormat]::Jpeg)

Function Get-WallPaper() {
	$wp=Get-ItemProperty -path 'HKCU:\Control Panel\Desktop\' -name wallpaper
	if(!$wp.WallPaper) { 
		"Wall paper is not set"
	} Else {
		$wp.WallPaper
	}
}

Function Set-WallPaper([string]$value="", [string]$format) {
    switch ($format) {
        "tiled" {$tile=1;$style=0}
        "centered" {$tile=0;$style=0}
        "fit" {$tile=0;$style=2}
        default {$tile=0;$style=0}
    }        
	Set-ItemProperty -path 'HKCU:\Control Panel\Desktop\' -name TileWallpaper -value $tile
	Set-ItemProperty -path 'HKCU:\Control Panel\Desktop\' -name WallpaperStyle -value $style

	#Set-ItemProperty -path 'HKCU:\Control Panel\Desktop\' -name Wallpaper -value $value
	#*** The following replaces the above line because the change is immediately reflected
	#*** on the user's desktop
	#*** the following block of code must be as-is with no indent
$signature = @'
[DllImport("user32.dll")]
public static extern uint SystemParametersInfo(
    uint uiAction,
    uint uiParam,
    string pvParam,
    uint fWinIni);
'@
	#*** end the no indent code
	$type = Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace SystemParametersInfo -PassThru

	$null = $type::SystemParametersInfo(20, 0, $value, 3)
	$rundll32="{0}\System32\RUNDLL32.EXE user32.dll, UpdatePerUserSystemParameters" -f  $env:windir
    Invoke-Expression -Command $rundll32
}

Set-Wallpaper $imgFileName

Today, I came across a Windows Service on a server that I inherited. By the name of the service I could tell that it was built in-house and I even found enough information to determine who the developed it.  However, since it was not in the Add / Remove Programs list of the Control Panel, I wasn’t sure how to remove it.  So, I found the following on Geeks With Blogs – Shahed’s Blog:

Recently, I was trying to delete a windows service. Normally it should not be necessary to manually delete a service. Uninstalling an application should remove its associated service (if any).

However, I installed some beta products and a service created by one of the applications was not removed automatically. Its very easy to remove a service from registry if you know the right path. Here is how I did that:

1. Run Regedit or Regedt32

2. Find the registry entry "HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services"

3. Look for the service there and delete it. You can look at the keys to know what files the service was using and delete them as well (if necessary).

alternatively, you can also use command prompt and delete a service using following command:

sc delete < SERVICE name>
or to create, simply type
sc create <SERVICE name>

Update:
If you have space in the file path you need to use quotation marks ("). For example: 
sc create "MySQL" binpath= "C:Archivos de programaMySQLMySQL Server 5.1binmysqld.exe"

I used the “sc delete” method to remove the service.

Thanks, Shahed!

I ran across this while working on a different project but thought that it might be handy.

SMTP relay behavior in Windows 2000, Windows XP, and Exchange Server

The basic test:

Start a command prompt.

1. Type telnet ServerName 25, where ServerName is the SMTP server name or IP address and 25 is the port number, and then press ENTER.
2. Type EHLO, and then press [ENTER].
3. At the Telnet session prompt, type RSET. The telnet session responds with text that is similar to the following:
   250 2.0.0 Resetting

4. Type MAIL FROM:UserName@DomainName.tld, where UserName is the name of the user, DomainName is the name of the domain, and tld is the top level domain such as .com or .net.
   The telnet session responds with text that is similar to the following:

   250 2.1.0 UserName@DomainName.tld….Sender OK

5. Type RCPT TO:RecipientName@DomainName.tld, where RecipientName is the e-mail address of the recipient.
   The telnet session responds with text that is similar to the following:

   550 5.7.1 Unable to relay for RecipientName@ DomainName.tld

We had a user submit an issue last week that was a little baffling.

User: “When I try to access an application in EnterpriseOne all I get is the processing image.”

Us: “Does the ‘processing’ ever go away?”

User: “No.  It just sits there.”

Us: “um, ok. Let us do some checking and we’ll get back to you.”

Our PC support staff was able to remote into the PC and found that there was an “element not found” Javascript error.

The error was fixed by modifying the following registry entry:

HKEY_CLASSES_ROOTTypeLib{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}1.1win32

Replace the current value on the default setting from:

"C:WINDOWSsystem32shdocvw.dll"

to

"C:WINDOWSsystem32ieframe.dll"

We didn’t have to reboot for this setting to take effect, but you may have to.

We have several work areas that require a longer inactive timeout setting than the default 20 minutes (Payroll Time-entry, Fixed Asset Accounting, etc.).

Unfortunately, EnterpriseOne’s architecture does not make the process of configuring a session timeout very easy.  There are three separate separate settings that need to configured correctly.  Depending on your architecture that could mean three separate machines using three separate platforms.  Whatever your architecture looks like there are at least three.

1. The Java Application Server (JAS).  No matter what flavor of JAS you are running (IBM Websphere, Oracle Application Server, BEA, etc.) you will need to configure the application’s user session timeout setting.  The screenshots for IBM Websphere 6.0.2.13 are below:

    
   

2. The EnterpriseOne HTML Server.  This setting is in the JAS.INI under the [CACHE] stanza.  The setting is in milliseconds (3600000 = 1 hour).  If you are running the Oracle Server Manager for EnterpriseOne, you can find it in the “Cache Configuration section under the “Web Runtime” configuration.
    
   
3. The HTTP server.  The instructions for both Microsoft IIS and IBM HTTP Server follows:
   • Microsoft IIS:
     • Display the properties of the website that is configured to host EnterpriseOne
     • On the “Website” tab, modify the “Connection Timeout” setting.  Please note that this value is in seconds, not milliseconds.
   • IBM HTTP Server:
     • Edit the “Timeout” setting in the HTTPD.CONF file.  Please note that this value is in seconds, not milliseconds.

NOTE:  To avoid record locking Oracle specifies to configure the system so that the JAS server will “timeout” the user’s session.  To do that both the EnterpriseOne HTML Server and the HTTP server need to be set to great times than the JAS server.  For example:

• To configure a timeout value of 30 minutes:
  • JAS Server: 30
  • EnterpriseOne HTML Server: 3600000
  • HTTP Server: 3600

While installing EnterpriseOne 8.97 on one of our instances, we ran across an issue that was pretty puzzling to both us and Oracle’s Global Support Services group.

We had successfully installed both the Server Manager Console and the Enterprise Server (System i, iSeries, AS/400) Management Agent.  When everything is working correctly, the Enterprise Server Management Agent is supposed to ping the Server Manager Console and magically show up in the Server Manager Console Dashboard.

Well, as you have probably guessed, our Enterprise Server Management Agent did not "magically" appear.  After sending Oracle several sets of logs and screen shots, I noticed something in the Server Manager Console logs:

Oct 27, 2008 12:33:23 PM com.jdedwards.mgmt.agent.Server$ClientConnection
 register  FINER: Received heartbeat from the remote management agent on 
DOMAIN.COM listening on port -1 of type 2 in managed home
 /JDEdwards/JDE_HOME

You’ll notice from the log excerpt above that the ping from the Enterprise Server Agent was successful, but the name of the device from which it is running is identified as "DOMAIN.COM".  Obviously, I changed the name to protect the guilty, but I thought it was really strange to not have a MachineName specified.

So, we inspected the host table of the Enterprise Server using CFGTCP and found that there really was an entry with just "DOMAIN.COM".  However, there were also 2 other entries for its IP address:

255.255.255.111     MACHINENAME
                    DOMAIN.COM
                    MACHINENAME.DOMAIN.COM

We weren’t really sure what we might screw-up if we removed the "DOMAIN.COM" entry so we just moved the "MACHINENAME.DOMAIN.COM" entry up in the list.

255.255.255.111     MACHINENAME
                    MACHINENAME.DOMAIN.COM
                    DOMAIN.COM

Before we even had a chance to logout of the Server Manager Console, the Enterprise Server magically appeared on the dashboard.

What should you learn from this?  Clean up your host table!

Have you tried to install Oracle Server Manager?  If so, how did it go?  If not, what are you waiting for?

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Jeff Stevenson has a great little tutorial on making the Services management console a lot more tolerable.

Quick breakdown:

1. right-click C:Windowssystem32services.msc
2. select ‘Author’
3. make changes as desired
4. close

Thanks for the great tip Jeff!

By the way, since Jeff is a CNC, I added a link to his site at the right.

While trying to do testing of the 8.97 Tools Release, we end up with having both the 8.96 Tools Release and the 8.97 Tools Release on our Deployment Server.  This is required so that we can continue to support package builds of the 8.96 environments. 

When switching from one to the other, I usually just rename the 3 important directories (OneWorld Client Install, SYSTEM & SYSTEMCOMP) by appending the Tools Release version to the end (i.e. SYSTEM -> SYSTEM_897). This can get a little tedious. 

So, I through together a quick BAT file to take care of it for me.

@ECHO OFF
IF EXIST "E:PeopleSoftE811OneWorld Client Install_897Install.inf"   GOTO :Activate897
IF NOT EXIST "E:PeopleSoftE811OneWorld Client Install_897Install.inf"   GOTO :Activate896

:Activate896
ECHO Activating 896
PAUSE
MOVE "E:PeopleSoftE811OneWorld Client Install" "E:PeopleSoftE811OneWorld Client Install_897"
MOVE "E:PeopleSoftE811OneWorld Client Install_896" "E:PeopleSoftE811OneWorld Client Install"
MOVE "E:PeopleSoftE811SYSTEM" "E:PeopleSoftE811SYSTEM_897"
MOVE "E:PeopleSoftE811SYSTEM_896" "E:PeopleSoftE811SYSTEM"
MOVE "E:PeopleSoftE811SYSTEMCOMP" "E:PeopleSoftE811SYSTEMCOMP_897"
MOVE "E:PeopleSoftE811SYSTEMCOMP_896" "E:PeopleSoftE811SYSTEMCOMP"
SET WHATDONE=Activated 896
GOTO :END

:Activate897
ECHO Activating 897
PAUSE
MOVE "E:PeopleSoftE811OneWorld Client Install" "E:PeopleSoftE811OneWorld Client Install_896"
MOVE "E:PeopleSoftE811OneWorld Client Install_897" "E:PeopleSoftE811OneWorld Client Install"
MOVE "E:PeopleSoftE811SYSTEM" "E:PeopleSoftE811SYSTEM_896"
MOVE "E:PeopleSoftE811SYSTEM_897" "E:PeopleSoftE811SYSTEM"
MOVE "E:PeopleSoftE811SYSTEMCOMP" "E:PeopleSoftE811SYSTEMCOMP_896"
MOVE "E:PeopleSoftE811SYSTEMCOMP_897" "E:PeopleSoftE811SYSTEMCOMP"
SET WHATDONE=Activated 897
GOTO :END

:END
ECHO %WHATDONE%
pause

Yes, I realize that I will need to do some replacing for the next Tools Release.

UPDATE: Don’t forget about changing the JDE.INI file to set the correct ports:

[JDENET]
serviceNameListen=
serviceNameConnect=

I added lines similar to the following:

REN "C:WindowsJDE.INI" "C:WindowsJDE_897.INI"
REN "C:WindowsJDE_896.INI" "C:WindowsJDE.INI"

via SearchWinIT.com

As of September 1, Microsoft will allow customers to move 41 server applications between servers and server farms without having to reassign licenses.

This move will make it much easier to get to the truely "dynamic data center"