How do I delete a Windows Service using registry editor ?

1. Start the registry editor (regedit.exe)
2. Move to the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services key
3. Select the key of the service you want to delete
4. From the Edit menu select Delete
5. You will be prompted "Are you sure you want to delete this Key" click Yes
6. Exit the registry editor

Overview of the .NET Framework

Overview of the .NET Framework

The .NET Framework is a technology that supports building and running the next generation of apps and XML Web services. The .NET Framework is designed to fulfill the following objectives:

·        To provide a consistent object-oriented programming environment whether object code is stored and executed locally, executed locally but Internet-distributed, or executed remotely.

·        To provide a code-execution environment that minimizes software deployment and versioning conflicts.

·        To provide a code-execution environment that promotes safe execution of code, including code created by an unknown or semi-trusted third party.

·        To provide a code-execution environment that eliminates the performance problems of scripted or interpreted environments.

·        To make the developer experience consistent across widely varying types of apps, such as Windows-based apps and Web-based apps.

·        To build all communication on industry standards to ensure that code based on the .NET Framework integrates with any other code.

The .NET Framework consists of the common language runtime (CLR) and the .NET Framework class library. The common language runtime is the foundation of the .NET Framework. Think of the runtime as an agent that manages code at execution time, providing core services such as memory management, thread management, and remoting, while also enforcing strict type safety and other forms of code accuracy that promote security and robustness. In fact, the concept of code management is a fundamental principle of the runtime. Code that targets the runtime is known as managed code, while code that doesn't target the runtime is known as unmanaged code. The class library is a comprehensive, object-oriented collection of reusable types that you use to develop apps ranging from traditional command-line or graphical user interface (GUI) apps to apps based on the latest innovations provided by ASP.NET, such as Web Forms and XML Web services.

The .NET Framework can be hosted by unmanaged components that load the common language runtime into their processes and initiate the execution of managed code, thereby creating a software environment that exploits both managed and unmanaged features. The .NET Framework not only provides several runtime hosts but also supports the development of third-party runtime hosts.

For example, ASP.NET hosts the runtime to provide a scalable, server-side environment for managed code. ASP.NET works directly with the runtime to enable ASP.NET apps and XML Web services, both of which are discussed later in this topic.

Internet Explorer is an example of an unmanaged app that hosts the runtime (in the form of a MIME type extension). Using Internet Explorer to host the runtime enables you to embed managed components or Windows Forms controls in HTML documents. Hosting the runtime in this way makes managed mobile code possible, but with significant improvements that only managed code offers, such as semi-trusted execution and isolated file storage.

The following illustration shows the relationship of the common language runtime and the class library to your apps and to the overall system. The illustration also shows how managed code operates within a larger architecture.

 .NET Framework in context

The following sections describe the main features of the .NET Framework in greater detail.

Features of the common language runtime

The common language runtime manages memory, thread execution, code execution, code safety verification, compilation, and other system services. These features are intrinsic to the managed code that runs on the common language runtime.

Regarding security, managed components are awarded varying degrees of trust, depending on a number of factors that include their origin (such as the Internet, enterprise network, or local computer). This means that a managed component might or might not be able to perform file-access operations, registry-access operations, or other sensitive functions, even if it's used in the same active app.

The runtime also enforces code robustness by implementing a strict type-and-code-verification infrastructure called the common type system (CTS). The CTS ensures that all managed code is self-describing. The various Microsoft and third-party language compilers generate managed code that conforms to the CTS. This means that managed code can consume other managed types and instances, while strictly enforcing type fidelity and type safety.

In addition, the managed environment of the runtime eliminates many common software issues. For example, the runtime automatically handles object layout and manages references to objects, releasing them when they are no longer being used. This automatic memory management resolves the two most common app errors, memory leaks and invalid memory references.

The runtime also accelerates developer productivity. For example, programmers write apps in their development language of choice yet take full advantage of the runtime, the class library, and components written in other languages by other developers. Any compiler vendor who chooses to target the runtime can do so. Language compilers that target the .NET Framework make the features of the .NET Framework available to existing code written in that language, greatly easing the migration process for existing apps.

While the runtime is designed for the software of the future, it also supports software of today and yesterday. Interoperability between managed and unmanaged code enables developers to continue to use necessary COM components and DLLs.

The runtime is designed to enhance performance. Although the common language runtime provides many standard runtime services, managed code is never interpreted. A feature called just-in-time (JIT) compiling enables all managed code to run in the native machine language of the system on which it's executing. Meanwhile, the memory manager removes the possibilities of fragmented memory and increases memory locality-of-reference to further increase performance.

Finally, the runtime can be hosted by high-performance, server-side apps, such as Microsoft SQL Server and Internet Information Services (IIS). This infrastructure enables you to use managed code to write your business logic, while still enjoying the superior performance of the industry's best enterprise servers that support runtime hosting.

.NET Framework class library

The .NET Framework class library is a collection of reusable types that tightly integrate with the common language runtime. The class library is object oriented, providing types from which your own managed code derives functionality. This not only makes the .NET Framework types easy to use but also reduces the time associated with learning new features of the .NET Framework. In addition, third-party components integrate seamlessly with classes in the .NET Framework.

For example, the .NET Framework collection classes implement a set of interfaces for developing your own collection classes. Your collection classes blend seamlessly with the classes in the .NET Framework.

As you would expect from an object-oriented class library, the .NET Framework types enable you to accomplish a range of common programming tasks, including tasks such as string management, data collection, database connectivity, and file access. In addition to these common tasks, the class library includes types that support a variety of specialized development scenarios. Use the .NET Framework to develop the following types of apps and services:

·        Console apps.

·        Windows GUI apps (Windows Forms).

·        Windows Presentation Foundation (WPF) apps

·        ASP.NET apps.

·        Windows services.

·        Service-oriented apps using Windows Communication Foundation (WCF).

·        Workflow-enabled apps using Windows Workflow Foundation (WF).

The Windows Forms classes are a comprehensive set of reusable types that vastly simplify Windows GUI development. If you write an ASP.NET Web Form app, you can use the Web Forms classes.

How to search stored procedures containing a particular text?

select *
      from information_schema.routines
      where routine_definition like '%employee_id%'
      and routine_type='procedure'

How I find a particular column name within all tables of SQL server database?

Find a particular column name within all tables of SQL server database.

select * from information_schema.columns
where column_name like '%employee_id%'



Find a particular column name and datatype details within all tables of SQL server database.

SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id where c.name like '%family_id%'

ORDER BY c.OBJECT_ID;

How to change datatype of an existing column in sql

ALTER TABLE dbo.tblEmployee
ALTER COLUMN status bit null

bit datatype in SQL Server

APPLIED FOR: SQL Server (starting with 2008 And above version)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

An integer data type that can take a value of 1, 0, or NULL.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Converting to bit promotes any nonzero value to 1.

Effective ways of writing stored procedure in sql server

Improve stored procedure performance in SQL Server

1. Use SET NOCOUNT ON. ...
2. Use fully qualified procedure name. ...
3. sp_executesql instead of Execute for dynamic queries. ...
4. Using IF EXISTS AND SELECT. ...
5. Avoid naming user stored procedure as sp_procedurename. ...
6. Use set based queries wherever possible. ...
7. Keep transaction short and crisp.

Terms Used in Stored Procedures

SET ANSI_NULLS ON

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.

SET NOCOUNT ON

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement. The setting of SET NOCOUNT is set at execute or run time and not at parse time.

SET QUOTED_IDENTIFIER ON

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

Syntax

-- Syntax for SQL Server and Azure SQL Database  

SET QUOTED_IDENTIFIER { ON | OFF }  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

SET QUOTED_IDENTIFIER ON   

Remarks

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements

SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE. For more information about database settings.

SET QUOTED_IDENTIFIER is takes effect at parse-time and only affects parsing, not query execution.

For a top-level Ad-Hoc batch parsing begins using the session’s current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session’s QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.
Static SQL in a stored procedure is parsed using the QUOTED_IDENTIFIER setting in effect for the batch that created or altered the stored procedure. SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static SQL.

For a nested batch using sp_executesql or exec(), the parsing begins using the QUOTED_IDENTIFIER setting of the session. If the nested batch is inside a stored procedure the parsing starts using the QUOTED_IDENTIFIER setting of the stored procedure. As the nested batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, but the session’s QUOTED_IDENTIFIER setting will not be updated.

Using brackets, [ and ], to delimit identifiers is not affected by the QUOTED_IDENTIFIER setting.

To view the current setting for this setting, run the following query.

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';  
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';  
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;  

Permissions

Requires membership in the public role.

Examples

A. Using the quoted identifier setting and reserved word object names

The following example shows that the SET QUOTED_IDENTIFIER setting must be ON, and the keywords in table names must be in double quotation marks to create and use objects that have reserved keyword names.

SET QUOTED_IDENTIFIER OFF  
GO  
-- An attempt to create a table with a reserved keyword as a name  
-- should fail.  
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);  
GO  

SET QUOTED_IDENTIFIER ON;  
GO  

-- Will succeed.  
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);  
GO  

SELECT "identity","order"   
FROM "select"  
ORDER BY "order";  
GO  

DROP TABLE "SELECT";  
GO  

SET QUOTED_IDENTIFIER OFF;  
GO  

B. Using the quoted identifier setting with single and double quotation marks

The following example shows the way single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

SET QUOTED_IDENTIFIER OFF;  
GO  
USE AdventureWorks2012;  
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
      WHERE TABLE_NAME = 'Test')  
   DROP TABLE dbo.Test;  
GO  
USE AdventureWorks2012;  
CREATE TABLE dbo.Test (ID INT, String VARCHAR(30)) ;  
GO  

-- Literal strings can be in single or double quotation marks.  
INSERT INTO dbo.Test VALUES (1, "'Text in single quotes'");  
INSERT INTO dbo.Test VALUES (2, '''Text in single quotes''');  
INSERT INTO dbo.Test VALUES (3, 'Text with 2 '''' single quotes');  
INSERT INTO dbo.Test VALUES (4, '"Text in double quotes"');  
INSERT INTO dbo.Test VALUES (5, """Text in double quotes""");  
INSERT INTO dbo.Test VALUES (6, "Text with 2 """" double quotes");  
GO  

SET QUOTED_IDENTIFIER ON;  
GO  

-- Strings inside double quotation marks are now treated   
-- as object names, so they cannot be used for literals.  
INSERT INTO dbo."Test" VALUES (7, 'Text with a single '' quote');  
GO  

-- Object identifiers do not have to be in double quotation marks  
-- if they are not reserved keywords.  
SELECT ID, String   
FROM dbo.Test;  
GO  

DROP TABLE dbo.Test;  
GO  

SET QUOTED_IDENTIFIER OFF;  
GO  

Here is the result set.

ID          String 
----------- ------------------------------ 
1           'Text in single quotes' 
2           'Text in single quotes' 
3           Text with 2 '' single quotes 
4           "Text in double quotes" 
5           "Text in double quotes" 
6           Text with 2 "" double quotes 
7           Text with a single ' quote

How to drop and create a stored procedure in SQL Server

USE [SampleDB]
GO

DROP PROCEDURE [dbo].[Sp_UpdateEmployee]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE Employee
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END

    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to drop a stored procedure in SQL Server

USE [SampleDB]
GO

DROP PROCEDURE [dbo].[Sp_UpdateEmployee]
GO

How to alter a stored procedure in SQL Server

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE Employee
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END

    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to create a stored procedure in SQL Server

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE incite_brand_expression
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END
    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to display all the Procedures, Function, Triggers and other objects those are present in the SQL Server DB and its details.

How to display all the Procedures, Function, Triggers and other objects those are present in the SQL Server DB and its details.

SELECT SPECIFIC_NAME,ROUTINE_DEFINITION from information_schema.routines

Use below queries as well for more information

SELECT SPECIFIC_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES

SELECT * FROM INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_TYPE

SELECT DISTINCT ROUTINE_TYPE FROM  INFORMATION_SCHEMA.ROUTINES