How to do a Costpoint platform change

Today, I am doing a Platform change. So I thought I'd share with you the approach we take on doing these projects.
1. Source is Oracle, Target is Microsoft SQL Server

2. Source is Microsoft SQL Server, Target is Oracle.

Case  1: Oracle to Microsoft SQL Server

1. Get a Copy of the MSS database that is similar in version and patch level as your source.
2. Compare the table, column and column positions of the Oracle and MSS database.
3. Create the columns that are missing in the MSS database only. (If there are more columns in the source those are usually Nullable).
4. Create a Linked Server in Microsoft SQL Server using MSORA OLEDB.
5. Truncate all the tables in MSS.(Do not drop them)
6. Create any custom table you wish.
7. Back up you blank database. It will save you so much time next time around.
8. Disable all the triggers.
9. Insert all the data via linked server.
10. Copy over those table with Long/text data types via DTS
11. Enable all the triggers.
12. Do a row count in Oracle and MSS.
13. Make sure they match up and you are done.

Case  2: Microsoft SQL Server to Oracle

1. The only difference is that you will be using a ODBC + HS link to connect to the MSS Server.
2. Also I wrote a stored procedure to copy over long and text fields in oracle.

The reason why people switch.

1. People go to Oracle because SQL server database is usually bigger. This has to do with the way MSS Costpoint database deal with a few of the datatypes. I.e. char instead of varchar
2. People go with SQL Server because it costs less. If you have a 6gb database, does it really matter if you save 2gb?

I will add more detail to each step in the coming weeks.

How to get pass security at Chicago faster.

If you are flying out if Chicago on United, do the mobile check in. They let you use the executive line. They don't have a reader at the regular security lines.

Deltek Web Service with Pervasive Process Design

Here is my Pervasive Process Design and each item's settings

A quick Deltek Web Service testing tool

Basically I needed a way to call the Deltek Web Service Integration Console for Genl_lab_cat INSERT/UPDATE. You can use SOAPUI and it will work just fine. I am using VB because I need to turn this into a full fledged program which can be used to run all interfaces we will have. Also a Web service is nothing more than a XML being posted via HTTP request. In the old HTTP days, these are the same as a form being posted to the next asp/html/jsp page. The only difference is that instead of form elements, you have an entire XML document. Hence my use of a HttpWebRequest and a HttpWebResponse to handle all the communications.

So I created this simple VB Form Project. The form object has 2 items on it. The first item is a button(Button1) that you click to start the web service request. The second item is a muti-line text box(TextBox1) to display the results.

In the code I used a custom written function to read in the file and return the result as a string.

strMyFilter = readXML("C:\GLCLOAD.XML")

You can do

strMyFilter = "ALL YOU XML HERE IF YOU WISH"

Finally The ouput result in the text box is seen below

You are only interested in the java:Severity tags. A 0 like the above means you record was processed successfully. There are other return codes the system can throw so check the docs/manual for that.

My Code:

Imports System.Web
Imports System.Net
Imports System.IO
Imports System.Text






Public Class Form1



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim request As HttpWebRequest
Dim response As HttpWebResponse = Nothing
Dim reader As StreamReader
Dim address As Uri
Dim byteData() As Byte
Dim postStream As Stream = Nothing
Dim strMyFilter As String


address = New Uri("http://192.168.174.6:7009/webservices/GlctestWS")
' Create the web request
request = DirectCast(WebRequest.Create(address), HttpWebRequest)

' Set type to POST
request.Method = "POST"
request.ContentType = "text/xml; charset=""UTF-8"""

'read the XML in as a string. The XML I posted on in the last post
strMyFilter = readXML("C:\GLCLOAD.XML")

' Create a byte array of the data we want to send
byteData = UTF8Encoding.UTF8.GetBytes(strMyFilter.ToString())

' Set the content length in the request headers
request.ContentLength = byteData.Length

' Write data
Try
postStream = request.GetRequestStream()
postStream.Write(byteData, 0, byteData.Length)
Finally
If Not postStream Is Nothing Then postStream.Close()
End Try


Try
' Get response
response = DirectCast(request.GetResponse(), HttpWebResponse)

' Get the response stream into a reader
reader = New StreamReader(response.GetResponseStream())

' Text application output
TextBox1.Text = reader.ReadToEnd()
Finally
If Not response Is Nothing Then response.Close()
End Try


End Sub

Deltek Costpoint Web Service Integration SOAP Message

Here are some images of the SOAP message. Use this if you want to craft your own web service calls.

First one is of a GLC insert

Next is of PLC query with condition where PLC code like '%JR%'.

This is assuming that of course you already build and deploy your Integration Module.

If you need this in a text file, please email me.