Try Visual Studio 2022 Preview and .NET MAUI

Visual Studio 2022 is now available for preview Visual Studio 2022 Preview (microsoft.com) You can install the preview version side by side with the existing Visual Studio setup. I have installed the Free Community Edition side-by-side with Visual Studio 2019. Features I liked in this version Hot Reload – A cool feature that enables many… Continue reading Try Visual Studio 2022 Preview and .NET MAUI

How to Transfer files from local machine to VPS or Dedicated Hosting Server without FTP

This is simple with the remote desktop connection. You can share the local system drives or other local system resources with the remote server in few clicks as below.   1. Open RDP   2. Expand Options and go to Local Resources Tab 3. Click on More.. to open the Local Devices and Resources selection… Continue reading How to Transfer files from local machine to VPS or Dedicated Hosting Server without FTP

SQL Server 2008 new datetime types, functions and the conversions between them

Datetime Types in SQL Server 2005: Lets start with a quick look at the existing date time types in SQL Server. The datetime and smalldatetime, these two types are well known to us, So we wont be spending much time here.   Data Type Representation Accuracy smalldatetime YYYY-MM-DD hh:mm:ss 1 minute datetime YYYY-MM-DD hh:mm:ss[.nnn] 0.00333… Continue reading SQL Server 2008 new datetime types, functions and the conversions between them

Dynamically getting the execution status of a SQL Server Agent Job

This problem seems trivial but there is no straight forward way to get this information. If you have tried the INSERT into EXEC command to insert the results of the sp_help_job procedure into a temporary table like below then you are familiar with the below error. insert into #jobstatus execute msdb..sp_help_job Msg 8164, Level 16,… Continue reading Dynamically getting the execution status of a SQL Server Agent Job

SQL Server 2008 R2 Free eBook from Microsoft Press

Just came to know about this book. More details on the Microsoft Press Blog. You can download the ebook in XPS format here and in PDF format here. The book contains 10 chapters and 216 pages, like so: PART I   Database Administration CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3 CHAPTER 2   Multi-Server… Continue reading SQL Server 2008 R2 Free eBook from Microsoft Press

Script to drop all objects of a Schema in SQL Server

I have spent almost 3 hours to complete this script and test it for couple of sample scenarios during this weekend (31/01/10).  It drops all objects of a schema and then drops the schema itself. The script automatically takes care of all the object dependencies with in the schema by dropping all of its objects… Continue reading Script to drop all objects of a Schema in SQL Server

How to get the File Group of a Table or an Index?

The file group information of both Index and a Table is stored in the sys.indexes table. You might wonder how the tables file group is stored in sys.indexes metadata table. It is because when ever a clustered index is created on a table in SQL Server the physical data pages of the table will be made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the clustered index file group is the file group of the table.

If there is no clustered index on the table then it is represented as a HEAP with index_id equal to ZERO in sys.indexes table.

A clustered index is always the index with index_id equal to 1 and a non clustered index will always have index_id greater than 1 in sys.indexes table .

Below query gets the file group of the table Employee in HumanResources schema of AdventureWorks database.

SELECT d.name AS FileGroup FROM sys.filegroups d JOIN sys.indexes i ON i.data_space_id = d.data_space_id JOIN sys.tables t ON t.object_id = i.object_id WHERE i.index_id<2 — could be heap or a clustered table AND t.name= 'Employee' AND t.schema_id = schema_id('HumanResources')

And below query gets the file group of the index ‘AK_Employee_rowguid’ on Employee table in HumanResources schema of AdventureWorks database.

SELECT d.name AS FileGroup FROM sys.filegroups d JOIN sys.indexes i ON i.data_space_id = d.data_space_id JOIN sys.tables t ON t.object_id = i.object_id WHERE i.name = 'AK_Employee_rowguid' AND t.name= 'Employee' AND t.schema_id = schema_id('HumanResources')

Please note that schema_id is important otherwise both the queries might return multiple results if you have same table name on different schemas.

Hope it helps

🙂 ranjith 🙂

Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options? Below is the typical usage of these options.… Continue reading Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

Automating Web Deployment using Windows Installer XML (WIX)

Deployment of web sites is usually done by copying the compiled ASP.NET web site files into the target virtual directory by using Copy Web Site or Publish web site features in Visual Studio and by manually creating and configuring the Web Site in IIS.

Though this method is simple, it involves lot of manual effort in verifying the Pre Requisites, Creating/Modifying or Configuring the Web sites in IIS. We can automate this whole process by building a simple Windows Installer Package using WIX in the Visual Studio itself while working on the development of your site.

The installer using WIX can

· Check for all the Pre Requisites (OS Version, IIS version, and .NET Framework etc..) before applying any changes

· Create/Modify the web site, Application Pool and configures it in IIS

· Provide the features like Install, Un-Install, Repair and change to the site

· Remove everything that is created (Web Site, Physical Directories) on Un Install and leaves the target server in clean state with just few button clicks

· Rollback all the changes in case of failure

Create a Sample Web Site:

Let’s create a simple website and add a Web Deployment Project to the Web Site. We will build the installer package to deploy this web site on to the target server.

Fig 1: Sample web site and its Web Deployment project

Right click on Web Deployment project and open the Property pages to set up the output location for our compiled web site files. Leave the default value which is set to project output folder. This location we will be the source for our installer package to pick up the required files while building the installer package.

Fig 2: Web Deployment Project property pages

Fig 3: Files in Web Deployment project output folder

Authoring Installer for our Sample Web Site:

We need to install the WIX 3.0 version from http://sourceforge.net/projects/wix/files/ to add the WIX plug-in to the Visual Studio. (Please use WIX documentation for basic understanding on Directory, Component and Feature elements in WIX source files)

Now add the new WIX project to our solution. After you add it the solution looks like this

Fig 4: The web site and the set up project together in one solution

The Product.wxs is the WIX Source File which we will modify shortly to define our package components. Before that we need to add reference to WixIISExtension.dll and WixUtilExtension.dll to our WIX Project. Now open the Product.wxs and add the following xml namespaces to get the intelliscenece for WIX IIS and other elements.

The default directory structure defined in WIX source file maps to “C:Program FilesApplicationName” which specifies the target install location for our package i.e. the location on target server which will have all the output files from our MyWebSite_deploy project (See Fig 3).

Now add the following under the INSTALLLOCATION directory to define our first component

The component MySite_root_Files defines all the files that are directly needs to be copied under the INSTALLLOCATION. The element specifies the actual file that needs to be copied and the source attribute specifies the complete source path of the file.

Source=”$(var.SolutionDir)MyWebSite_deploy$(var.Configuration)Default.aspx”

$(var.SolutionDir) is a WIX pre-processor which gives the Solution folder path to the WIX compiler

$(var.Configuration) is another pre-processor which specifies the Active Configuration of the solution (i.e. Debug | Release)

Along with the files Default.aspx and Web.Config we also have bin folder in project output directory which needs to be created under the install location. So create the folder mapping INSTALLLOCATIONbin by adding the directory element like below under the INSTALLLOCATION directory. And define the component and file or Directory element for each of the files and directories under the bin folder as we have done for INSTALLLOCATION directory.

The same way we can add any number of directories and files mapping from source to the target location.

Setting up IIS web site:

So far we have seen how to move files from source to the target location by using the Directory, File and Component elements. But how can we configure IIS?

WIX has an API or an Extension (WIXIISExtension.dll) to interact with IIS. Remember that we have already added reference to this to our WIX Project. Add another component under the INSTALLLOCATION directory to define the configuration to create a web site in IIS.

Most of the elements and their attributes in this component are self descriptive.

define the domain user which can be referenced anywhere in the source file using the Id MyWebSite_AppPoolUser.

creates the application pool with the name MyWebSiteApplication. The attribute Identity = “Other” specifies that this application pool uses Custom account for identity. And the user attribute specifies the ID of the domainusername created anywhere in the source file using

and its child elements , and define the complete web site in IIS. The Directory attribute of Web Site element is set to INSTALLLOCATION i.e. C:Program FilesMyWebSite which is our target location to copy the compiled ASP.NET files to run our Web Site.

The bindings IP, PORT and Host Header for our web site are specified by element, and the mapping between the application pool MyWebSite_AppPool and the site is defined by . The Default Dcoument and the Authentication are specified by .

So we have defined all the components (MySite_root_Files, MySite_bin_Directory, and MyWebSite_IISConfigure) that need to be installed on to the target server by our installer. But we know that every installer needs at least one feature which is a set of components that define one complete install feature i.e. our Web Site in this case. We have to define it using the feature element.

That is it. We have completed authoring the installer package for our Web Site. Upon building the entire solution again our Set up project reads the compiled ASP.NET files from our Web Deployment Project out put folder and embeds them into a Windows Installer package which is created in the out put directory of our setup project.

Fig 5: Installer package in Setup project output location

We just need to copy this installer package to the target server and double click and wait for the job to be done.

Fig 6: while installing our setup file

Once the install is complete, open the IIS Manager to see that our web site running.

Summary:

The web deployment using WIX is simple, flexible, and gives a overall great web deployment experience.

Hope this gives you a start-up point

WIX: Update Application Configuration Files during Installation

Often we need to modify application configuration files during installation of the application. Generally to set some application settings, or modify database connection strings etc. We can do this in WIX by using the <util:XmlFile/> custom actions. To use these custom actions we need to reference WIXUtilExtension to the setup project. <util:XmlFile Id="UpdateConnectionString" File="[#FileID]" ElementPath="XPATH"… Continue reading WIX: Update Application Configuration Files during Installation