Category: How To | Page 18

How to Use SQL Server Express to Store ASP.NET Session State

ASP.NET Session State SQL Server Mode provides a good way to have a persistent session, especially for server farm configuration. Here are the steps to quickly configure the environment to do so using SQL Server Express edition (because sometimes, you don’t need the full-blown version of SQL Server just to track sessions).

Steps

Download and install SQL Server Express.
Open the file, select install and follow the instructions.
On the Instance Configuration screen, you can change the Named instance if you want to, but for this example, it’s left with the default SQLExpress.
On the Server Configuration screen, make sure SQL Server Browser is set to Automatic so you can connect from a remote machine.
Keep moving along the screens and wait until the installation process completed. Make sure all components are successfully installed and click on Close button.

Create ASPState Database in SQL Server

In your DB server, open the Command Prompt (Admin) and type in the following command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql -ssadd -d [DATABASENAME] -S [SERVERNAME]\SqlExpress -sstype c -E

If you use a custom database name (instead of the default ASPState by using option -sstype p), then at the end of the process, you’ll see the following message:

To use this custom session state database in your web application, please specifiy it in the configuration file by using the 'allowCustomSqlDatabase' and 'sqlConnectionString' attributes in the <system.web>\<sessionState> section.

So you have to change your default connectionString from:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;User Id=MySession;Password=MySessionPassword" />

To:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;Initial Catalog=[DATABASENAME];User Id=MySession;Password=MySessionPassword" allowCustomSqlDatabase="true" />

If you can’t still see the difference, there are two additions on the sessionState element. The first one is the addition of Initial Catalog=[DATABASENAME] under sqlConnectionString and the second one is at the end of markup, allowCustomSqlDatabase=”true”.

How to Fix Invalid SendUsing Configuration using CDO

When you use CDOsys to send email from a classic ASP, you might see an error message below and if you look at the line error, it’s always on the message.Send:

The page cannot be displayed

There is a problem with the page you are trying to reach and it cannot be displayed.

Please try the following:

Contact the Web site administrator to let them know that this error has occurred for this URL address.

HTTP 500.100 - Internal server error: ASP error.

Internet Information Services

Technical Information (for support personnel)

Error Type:

CDO.Message.1 '80040220'

The "SendUsing" configuration value is invalid.

/path/filename.asp, line 100

Browser Type:
Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.71 Safari/537.36

Page:
POST 279 bytes to /path/filename.asp
POST Data:
category=...

Time:
Monday, October 26, 2015, 10:12:55 PM

More information:

Click on Microsoft Support for a links to articles about this error.

Go to Microsoft Product Support Services and perform a title search for the words HTTP and 500.

Open IIS Help, which is accessible in IIS Manager (inetmgr), and search for topics titled Web Site Administration, and About Custom Error Messages.

In the IIS Software Development Kit (SDK) or at the MSDN Online Library, search for topics titled Debugging ASP Scripts, Debugging Components, and Debugging ISAPI Extensions and Filters.

Several posts would suggest that you put the SMTP server in the code. But when you already have an SMTP server setup on IIS 6.0, here’s a couple things you might want to check as well:

Check if the Application Pool is at least running under version 2.0.
Give user MACHINE\IIS_IUSRS modify permission on C:\inetpub\mailroot directory (or if you moved the SMTP folders somewhere else, give that permission to the new directory instead).

Also check the links under Further Reading below for the common solutions pertaining to this error.

How to Show Field Values as Columns in SQL Server

One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.

Without PIVOT

USE [MyDB];

--WITHOUT PIVOT
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type]
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type]
ORDER BY CostCenter;

With PIVOT

USE [MyDB];

--WITH PIVOT
SELECT CostCenter, Desktop, Laptop FROM (
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type] 
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type] 
) t1
PIVOT
(SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2
ORDER BY CostCenter;

How to Fix Permission Denied Error 800a0046

If you still use CDONTS object in your ASP page and run into this kind of error:

Microsoft VBScript runtime error '800a0046'

Permission denied

/path/filename.asp, line 4

Then when you open the file, you see something like this.

Set oCDOMail = Server.CreateObject("CDONTS.NewMail")

oCDOMail.Importance = 1
oCDOMail.Send

Just by reading the error message, you can probably guess that this error occurs usually because the anonymous user account is not granted a Modify permission to the mailroot folder and it can be easily resolved by following these steps:

Open Windows Explorer and locate the mailroot folder. The default should be C:\inetpub\mailroot, but this may be moved by your system administrator.
Right-click the mailroot folder, then click Properties.
On the Security tab, click on the Edit button.
Click Add.
Add IUSR_ and IWAM_ (separated by a semi-colon). Make sure the location is your desktop, not your domain name. Click Check Names, then OK.
Give Modify permission for each of the account added previously. Click OK.
Click OK to close the dialog box.

This time your asp page should render without any issues.

As in our exprience, there will be time when you upgrade a very old system, when you try to grant these permissions, you’ll see some garbled error message and it still doesn’t resolve the problem. When all else fails, and it may be easier and faster to replace the code using CDO (cdosys.dll).

How to Replace CDONTS with CDOSYS on Classic ASP Pages

Replacing CDONTS with CDOSYS, while can be time consuming for a large classic ASP application, is actually very easy to do. Here’s a simple example of what need to be changed at a minimum:

Set oCDOMail = Server.CreateObject(“CDONTS.NewMail”) to:
Set oCDOMail = Server.CreateObject(“CDO.Message”)
Remove BodyFormat property, oCDOMail.BodyFormat = 0 since it’s unnecessary.
Replace MailFormat property oCDOMail.MailFormat = 0 with MimeFormatted property oCDOMail.MimeFormatted = True.
You may also want to add Charset property.
oCDOMail.BodyPart.Charset = “utf-8”
Replace oCDOMail.Body with oCDOMail.HTMLBody.
Replace Importance property oCDOMail.Importance = 1 with oCDOMail.Fields(“urn:schemas:mailheader:priority”).Value = 1

The typical page composition to send email using either object can be seen below:

CDONTS

<%
  Dim oCDOMail
               
  Set oCDOMail = Server.CreateObject("CDONTS.NewMail")
               
  oCDOMail.From = ...
  oCDOMail.To = ...

  oCDOMail.Subject = "CDONTS email"
  oCDOMail.BodyFormat = 0
  oCDOMail.MailFormat = 0
  oCDOMail.Importance = 1
               
  oCDOMail.Body = "<html><body><p>This is a test.</p></body></html>"
               
  oCDOMail.Send
               
  Set oCDOMail = Nothing
%>

CDO

<%
  Dim oCDOMail
               
  Set oCDOMail = Server.CreateObject("CDO.Message")
               
  oCDOMail.From = ...
  oCDOMail.To = ...
  oCDOMail.Subject = "CDO email"
  oCDOMail.BodyPart.Charset = "utf-8"
  oCDOMail.MimeFormatted = True
  oCDOMail.Fields("urn:schemas:mailheader:priority").Value = 1
               
  oCDOMail.HTMLBody = "<html><body><p>This is a test.</p></body></html>"
               
  oCDOMail.Send
               
  Set oCDOMail = Nothing
%>

And here’s the two pages compared side-by-side:

Since CDONTS was deprecated since Windows 2000, ideally, all applications still using it should be rewritten to use the newer CDO object. However, it’s very common to see many enterprises prefer to make CDONTS work on the newer Windows Server to save time and money from re-writing their legacy applications.

« Previous Page

Steps

Create ASPState Database in SQL Server

Further Reading

Further Reading

Without PIVOT

With PIVOT

Further Reading

CDONTS

CDO

Recent Posts

Tags