As the majority of you will know, an index can enhance the performance of the query most of the time ; internally sql server has to carry out lots of function to keep these indexes in SQL Server 2014. When I started a brand new occupation, I arrived to discover lots of indexes were developed for a few in our production tables. Thus I made a decision to get yourself a script which returns the indexes that’s not active.

Note : it uses the dmv’s so, this isn't a fairly accurate query. However you could use this like a start and apply reasonable just before running the drop index statement.
SELECT  DB_NAME() AS database_name ,
        S.name AS [schema_name] ,
        O.name AS [object_name] ,
        C.name AS column_name ,
        I.name AS index_name ,
        ( CASE WHEN I.is_disabled = 1 THEN 'Yes'
                ELSE 'No'
           END ) AS [disabled] ,
        ( CASE WHEN I.is_hypothetical = 1 THEN 'Yes'
                ELSE 'No'
            END ) AS hypothetical ,
        rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id
                        AND p.object_id = i.object_id GROUP BY p.index_id, p.OBJECT_ID),
        N'USE ' + DB_NAME() + N'; DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) +
                    '.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS 'drop statement'
FROM    [sys].[indexes] I
       INNER JOIN [sys].[objects] O ON O.[object_id] = I.[object_id]
                                       AND O.[type] = 'U'
                                        AND O.is_ms_shipped = 0
                                        AND O.name <> 'sysdiagrams'
        INNER JOIN [sys].[tables] T ON T.[object_id] = I.[object_id]
        INNER JOIN [sys].[schemas] S ON S.[schema_id] = T.[schema_id]
        INNER JOIN [sys].[index_columns] IC ON IC.[object_id] = I.[object_id]
                                                AND IC.index_id = I.index_id
        INNER JOIN [sys].[columns] C ON C.[object_id] = IC.[object_id]
                                        AND C.column_id = IC.column_id
WHERE   I.[type] > 0
        AND I.is_primary_key = 0
        AND I.is_unique_constraint = 0
        AND NOT EXISTS ( SELECT *
                            FROM   [sys].[index_columns] XIC
                                INNER JOIN [sys].[foreign_key_columns] FKC ON FKC.parent_object_id =
IC.[object_id]
                                                    AND FKC.parent_column_id = XIC.column_id
                            WHERE  XIC.[object_id] = I.[object_id]
                               AND XIC.index_id = I.index_id )
        AND NOT EXISTS ( SELECT *
                            FROM   [master].[sys].[dm_db_index_usage_stats] IUS
                            WHERE  IUS.database_id = DB_ID(DB_NAME())
                                AND IUS.[object_id] = I.[object_id]
                                AND IUS.index_id = I.index_id )

Today, I will write an article about How to add My Control token to my new skin in DotNetNuke. I did this job by editing database. Afrer uploading my new control I have to change manualy skin.ascx file every time I did a few changes.

Manual method in VisualStudio. NET :
1. First, You must go to directory of parrsed skin bundle inside the Solution Explorer and Refresh Folder

2. Now change the skin.ascx file to add control registration at the top:
<%@ Register TagPrefix=”dnn” TagName=”SIMPLECONTROL” Src=”~/DesktopModules/SimpleControl/SimpleControl.ascx” %>

3. Then, you can add your control to where you want it to be:
<dnn:SIMPLECONTROL ID=”SIMPLECONTROL1″ runat=”server” />

Simple method could be adding an easy token SIMPLECONTROL to skin. htm file, therefore the token could be parsed in to skin. ascx every time you reparse a skin package. To allow this DotNetNuke function you can allow it inside the Admin. In case your version of DotNetNuke doesn't have this choice, you are able to merely add 1 line to some database.

1. First we have to understand a PackageID that many of us discover inside the Packages table. Click Show Data Table upon the Packages table and scroll right all the way down to the place you’ll discover your uploaded module package. Inside my case the PackageID was 84.

2. Next Step, you only got to open up SkinControls table and add this control token definition by adding this values :
PackageID = 84
ControlKey : SIMPLECONTROL
ControlSrc :/DesktopModules/SimpleControl/SimpleControl. Ascx
SupportPartialrendering : False

Tokens are actualy values hidden in SkinControls table.

The very first thing that we need to do is create a new SSRS 2012 Role that may be used across multiple users/groups which gave minimum permissions towards the SSRS folder structure, however permitted all of these to make, edit, and delete their own reports. In an effort to produce the new Role we linked to Reporting Services by using the Microsoft SQL Management Studio.

Now, expand Security and then right click on Roles and click New Role

Next Enter a name and description to the Role, assign the View Reports, Handle Reports, and Consume Reports permissions, and click OK. We named our Role “Users”.

Then Browse within your SSRS Report Manager website (http://yoursqlsite/Reports)  and click on Folder Settings.

Click on New Role Assignment and type inside the user or group name you would like the Role assigned to, click the check box next to the Browser Role then click OK.

In our case we've 2 folders made, TestFolder1 and TestFolder2. We'll assign the Test1 user to TestFolder1 and also the Test2 user to TestFolder2 after which lock it down wherever they can't view each others’ folders and can't delete their root folder (TestFolderX). Hover your mouse around TestFolder1 then click the down arrow and click Security.

Click Edit Item Security, click OK upon the inherited security alert, and eliminate any users which you don't need to discover or get access to the folder. Click Edit next towards the user which you would like to have create/edit/delete report capabilities and add the new Role you produced in stage 3. The user ought to have Browser and also the new Role permissions.

Do the same thing for each folder, limiting the permissions to only the users who need use of the folders.
Login as perhaps one of the users and verify which they simply notice their assigned folders and therefore are unable to delete their parent folder.

Once Reports are produced you are also able to limit the permissions upon the Reports themselves in an effort to avoid all of these from edited or deleted.

From SQL Server 2005, all tables are grouped into schemas. Even though making a table in case the schema name isn't specified it's developed inside the default schema from the user making it. You are able to use ALTER SCHEMA command to move tables in among schemas. For instance, in case I develop a table using below script it is going to be developed below my default schema that is dbo:
USE [hostsql]
GO
CREATE TABLE Employee
(
       ID     INT,
       Name VARCHAR(20)
)
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'Employee'
GO

Result Set:
name            schema
Employee      dbo
(1 row(s) affected)

As you are able to notice coming from the output the table is currently in dbo schema. Currently to move this table to another schema utilizing ALTER SCHEMA command, first we have to create the schema in case it doesn't exist by now. When that many of us can move table to new schema.
USE [SqlAndMe]
GO CREATE SCHEMA Staff
GO 
ALTER SCHEMA Staff
TRANSFER dbo.Employee
GO 
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'Employee'
GO

Result Set:
name            schema
Employee      Staff 
(1 row(s) affected)

As you can see from the result, the table of Employee is now moved to Staff schema.

I have been focusing on SQL Reporting Service (SSRS) 2012 using a supply database that had terribly fascinating worth inside the field. It looked such as HTML upon the website :

It is very annoying for business users to discover some thing similar to this on SSRS report :
SELECT TheName = 'Put your <span style="color: #ff0000; font-weight: bold; text-decoration: underline">name </span>in the box:'

This was a " name " column simply beneath the ID in database and there wasn't some other columns along with no HTML. I'm not so positive in case somebody took a incorrect approach making this field using HTML or that was an intension of the developer creating his life easier on the online interface, however I've determined to get yourself a quick answer to eliminate it and to provide the report when I can.
CREATE FUNCTION fn_RemoveHTMLFromText (@inputString nvarchar(max))
RETURNS nvarchar(MAX)
AS
BEGIN

  /*Variables to store source fielde temporarily and to remove tags one by one*/
  DECLARE @replaceHTML nvarchar(2000), @counter int, @outputString nvarchar(max)
  set @counter = 0
  SET @outputString = @inputString

  /*This was extra case which I've added later to remove no-break space*/
  SET @outputString = REPLACE(@outputString, '&nbsp;', '')

  /*This loop searches for tags beginning with "<" and ending with ">" */
  WHILE (CHARINDEX('<', @outputString,1)>0 AND CHARINDEX('>', @outputString,1)>0)
  BEGIN
    SET @counter = @counter + 1

    /*
    Some math here... looking for tags and taking substring storing result into temporarily variable, for example "</span>"
   */
   SELECT @replaceHTML = SUBSTRING(@outputString, CHARINDEX('<', @outputString,1), CHARINDEX('>',   @outputString,1)-CHARINDEX('<', @outputString,1)+1)

   /* Replace the tag that we stored in previous step */
   SET @outputString = REPLACE(@outputString, @replaceHTML, '')

   /* Let's clear our variable just in case... */
   SET @replaceHTML = ''

   /* Let's set up maximum number of tags just for fun breaking the loop after 15 tags */
  if @counter >15
      RETURN(@outputString);
  END
  RETURN(@outputString);
END

And now, let use the fuction:
SELECT TheName = dbo.fn_RemoveHTMLFromText ('Put your <span style="color: #ff0000; font-weight: bold; text-decoration: underline">name </span>in the box:')

And finally, this is what I wanna see:

This is simply easy resolution that has been applied upon the field along with not so many HTML tags, other then may be simply changed into more serious HTML cleaner.

The database I inheritable when I started my current job sends out a lot of mails. It does therefore using database Mail with SQL Server, and it's a lot of mail accounts and mail profiles outlined. I do know that many of these profiles are used, i think that some aren't, and that I haven't any plan concerning the rest – in the future I'll notice the time to clean up, however to this point there have always been a lot of pressing matters to attend to.

But nowadays the mail administrator told me that thanks to a modification in design, SQL Server had to start using a completely different SMTP server for sending mails. Quite easy task if you've got just a single profile. Just a couple of clicks within the database Mail Configuration Wizard, and done. But continuation those self same mouse-clicks for each profile within the list wasn't my plan of a morning well spent, therefore I made a decision that I’d got to script this. (This ought to be easy – we've just a single SMTP server, therefore I may hit each single mail account and did not get to trouble with exceptions).

Usually, scripts for such a task are terribly easy. You just type a felicitous search string in your favorite Search Engine, check the primary 2 or 3 hits, and you’ll have a script. Sometimes even over one. Carefully inspect the script,copy the script, paste into SSMS, build adjustments for your own scenario, do a new review simply to take care. Bottom line, I did not notice a pre-made script for this task, therefore I put in the effort to put in writing one, so determined to share it with you.

Note that the script below was tested on SQL Server 2008R2 only. also note that it'll update all mail accounts to use the new SMTP server. If you've got a a lot of complicated setup with multiple servers and just some got to change, you may got to add the correct filtering criteria to the wherever clause.

DECLARE @NewServer sysname = 'NotTelling.mail', -- New SMTP server
        @OldServer sysname = 'MySecret.mail',   -- Old SMTP server
        @account_id int;
DECLARE Cursor_MailAccounts CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT account_id
    FROM   msdb.dbo.sysmail_server
    WHERE  servername = @OldServer;             -- Add extra logic here
OPEN Cursor_MailAccounts;

FETCH NEXT
FROM  Cursor_MailAccounts
INTO  @account_id;

WHILE @@FETCH_STATUS = 0
BEGIN;
    EXECUTE msdb.dbo.sysmail_update_account_sp
                @account_id = @account_id,
                @mailserver_name = @NewServer;
   
    FETCH NEXT
    FROM  Cursor_MailAccounts
    INTO  @account_id;
END;

CLOSE Cursor_MailAccounts;
DEALLOCATE Cursor_MailAccounts;

I hope it will works for you!

In  this tutorial, I will tell you how to display records from your database using Crystal Report 2013 Hosting. Crystal Report 2013 is an integrated development environment you'll be able to use to form applications and libraries with many alternative frameworks and languages. it's a rich feature set together with an intelligent editor, built in compiler (and related tools) and context sensitive facilitate. SAP Crystal Reports 2013 (Crystal Reports) is intended to figure along with your database to assist you analyze and interpret vital data. Crystal Reports makes it straightforward to form simple reports, and, it conjointly has the great tools you would like to provide advanced or specialized reports.

Crystal Reports will facilitate a company build sensible business selections. It will provide you with a warning to areas of a business's operations that aren't performing on all cylinders, or means areas that are a model for excellence. Reports will permit staff to target the business info instead of the production of data. Application and net developers will save time and meet their users wants by integrating the report process power of Crystal Reports into their info applications.

And here is code for Default.aspx file
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ReportViewer._Default" %>
<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">   
<title>Untitled Page</title>
</head><
body>
    <form id="form1" runat="server">
    <div>
    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
        AutoDataBind="true" />
    </div>
    </form>
</body>
</html>

And now, here is the Default.aspx.cs code:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
namespace ReportViewer{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr =
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlDataAdapter sqlda = new SqlDataAdapter();
        SqlCommand com = new SqlCommand();
        DataTable dt;
        DataSet1 ds=new DataSet1();
        ReportDocument rptDoc=new ReportDocument(); 
        protected void Page_Load(object sender, EventArgs e)
        {
            bindReport(); 
        }
        private void bindReport()
        {            
            SqlConnection conn = new SqlConnection(connStr);
            dt = new DataTable();
            dt.TableName = "Crystal Report Example";
            com.Connection = conn;
            com.CommandText = "SELECT * FROM product";
            sqlda = new SqlDataAdapter(com);
            sqlda.Fill(dt);
            ds.Tables[0].Merge(dt);
            rptDoc.Load(Server.MapPath("CrystalReport1.rpt"));
            rptDoc.SetDataSource(ds);
            CrystalReportViewer1.ReportSource = rptDoc; 
        }
    }

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team proudly announces New Data Center in Paris (France) for all costumers. HostForLIFE’s new data center in Paris 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 Paris (France) 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 Paris gives us 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 Paris data centers with high transfer speeds and unmetered bandwidth (at no charge) between facilities. Paris 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.

Paris 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 Paris, please visit http://hostforlife.eu/Paris-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.

At this moment, we are going to discussed about: How to Kill all sessions in MSSQL using database. Before an existing database are often restored, there ought to be connections using the database in question. If the database is presently in use the RESTORE command fails with error on below:
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To avoid that error, we'd like to kill all sessions using the database in MSSQL. All sessions using the database are often queries using system hold on procedure sp_who2 or using sys.dm_exec_sessions DMV:
SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'SqlAndMe'

You need to terminate every of the sessions came back one by one by using KILL command. If there are sizable amount of sessions to kill, otherwise you got to try this on a routine basis it gets boring to do it this manner. you'll be able to *automate* this using below script, that takes database name as input, and kills all sessions connecting to that.
USE [master]
GO 
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX) 
SET @sqlCmd = ''
SET @dbName = 'SqlAndMe' -- Change database NAME
 SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
         CHAR(13)
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName 
PRINT @sqlCmd
--Uncomment line below to kill
--EXEC (@sqlCmd)

Hope this tutorial works for you!

Today, I will write about How to fix Error:  “The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node" on SQL Server 2012. And this is the error message:

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

• The local node is not able to communicate with the WSFC cluster.
• No quorum set across the WSFC cluster.

The local node isn’t part of quorum and so unable to process that operation.This prompt me that perhaps the second instance doesn't understand itself its HADR enable. thus I qery sys.dm_hadr_cluster_members and sys.dm_hadr_cluster for both nodes.

On node1, it will come correct information. On node2, the result's empty.
SELECT *
FROM sys.dm_hadr_cluster_members;
Go
select * from sys.dm_hadr_cluster

The way to fix it's disable the HADR from SQL Server configuration manager . Bounce SQL Server and SQL agent. rentable HADR and bounce SQL server and SQL agent. The issue was resolved after second bounce

.