Enterprise Software Development

Webservice fix for error message – Maximum request length exceeded

So I ran into a weird error this morning on one of my webservices. It was a SOAP exception which yielded a 500 HTTP Status error (as viewable from the trace.axd)

“There was an exception running the extensions specified in the config file. –> Maximum request length exceeded.”

The WebService call was posting a DataSet to a WebMethod. I figured out that the DataSet that I was posting was larger than 4MB (4096 KB) which happens to be the default maximum request length for posting to ASP.NET web applications.

You can change this to fix the problem. It is important to note the reason there is a limit in the first place … this setting is there to help protect against DOS attacks. So take precaution when changing this on internet web applications

To increase the default posting limit for your web application. Just add / change the httpRuntime section inside of the system.web element.

   . . . 

This changes it from 4 MB to 8 MB (8192 KB)

Utilizing SQL 2005 Linked Server to connect to external systems via OLE DB

You can utilize MS SQL Server 2005′s Linked Servers ability to connect to any OLE DB provider. Here we are connecting to an iSeries (AS/400) DB2 using the IBMDA400 ole db provider

Here is the command to add a linked server:

EXEC master.dbo.sp_addlinkedserver
  @server = N'MYSERVER',
  @srvproduct=N'IBMDA400',
  @provider=N'IBMDA400',
  @datasrc=N'MYSERVER',
  @provstr=N'Transport Product=Client Access;
    Force Translate=0;
    DATA SOURCE=MYSERVER;
    USER ID=MYUSERID;
    PASSWORD=MYPASSWORD;
    Connect Timeout=30;'

You’ll notice the Force Translate is set to 0, this is to translate the CSSID 65535 characters. For some reason on my system a 0 makes this work… I have seen other people use a value of 1 or 37 for this.

Querying data on a Linked Server

I had to use the OpenQuery statement to access the data on my linked server. Although I have seen other syntax that would suggest that you can access the linked server with out this. I was unable to make that syntax work with my instance.

Here is an example query I was able to execute against my JD Edwards library:

SELECT * FROM OpenQuery(JDEPROD, 'select * from F0911 where gldgj > 107001')

Why is this SO COOL!?

Not only can you select data from a linked system, but you can use most of the extra select parameters with this, like the INTO clause, or the TOP clause.

I was able to use this to transfer data from my JD Edwards EnterpriseOne production library with a command like this:

SELECT *
 INTO #glData
 FROM OpenQuery('select * from f0911 where glfy = 7')

This is just another option you have for integrating, reporting, or data warehousing. Think of composite reporting with your financial system, your project management system, your time keeper system, your HR system, your <insert anything here> system!

Drop me a note if any of you have used this, your likes / dislikes about this method.

Publishing a .NET ClickOnce Application to a linux Apache Server

You might think that you can only publish a ClickOnce application to a IIS server. This is in fact not the case, you CAN publish a ClickOnce application to a apache web server either running on Linux or Windows. In fact its pretty simple.

Configure your apache publish directory for ClickOnce applications

At the root directory of where you are publishing your ClickOnce application, create / edit a .htaccess file. (click here for the apache documentation on AddType) (if you still need help with apache I suggest the following book Apache: The Definitive Guide (3rd Edition) )
Add the following lines to it:

AddType application/x-ms-application application
AddType application/x-ms-manifest manifest
AddType application/octet-stream deploy

For some reason I also needed the following line in my .htaccess file

AddType application/x-msdownload dll

For more reading on ClickOnce check out Smart Client Deployment with ClickOnce(TM): Deploying Windows Forms Applications with ClickOnce(TM) (Microsoft .NET Development Series).

Use FTP when publishing from Visual Studio, Unless you want to install FrontPage Server Extensions on your Apache, Yuk!

Using a ClickOnce application with FireFox

Currently ClickOnce applications will only work with Internet Explorer, you CAN however use FireFox, but you will need to install the FFClickOnce extension.

Creating a Period Date Range Table, with SQL!

You need a way to lump dated records into periodic chunks, like say for a timesheet system. Say your company’s payroll periods are bi-weekly (that’s every other week). So you need a way to distinguish the dated payroll data into two week timesheets.

Sure you could spend three days figuring out a fancy algorithm, only to find out 6 months into your project that there is just this one teeny-tiny instance where they manually change the dates around, and your algorithm doesn’t work.

Well my friend what you need is a table that you can look up against. How are you going to create the data for that table? Very simple, especially with my example of a bi-weekly pay period schedule. We’ll use a SQL Query to perform the insert for us.

Here is what the table will look like for us when we are done:

Here is the SQL Script that I used to generate the data for the period table (bi-weekly):

declare @i int, @ws datetime, @we datetime

select @ws = '2006-09-25', @i = -2

while @i < 2000
begin
	select @i = @i + 2
	select @we = dateadd(day, -1, dateadd(week, @i+2, @ws))
	insert into PayPeriod (PeriodStart, PeriodEnd) values (dateadd(week, @i, @ws), @we)
	print cast(dateadd(week, @i, @ws) as varchar) + ' | ' + cast(@we as varchar)
end

Methodology used here

• We use a sentinel start date that we know 9-25-2006
• Then in a loop:
  • we increment a counter by 2
  • the start date is sentinel date + counter (in weeks with the date add function)
  • the finish date is sentinel date + counter + 2 weeks , then subtract 1 day to get the date just prior to the start of the next period

There you go, now you have a table with a unique id given to a date range of start date and end date. So now if you want to know what period a particular record is in, you can just join to that table in this way:

select employee, dateWorked, PayPeriod.id as payPeriod
 from Timesheet, PayPeriod
where
 Timesheet.dateWorked between PayPeriod.PeriodStart and PayPeriod.PeriodEnd

Notice there is no JOIN statement used, that means we need to specify the join on criteria in the WHERE clause. We do it this way in order to utilize the BETWEEN function (inclusive of the start date and end date).

There is always a simple solution; some times it takes a little longer than other times to work it out. Coffee always seems to help me, Oh! and google.

For more SQL recipies and help you should check out the SQL Pocket Guide it covers Oracle, Microsoft SQL Server, MySql, IBM DB2, and PostreSQL.

Getting the First and Last day of the month with .NET C#

Ok so now you know how to get the last day of the month with SQL, but how can you get the first day or the last day of the month given a specific date? That problem is so simple with dot net and csharp! There is a convenience constructor for the DateTime object that can help us with it. This will even work with the computers local date time, or localizations.

Here is the DateTime constructor signature that we are going to use:

public DateTime(
   int year,
   int month,
   int day
);

First day of the month

Here we take the date, and using the DateTime constructor (year, month, day). We can create a new DateTime object with the first day of the month.

Here is the simple wrapper method to get the first day of the month:

public DateTime FirstDayOfMonthFromDateTime(DateTime dateTime)
{
   return new DateTime(dateTime.Year, dateTime.Month, 1);
}

Last day of the month

For the last day of the month we do basically the same thing as the first day of the month, except after we have that value we add 1 month, then subtract 1 day. Walla! We have the last day of the month with c#!

Here is the simple wrapper method to get the last day of the month:

public DateTime LastDayOfMonthFromDateTime(DateTime dateTime)
{
   DateTime firstDayOfTheMonth = new DateTime(dateTime.Year, dateTime.Month, 1);
   return firstDayOfTheMonth.AddMonths(1).AddDays(-1);
}

How to reset a DNN password at the Database

If your DotNetNuke passwords all of a sudden stop working (like due to a failed upgrade), have no fear you can reset them at the database! This is not a hack, it uses the official aspnet Password Reset.

create procedure uap_ResetPassword
  @UserName NVarChar(255),
  @NewPassword NVarChar(255)
as
begin
	Declare @PasswordSalt NVarChar(128)
	Declare @Application NVarChar(255)

	Set @Application = (SELECT [ApplicationID] FROM aspnet_Users WHERE UserName=@UserName)
	Set @PasswordSalt = (SELECT PasswordSalt FROM aspnet_Membership WHERE UserID IN (SELECT UserID FROM aspnet_Users WHERE UserName=@UserName))

	Exec dbo.aspnet_Membership_ResetPassword @Application, @UserName, @NewPassword, 10, 10, @PasswordSalt, -5
end

C# method to mimic PHP file_get_contents

Here is a nifty little function that I wrote to mimic the PHP function file_get_contents. This C# method will read the string contents of a regular file, or a file from a URL (the response from a http request, not the actual file itself).

Notice you have to convert the byte array results to ASCII using the System.Text assembly.

note:
You might be able to read the actual file from the webserver if you replace the DownloadData method on the webclient object to DownloadFile, but this is purely speculation. This kind of action would be useful if you wanted the actual script file not the results from it.

/// <summary>

/// Will return the string contents of a

/// regular file or the contents of a

/// response from a URL

/// </summary>

/// <param name="fileName">The filename or URL</param>

/// <returns></returns>

protected string file_get_contents(string fileName) 

{ 

 string sContents = string.Empty; 

 if (fileName.ToLower().IndexOf("http:") > -1) 

 { // URL 

 System.Net.WebClient wc = new System.Net.WebClient(); 

 byte[] response = wc.DownloadData(fileName); 

 sContents = System.Text.Encoding.ASCII.GetString(response); 

 } else { 

 // Regular Filename 

 System.IO.StreamReader sr = new System.IO.StreamReader(fileName); 

 sContents = sr.ReadToEnd(); 

 sr.Close(); 

 } 

 return sContents;

}

HOWTO Get the last day of the month with SQL

Have you ever needed to find out what the last day of the month was with SQL. Lets say you needed to round up a date to the last day of the month… I found this necessary when writing a HR Benefits extract… They needed the benefits end date to be the last day of the month, so there was no lapse in medical coverage.

This will get you the actual day number

SELECT DAY(DATEADD(DAY,-1, DATEADD(MONTH,1, DATEADD(DAY,1-DAY(@d),@d)))) AS 'Last day of the month'

If you want the full date just remove the outer DAY function

see http://www.extremeexperts.com/SQL/Tips/DateTrick.aspx for more ways to do it

Quest Collaborate 2006

Quest collaborate 2006 just finished up here in Nashville Tennessee. Some of the highlights:

• Support for EnterpriseOne XE until 2013
• The new tool sets for EnterpriseOne include a web services interface
• EnterpriseOne 8.12 is now available
• The first version of Fusion will ship in 2008

side note: The GL Company has a cool tool called, oddly enough, GL Inquiry… this tool is huge for accounting, job cost, and procurement. They are coming out with a payroll module at some point as well. As a programmer, this product is simple, really simple… in fact with a good grid control, one could easily write a simular inquiry tool in just a matter of weeks… (hmmm…. i might be able to squeeze it into a few weekends ) … so on that note, I have a hard time swallowing the high price tag on this product.

Querying Data From JDE World or Co-Existance OneWorld XE

Here is a short list of resources and some short examples of how to query or extract data from JD Edwards World or OneWorld XE (ExterpriseOne), this also applies to most databases on an iSeries AS/400.

First a warning. Whenever you have direct SQL access to any database you have the power in one accidental enter key press when you ment to press a backspace, and bam! you’ve wiped out your production address book. And whats more older versions of DB2 do NOT incorporate SQL transactions… so you cant rely on the old ‘begin tran / rollback tran’ safety method. you can however use the block comment method to keep your risky updates/deletes away from the sql interpreter (/* */) until your ready for them.

Additionally, JDE in my experiance is very picky about its data, so think twice before you decide to insert or update, and be prepared to restore your database (or at least that table)…

Ok so heres what you need to get on a AS/400 (iSeries) DB2 database.

• A user account with ODBC access to the AS/400
• A good knowledge of the SQL command language
• IBM Client Access installed on your Windows PC (make sure the ODBC and OleDB drivers are installed)
• if you are not on a windows pc you’ll need to find another driver for accessing your iSeries
• A Query Tool
• DB2 SQL Reference Manual, and a DB2 SQL Programming Manual
• Usually you want to turn on the ‘Translate CSSID’ option in your SQL Connection string or your ODBC connnection profile.
  Otherwise youll see what looks like garbage for strings, actually this is the database returning the data back in byte arrays.

Do you know what you want to query from JDE, and want to know what the SQL statement is?

Just post your question here as a comment… I will be more than happy to answer what I can.