Archive for EnterpriseOne – SQLs

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

Print Friendly

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>' 

Cool Data Selection When Purging Work Submitted Jobs

Print Friendly

Normally, we purge out all records from the Work Submitted Jobs screen that are at least 45 days old.

However, one of our clients requested to have all versions of R43510 purged after only one day.

While setting up the R9861101 data selection, I found that the Literal –> Range of Values method worked in a interesting way.  I was able to enter the following range on the F986110|RNDFUF2 (Foundation – Future Use 2):

R5543500_MRI0000_0000_PDF - R5543500_MRI9999_9999_PDF

That is about the most robust string/integer range I’ve seen.

Then, I thought, “How the heck is this happening?  Is it some kind of special JDE E1 processing that is parsing through the characters and integers?” 

Well, as some of you already know, SQL has a “BETWEEN” SQL operator

The SQL equivilant looks something like this:

select * from svm910.F986110<br>where JCFNDFUF2 between 'R5543500_MRI0000_0000_PDF' and 'R5543500_MRI9999_9999_PDF'

SQL To Get The Email Address From The Address Book Who’s Who For Each User Profile

Print Friendly

Below is the SQL used to get the emails address from the Address Book Who’s Who (F01151) for each user profile (F0092):

 SELECT A.ULUSER,A.ULAN8, B.EAEMAL  FROM JDE910.SY910.F0092 A, JDE_PRODUCTION.PRODDTA.F01151 B  WHERE ULUGRP<>'*GROUP' AND B.EAAN8 IN (SELECT ABAN8 FROM TESTDTA.F0101 WHERE ABAT1='E') AND A.ULAN8=B.EAAN8 ORDER BY ULAN8 

Use Oracle SQL Developer To Modify The JDE LOCAL Database

Print Friendly

During our upgrade process, we apply all of the ESUs to become “code current” before mirroring the upgraded code to the other pathcodes.

The process of doing this does not automatically update the Electronic Software Updates application to reflect that all of the ESUs are in all the pathcodes.

There a couple of different way to update the grid:

  1. Apply the ESUs to the other environments.  This can be a very long and tedious endeavor because there could be thousands of ESUs to apply.
  2. Update the JDE LOCAL database.

I have done option 1 in the past but found it kind of a pain and since I knew that I didn’t really want the process to really do anything except update the table, I chose to come up with a process to do option 2.

Here is what I did:

  1. Install and configure Oracle SQL Developer.  This is really the hardest part, but really not much of an issue.
  2. Create a connection to the JDESY910 database.  Remember, not only is JDESY910 the name of the database, but by default it is also the UserID and Password.
    image
  3. Run the following SQL
     INSERT INTO F9671 (SDPKGNAME,SDSUDET,SDSUDS,SDSUDATE, SDSUTIME,SDSUDFUT1,SDSUDFUT2,SDSUDFUT3,SDUSER,SDPID, SDUPMJ,SDUPMT,SDJOBN) SELECT SDPKGNAME,90,15,113284,111111,' ','DV910',0, 'JDE','SQL',113284,111111,'&lt;deployment name server&gt;' FROM F9671 WHERE SDSUDET=90 AND SDSUDFUT2='PD910' 
    • In this SQL, we upgraded PD910 and then moved the code to DV & PY.
    • We only need the “status 90” records because those are the ones that the grid uses.

Hope this helps.

Upgrade Gotcha: Media Objects

Print Friendly

I got a great reminder from a post today on Shannon’s JDE CNC Technical Blog titled “Media Objects Gotcha-upgrade / deployment server name”.

During an upgrade, switching deployment servers or changing the location of media objects, after moving all the files to the new location, you will need to change the internal pointers to the new location.  Run the following SQL on the F00165:

 UPDATE PRODDTA.F00165 SET GDGTFILENM = REPLACE(RTRIM(GDGTFILENM),
'<OLD_SERVER>','<NEW_SERVER>') 
WHERE GDGTFILENM LIKE '<OLD_SERVER>'

How To Compare Field Values Between Tables

Print Friendly

I used the following SQL to compare the difference between the F9200 (Data Dictionary) used for EnterpriseOne 9.1 and the one used for EnterpriseOne 8.12 that I copied to the SQL Server using R98403:

 SELECT a.FRDTAI,a.FRCDEC AS a_FRCDEC, b.FRCDEC AS b_FRCDEC  FROM JDE910.DD910.F9210 a  INNER JOIN DD812.PRODDTA.F9210 b ON  	(a.FRDTAI COLLATE DATABASE_DEFAULT = b.FRDTAI COLLATE DATABASE_DEFAULT)  WHERE a.FRCDEC COLLATE DATABASE_DEFAULT <> b.FRCDEC COLLATE DATABASE_DEFAULT  

SQL Server Error: Cannot resolve the collation conflict between "Latin1_General_CI_AS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

Print Friendly

I received the following error when trying to join an EnterpriseOne 9.1 table with an EnterpriseOne 8.12 table that was copied to the SQL Server from an iSeries using R98403:

Cannot resolve the collation conflict between “Latin1_General_CI_AS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation

The way I resolved the issue was to specify the field’s collation by placing “COLLATE DATABASE_DEFAULT” after the field name:

 SELECT a.FRDTAI,a.FRCDEC AS a_FRCDEC, b.FRCDEC AS b_FRCDEC FROM JDE910.DD910.F9210 a INNER JOIN DD812.PRODDTA.F9210 b ON 	(a.FRDTAI COLLATE DATABASE_DEFAULT = b.FRDTAI COLLATE DATABASE_DEFAULT) WHERE a.FRCDEC COLLATE DATABASE_DEFAULT <> b.FRCDEC COLLATE DATABASE_DEFAULT 

How To Change The Schema Of All Tables In A SQL Server Database

Print Friendly

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

Finding The Row Count For All Files In An iSeries Library And All Tables In An SQL Server Database

Print Friendly

I’ve been moving several EnterpriseOne databases to SQL Server lately.  In order to do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables.  Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:

iSeries – SQL query to get a row count on all files in an iSeries library:

SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS 
FROM QSYS2.SYSTABLESTAT 
WHERE TABLE_SCHEMA = 'PRODDTA' 
AND NUMBER_ROWS > 0 
ORDER BY NUMBER_ROWS DESC, TABLE_NAME DESC 

SQL Server – SQL query to get a row count on all tables in an SQL Server database:

  
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows 
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id 
AND i.indid < 2 
WHERE i.rows > 0
ORDER BY i.rows DESC, t.name DESC

NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.

Are there any methods that you use to do a quick data integrity check?

How To Drop All Tables In An SQL Server Database

Print Friendly

I’ve been doing a lot of work with SQL Server lately and have found the following SQL that can be used to drop (delete) all tables in an SQL Server database:

  EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  

Do you have any really handy SQLs?  Post them in the comments.