Sathish's world of technology

White Paper – Commerce Server Profiles

New white paper on integrating commerce server profiles with external data stores has been published.

http://www.microsoft.com/commerceserver/en/us/white-papers.aspx

Direct Link - http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ce5629ad-2473-4c25-8f89-91c8530cd764

Commerce Server Order Schema Manager- Tool to manage OrderObjectMapping.xml

Introduction

Order schema manager is a custom tool to extend Commerce Server Order Schema. Commerce Server order schema is represented through an xml file name OrderObjectMapping.xml. Extending the schema is accomplished by modifying the xml file manually. I thought of automating the process and end up developing the schema manager tool. I use this for extending order schema and found it very useful.

How does the tool work?

Schema manager works on the schema file and allows the developer to extend the default schema. It also allows the developer to compile the schema file to generate the database script file. It internally uses the OrderMapping.exe to compile the schema file. The tool works on some initialization parameters to compile the schema. Initialization parameters are stored in the application configuration file and the same can be modified by the developers in the compiler page. Following are the details about the initialization parameters and its usage.

• OrderMapping.exe
• Dependent assemblies.
• Web.config
• OrderObjectmapping.xml

Supported Features

Managing table definitions

• Creating new tables
• Creating new columns
• Managing Foreign and Primary key constraints
• Adding marshaled data column

Managing Classes /Types

• Creating a new class.
• Changing an existing class to a new one and updating the related information.
• Managing Collection relationships.

Managing Table-Class Mapping

• Create new mapping
• Delete existing mapping

Note

Commerce Server order schema is represented in a tree view and all the operations are initiated through context menus. Right clicking on the nodes will show appropriate menus. Context menus are activated only through mouse clicks and not through key board shortcuts. Current version of the tool doesn't provide an option to manage OrderPipelineMapping.xml (part of order schema).

Happing coding J

Commerce Server 2007 Ramp up

I will be writing Hand's on Labs to enable developers to ramp up in Commerce Server 2007. The main objective of the HOL is to give step by step instructions to build a basic e-commerce site. Stay tuned 

Importing Commerce server Profiles – Deployment utility

Introduction

Deploying commerce server profiles is a repetitive task / requirement in many commerce server engagements. Commerce server profile system is extensible and supports various data bases as data stores. Every engagement takes this advantage and extends the profiles based on the project requirement. Question about profile deployment is very common in all the commerce server engagements with some differing profile implementations. Some projects use SQL server as data source and some of them use the combination of SQL & AD. In all these cases, deploying commerce server profiles still stands as a common requirement. While thinking about this, I got the idea of developing a generic deployment tool which can be used across engagements with the support of extensibility to deal with project specifics. Common solutions across implementations  , good old concept of Design Patterns strikes my mind. Very powerful and difficult concept to convince people….. Let's target the problem with design patterns…

Problem statement

Commerce server represents profiles in xml format and provides options to export and import profiles of commerce site. In the development environment we use Commerce Server manager to import or export profiles. Automating this will be great value add in the development life cycle. The tool will also play a very vital role in defining the automated build and deploy process for engagements. Commerce server runtime provides api's to import and export profiles programmatically. The code is very simple and doesn't take much time to implement specific to an engagement. The main idea of this tool is not to deal with the project specifics; instead it should be generalized tool which can be adopted in any commerce server engagements with the provision of extensibility. Let' dive in to the technical specification of the requirement.

Technical Specification

Exporting a profile is very generic and doesn't need any specific code to make it generic. It just expects a site name and output file name. The logic can be directly burned in the tool by getting the above specified parameters. Importing profile is little tricky. Commerce runtime API takes Profile/Bizdata connection string and the profile xml as the input to perform the process of import. The first parameter can be parameterized easily. Second parameter takes the profile xml file path. Every engagement would have implemented different profile data stores in commerce server profiles. Every data source expects certain parameters like connection string, Active directory credentials etc. Basically the xml file content needs to be updated with project specific implementation and then the same needs to be passed to commerce server runtime api's.

I have tailored the observer design pattern to achieve the same. Profile import tool has an initialization file named InitData.xml. Initdata xml represents the initialization data / parameters used by the tool to perform the process of import and export. The xml file also allows the users to specify handler's information. Handlers are nothing but Assembly & Type combination information stored in the initialization file. Before starting the process of import / export, the tool looks into the configured handlers and instantiates the same through reflection and call appropriate interface methods to parse the xml document. I also expect the configured Type to implement the IProfileDocumentHandler interface. Handlers are specific to a partition or data source defined in the profile schema. One Data source can map to multiple handlers. Import tool identifies all the data sources defined in the profile document and searches for the handlers in the initialization file (InitData.xml) and calls the appropriate handlers to modify the xml document. Code to modify the profile xml document is implemented in the handler implementation. This gives us a clean way to isolate import process and project specific information processing. It also gives chance to the implementers to customize the tool to their requirements. Initialization file also allow the users to specify custom initialization or configurable parameters.

Initialization File Format

<?xml version="1.0" encoding="utf-8" ?>

<InitData>

    <!--

     <SiteConnection>

        <SiteName>Commerce Site Name</SiteName>

        <BizConnectionString>

             Profile resouce connection string

        </BizConnectionString>

        <UserName> future use.</UserName >

        <Password></Password>

    </SiteConnection>

    -->

    <SiteConnection>

        <SiteName>Framework</SiteName>

        <BizConnectionString>Provider=SQLOLEDB;Data Source=local-CS;Initial Catalog=Framework_profiles;Integrated Security=SSPI;Network Library=;</BizConnectionString>

        <UserName></UserName >

        <Password></Password>

    </SiteConnection>

    <!--

     <DataSources>

        <Partition name="Commerce User Profile PartitionName">

        User can have there own inititalization paramterd under partition node.

        Same will be passed to the handlers in runtime.

            <connectionString>Target Connection String</connectionString>    

        </Partition>

     </DataSources>

     -->

    <DataSources>

        <Partition name="UserObjectDS">

            <connectionString>Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=site_profiles;</connectionString>

        </Partition>

        <Partition name="DataSource1">

            <connectionString> Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=site_profiles;</connectionString>

        </Partition>

    </DataSources>

    <!--

     <Handlers>

        <Handler partitionName="Commerce User Profile PartitionName">

            <assembly path ="Assembly Path" type="TypeName" enable="1"></assembly>

        </Handler>

        <Handler partitionName="Commerce User Profile PartitionName">

            <assembly path ="Assembly Path" type="TypeName" enable="1"></assembly>

        </Handler>

    </Handlers>

     Partition Name configured in handlers section should match with the partition name

     in the data sources section.

    -->

    <Handlers>

        <Handler partitionName=" UserObjectDS">

            <assembly path ="<fullpath>Handlers.dll" type="Microsoft.Commerce.ProfileManager.Handlers.ConnectionStringHandler" enable="1"></assembly>

        </Handler>

        <Handler partitionName=" DataSource1">

            <assembly path ="<fullpath>Handlers.dll" type="Microsoft.Commerce.ProfileManager.Handlers.ConnectionStringHandler" enable="1"></assembly>

        </Handler>

    </Handlers>

</InitData>    

SQL Connection String Handler implementation

Connection string handler can be used to handle any data sources which has SQL server as the data store. Handler expects connection string information in the following location in the initdata.xml.

<DataSources>

<Partition name="UserObjectDS">

            <connectionString>Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI;Initial Catalog=site_profiles;</connectionString>

        </Partition>

</DataSources>

Handler uses this information to update the connection string information in the specified partition name in the profile document.

Profile Import Tool Usage

CSProfileManager import <Init data file Path> <Profile definition file path>

CSProfileManager export <Init data file Path> <xml file path to export>

Conclusion

Profile import tool can be customized with little dev effort to suit the requirement of a project. Tool provides extensibility based on observer pattern. Created SQL Connection String handler to handle to SQL based data sources. Similarly we can create handlers to handle other data stores like AD etc.

Packaging and Unpacking Commerce Server Sites through PUP Package

Introduction

    Commerce server provides PUP.exe to automate the process of packing and un-packing a commerce site. Tool has its existence from the days of Site Server. Complete Meta data of a commerce site is managed by pup package. Using the pup package in GUI mode is straight forward and it's quite simple. Every commerce server engagement demands for an automated process to pack and un-pack a commerce server site in an unattended mode. I tried the same in one of my commerce server engagement and tried pup.exe to automate the above process. I faced many hurdles while automating the process and got good support from Commerce Server PSS team in overcoming the hurdles. I would like to share my learning's in this article.

Configuration files

    PUP package can be executed in un-attended / silent mode through a configuration file. Configuration file has complete information about the site to be unpacked. The configuration file doesn't play any role in the process of packing the site.PUP.exe packs the entire site content configured in commerce server manager. Profile resource is driven through an additional configuration file which has the information about the profile schema and profile SQL definition. Following are the list of configuration files expected by pup.exe to run in unattended mode.

Site Configuration File

Site configuration file instructs the packager about the resources and applications needs to be unpacked. Following configuration file can be used to unpack the EmpySite in command line. The same file can be modified to unpack any commerce site through command line. Connection strings specified in the below configuration should be in the same format. PUP.exe expects the connection string only in that format.

[General]

SiteName=GameServiceSetup1

Description=Silent CSharpSite Install with Windows Authentication

NumOfResources= 7

NumOfApplications= 5

Resource0=Product Catalog

Resource1=Transactions

Resource2=Transaction Config

Resource3=Biz Data Service

Resource4=Site CS Authentication

Resource5=Marketing

Resource6=Inventory

Application0=CatalogWebService

Application1=CSharpSite

Application2=MarketingWebService

Application3=OrdersWebService

Application4=ProfilesWebService

NumOfConnStrs= 7

[ConnStrs]

ConnStrResourceName0=Product Catalog

ConnStrPropertyName0=connstr_db_Catalog

ConnStrFriendlyResourceName0=Product Catalog

ConnStrFriendlyPropertyName0=connstr_db_Catalog

ConnStrValue0=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_ProductCatalog;Data Source=localhost;

ConnStrResourceName1=Transactions

ConnStrPropertyName1=connstr_db_Transactions

ConnStrFriendlyResourceName1=Transactions

ConnStrFriendlyPropertyName1=connstr_db_Transactions

ConnStrValue1=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_Transactions;Data Source=localhost;

ConnStrResourceName2=Transaction Config

ConnStrPropertyName2=connstr_db_TransactionConfig

ConnStrFriendlyResourceName2=Transaction Config

ConnStrFriendlyPropertyName2=connstr_db_TransactionConfig

ConnStrValue2=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_Transactionconfig;Data Source=localhost;

ConnStrResourceName3=Biz Data Service

ConnStrPropertyName3=connstr_db_bds

ConnStrFriendlyResourceName3=Profiles

ConnStrFriendlyPropertyName3=connstr_db_bds

ConnStrValue3=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_Profiles;Data Source=localhost;

ConnStrResourceName4=Marketing

ConnStrPropertyName4=connstr_db_Marketing

ConnStrFriendlyResourceName4=Marketing

ConnStrFriendlyPropertyName4=connstr_db_Marketing

ConnStrValue4=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_Marketing;Data Source=localhost;

ConnStrResourceName5=Inventory

ConnStrPropertyName5=connstr_db_inventory

ConnStrFriendlyResourceName5=Inventory

ConnStrFriendlyPropertyName5=connstr_db_inventory

ConnStrValue5=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_ProductCatalog;Data Source=localhost;

ConnStrResourceName6=Marketing

ConnStrPropertyName6=connstr_db_Lists

ConnStrFriendlyResourceName6=Marketing

ConnStrFriendlyPropertyName6=connstr_db_Lists

ConnStrValue6=Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=CSharpSite_Marketing_Lists;Data Source=localhost;

[Product Catalog]

ResourceFlags=2

PupFlags=1

ProgidPUP=Commerce.CatalogPUP

ResourceType=Catalog

FriendlyName=Product Catalog

[Transactions]

ResourceFlags=2

PupFlags=1

ProgidPUP=Commerce.OrdersPup

ResourceType=Transactions

FriendlyName=Transactions

[Transaction Config]

ResourceFlags=2

PupFlags=1

ProgidPUP=Commerce.OrdersPup

ResourceType=TransactionConfig

FriendlyName=Transaction Config

[Biz Data Service]

ResourceFlags=7

PupFlags=1

ProgidPUP=Commerce.UPMPuP

ResourceType=BizDataStore

FriendlyName=Profiles

GlobalResourceName= (Add new global resource)

[Site CS Authentication]

ResourceFlags=3

PupFlags=1

ProgidPUP=Commerce.AuthPuP

ResourceType=AuthManager

FriendlyName=CS Authentication

GlobalResourceName= (Add new global resource)

[Inventory]

ResourceFlags=2

PupFlags=1

ProgidPUP=Commerce.CatalogPUP

ResourceType=Inventory

FriendlyName=Inventory

[Marketing]

ResourceFlags=2

PupFlags=1

ProgidPUP=Commerce.MarketingPup

ResourceType=Marketing

FriendlyName=Marketing

[CatalogWebService]

ResourceFlags=5130

PupFlags=1

ProgidPUP=Commerce.AddressPuP

ResourceType=Address

FriendlyName=Address

AccessFlags=513

AuthFlags=4

AppIsolated=0

AspCodePage=0

VRoot=CatalogWebService

CSAppName=CatalogWebService

Website=Default Web Site

Machine=localhost

[CSharpSite]

ResourceFlags=10

PupFlags=1

ProgidPUP=Commerce.AddressPuP

ResourceType=Address

FriendlyName=Address

AccessFlags=513

AuthFlags=7

AppIsolated=0

AspCodePage=0

VRoot=CSharpSite

CSAppName=CSharpSite

Website=Default Web Site

Machine=localhost

[MarketingWebService]

ResourceFlags=9226

PupFlags=1

ProgidPUP=Commerce.AddressPuP

ResourceType=Address

FriendlyName=Address

AccessFlags=513

AuthFlags=4

AppIsolated=0

AspCodePage=0

VRoot=MarketingWebService

CSAppName=MarketingWebService

Website=Default Web Site

Machine=localhost

[OrdersWebService]

ResourceFlags=3082

PupFlags=1

ProgidPUP=Commerce.AddressPuP

ResourceType=Address

FriendlyName=Address

AccessFlags=513

AuthFlags=4

AppIsolated=0

AspCodePage=0

VRoot=OrdersWebService

CSAppName=OrdersWebService

Website=Default Web Site

Machine=localhost

[ProfilesWebService]

ResourceFlags=17418

PupFlags=1

ProgidPUP=Commerce.AddressPuP

ResourceType=Address

FriendlyName=Address

AccessFlags=513

AuthFlags=4

AppIsolated=0

AspCodePage=0

VRoot=ProfilesWebService

CSAppName=ProfilesWebService

Website=Default Web Site

Machine=localhost

BizData.ini – Profile specific configuration file

This file needs to be placed in the commerce server installation directory and in the executing directory of pup.exe.

[BizDataSchema]

catalogSchema=catalogSchema.sql

expressionSchema=es_create.sql    

expressionSprocs=es_stored_procs.sql

profileDefs=ProfileSQL_Starter.xml

siteTermDefs=SiteTerms.xml

expressionDefs=ExpressionDefinition.xml

[BizDataStoreFlags]

IsSilentMode=True

[UPM_SQLSource.SQLSource]

IsBizDataStore=True

ProviderString=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=StarterSite_Profiles;Data Source=localhost;

schemaDefs=ProfileSQL_Starter.sql

dataDefs=

PUP Log File

Pup.exe creates a log file named pup.log in C drive and logs information to the file during the process of packing and unpacking a commerce site. The information in the file will be of great use for troubleshooting.

Packing a commerce site

Following command should be used to pack a commerce site in command prompt. Execute the below command in Commerce Server Command prompt.

PUP.exe /p /f:<valid siten ame>/i:Config.ini /s:<SiteName>

Config.ini is of no use in the process of packing a commerce site. Pup.exe packs the entire site content. If we are not using the /i: parameter, pup.exe will pop up the UI. To make the packing process silent, we need to use /i: option J

Unpacking a Commerce Site

Following command should be used to un-pack a commerce site in command prompt. Execute the below command in Commerce Server Tools Command prompt.

PUP.exe /u /f:<pup package path> /i:<Full path of the configuration file>/s:<New Site Name>

Sample Automation Tool

I have created a sample automation tool to automate the process of packing and unpacking commerce site. The Batch file takes parameters needed by pup.exe and calls the pup.exe with appropriate parameters. Following are the details about the tool. Automation tool has the configuration file in the Master Template folder with needed place holders. For every environment I have created a separated folder (Test, Staging etc) which contains the following files.

Using Automation Tool

1. Download PUP Automation.zip .
2. Extract the file content to a directory.
3. Exaction process will create the following directories.
   1. Master Template &
   2. Test
4. Use Commerce Server Tools prompt to execute installcommerce.bat with site name as parameter. (Batch file is located in the test directory).
5. Above command will a new commerce site using empty.pup.

Packaging tool limitation

PUP.exe cannot be used to unpack a commerce server site in silent mode which has AD integration in profiles. The same is supported in GUI mode. It's a limitation in the tool.

Conclusion

PUP.exe is the not recommended way to deploy commerce sites in production environment. We need to use staging for the same. This can be used only to setup the initial site and further updates should be done through staging. To set-up the base commerce site pup package is the only option and can be automated with the command line options. Development stage of commerce server engagement demands for the above automation. The above process should be used only in the stage of development.

Commerce Server 2007 - Data Migration

Introduction:-

In this article I will be discussing about various challenges we face during data migration from CS 2000 to CS 2007. Before getting into the issues let's discuss the basic process involved in the process of data migration. This article covers transactions data migration.

Commerce Server 2007 Migration Wizard:-

Commerce server 2007 comes with a migration wizard to migrate previous versions of commerce server to CS 2007. Commerce server 2007 underwent various design changes in the product. Previous versions of commerce are COM based. Most of the sub-systems in Commerce 2007 have been re-written in managed code. Commerce Server 2007 provides and extensible architecture to extent various sub systems. Commerce Server 2007 wizard works very similar to the runtime architecture. Entire order system of the site has been represented through OrderObjectMapping.xml and OrderPipeLineMapping.xml. Commerce server 2007 wizard follows the same structure to migrate the data from previous version of commerce server.

Various parts of Upgrade wizard:-

Upgrade wizard is a .Net win form application and work based on the configuration files for various configurations. Configuration file stores information very similar to the web.config of the runtime site. It stores information like Order System assemblies and adapter mapping information. Application configuration file store the path of the Storage Mapping File path and Order Migration mapping file. Order Migration mapping file is very similar to the Pipeline mapping file. Migration mapping file maps information between the order Type properties and keys of the legacy system marshaled data.

Migrating Discounts Applied:-

Commerce server 2007 stores various discounts applied information in a separate table. The wizard expects a separate list in the order form header marshaled data for applied discounts. Commerce Server 2000 and 2002 doesn't store information in a list, instead it stores the information in order form header in a flat hierarchy. Due to this design constraint, migration wizard doesn't provide out of box feature to migrate the same. One way to do the migration is through custom SQL migration. For this we need to grab the information from marshaled data to physical columns in the order form header table.

Following are the mapping information about in discounts in the migration mapping file.

<Collection Name="ItemLevelDiscountsApplied" DictionaryKey="_itemlevel_discounts_applied" KeyType="SimpleList" ReferTo="DiscountApplicationRecord" />

<Collection Name="OrderLevelDiscountsApplied" DictionaryKey="_orderlevel_discounts_applied" KeyType="SimpleList" ReferTo="DiscountApplicationRecord" />

Migration wizard expects discounts information in the above specified keys and the same is not available in earlier versions. We need to find the discount related information in the legacy marshaled data and import the same to the SQL tables. Previous version of commerce server stores all the information in blob and also stores some information in tables. Legacy commerce runtime looks in to the keys and checks the same against the target table column list. If the table has mapping columns, runtime will store the data in the appropriate columns. We can use this flexibility to migrate the data from marshaled data to SQL tables. We can create a sample VB application to read the Orders information through commerce API's. After exploring the data through the application, we can create the needed columns in the legacy commerce tables like Order Group, Order Form Header etc. Loading and saving the order back to the system through SaveAsOrder method will force the runtime to do data persistence. After this we can do SQL migration to CS 2007 tables.

Alternatively we can create appropriate lists in the legacy application marshaled data as expected by CS 2007 wizard through a sample VB application.

The same issue is there for Payment methods as well. We need to follow the above steps to migrate the same to CS 2007 

Exploring Legacy marshaled data – CS 2000 PInvoke API

I have created a sample visual basic application to do the discounts and payment methods migration. This application will be very useful to tweak the data stored in the legacy systems.

Trouble shooting Commerce Server pipelines

Good reference book on working and troubleshooting commerce server pipelines.

http://msdn2.microsoft.com/en-us/library/ms959842.aspx

Secret Question Answer - UPM Membership Provider

Commerce UPM Member ship provider always treats the security question answer as case insensitive. While creating the user , UPM Membership provider does ToLower()  of the provided secret question answer and also converts the supplied answer to lower case while doing comparison. If both Create and Forgot password functionality goes through the same UPM Provider, we will not face any issues. If we are creating the users through different API's like commerce runtime or web services, keep this as one of the design constraints.

Commerce Server 2007 Sub Systems - Unmanaged

Commerce Server still runs unmanaged code in few of their sub-systems. Profiles, marketing and Pipelines are the sub-systems which are unmanaged.

All other sub-systems are re-written in managed code.

Good Article on Transactional Pipleines

David Messner's Transactional pipeline components

Cool way to skip implementation of an abstract method in an inherited class (Abstract Override keyword)

Making a class abstract helps us to abstract some base functionality in the base class and it will en-force all the inherited classes to give implementation for the abstract methods. Not in all cases we will be able to give implementation for all the methods. In these cases we give dummy implementation for those methods which is not relevant for the class. We can get rid of this through abstract override keyword in C#. For example abstract base class Mobile might have abstract (Must Override) methods like DisplayCamera, ActivateRadio, Init etc... Abstract methods like DisplayCamera, ActivateRadio needs implementation from the actual mobile class. We might have many implementation of SimpleMobile phone class. In those cases we might need a SimpleMobile base class which handles all the common operations of simple mobile phones, but need to delegate the implementation of some methods to the actual implementers. In these cases we can use Abstract Override keyword to dictate or delegate the implementation of some of the methods to the actual child implementers.  Making use of Abstract Override keyword makes the class again abstract.

public abstract class Mobile
{
      public abstract void Init();
      public abstract void ActivateRadio();
      public abstract void DisplayCamera();
}

public abstract class SimpleMobile : Mobile
{
       public override void Init()
      {
           // Implementation in child class.
       }

      // skipping the implementation. 
      public abstract override void ActivateRadio();

      // skipping the implementation. Which is not relevant
       public abstract override void DisplayCamera();
}

 Note - Thanks for the comments.

Getting Service Unavailable error message in Commerce Server Manager ?

If Enhanced Security configuration is enabled, Commerce Server manager will give Service Unavailable error. Removing  Enhanced IE security setting through Add/Remove Programs will solve the issue.

Enabling Secure Socket Layer for commerce sites

By default all the business manager UI's connect to the commerce web services through SSL. To enable SSL we need to do the following steps. The same steps can also be applied to repair the SSL installation in instances like changing the machine name etc.

1. Install Internet Information Services (IIS) 6.0 Resource Kit Tools.
2. Resource Tool Kit can be downloaded from http://go.microsoft.com/fwlink/?LinkID=59276.
3. After installation Click Start , point to Programs, point to IIS resources and point to SelfSSL and then click on SelfSSL.
4. In the command prompt execure SelfSSL /T /V:100
5. Command will prompt to replace the existing SSL configuration if any

By default the command will enable SSL for IIS Default Site. The command line utility also has the option to specify the site name to which SSL needs to be enabled.

Customizing and Layering WinForm Application to acheive customization

http://www.codeproject.com/vb/net/UILayering.asp

Undocumented SQL Server 2000 functions

•  Encryption in SQL server [pwdencrypt,pwdcompare]

There are some hidden functions in SQL server through which we can encrypt any string and store the same in the table. This will be very helpful in encrypting the user password and other sensitive user data. Encryption supported by SQL server is one way hash. One way hash is nothing but the string encrypted cannot be decrypted. The only way is to compare values with encrypted string.

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'test'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

In the above example @EncryptedPIN  will store the cipher Text. The data in this string is not the encrypted string instead it will return the hash code of the supplied plain string.

• Extended Stored Procedures
  • sp_MSgetversion
    This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:
    EXEC master..sp_MSgetversion
    Note. A more common way to retrieve the current SQL Server version (this way provides more information) is to use following SELECT statement:
    SELECT @@version
  • xp_dirtree
    This extended stored procedure can be used to get a list of all the folders for the folder named in the xp. To get a list of all the folders in the C:\MSSQL7 folder, run: EXEC master..xp_dirtree 'C:\MSSQL7'
  • xp_subdirs
    This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.
    This is the example:
    EXEC master..xp_subdirs 'C:\MSSQL7'
  • xp_enum_oledb_providers
    This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:
    EXEC master..xp_enum_oledb_providers
  • xp_enumcodepages
    This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:
    EXEC master..xp_enumcodepages
  • xp_enumdsn
    This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:
    EXEC master..xp_enumdsn
  • xp_enumerrorlogs
    This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:
    EXEC master..xp_enumerrorlogs
  • xp_enumgroups
    This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:
    EXEC master..xp_enumgroups
  • xp_fileexist
    You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:
    EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]
    For example, to check whether the file boot.ini exists on disk c: or not, run:
    EXEC master..xp_fileexist 'c:\boot.ini'
  • xp_fixeddrives
    This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:
    EXEC master..xp_fixeddrives
  • xp_getnetname
    This extended stored procedure returns the WINS name of the SQL Server that you're connected to. To view the name, run:
    EXEC master..xp_getnetname
  • xp_readerrorlog
    This extended stored procedure returns the content of the errorlog file. You can find the errorlog file in the C:\MSSQL7\Log directory, by default. To see the text of the errorlog file, run:
    EXEC master..xp_readerrorlog
  • xp_regdeletekey
    This extended stored procedure will delete an entire key from the registry. You should use it very carefully. The syntax is:
    EXECUTE xp_regdeletekey [@rootkey=]'rootkey', [@key=]'key'
    For example, to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:
    EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test'
  • xp_regdeletevalue
    This extended stored procedure will delete a particular value for a key in the registry. You should use it very carefully. The syntax is:
    EXECUTE xp_regdeletevalue [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name'
    For example, to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:
    EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'
  • xp_regread
    This extended stored procedure is used to read from the registry. The syntax is:
    EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT]
    For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:
    DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test
  • xp_regwrite
    This extended stored procedure is used to write to the registry. The syntax is:
    EXECUTE xp_regwrite [@rootkey=]'rootkey', [@key=]'key', [@value_name=]'value_name', [@type=]'type', [@value=]'value'
    For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:
    EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @type='REG_SZ', @value='Test'
    Keep in mind that these undocumented extended stored procedures are not officially supported by Microsoft, and that they may not be found in the next version of SQL Server.