May
18

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

Ranjith on May 18th, 2011

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

                                image

 

2. Expand Options and go to Local Resources Tab

                                image  

  

3. Click on More.. to open the Local Devices and Resources selection wizard

                                 image

Select the drives you want to have access from the VPS/Dedicated server and click on OK.

 

4. Log on to your VPS/Dedicated server and go to My Computer

                                  RDP

You can see that the selected local drives are available for you to access directly from your server. This is very useful when you want to do the initial setup of the server with necessary software and there is no FTP setup available on the server .

 

-Ranjith.

Windows Server 3 Comments »
Apr
27

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

Ranjith on April 27th, 2010

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 second

 

Though these types provide a whole lot of datetime functionality required for any database. The problem is that the date representation is always combined with the time in both the types and there is no way to represent the date only component or time only component in a database with out doing a lot of cast and convert.

New Datetime Types in SQL Server 2008

SQL Server 2008 has introduced four new datetime data types for the date and time representations in SQL Server Databases.

 

Data Type Representation Accuracy
date YYYY-MM-DD 1 day
time[n] hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetime2[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetimeoffset[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 100 nanoseconds

 

The new Date type allows us to represents the date as an individual component with out the time field attached to it. And the Time type represents the Time as an individual component in databases. Note that the new Time type is at higher precision than the regular time in datetime type.

 

Datetime2 can be considered as an extension to regular datetime type with the time representation at an accuracy of 100 nanoseconds than at the regular 0.00333 seconds. The datetime2 type can be used with a default precision (datetime2(7)) or can be used with a user defined precision like datetime2(n) format. If the time precision of datetime2 is set to 3 (datetime2(3)) which gives the exact equivalent representation of a regular datetime type.

 

DateTimeOffset is the combination of default datetime2 with the system time zone offset attached to it. The time zone offset displayed based on the Operating System date, time and culture settings. The offset should be between –14:00 to +14:00. The DateTimeOffset type does not support the day light saving times.

 

Here is my machine current datetimeoffset value 2010-04-25 23:24:07.7086518 +05:30. Notice that the time zone offset returned is +05:30 i.e. IST (Indian Standard Time) attached to datetime2 data.

New Date and Time Functions

SYSDATETIME() Similar to GETDATE() but it returns the current system date as new datetime2(7) type
SYSUTCDATETIME() Similar to GETUTCDATE() but it returns the current system date in UTC as a new datetime2(7) type
SYSDATETIMEOFFSET() Returns the current system date time as DateTime2(7) with system time zone offset attached to it
SWITCHOFFSET(expr, tz) Function to convert the datetimeoffset in one time zone to a datetimeoffset in another timezone
TODATETIMEOFFSET(expr, tz) Function to convert the datetime, datetime2 or datetimeoffset to a datetimeoffset with the specified time zone

Conversions between different datetime types

1. How to convert datetime or datetime2 to datetimeoffset with specified time zone?

SELECT GETDATE(), TODATETIMEOFFSET(GETDATE(), '+08:30')
 -- results: 2010-04-26 00:18:36.927  | 2010-04-26 00:18:36.927 +08:30

SELECT SYSDATETIME(), TODATETIMEOFFSET(SYSDATETIME(), '+05:30')
-- results: 2010-04-26 00:18:36.9316407 | 2010-04-26 00:18:36.9316407 +05:30

TODATETIMEOFFSET() combines the specified datetime or datetime2 to the specified time zone offset.

 

2. How to convert the datetimeoffset in one time zone to datetimeoffset in other time zone?

-- my current system time in IST (GMT + 5:30)
SELECT SYSDATETIMEOFFSET()
       -- 2010-04-26 00:32:48.1838343 +05:30

-- IST (GMT + 5:30) converted to North America PST (GMT - 8:00)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00')
       -- 2010-04-25 11:02:48.1838343 -08:00

-- IST (GMT + 5:30) converted to Europe BST (GMT + 1:00)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+01:00')
       -- 2010-04-25 20:02:48.1838343 +01:00

3. How to convert local datetime or datetime2 to UTC (Coordinated Universal Time)?

DECLARE @LocalDateTime datetime
DECLARE @TimeZoneOffset INT

-- convert my current system datetime to UTC
SET @LocalDateTime = GETDATE()
     -- 2010-04-26 00:44:19.107

-- get the timezone offset of the system
SET @TimeZoneOffset = DATEDIFF(MI, SYSDATETIME(),SYSUTCDATETIME())
     -- 330 mins (i.e. 5:30 )

-- get the utc datetime
SELECT DATEADD(MI,@TimeZoneOffset, @LocalDateTime)
      -- 2010-04-25 19:14:19.107

4. How to convert datetimeoffset to UTC?

DECLARE @DTO DATETIMEOFFSET
-- convert my system datetimeoffset to UTC
SET @DTO = SYSDATETIMEOFFSET()
-- 2010-04-26 00:50:35.3701337 +05:30

-- switch offset to '+00:00' i.e. UTC
SELECT SWITCHOFFSET(@DTO, '+00:00')
      ,CAST(SWITCHOFFSET(@DTO, '+00:00') AS DATETIME2)
-- 2010-04-25 19:20:35.3701337 +00:00  | 2010-04-25 19:20:35.3701337

5. Other simple conversions?

SELECT CAST(GETDATE() AS Time)
      ,CAST(GETDATE() as date)
      ,CAST(SYSDATETIMEOFFSET() AS datetime2)
 -- results: 01:03:35.5700000 | 2010-04-26 | 2010-04-26 01:03:35.5971486

-- get timezone offset from datetimeoffset
SELECT DATEPART(TZ, SYSDATETIMEOFFSET())
 -- results: 330 (number of minutes)

The new date time types in SQL Server 2008 are more portable than datetime or smalldatetime and it is recommended to use them in the future development work.

 

-Ranjith.

Tags: , , , , , ,

SQL Server 9 Comments »
Apr
23

Dynamically getting the execution status of a SQL Server Agent Job

Ranjith on April 23rd, 2010

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, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

So you can not directly insert the results to a temp table from this procedure. Here is a trick I have used using the OPENROWSET to get around with the INSERT EXEC problem. This query results can be stored into a temporary table or a table variable using the INSERT INTO or SELECT INTO command which can be used for further processing.

select name, case when current_execution_status = 1 then ‘Executing’
            when current_execution_status = 2 then ‘Waiting For Thread’
            when current_execution_status = 3 then ‘Between Retries’
            when current_execution_status = 4 then ‘Idle’
            when current_execution_status = 5 then ‘Suspended’
            when current_execution_status = 6 then ‘[Obsolete]‘
            when current_execution_status = 7 then ‘PerformingCompletionActions’
        else NULL end as [status]  from
   openrowset(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,‘EXEC msdb..sp_help_job’)
 

The OPENROWSET is one of the workarounds for the nested INSERT EXEC problem and many other solutions are proposed here (Thanks to Kalman for the workarounds).

 

Hope it helps

- Ranjith

Tags: , ,

T-SQL 4 Comments »
Apr
22

SQL Server 2008 R2 Free eBook from Microsoft Press

Ranjith on April 22nd, 2010

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 Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

 

Tags:

SQL Server No comments »
Jan
31

Script to drop all objects of a Schema in SQL Server

Ranjith on January 31st, 2010

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 the objects of the schema and then drops the schema itself. And automatically takes care of all the object dependencies with in the schema by dropping all of them in a specific order that will resolve the dependency issues.

 

You can download the SP created using the script from here (MS Word Document) or use this Google Doc link to view the script ( Thanks to Elias for the link ).

 

The stored procedure takes two parameters; the @schemaname and the work test. Use @worktest equal to ‘t’ to print all the drop statements without executing them or specify anything else to execute the drop operations. The default option is ‘w’ i.e. it drops all the objects in the specified schema.

EXEC CleanUpSchema 'MySchema', 't'        -- debug
GO
EXEC CleanupSchema 'MySchema', 'w'        -- work for me

These are the known limitations of the script

  • It can not drop a PK table in the schema with an XML index or Spatial index defined
  • It can not drop the schema which is referred by a XML schema collection

Please let me know if you find any more issues with the script. I will list all of them here for others reference and will fix them to improve it further.

 

Hope it helps

- Ranjith

Tags: , , ,

T-SQL 26 Comments »
Jan
31

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

Ranjith on January 31st, 2010

This might sound simple to SQL experts, Apart from the query I feel that the first paragraph of this post is useful to all. So read on.

 

The file group information of both an Index and a Table are stored in the sys.indexes metadata 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 sorts the physical data pages using the clustered index key and the data pages are made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the file group of the clustered index is the file group of the table.

 

What if there is no clustered index on 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. At any point a clustered index with index_id equal to 1 or a heap with index_id equal to ZERO exists for a table in sys.indexes table.  All non clustered indexes will have index_id greater than 1.

 

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

Tags: ,

SQL Server 3 Comments »
Jan
10

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

Ranjith on January 10th, 2010

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.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SampleProcedure
AS
BEGIN
 -- select employees
 SELECT * FROM HumanResources.Employee
END

Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.

 

SET QUOTED_IDENTIFIER ON/OFF:

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes. When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a literal.


SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)  -- SUCCESS
GO


SET QUOTED_IDENTIFIER ON
SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal

Though the “SELECT” and “TABLE” are reserved keywords  we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.

When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.


SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT"(“TABLE” int) -- FAIL
GO


SET QUOTED_IDENTIFIER OFF
SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated as a literal

You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal. The default behavior is ON in any database.

 

SET ANSI_NULLS ON/OFF:
The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL. And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.
SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same
SET ANSI_NULLS OFF
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  same (now NULL = NULL works as 1=1)

The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

 

Why are these two options Special?:

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure,Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.

You can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier
 FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')

And if you need to guarantee the behavior for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure are only applicable until the procedure completes its execution.

 

Hope it helps.

- Ranjith

Tags: , , ,

SQL Server 38 Comments »
Dec
17

Automating Web Deployment using Windows Installer XML (WIX)

Ranjith on December 17th, 2009

Deployment of web sites is usually done by copying the compiled ASP.NET web site files into the target virtual directory using Copy Web Site or Publish web site features in Visual Studio and 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.

WIX installer package

  • Provides the features like Install, Un-Install, Repair and Remove to your Web Site similar to any other product install on your machine
  • You can check for all the Pre Requisites like OS Version, IIS version, and .NET Framework etc.. before proceeding with the deployment and inform the user about what happened with a nice UI
  • You can create a new web site, modify the existing web site,  Create Application Pools and configures IIS
  • During the un-install you can remove everything that is created (Web Site, Physical Directories) on Install and leave the target server in clean state
  • You can rollback the install changes in case of a failure

Create a Sample Web Site:

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

clip_image002

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 values for this demo which is set to the 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.

clip_image004

Fig 2: Web Deployment Project property pages

Build the whole solution to see the output files in deployment project output location

clip_image006

Fig 3: Files in Web Deployment project output folder

 

Authoring Installer for our Sample Web Site:

Install the WIX 3.0 Visual Studio plug in from http://sourceforge.net/projects/wix/files/. And for basic understanding on the Directory, Component, Feature and other elements in WIX source files use the WIX documentation file in the installed location of the plug in or go to http://www.tramontana.co.hu/wix/.

Once the plug in is installed add the new WIX project to our sample web site solution by going to add new project –> Select WIX.

After you add it the solution looks like this

clip_image008

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 add references to WixIISExtension.dll and WixUtilExtension.dll in WIX binaries location 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.

<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"
     xmlns:iis="http://schemas.microsoft.com/wix/IIsExtension"
     xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">

The default directory structure defined in Product.wxs 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).

Under the INSTALLLOCATION directory add the following to define our first component

<!-- root level files –>
<Component Id="MySite_root_Files" Guid="E06FD7E9-8360-4e78-B10F-3F53E88FE1FB">
 <File Id="MySite_Default_aspx" Source="$(var.SolutionDir)MyWebSite_deploy$(var.Configuration) Default.aspx"/>
 <File Id="MySite_Web_Config" Source="$(var.SolutionDir)MyWebSite_deploy$(var.Configuration)Web.Config"/>
</Component>

 

The component MySite_root_Files defines all the files that are directly needs to be copied under the INSTALLLOCATION. The <File/> 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 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.

<!-- bin directory –>
 <Directory Id="MySite_bin_Directory" Name="bin">
   <Component Id="MySite_bin_Files" Guid="2ECC2543-856E-4ca7-8DB3-D1657245A41E">
     <File Id="MYSite_MySite_deploy_dll" Source="$(var.SolutionDir)MyWebSite_deploy$(var.Configuration)
             binMyWebSite_deploy.dll">
      </File>
    </Component>
  </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.

<ComponentId="MyWebSite_IISConfigure" Guid="5146762F-0E78-47d2-A105-6E18E2993619" KeyPath="yes">
  <util:UserId="MyWebSite_AppPoolUser" Name="domainusername" Password="pwd"/>

  <!--define application pool-->
  <iis:WebAppPool Id="MyWebSite_AppPool" Name="MyWebSiteApplication"
                  Identity="other" User="MyWebSite_AppPoolUser"
                  RecycleMinutes="120" />

  <!--define web site-->
  <iis:WebSite Id="MyWebSite_Website" Description="MyWebSite"
               AutoStart="yes" StartOnInstall="yes" ConfigureIfExists="yes"
               Directory="INSTALLLOCATION" ConnectionTimeout="360" >

    <iis:WebAddress Id="MYSite_Bindings" IP="xx.xx.x.xxx" Port="80" Header="MyWebSite" />
    <iis:WebApplication Id="MY_WebApp" Name="MY Web Site" WebAppPool="MyWebSite_AppPool"
                        ScriptTimeout="360" />
    <iis:WebDirProperties Id="MyWebSite_Properties" AnonymousAccess="yes" WindowsAuthentication="no"
           DefaultDocuments="Default.aspx" />
  </iis:WebSite>
  </Component>

 

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

<Util:User/> define the domain user which can be referenced anywhere in the source file using the Id MyWebSite_AppPoolUser.

<iis:WebAppPool/> 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 <Util:User/>

<iis:WebSite/> and its child elements <iis:WebAddress/>, <iis:WebApplication/> and <iis:WebDirProperties/> 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 <iis:WebAddress/> element, and the mapping between the application pool MyWebSite_AppPool and the site is defined by <iis:WebApplication/> . The Default Dcoument and the Authentication are specified by <iis:WebDirProperties/>.

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.

  <Feature Id="ProductFeature" Title="My WebSite" Level="1">
     <!-- add the components comprise of this feature -->
     <ComponentRef Id="MySite_root_Files"/>
     <ComponentRef Id="MySite_bin_Files"/>
     <ComponentRef Id="MyWebSite_IISConfigure"/>
  </Feature>

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.

 

clip_image002[4]

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.

clip_image004[4]

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 it helps

- Ranjith

Tags: , , ,

Windows Installer XML 18 Comments »
Nov
6

WIX: Update Application Configuration Files during Installation

Ranjith on November 6th, 2009

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"
                    Action="setValue"
                    Value=""
        </util:XmlFile>

The File attribute specifies the ID of the configuration file which is defined using the <File/> element in WIX source file; ElementPath specifies the XPATH to an element in the configuration file which needs to be modified, Action specifies what to do with the element; it should be either createElement, deleteElement, setValue, or bulkSetValue. And the Value specifies the target value to be set to the element. Mostly we would use a Property that has been set by the installer UI sequence as a target value.

Below example shows how to modify the connection string DBConn and the application setting MySetting in the Web.Config file.

<Component Id=”ConfigureWebConfig” Guid=”*”>
 <File Id=”MyConfigfile” Source=”$(var.Configuration)web.config” KeyPath=”yes” />

<!-- update a connection string  -->
<util:XmlFile Id="UpdateConnectionString"
  File="[#MyConfigfile]"
  Action="setValue"
  ElementPath=
  "//configuration/connectionStrings/add[[]@name=’DBConn’[]]/@connectionString"
  Value="Server=[DBSERVER];Database=[DBNAME];Integrated Security=SSPI;"/>

<!-- update an application setting  -->
<util:XmlFile Id="UpdateMySetting"
  File="[#MyConfigfile]"
  Action="setValue"
  ElementPath="//configuration/appSettings/add[[]@key=’MySetting’[]]/@value"
  Value="[TARGETVALUE]" />
</Component>

Note that we need to escape the square brackets in XPATH to the element.

 

Hope it helps.

- Ranjith

Tags: ,

Windows Installer XML No comments »