In this short tutorial, I will tell you how to self-host the Web API, using OWIN custom host. First step that you must do is Add a new Console application as you can see on the below picture.

Next, in order to make a web API and use OWIN custom host, we tend to add references to Microsoft.AspNet.WebApi.OwinSelfHost, using the Nuget Package Manager. this may not only add the references for the web API however also the OWIN components, along with the opposite needed dependencies.

Add a replacement web API controller class. we tend to take away all the methods and add an easy GET method to get the sum of two random numbers.

Add a new class named Startup.cs. this can be as per the OWIN specifications. Use the HttpConfiguration class to make the web API routing template and add it to the request pipeline using the appBuilder.UseWebApi method, where appBuilder is of type IAppBuilder.

Open the Program.cs file and begin the server using the WebApp.Start method, specifying the StartUp class because the entry point for the settings needed. this is the OWIN specification of starting the server within the custom host.

Now simply run the application and the server is started.

To test the Web API, we’ll use the Chrome browser Postman extension. So type the URL of the Web API that we specified in the Program.cs and Send the request. See the results received as you can see on the following picture:

Free ASP.NET Hosting with OWIN Support

Try our Free ASP.NET Hosting with OWIN Support today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.

In this tutorial, let me tell you about How to Fix an Error on Reporting Services “The report server installation is not initialized” ? I had an undertaking for restoring the Reporting Services database from a live situation to a test domain.  Directly after the effective restore, I took  opening the Report Manager site and I was given the error "The report server establishment is not introduced. (rsReportServerNotActivated)". As you can see on the following picture:

SOLUTION

This is a result of the  mismatch in the encryption key. To alter this, either restore the encryption key from the live environment or erase the encryption key from the restored reporting administrations database.  The restoration or cancellation of encryption key could be possible by utilizing the Reporting Services Configuration Manager.

I hope this tutorial works for you!

SQL Reporting Service with Free ASP.NET Hosting
Try our SQL Reporting Service with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.

In this tutorial, I will show you how to handling NULL Character \x00 when Exporting to File Using BCP in SQL Server.

This article talks about the conduct of the BCP utility when removing information from SQL Server to records and all the more particularly to CSV documents. Imagine that you have the underneath table:

As you can see on the above picture, records 2 and 3 contain null values. Now, you need to export the table contents to CSV using BPC: bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" -T

And here is the output from the code:

As should be obvious on the above screenshot, the output appears to be correct. The records' NULL value have been supplanted with unfilled strings. Now, consider that the first table, rather than NULL qualities has unfilled strings:

Let's try again to export the new table contents to CSV using BPC:
bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" –T

And here is the output:

As should be obvious, the unfilled strings have been replaced by NULLs which relate to the hexadecimal character \x00. It appears from the above sample, that the queryout keyword has the same behavior regarding null and empty strings.

Presently, if you are going to nourish the CSV document to another application for parsing and handling it, if the application does not expressly handle conceivable events of the NULL character then most probably an error will be occurred. To this end, dependably have as a top priority the above conduct when extracting data to files using BCP.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this post, I will tell you about rename column name in In SQL Server. Now, I’ve got a table “tblRename” in which there are the two columns Id and Name.

But by mistake I misspelled Name to “Names” and now I want to change the column name to the correct name -> “Name”. So, how is it possible to rename the column name without dropping the table?

We can use a System Stored Procedure sp_rename.
EXEC sp_rename 'tblRename.Names', 'Name','column'

sp_rename is the Stored Procedure.The first value is the tblRename that is the table in which the column is to be renamed, as in "Names" -> tblRename.Names. The second value is the new segment name that we need to change it to, in other words Name. The third value is the sort and here the sort is segment.
Execute the previous query.

Finally, the column name is now changed to Name. But when we execute our query we get an error message:

Along these lines, if there are any Stored Procedures and scripts indicating this tblRename table in which you have determined the "Names" segment then that Stored Procedure or script won't come being used any longer in light of the fact that now there is no section present with a name "Names". In this way, in the event that you need to change the segment name then do it before making any important Stored Procedures or scripts.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Here's a convenient script that is a piece of my tool stash all over I go. Scripts out and identifies basic database level security objects, and creates a TSQL proclamation to reproduce the items. Note that this script just chips away at SQL 2005 or above. This is a long way from an authority script.

I invite to all input on these scripts. I likewise have a server-level security script here.
--Run the below on each database for database-level security. 
SELECT DB_NAME() as Database_Name 
--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public') 
--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
   ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
    sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Alternately, wrap the entire thing in a msforeachdb:
exec sp_msforeachdb 'use [?];
SELECT DB_NAME() as Database_Name
--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')
--Database Level Security
SELECT  rm.state_desc
 ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%''
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

HostForLIFE.eu, a leading Windows hosting provider with innovative technology solutions and a dedicated professional services team proudly announces new Data Center in Frankfurt (Germany) for all costumers. HostForLIFE’s new data center in Frankfurt will address strong demand from customers for excellent data center services in Europe, as data consumption and hosting services experience continued growth in the global IT markets.

The new facility will provide customers and our end users with HostForLIFE.eu services that meet in-country data residency requirements. It will also complement the existing HostForLIFE.eu. The Frankfurt (Germany) data center will offer the full range of HostForLIFE.eu web hosting infrastructure services, including bare metal servers, virtual servers, storage and networking.

HostForLIFE.eu expansion into Frankfurt gives them a stronger European market presence as well as added proximity and access to HostForLIFE.eu growing customer base in region. HostForLIFE.eu has been a leader in the dedicated Windows & ASP.NET Hosting industry for a number of years now and we are looking forward to bringing our level of service and reliability to the Windows market at an affordable price.

The new data center will allow customers to replicate or integrate data between Frankfurt data centers with high transfer speeds and unmetered bandwidth (at no charge) between facilities. Frankfurt itself, is a major center of business with a third of the world’s largest companies headquartered there, but it also boasts a large community of emerging technology startups, incubators, and entrepreneurs.

Our network is built from best-in-class networking infrastructure, hardware, and software with exceptional bandwidth and connectivity for the highest speed and reliability. Every upstream network port is multiple 10G and every rack is terminated with two 10G connections to the public Internet and two 10G connections to our private network. Every location is hardened against physical intrusion, and server room access is limited to certified employees.

All of HostForLIFE.eu controls (inside and outside the data center) are vetted by third-party auditors, and we provide detailed reports for our customers own security certifications. The most sensitive financial, healthcare, and government workloads require the unparalleled protection HostForLIFE.eu provides.

Frankfurt (Germany) data centres meet the highest levels of building security, including constant security by trained security staff 24x7, electronic access management, proximity access control systems and CCTV. HostForLIFE.eu is monitored 24/7 by 441 cameras onsite. All customers are offered a 24/7 support function and access to our IT equipment at any time 24/7 by 365 days a year. For more information about new data center in Frankfurt, please visit http://hostforlife.eu/Frankfurt-Hosting-Data-Center

About HostForLIFE.eu
HostForLIFE.eu is an European Windows Hosting Provider which focuses on the Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

This time we will follow a complete and a smart way to create a HTTP server that is exact and quick in Node.js.

Express is insignificant and adaptable Node.JS web application framework that gives a vigorous set of gimmicks of web and portable application. What's more, it will help you in making APIs that verifiably take a shot at the HTTP protocol.

Installation
Express.JS is a framework file hosted by the Node Package Manager (NPM). For installation, we have a special node command to install on the local computer. Write the following code:
npm install [PACKAGE NAME]

And this is code for installing express:
node install express

Note: express is the package name.

After Installing, you will get a folder node_modules that consists of all the modules that you install from npm.

Finally, you have successfully installed the express module in your node folder. Now, write the following code:
// Express
var fs=require("fs");
var host="127.0.0.1";
var port="1337";

//Express
var express=require("express");
var app=express();
app.ger("/",fuction(request, response){
  response.send("Express is Working !!");
});

//Listening Socket
app.listen(port,host);

We utilize the term standard code with express on the grounds that it evacuates the undesirable lines. The code that minimizes the lines of code to do a certain task.  Also, express permits us to make a HTTP server in a couaple of lines of code. And here is the output:

I hope it works for you!

HostForLIFE.eu Node.js Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

One of the benefits and uses of Filtered Indexes in SQL Server is to produce filtered uniqueness. clearly this has some implications, thus please perceive what you are making an attempt to accomplish. In the below example, we've a compound natural key of the 2 fields key1 and key2. the field bit1 isn't a member of the natural key, however will inform us as to some table usage. maybe bit1 indicates that this record is Active (1) or Inactive (0), or whether or not the info is Confirmed (1) or unconfirmed  (0), or Deleted (0) or Not Deleted (1).

In any case, we have a tendency to solely wish to enforce uniqueness for when bit1 = 1, that indicates:
This value is often filtered to be used wherever bit1 = 1
We do not care whether or not there are duplicate records for once bit1 = 0.
In this means, you'll "deactivate" (in business terms) a record by setting bit1 = 0, while not violating your natural key's uniqueness on (key1, key2).
drop table dbo.tabletestdups
go
create table dbo.tabletestdups
( key1 int not null
, key2 int not null
, bit1 bit not null
)
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1)
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,0) –succeed
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –fails
go
drop index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) WHERE bit1 = 1 --Note the important WHERE clause here at the end of the index.
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeeds because the unique constraint only enforces bit1 = 1.
go
select * from dbo.tabletestdups

And here is the output:

Note that rows four and seven have allowed duplicate combination of key1 =1, key2= 1 and bit1 = 0, but that previous attempts to insert a duplicate combination of key1 =1, key2= 1 and bit1 = 1 unsuccessful.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

There are some condition & times once SQL Server CPU usage exceeds threshold value (ex 80th or more) and keep constant even until 100 percent for a undefined duration. A DBA don't get notified mechanically concerning performance degrade till its according by application team or service desk.

This typically happens once all in explosive unannounced application work goes to SQL Server for processing; but at smart range of times, it’s as a result of some T-SQL Queries are taking longer than expected or as a result of bad execution plan or block. There is also a lot of reason to add, but ultimately high processor usage on production systems goes to harm performance.

Given below could be a script that you simply will convert into stored procedure and call from SQL Server agent job, regular for each five minutes or additional to ascertain CPU usage on a vital info server. This script uses sys.dm_os_ring_buffers to spot CPU usage from last five minutes and if average CPU usage goes beyond 80th then send an alert email together with top 50 CPU consuming SQL sessions and details. And now change the 3rd code line to: SELECT @TimeNow = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info

SCRIPT
SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
-- To send detailed sql server session reports consuming high cpu
-- For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
-- if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
-- For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=80
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
   N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
   N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th></tr>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'', 
td= ses.host_name,'',  
td= er.blocking_session_id,'', 
td= er.database_id,'', 
td= er.command,'', 
td= st.text,'', 
td= er.total_elapsed_time,'', 
td= er.cpu_time,'', 
td= er.reads,'', 
td= er.writes,'', 
td= er.last_wait_type,'', 
td= er.start_time,'', 
td= con.net_transport,'', 
td= con.num_writes,'', 
td= con.num_reads,'', 
td= con.client_net_address,'', 
td= con.auth_scheme,'' 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id =er.session_id  LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail
@recipients='dk@sqlknowledge',
@profile_name = 'SQLProfileName',   
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this post, I will tell you how to change primary key column for existing table in MSSQL 2014. First, you must create table User_Details with the code below:

CREATE TABLE [dbo].[Users_Details](
            [Username] [nchar](20) NOT NULL,
            [Password] [nchar](20) NOT NULL,
            [Email] [nchar](30) NULL,
            [Mobile] [nchar](15) NULL,
            [Address] [nchar](100) NULL,
            [USER_ID] [int] IDENTITY(1,1) NOT NULL,
            [Gender] [varchar](15) NULL,
            [Country] [varchar](50) NULL,
    CONSTRAINT [PK_Username] PRIMARY KEY CLUSTERED
    (
            [Username] ASC
    )
    )

And the change the column as
ALTER TABLE Users_details
ALTER COLUMN USERname NVARCHAR(100)

Now, Run it. It will show the error. Because Username column contains Primary Key.
Msg 5074, Level 16, State 1, Line 1
The object 'PK_Username' is dependent on column 'Username'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Username failed because one or more objects access this column.

First Drop the Primary Key Constraint and alter table and add  Primary Key Constraint
ALTER TABLE Users_Details
DROP CONSTRAINT PK_Username
ALTER TABLE Users_Details
ALTER COLUMN Username NVARCHAR(100) NOT NULL
ALTER TABLE Users_Details
DD CONSTRAINT PK_Username PRIMARY KEY(Username)

Hope this tutorial works for you!

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.