How to remove an identity from a column in SQL Server

Removing an identity in SQL Server

We have a table with an identity column. We have to delete the identity from this column.
Unfortunately, in SQL Server, once we have set an identity, we cannot delete it: to remove an identity we must drop the column.


If we want to preserve the data in our column, we must follow some simple steps.
Let's see these steps with an example.


This is our table:

CREATE TABLE dbo.Log
    (
    ID           INT IDENTITY NOT NULL,
    ERROR        VARCHAR(1000),
    STACKTRACE   VARCHAR(1000),
    ...
    CONSTRAINT   PK_LOG PRIMARY KEY (ID)
    )
GO



1. add a new column identical to the column with the identity

ALTER TABLE Log ADD ID_Temp INT


2. copy the content of the column ID into the new column

UPDATE Log SET ID_Temp = ID


3. drop the old column

ALTER TABLE Log DROP COLUMN ID

Actually, in our example table we have to drop the primary key before we can drop the column, so the right sequence of statements is this one:

ALTER TABLE Log DROP PK_LOG
ALTER TABLE Log DROP COLUMN ID


4. rename the new column

sp_rename 'dbo.LOG.LGNUM2.ID_Temp', 'ID', 'COLUMN'


5. in our example we should also establish the primary key again

ALTER TABLE Log ADD PRIMARY KEY (ID)


In this way we can in the end delete an identity, preserving all existing data in our table.

.NET WCF webservices: single wsdl before framework 4.5

How to generate a single wsdl with a self hosted WCF web service

WCF services use to export multiple wsdl files.
This works fine when you remain within Windows environment, but it can generate problems when you have to interoperate with different systems.

With .NET framework 4.5 you can directly ask the webservice to generate a single wsdl file. But what if you are using an older framework?

In my case I had to receive data from a system integration program which could not import a wsdl splitted into different files: I got 2 wsdl file and 4 XSD files.

Multiple XSD files

First of all I searched the internet for a solution to the XSD file splitting issue.

I discovered that it was necessary to create a new endpoint behavior and enforce it to the endpoint.
I found a couple of implementations of what I needed: the first one is WcfExtras, and the other one is FlatWsdl.

WcfExtras is good, but it looks like it works well if you use the config file.
My webservice adds the endpoints and the bindings programmatically, so the config file is not used.

Then I tried FlatWsdl. Actually I used a piece of code from FlatWsdl that I found here.

This is the piece of code:


using System.Collections;
using System.Collections.Generic;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.Xml.Schema;
using System.ServiceModel.Configuration;
using ServiceDescription = System.Web.Services.Description.ServiceDescription;

namespace Thinktecture.ServiceModel.Extensions.Description
{
    public class FlatWsdl : BehaviorExtensionElement, IWsdlExportExtension, IEndpointBehavior
    {
        public void ExportContract(WsdlExporter exporter, WsdlContractConversionContext context)
        {
        }

        public void ExportEndpoint(WsdlExporter exporter, WsdlEndpointConversionContext context)
        {
            XmlSchemaSet schemaSet = exporter.GeneratedXmlSchemas;

            foreach (ServiceDescription wsdl in exporter.GeneratedWsdlDocuments)
            {
                List<XmlSchema> importsList = new List<XmlSchema>();

                foreach (XmlSchema schema in wsdl.Types.Schemas)
                {
                    AddImportedSchemas(schema, schemaSet, importsList);
                }

                wsdl.Types.Schemas.Clear();

                foreach (XmlSchema schema in importsList)
                {
                    RemoveXsdImports(schema);
                    wsdl.Types.Schemas.Add(schema);
                }
            }
        }

        private void AddImportedSchemas(XmlSchema schema, XmlSchemaSet schemaSet, List<XmlSchema> importsList)
        {
            foreach (XmlSchemaImport import in schema.Includes)
            {
                ICollection realSchemas =
                    schemaSet.Schemas(import.Namespace);

                foreach (XmlSchema ixsd in realSchemas)
                {
                    if (!importsList.Contains(ixsd))
                    {
                        importsList.Add(ixsd);
                        AddImportedSchemas(ixsd, schemaSet, importsList);
                    }
                }
            }
        }

        private void RemoveXsdImports(XmlSchema schema)
        {
            for (int i = 0; i < schema.Includes.Count; i++)
            {
                if (schema.Includes[i] is XmlSchemaImport)
                    schema.Includes.RemoveAt(i--);
            }
        }

        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
        }

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
        }

        public void Validate(ServiceEndpoint endpoint)
        {
        }

        public override System.Type BehaviorType
        {
            get { return typeof(FlatWsdl); }
        }

        protected override object CreateBehavior()
        {
            return new FlatWsdl();
        }
    }
}


After copying this code (special thanks to Christian Weyer), all I had to do is adding the new behavior to my endpoints:

var ep = _hostTMS.AddServiceEndpoint(servEndpoint, binding, "");
ep.Behaviors.Add(New FlatWsdl());


In this way I obtained a wsdl which included all the previously splitted XSD pieces.

Multiple wsdl files

Anyway I hadn't still finished: in fact I had 2 wsdl files, referencing each other via <wsdl:import>.
I need one independent wsdl file, so my result wasn't still correct.

I found out that the WCF service splits wsdl file when the contract, the implementation and the binding have a different namespace.

I had defined the namespace in the binding, but I had forgotten to define it in the contract and in the implementation.
This is what I did to solve this issue:

binding.Namespace = "http://MyNamespace";
...

[ServiceContract(Namespace = "http://MyNamespace")]
Public Interface IMyService
{
   ...

}

[ServiceBehavior(Namespace= "http://MyNamespace")]
Public Class MyServiceImpl : IMyService
{
   ...
}

For the details, read this post.

In this way I finally obtained one single wsdl!