PIVOT query

SELECT       'item_code' AS item_code_count,
[001], [002]
FROM            (SELECT        loc_id, item_code, entry_no
                          FROM            exp_tran) AS derivedtbl_1
PIVOT
(
count(item_code)
FOR loc_id IN ([001], [002])
) AS PivotTable
order by entry_no

Query to Find First and Last Day of Current Month, Year

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'

----------


SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, -1, GETDATE())), 0),
            'First Day of Previous Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3, DATEADD(YEAR,
            DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)),
            'Last Day of Previous Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),
            'First Day of Current Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)),
            'Last Day of Current Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR,1,GETDATE())), 0),
            'First Day of Next Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, 1, GETDATE())) + 1, 0)),
            'Last Day of Next Year'

Passing table valued parameters in SQL Server 2008

SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data. Learn about other benefits of the feature, as well as its minor drawbacks.

I have always wanted to be able to pass table variables to stored procedures. If a variable is able to be declared, it should have the functionality to be passed as necessary.

I was thrilled to learn that SQL Server 2008 offers this functionality. Here are instructions on how to pass table variables (and the data in them) into stored procedures and functions.

Why pass tables as parameters?

Over the years, I have run into hundreds of situations where it was necessary to pass a container of values rather than individual values into a stored procedure. In most programming languages, passing container data structures in and out of routines is not only common but absolutely necessary. TSQL is no different, especially since the database is where the data lives.

SQL Server 2000 made this possible with the use of OPENXML, which allows you to store data into a VARCHAR data type and pass it around. This became even easier in SQL Server 2005 with the advent of the XML data type and XQuery. You still need to construct and shred the XML data before you can use it; this is functional but not simple.

SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data. The feature also allows the client-side developer to pass data tables from client-side code to the database as a simple parameter.

How to pass table parameters

I need to set up my SalesHistory table, which holds my product sales. The following script will create the table in the database of your choice:

IF OBJECT_ID('SalesHistory')>0    
  DROP TABLE SalesHistory;
  GO
  CREATE TABLE [dbo].[SalesHistory]
  (          
        [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,          
        [Product] [varchar](10) NULL,                
        [SaleDate] [datetime] NULL,                
        [SalePrice] [money] NULL
  )
  GO

The first step in setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine. This allows you to define the type of table and reuse it as needed in your procedure code. This code creates the SalesHistoryTableType table type definition:

CREATE TYPE SalesHistoryTableType AS TABLE
  (                     
        [Product] [varchar](10) NULL,                
        [SaleDate] [datetime] NULL,                
        [SalePrice] [money] NULL
  )
  GO

If you want to view other types of table type definitions in your system, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

I want to define the stored procedure that I plan on using to handle my table valued parameter. The following procedure accepts a table valued parameter, which is of the specific SalesHistoryTableType, and loads the SalesHistory with the records in the table parameter with a value of ‘BigScreen’ in the Product column.

CREATE PROCEDURE usp_InsertBigScreenProducts
 (
       @TableVariable SalesHistoryTableType READONLY
 )
 AS
 BEGIN
       INSERT INTO SalesHistory
       (
             Product, SaleDate, SalePrice
       )
       SELECT
             Product, SaleDate, SalePrice
       FROM
             @TableVariable
       WHERE
             Product = 'BigScreen'    

 END
 GO

You can use the table variable being passed in as any other table for querying data.

Limitations to passing table parameters

You must use the READONLY clause when passing in the table valued variable into the procedure. Data in the table variable cannot be modified — you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters — you can only use table variables as input parameters.

Putting my new table variable type to use

First, I must declare a variable of type SalesHistoryTableType. I don’t need to define the structure of the table again because it was defined when I created the type.

DECLARE @DataTable AS SalesHistoryTableType

The following script adds 1,000 records into my @DataTable table variable:

DECLARE @i SMALLINT
 SET @i = 1          

 WHILE (@i <=1000)
 BEGIN                         

     INSERT INTO @DataTable(Product, SaleDate, SalePrice)                      
 VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))                

     INSERT INTO @DataTable(Product, SaleDate, SalePrice)          
 VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                     

     INSERT INTO @DataTable(Product, SaleDate, SalePrice)              
 VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                            

     SET @i = @i + 1
 END

Once I have data loaded into my table variable, I can pass the structure to my stored procedure. (Make sure you add the data to the table variable and pass the table to the procedure all in the same batch. Table variables go out of scope as soon as the procedure or batch returns.)

Note: When table variables are passed as parameters, the table is materialized in the tempdb system database rather than passing the entire data set in memory; this makes handling large amounts of data rather efficient. All server side passing of table variable parameters are passed by reference, using the reference as a pointer to the table in the tempdb.

EXECUTE usp_InsertBigScreenProducts
 @TableVariable = @DataTable

To see if my procedure performed the way I expect, I’ll run this query to see if the records were inserted into the SalesHistory table:

SELECT * FROM SalesHistory

Considerations

SQL Server 2008’s table parameter feature is a huge step forward in terms of development and potentially performance. The benefits to this feature are that it can: reduce server round trips, use table constraints, and extend the functionality of programming on the database engine.

There are some limitations to keep in mind, such as not being able to alter the data in the parameter and not being able to use the variable as output. Despite these minor drawbacks, I definitely recommend exploring the table parameter feature when SQL Server 2008 comes out.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

SQL - selecting record of current date

select * from table_name where _date = convert( varchar,  getdate() ,101)

Round and truncate for decimal

select round(123.456, 2)

select cast((123.456) as decimal (18,2))

TRIGGERS IN SQL SERVER

INTRODUCTION

TRIGGERS IN SQL SERVER

BACKGROUND

This article gives a brief introduction about Triggers in Sql Server 2000/2005.

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:- 

(i) After Triggers (For Triggers) 
(ii) Instead Of Triggers 

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views. 
AFTER TRIGGERS can be classified further into three types as: 

(a) AFTER INSERT Trigger. 
(b) AFTER UPDATE Trigger. 
(c) AFTER DELETE Trigger. 

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers. 

 Collapse | Copy Code

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. The main purpose of this audit table is to record the changes in the main table. This can be thought of as a generic audit trigger. 

Now, create the audit table as:-

 Collapse | Copy Code

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) AFTRE INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

 Collapse | Copy Code

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 set @audit_action='Inserted Record -- After Insert Trigger.';

 insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER INSERT trigger fired.'
GO

The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same. 
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table. 
To see the newly created trigger in action, lets insert a row into the main table as : 

 Collapse | Copy Code

insert into Employee_Test values('Chris',1500);

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:-

 Collapse | Copy Code

6   Chris  1500.00   Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700

(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-

 Collapse | Copy Code

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 
 if update(Emp_Name)
  set @audit_action='Updated Record -- After Update Trigger.';
 if update(Emp_Sal)
  set @audit_action='Updated Record -- After Update Trigger.';

 insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER UPDATE Trigger fired.'
GO

The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from theupdate(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly checked the column Emp_Sal for an update. 
Let’s update a record column and see what happens. 

 Collapse | Copy Code

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

This inserts the row into the audit table as:- 

 Collapse | Copy Code

6  Chris  1550.00  Updated Record -- After Update Trigger.   2008-04-26 12:38:11.843 

(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let’s create the trigger as:- 

 Collapse | Copy Code

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=d.Emp_ID from deleted d; 
 select @empname=d.Emp_Name from deleted d; 
 select @empsal=d.Emp_Sal from deleted d; 
 set @audit_action='Deleted -- After Delete Trigger.';

 insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER DELETE TRIGGER fired.'
GO

In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table. 
Let’s fire a delete on the main table. 
A record has been inserted into the audit table as:- 

 Collapse | Copy Code

6  Chris 1550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867 

All the triggers can be enabled/disabled on the table using the statement 

 Collapse | Copy Code

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL 

Specific Triggers can be enabled or disabled as :- 

 Collapse | Copy Code

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table. 

(ii) Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define anInstead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:- 

(a) INSTEAD OF INSERT Trigger. 
(b) INSTEAD OF UPDATE Trigger. 
(c) INSTEAD OF DELETE Trigger. 

(a) Let’s create an Instead Of Delete Trigger as:-

 Collapse | Copy Code

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
 declare @emp_id int;
 declare @emp_name varchar(100);
 declare @emp_sal int;
 
 select @emp_id=d.Emp_ID from deleted d;
 select @emp_name=d.Emp_Name from deleted d;
 select @emp_sal=d.Emp_Sal from deleted d;

 BEGIN
  if(@emp_sal>1200)
  begin
   RAISERROR('Cannot delete where salary > 1200',16,1);
   ROLLBACK;
  end
  else
  begin
   delete from Employee_Test where Emp_ID=@emp_id;
   COMMIT;
   insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
  end
 END
GO

This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed. 
Now, let’s try to delete a record with the Emp_Sal >1200 as:- 

 Collapse | Copy Code

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement as:- 

 Collapse | Copy Code

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200 

And this record will not be deleted. 
In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables. 

CONCLUSION

In this article, I took a brief introduction of triggers, explained the various kinds of triggers – After Triggers and Instead Of Triggers along with their variants and explained how each of them works. I hope you will get a clear understanding about the Triggers in Sql Server and their usage.

query from different database

sp_addlinkedserver [mycom-devp]
EXEC sp_addlinkedsrvlogin [mycom-devp], 'false', NULL, 'sa', '123456'

select  * from [mycom-devp].restdb_ff.dbo.bill_tran

Windows Service

As a matter of fact Microsoft Windows services, formerly known as NT services enable you to create long-running executable applications that run in its own Windows session, which then has the ability to start automatically when the computer boots and also can be manually paused, stopped or even restarted.

This makes services ideal for use on a server or whenever you need long-running functionality that does not interfere with other users who are working on the same computer. You can also run services in the security context of a specific user account that is different from the logged-on user or the default computer account.

Windows services don’t have any interface to the user, so it can not be debugged like any regular application, but it’s debugged as a process. .NET has a very nice tool that enables processes debugging while it’s in the run status, by easily pressing Ctrl + Alt + P shortcut.

Background

I’ve searched well so many sites about a code that I can with the help of it, build a simple Windows service, but I found a lot of code on how to manage the current Windows services of the system and that’s through theServiceController class.

After searching the MSDN, I’ve found some nice code that helped me to create this simple Windows service. Hope it can help as a basic architecture for and usage of such a Windows service.

Using the code

At first you should simply open VS.NET and then at the File menu click on New, Project. From the New Project Dialog Box, choose the Windows service template project and name it MyNewService like shown below:

The project template automatically adds a component class that is called Service1 by default and inherits fromSystem.ServiceProcess.ServiceBase.

Click the designer. Then, in the Properties window, set the ServiceName property for Service1 toMyNewService.

Set the Name property to MyNewService. Set the AutoLog property to true.

In the code editor, edit the Main method to create an instance of MyNewService. When you renamed the service in step 3, the class name was not modified in the Main method. To access the Main method in VC#, expand the Component Designer generated code region.

 Collapse | Copy Code

static void Main()
{ 
    System.ServiceProcess.ServiceBase[] ServicesToRun; 
    //Change the following line to match. 
    ServicesToRun = new 
        System.ServiceProcess.ServiceBase[] { new MyNewService() }; 
    System.ServiceProcess.ServiceBase.Run(ServicesToRun);  
}

In the next section, you will add a custom event log to your Windows service. Event logs are not associated in any way with Windows services. Here the EventLog component is used as an example of the type of components you could add to a Windows service.

To add custom event log functionality to your service:

1. In the Solution Explorer, right-click Service1.vb or Service1.cs and select View Designer.
2. From the Components tab of the Toolbox, drag an EventLog component to the designer.
3. In the Solution Explorer, right-click Service1.vb or Service1.cs and select View Code.
4. Edit the constructor to define a custom event log.

To access the constructor in Visual C#, expand the Component Designer generated code region.

 Collapse | Copy Code

public MyNewService()
{
 
    InitializeComponent()
    if(!System.Diagnostics.EventLog.SourceExists("DoDyLogSourse"))
    System.Diagnostics.EventLog.CreateEventSource("DoDyLogSourse",
                                                          "DoDyLog");

    eventLog1.Source = "DoDyLogSourse";
    // the event log source by which 

    //the application is registered on the computer

    eventLog1.Log = "DoDyLog";
}

To define what happens when the service starts, in the code editor, locate the OnStart method that was automatically overridden when you created the project, and write code to determine what occurs when the service begins running:

 Collapse | Copy Code

protected override void OnStart(string[] args)
{ 
    eventLog1.WriteEntry("my service started"); 
}

The OnStart method must return to the operating system once the service's operation has begun. It must not loop forever or block. To set up a simple polling mechanism, you can use the System.Timers.Timer component. In theOnStart method, you would set parameters on the component, and then you would set the Timer.Enabledproperty to true. The timer would then raise events in your code periodically, at which time your service could do its monitoring.

To define what happens when the service is stopped, in the code editor, locate the OnStop procedure that was automatically overridden when you created the project, and write code to determine what occurs when the service is stopped:

 Collapse | Copy Code

protected override void OnStop()
{ 
    eventLog1.WriteEntry("my service stoped");
}

You can also override the OnPause, OnContinue, and OnShutdown methods to define further processing for your component. For the method you want to handle, override the appropriate method and define what you want to occur. The following code shows what it looks like if you override the OnContinue method:

 Collapse | Copy Code

protected override void OnContinue()
{
    eventLog1.WriteEntry("my service is continuing in working");
}

Some custom actions need to occur when installing a Windows service, which can be done by the Installer class. Visual Studio can create these installers specifically for a Windows service and add them to your project. To create the installers for your service.

1. Return to design view for Service1.
2. Click the background of the designer to select the service itself, rather than any of its contents.
3. In the Properties window, click the Add Installer link in the gray area beneath the list of properties. By default, a component class containing two installers is added to your project. The component is namedProjectInstaller, and the installers it contains are the installer for your service and the installer for the service's associated process.
4. Access design view for ProjectInstaller, and click ServiceInstaller1.
5. In the Properties window, set the ServiceName property to MyNewService.
6. Set the StartType property to Automatic.

Tip

To avoid being asked about the system username and password you must change the Account for theserviceProcessInstaller to LocalSystem. This is done by opening the ProjectInstaller design and then selecting the serviceProcessInstaller, press F4 and then change the Account property to LocalSystem. Or you can manually do that by creating a class that inherits from System.Configuration.Install.Installerlike this:

 Collapse | Copy Code

[RunInstaller(true)]

public class ProjectInstaller : System.Configuration.Install.Installer 
private System.ServiceProcess.ServiceProcessInstaller 
                                  serviceProcessInstaller1;
private System.ServiceProcess.ServiceInstaller serviceInstaller1; 
/// <summary> 
/// Required designer variable. 
/// </summary> private System.ComponentModel.Container components = null;

public ProjectInstaller()
   // This call is required by the Designer.
   InitializeComponent();

   // TODO: Add any initialization after the InitComponent call 
} 
private void InitializeComponent() 
{ 
   this.serviceProcessInstaller1 = 
     new System.ServiceProcess.ServiceProcessInstaller(); 
   this.serviceInstaller1 = 
     new System.ServiceProcess.ServiceInstaller(); 
   // serviceProcessInstaller1 
   // 
   this.serviceProcessInstaller1.Account = 
     System.ServiceProcess.ServiceAccount.LocalSystem; 
   this.serviceProcessInstaller1.Password = null;
   this.serviceProcessInstaller1.Username = null; 
   // 
   // serviceInstaller1 
   // 
   this.serviceInstaller1.ServiceName = "MyNewService"; 
   this.serviceInstaller1.StartType = 
     System.ServiceProcess.ServiceStartMode.Automatic;

   // 
   // ProjectInstaller 
   // 
   this.Installers.AddRange
     (new System.Configuration.Install.Installer[] 
   { 
       this.serviceInstaller1, 
       this.serviceInstaller1});
   }
}

To build your service project

1. In Solution Explorer, right-click your project and select Properties from the shortcut menu. The project'sProperty Pages dialog box appears.
2. In the left pane, select the General tab in the Common Properties folder.
3. From the Startup object list, choose MyNewService. Click OK.
4. Press Ctrl+Shift+B to build the project. 

Now that the project is built, it can be deployed. A setup project will install the compiled project files and run the installers needed to run the Windows service. To create a complete setup project, you will need to add the project output, MyNewService.exe, to the setup project and then add a custom action to have MyNewService.exe installed.

To create a setup project for your service

1. On the File menu, point to Add Project, and then choose New Project.
2. In the Project Types pane, select the Setup and Deployment Projects folder.
3. In the Templates pane, select Setup Project. Name the project MyServiceSetup.

A setup project is added to the solution. Next you will add the output from the Windows service project,MyNewService.exe, to the setup.

To add MyNewService.exe to the setup project

1. In Solution Explorer, right-click MyServiceSetup, point to Add, then choose Project Output. The Add Project Output Group dialog box appears.
2. MyNewService is selected in the Project box.
3. From the list box, select Primary Output, and click OK.
   A project item for the primary output of MyNewService is added to the setup project. Now add a custom action to install the MyNewService.exe file.

To add a custom action to the setup project

1. In Solution Explorer, right-click the setup project, point to View, then choose Custom Actions. The Custom Actions editor appears.
2. In the Custom Actions editor, right-click the Custom Actions node and choose Add Custom Action. The Select Item in Project dialog box appears.
3. Double-click the application folder in the list box to open it, select primary output from MyNewService(Active), and click OK. The primary output is added to all four nodes of the custom actions — Install, Commit, Rollback, and Uninstall.
4. Build the setup project.

To install the Windows Service

Browse to the directory where the setup project was saved, and run the .msi file to install MyNewService.exe.

To start and stop your service

1. Open the Services Control Manager by doing one of the following:
   • In Windows 2000 Professional, right-click My Computer on the desktop, then click Manage. In theComputer Management console, expand the Services and Applications node.
     - Or -
   • In Windows 2000 Server, click Start, point to Programs, click Administrative Tools, and then clickServices.
     Note: In Windows NT version 4.0, you can open this dialog box from Control Panel.
2. You should now see MyNewService listed in the Services section of the window.
3. Select your service in the list, right-click it, and then click Start.

Right-click the service, and then click Stop.

To verify the event log output of your service

1. Open Server Explorer and access the Event Logs node. For more information, see Working with Event Logs in Server Explorer.
   Note: The Servers node of Server Explorer is not available in the Standard Edition of Visual Basic and Visual C# .NET.
   

To uninstall your service

• On the Start menu, open Control Panel and click Add/Remove Programs, and then locate your service and clickUninstall.
• You can also uninstall the program by right-clicking the program icon for the .msi file and selecting Uninstall.