Saturday, November 15, 2008

Setting up SQL server 2008 (to host Dynamics NAV)

I installed SQL 2008 Developer edition because the SQL 2005 Express edition I had been using isn't able to be accessed from web applications running outside the VS IIS instance and I needed to test an IIS hosted web service application.

The install took nearly 2 hours!  Becuase I was installing SSIS, SSRS, and the other tools.  It required an upgrade to .Net 3.5 SP1, VS 2008 SP1, in addition to 2.5 gb of HD space.

After the install, SQL 2005 Express could no longer access the databases so I had a momentary freak-out until I could mount them into SQL 2008, at which point I uninstalled SQL 2005 Express.

Upon attempting to connect from Dynamics NAV, I was reminded of the SQL setup you have to do for Dynamics NAV.  I always seem to forget that, and I am not the only one as I have been onsite to implement behind another analyst a few times and found that the setup hadn't been completed.  Here are the SQL modifications required for Dynamics NAV:

You have to set a trace flag by adding "-T 4616" to the startup parameter string:

You have to add two extended stored procedures (xp_ndo_enumusersids and xp_ndo_enumusergrous) to the master database and grant execute permissions to the public role  as explained here:


As a side note, all clients from 4.0 SP3 and later are compatible with SQL 2008, so there is no reason to use SQL 2005 with new installs.



Wednesday, November 12, 2008

Inserting into SQL table with Identity field

Sometimes you want to insert records into a table that has an Identity field but you need to insert a value into that field (normally the system assigns identity values).

The following code allows you to do that:


SET IDENTITY_INSERT BasicAttribute ON

insert into BasicAttribute (rid, typeid, code, description) values (0,0,'DEFAULT','Default')

SET IDENTITY_INSERT BasicAttribute OFF

How to add a foreign key constraint in t-sql

The WITH NOCHECK clause is not recommended since future table updates could fail as the contstraint is re-validated, but I needed it to get it to work on some tables and will have to deal with the missing data later.



ALTER TABLE dbo.SKU_Price WITH NOCHECK ADD CONSTRAINT
FK_SKU_Price_PriceGroupID FOREIGN KEY
(
PriceGroupID
)
REFERENCES dbo.SKU
(
rid
)
ON UPDATE NO ACTION
ON DELETE NO ACTION

Thinking in Layers

When creating a web application, it is very important to think in Layers.  Your web application may only have 1 layer because it is so simple, but if you plan to have more than one developer, or permit web-service access to the applicaiton, or just plan on allowing the application to grow, you should have 3-5 layers:
  1. Client - Scripts that enhance the usability of the site and run on the client interface
  2. Presentation - Objects that display the data in HTML or in a Winform
  3. Communication - Objects that expose or consume services so the application can be spread across multiple physical or virtual servers.
  4. Business Logic - Objects that contain the working data and enforce the business rules.
  5. Data Access - Objects that interact with the database
  6. Data - The database server
The manditory layers for a web application are Presentation, and Data but if you neglect to include layers in between them you will probably regret it.  

If you have a single object that interacts with the web page, enforces the business rules and interacts with the database:
  • What are you going to do when you need to expose that information via a web service?
  • What will you do if you need to optimize for performance and cache the information in a session variable?
  • Or what if you need to add another web server to handle the volume of user demand?