Slick trick for doing a Choose() in C#

Back in my VB days, I frequently found the Choose() function handy.
I frequently used it for converting integer values to boolean for checkboxes and such.

Dim i as integer
i=1
Checkbox1.checked=Choose(i,false,true)

Nice and clean.

However, in C# I have been relegated to doing this:

int i=1;
if(i==1)
{
Checkbox1.checked=true;
}
else
{
Checkbox1.checked=false;
}

Much messier and longer.  If I have a dozen checkbox fields, this becomes excessively long.

So today I finally had enough and searched for a better way until I found this trick.  I just declare an array of boolean values on the fly and access the one I want.

int i=1;
Checkbox1.checked=new[] {false,true }[i];

There we go!  Back to a 1 liner that is easy to read.

Shrinking a database

Most companies running Dynamics GP are using the Full backup model.  This means that they are backing up the log files more frequently than the database.

When on this model, it should not be necessary to shrink the data or log file very often.

However, there are instances where you would want to do this, such as immediately after archiving (removing) a large amount of data from the database.

Here are the commands to get the files shrunk back down. You should do this at a time when no users or other processes are accessing the database.

--1. Create a full backup of the database.  Since this will involve changing the backup model, we will be breaking any log chains that exist.

--2. Set the database to single user mode.  This will prevent anything from connecting to the database while we are working on it

ALTER DATABASE [YourDbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

--3. Set the recovery model to simple.  This will allow us to quickly shrink the files.

ALTER DATABASE [YourDbName] SET RECOVERY SIMPLE;

GO

--4. Shrink the data and log files.  The number here represents 10% free space after shrinking.  This can take a while to run.

DBCC SHRINKDATABASE ([YourDbName], 10);

GO

--5. Shrinking trashes the indexes, so now we reorganize them.  This can take a very long time to run.

USE [YourDbName]

GO

DECLARE @TableName VARCHAR(255),

@sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE;'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

--6. Set recovery model back to full

ALTER DATABASE [YourDbName] SET RECOVERY FULL;

GO

--7. Set Database back to multiuser mode

ALTER DATABASE [YourDbName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

--8. Remember to take a new full backup after you are done so that the full backup model can start a new log chain.

SmartConnect can't see my custom proc

I have repeatedly been frustrated when trying to install custom nodes in eOne SmartConnect.
There is little documentation on the web and this has been a pain for several versions, so I have decided to maintain this post to list ways in which we have gotten it to work.

First, lets detail the steps installing a node in smartconnnect (assuming whe stored proc is already installed.  Let's assume the stored proc name is MyEconnectProc.)
1. Launch Smartconnect as an administrator
2. Click the Maintenance Tab and then choose Node Maintenance.

Note:  If you do not see a list of nodes in the window you need to stop here and follow the SmartConect installation documentation to install the default GP nodes before proceeding.  Make sure you restart SQL server after this is done, and then restart SmartConnect.

3. Navigate to the point in the tree where you want your node to appear.  I want this one to appear under Payables > Vendors, so I will right click on that node and choose Add New Node.

4. In the window that pops up, I need to enter the name of my proc in the Technical Name.  It is important that it be capitalized the same as the proc in the database.  Click tab.  The Technical Name should gray out.  If you get an error, see troubleshooting steps below.

5. Enter the Display Name

6. Check the parameters that should be required.

7. Click Save.

You can now create maps and direct SmartConnect to call this proc as an eConnect proc.



So here is the scenario when it goes wrong:

1. I create a custom eConnect proc.  For the sake of this example I am NOT using node builder or any other tool.  I am just writing a sql proc and is econnect compliant.

2. I install this proc on a Dynamics GP company database.

3. I launch SmartConnect as an admin.  Go to node maintenance to add the node. For seemingly no reason, SmartConnect frequently decides that the proc does not exist.


Things to check before pulling out the old sledgehammer and reinstalling SmartConnect:

1. Make sure the proc is eConnect compliant.  Spelling on the output parameter names is important.
@O_iErrorState INT=0 OUTPUT,
@oErrString VARCHAR(255)='' OUTPUT

2. Try installing the custom node on all company databases and the DYNAMICS database before adding the node.  SmartConnect doesn't give us a way to tell it WHERE the proc is, so this works sometimes to get the node added.  You can then drop the proc from the databases where you don't need it.

3. Make sure the proc does not have any funky characters in the name (like - (dash)).  SmartConnect does not seem to like that.

4. Make sure there is a Pre and Post proc present, even if you are not using them.

5. Make sure all parameters start with @I_v except the 2 output parameters at the end.

6. Try restarting SQL server if possible, then restarting SmartConnect


If all else fails, you may have to use SQL Stored Procedure as that destination instead of eConnect Proc.

Why is my database application running so slowly?

There are many reasons that a database application can be slow.  In large ERP systems like Dynamics GP, there are often complex operations happening behind the scenes when batches are being generated or posted.  All too often, we throw more hardware at a performance problem., when the issue could be as simple as a poor join or missing index.

So how do we figure out what to do about a performance problem?

First, I would make sure the SQL server is set up in a manner that follows best practices.  Here are some links to posts on setup:
Why you should not store user data on a sql server.
SQL Server setup and maintenance tips
Dream SQL Install
Creating a good set of SQL maintenance plans

If SQL is installed in a virtual environment, start here:
Keys to SQL on VMWare
Essential Guide to SQL Server Virtualization

Let's say I have done all that and I still have performance issues with posting a large batch.  The users normally use the application on a terminal server, so I am not sure if this is a network, terminal server, or application issue.

If you have a good tool, such as LiteSpeed, turn it on and post the batch.  In other words, use your tool to figure out why it is slow and make changes to fix it.  If you still have issues, or if you only have the tools that install with SQL server, here is what I would do:
1. Get a base line
- Restore the production database to a test database on the same server
- Post the batch from the terminal server while running a SQL Profiler trace
   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set)
2. To see if terminal server is the issue, run the process from a fat client while running a trace

   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set
3. To rule out network issues, run the process from the SQL server while running a trace

    * Time the process

   * Save the trace
- Restore prod to test (so we keep using same data set

At this point you will know whether it is a network issue, terminal server issue, or SQL issue, based on the difference in run times.
. 

If it is a SQL issue:

- Use trace results to identify the SQL objects involved and manually generate a list of those objects.

   * Tables: Check row count and explore options for purging or archiving records on large tables. Check index fragmentation.

   * Views: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.

   * Stored Procedures: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.  Try switching between #temp and @temp variables where used.  Look for opportunities to reduce the size of local or temp table record sets early in the processing.  Eliminate cursors where possible.

After all that, now it is time to look deeper in SQL.  
Check the performance counters for PLE and RAM in use to help determine if more RAM is needed. A good rule of thumb is that if PLE regularly falls below (75*(GB of RAM)), more RAM is needed.

Check the performance counters for disk reads and writes per second to help determine if a faster hard drive is needed.

A

 This should get you started.  I will add to this post as I find tricks that help.

Why don't any images display on my ASPX page?

Over the years, I have had more than one occasion to wonder what planet the developers of IIS are from.  It seems that every version of IIS has had at least one default setting that made no sense at all... probably not even on their planet.

Today installed an aspx application on a shiny new Windows 2008 R2 / IIS 7.5 environment.

When I ran the application, all the image tags were broken.  I verified that the images were in the correct file location and that IUSR had read access to that folder.  So why would NONE of the images display?

From the people who brought you Windows ME:
The answer is another poor decision by some IIS developer.  By default, the static content role is not installed.  Installing it fixes the issue.



I hereby issue a challenge to anyone who wishes to take it.  Go try to find a single web site running on IIS that does not have at least one image AND does not use CSS.  You will fail, because there isn't one... anywhere... I bet you would have trouble faking one.  The whole point of a website is to render content, not just text.

So I am left to ponder the extreme depth of this obtuseness.... and dream of visiting this other planet.

Did my trigger fire from and update, insert, or delete?

I recently got a question on a trigger and I could see that the coder was trying to handle two operations in a single trigger and perform separate actions based on the operation.

Now, we all know that we could create a separate trigger for each operation.  However, it is sometimes helpful for maintaining code if we use a single trigger.


Here is a simple example of how to handle the different operations in a single trigger.

create table test(
myfield varchar(1)
)
go

create  TRIGGER cstr_test
ON test
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
if exists(select 1 from deleted)
begin
if exists(select 1 from inserted)
begin
print 'update'
end
else
begin
print 'delete'
end
end
else
begin
print 'insert'

end
END
go


insert into test select 1

update test set myfield=2 where myfield=1

delete from test



I hope this helps someone!

Add items to list object in Dex using VBA

Let's say you have a drop down list box on your Dynamics GP window and you want to add an item to it.  Unfortunately, the list items are being set by dexterity code when the window opens, so this seems impossible.

However, using a little VBA, we can add our own items to the list.

We do this using SanScript!

Here is an example of how to accomplish this:

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

 Dim CompilerApp As Object

    Dim CompilerMessage As String

    Dim CompilerError As Integer

    Dim CompilerCommand As String

    ' Create link without having reference marked

    Set CompilerApp = CreateObject("Dynamics.Application")

    CompilerCommand = ""

    CompilerCommand = CompilerCommand & "add item str(2099) to 'Year' of window MyWindow of form MyForm;"

    ' Execute SanScript

    CompilerApp.CurrentProductID = 131

    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"

    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)

    If CompilerError <> 0 Then

        MsgBox CompilerMessage

    End If

End Sub