An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

IIS 7.5 displays this error for ASP.NET application.

Solution: for the application pool set Managed Pipeline Mode to Classic.

The 'VFPOLEDB.1' provider is not registered on the local machine

On 64-bit Win2008 Server, ASP.NET web page can display this error message even if Microsoft OLE DB Provider for Visual FoxPro 9.0 is properly downloaded and installed.

In my case the solution was simple: open Advanced Settings of the DefaultAppPool and set "Enabled 32-Bit Applications" to True. That of course assumes that ASP.NET application runs in the DefaultAppPool.

2010 WinForms.ReportViewer control requires at least SQL Server 2008

With ProcessingMode set to Remote, ReportViewer control requires SQL Server 2008 or later. Another reason to keep development and production tool versions close. Or using ReportViewer's Local mode.

Fetching inventory balance with a single T-SQL statement

The main challenge is the calculation of On Hand and On Order totals in each row: for that the data from the previous row must be somehow accessed. Certainly this task can be delegated to an application, but this is inelegant as much as not practical. Writing a table-valued function is another probable approach. Most likely that means opening and scanning a cursor, INSERTing data at each scan.

A solution exists solely within SQL Server query domain: use ROW_NUMBER() and CTE recursive queries. Here's the result. As you can see, the last two columns contain accurately calculated current balance for each row.

Implementation
On the first step the data from orders, receiving documents and shipping documents is collected and UNIONed ALL into csInventory1. This is simple enough and need no illustration.

On the second step, a column containing ROW_NUMBER() is added. This column is intended to be used for joining parts in subsequent CTE recursive query. In other words, it allows a row reach the data of the previous row. Here's the second step's query.

csInventory2 as (
select
   row_number() over (
      partition by product_nbr 
      order by document_dte, 
      document_tpe desc) as rowno,
   product_nbr, 
   document_dte, 
   document_tpe,
   document_nbr, 
   qty_ordered,
   qty_avail,
   qty_shipped
from csInventory1

The third step is a CTE recursive query. It's a bit long piece of code but worthy to be displayed here.

csInventory3 as (
-- anchor member definition
select
   i2.rowno,
   i2.product_nbr, 
   i2.document_dte, 
   i2.document_tpe,
   i2.document_nbr, 
   i2.qty_ordered,
   i2.qty_avail,
   i2.qty_hold,
   i2.qty_shipped,
   i2.location,
   cast(i2.qty_avail - 
      i2.qty_shipped as int) as on_hand,
   cast(i2.qty_ordered - 
      i2.qty_shipped as int) as on_order
from csInventory2 i2
where rowno=1 -- top row for each product
union all
-- recursive member definition
select
   i2.rowno,
   i2.product_nbr, 
   i2.document_dte, 
   i2.document_tpe,
   i2.document_nbr, 
   i2.qty_ordered,
   i2.qty_avail,
   i2.qty_hold,
   i2.qty_shipped,
   i2.location,
   on_hand + 
   cast(i2.qty_avail - i2.qty_shipped as int),
   on_order + 
   cast(i2.qty_ordered - i2.qty_shipped as int)
from csInventory2 i2
   inner join csInventory3 i3 on 
      i3.product_nbr = i2.product_nbr -- same product
      and i3.rowno = i2.rowno-1 -- link to the prev.row

And the final SELECT is simple.

select top 100 percent

   rowno,

   product_nbr, 

   document_dte, 

   document_tpe,

   document_nbr, 

   qty_ordered,

   qty_avail,

   qty_shipped,

   on_hand,

   on_order

from csInventory3

order by product_nbr, rowno

Configuring Database Mail (dbmail) on SQL Server Express 2008

Database Mail (aka DBMail) is an enterprise solution for sending e-mail messages from the Microsoft SQL Server Database Engine. It is easy configurable on a full scale MS SQL Server via Database Mail Configuration Wizard. None of SQL Express versions so far has this convenience, which means on SQLExpress the DBMail has to be configured manually.

All required information can be found on this page Enabling Database Mail on SQL Server Express. The most of it is applicable to either 2005 or 2008 SQLExpress version. For my own comfort (for sure I will need this on a later occasion) I wrote down all today's steps that lead to a successful DBMail configuration.

SQL Server Express instance configured today

• Product Version: 10.50.1600.1
• Product Level: RTM
• Edition: Express Edition with Advanced Services (64-bit)

DBMail Support Files
SQLExpress 2005 installation does not include DBMail support files:

• DatabaseMail.exe
• DatabaseMailengine.dll
• DatabaseMailprotocols.dll

Get these files from a regular SQL Server 2005 installation (not a Express one) and copy to MSSQL\Binn folder. No registration is required for the two DLL files.

SQL Server Express 2008 does contain DBMail support files. On my computer they are located in
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn

Starting DBMail
The following script turns the Database Mail option on.
USE master

GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure 
GO
sp_configure 'show advanced options', 0
GO

use msdb
exec dbo.sysmail_start_sp

Using Database Mail Configuration Wizard in SSMS
If you are not on Express Edition, this Wizard is a great help and convenience. Otherwise skip to the next topic.

I am surprised that neither the SSMS nor the wizard allow a profile to be scripted out.

Using SSMS template
Each SQL Server SSMS install comes with a variety of templates. "Simple Database Mail Configuration" template has all basics scripted nicely.

All you have to do is to fill in values for Profile, Account, SMTP Server, Email Address, and Display Name. Though note that this a simplified script. For example, as it is, it does not prompt where to put values for SMTP port and SSL.

Choosing email account
Before going any further, gather email account settings to be used. The following settings are absolutely required. Replace them with your values.

• Email Address: myaddress@mydomain.com
• Display Name: John Smith
• Mail Server: smtp.mydomain.com
• Port: 25 -- this is default SMTP port
• User Name: myaddress@mydomain.com
• Password: mypassword
• SSL enabled: Yes/No

Creating new profile
use msdb
-- creates new profile

exec msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DefaultMailProfile',  -- provide your own value
@description = 'Default email profile'  -- provide your own value
GO


-- sets new profile as the default one

exec msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DefaultMailProfile',  -- provide your own value
@principal_name = 'public', -- do not change
@is_default = 1 -- do not change
GO



Creating an account and adding it to the profile

use msdb

-- creates an account

exec msdb.dbo.sysmail_add_account_sp

@account_name = 'DefaultMail',

@description = 'My email account',

@email_address = 'myaddress@mydomain.com',

@display_name = 'John Smith',

@replyto_address = null,

@mailserver_name = 'smtp.mydomain.com',

@username = 'myaddress@mydomain.com',

@password = 'mypassword',

@port = 25,

@enable_ssl = True

GO

-- adds the account to the default profile
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DefaultMailProfile',
@account_name = 'DefaultMail',
@sequence_number = 1
GO

Sending a test email

exec msdb.dbo.sp_send_dbmail 
@recipients='myfriend@myfrienddomain.com',
@subject = 'Test email sent via DBMail',
@body = 'This is a test email. Please do not reply.',
@reply_to = 'myaddress@mydomain.com'
GO

Verifying that the test email left the server

The last script normally returns message "Mail queued". There is no indication of a mail actually leaving the database server. Query msdb.dbo.sysmail_log and msdb.dbo.sysmail_mailitems tables to obtain complete results.

First of all, there should be recently created sysmail_log records indicating that "The mail queue was started...", and "DatabaseMail process is started".

A failed attempt to send an email creates sysmail_log entry with description "The mail could not be sent to the recipients because of...". Read the description and take appropriate steps. May be validate again email settings: SMTP server, port, user name and password.

DBMail tables and views
Database MSDB contains several DBMail tables. Note that these are all System Tables. The above configuration scripts make changes in DBMail tables. But of course a carefully calculated manual change is also an option.

Tables:

• sysmail_mailitems
• sysmail_log
• sysmail_configuration
• sysmail_servertype
• sysmail_profile
• sysmail_principalprofile
• sysmail_account
• sysmail_server
• sysmail_profileaccount
• sysmail_attachments
• sysmail_attachments_transfer
• sysmail_query_transfer
• sysmail_send_retries

Views

• sysmail_allitems
• sysmail_eventlog
• sysmail_faileditems