A Recovery Model is property of a database that control how transaction log is maintained. SQL Server supports simple, FULL and BULK-LOGGED recovery models. There are multiple ways in which to check recovery model of a database in SQL Server.

1. Using SQL Server Management Studio:
Right click on database in Object explorer -> Go to Properties dialog box -> Options page -> Recovery model

2. Using Metadata function – DATABASEPROPERTYEX():
SELECT [RecoveryModel] = DATABASEPROPERTYEX('SqlAndMe','Recovery')
GO 

Result Set: 
RecoveryModel
SIMPLE 

3. Using catalog view – sys.databases:
SELECT [DatabaseName] = name,
       [RecoveryModel] = recovery_model_desc
FROM   sys.databases
GO  

Result Set:
DatabaseName   RecoveryModel
master         SIMPLE
tempdb         SIMPLE
model          FULL
msdb           SIMPLE
Pubs           SIMPLE
EuWindows      SIMPLE
TestDB         SIMPLE
ProductCatalog SIMPLE
ReportDemo     SIMPLE
ReportServer   FULL
ReportServerTempDB  SIMPLE

(11 row(s) affected)

Using sys.databases catalog view is easier as it returns information of all databases on server. Hope this tutorial works for you!

In SQL Server there is not any inherent capacity accessible for discovering the number of words in a String. Here I reveal to both of you diverse methodologies for doing this, the first is the most simpleone, and is applicable only of these words are separated by a single space.

DECLARE @String VARCHAR(4000)
SELECT @String = 'SQL Server 2005'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

As I said prior, the above query will provides for you the right result, just if the words are differentiated with a solitary space. Presently on the off chance that they are differentiated by more than one space, this will provide for you off base results as the results are basically relied on upon  Length of the original string. Along these lines, what will be the arrangement, simply compose a function  to do this.

CREATE FUNCTION dbo.udfWordCount(
@OriginalText VARCHAR(8000)
)
RETURNS int
as
/*
SELECT dbo.udfWordCount ('hello   world')
*/
BEGIN
    DECLARE @i int ,@j INT, @Words int
    SELECT     @i = 1, @Words = 0
    WHILE @i <= DATALENGTH(@OriginalText)
    BEGIN
        SELECT    @j = CHARINDEX(' ', @OriginalText, @i)
       if @j = 0
        BEGIN
            SELECT    @j = DATALENGTH(@OriginalText) + 1
        END
        IF SUBSTRING(@OriginalText, @i, @j - @i) <>' '
              SELECT @Words = @Words +1
        SELECT    @i = @j +1
    END
    RETURN(@Words)
END
GO
SELECT dbo.udfWordCount ('SQL Server2012')
SELECT dbo.udfWordCount ('SQL Server 2012 ')

This can be a decent administrator device to screen all reports create on SSRS case and it can be helpful particularly if the rundown of your reports is getting huge.  It's a basic question on "Reportserver" database which can be once in a while named differently much the same as mine is: "Reportserver$mike" (named occasions), yet for SSRS introduced on default case it ought to be this one from beneath:

USE [ReportServer]
GO
SELECT
  Name,
  [Path]
  --,[Description]
FROM [dbo].[Catalog]
WHERE [Type] = 2
ORDER BY [Path]

Result:
Name Path
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AdventureWorks_Base /AdventureWorks/AdventureWorks_Base
Customers_Near_Stores /AdventureWorks/Customers_Near_Stores
Employee_Sales_Summary /AdventureWorks/Employee_Sales_Summary
Sales_by_Region /AdventureWorks/Sales_by_Region
Sales_Order_Detail /AdventureWorks/Sales_Order_Detail
Store_Contacts /AdventureWorks/Store_Contacts
(6 row(s) affected)

Likewise, here is a little extensio for the query which I want to utilize this one with guardian envelope structure included:
USE [ReportServer]
GO
SELECT
  Name,
  FullPath = [Path]
  ,ReportParentPath = REVERSE(SUBSTRING(REVERSE(Path), CHARINDEX('/', REVERSE(Path)), LEN(REVERSE(Path))))
  --,[Description]
FROM [dbo].[Catalog]
WHERE [Type] = 2
ORDER BY [Path]

For more extensive checking contemplations I prescribe probably the most vital tables in Reportserver database: USE [ReportServer]
GOSELECT * FROM [Catalog]
SELECT * from [dbo].[DataSets]
SELECT * FROM [dbo].[DataSource]
SELECT * FROM [dbo].[Users]

Today, I am going to tell you how to replace the special characters in a string with spaces. In this case, I need to use PATINDEX.

PATINDEX
It will returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. And this is the code that I used:
PATINDEX ( '%pattern%' , expression )

Example:
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+'
SELECT PATINDEX('%SQL%', @Str)

Here is the result from that code:

Remove Special Characters from String in SQL Server DECLARE @regexp INT
DECLARE @Str varchar(100)
SET @Str='Welcome!@+to+#$%SQL+^&*(SERVER)_+   '
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
WHILE @regexp > 0
BEGIN
SET @Str = STUFF(@Str, @regexp, 1, ' ' )
SET @regexp = PATINDEX('%[^a-zA-Z0-9 ]%', @Str)
Print @regexp
END
SELECT @Str

Result:

STUFF
This STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. This is the code:
STUFF ( character_expression , start , length , replaceWith_expression )

Example:
DECLARE @regexp INT
DECLARE @Str varchar(100)
SET @Str='welcome to sql server'
SET @Str = STUFF(@Str, 1, 1, '@' )
Select @str 

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.

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

.

Today I had a problem with a table that has around 5 million records on my SQL Server 2012. The table has as primary key “Header No.”,”Transaction No.”. In the table is a field “Order No.” that at the moment is blank for all records. I also had an index on that field, because I will need to search for it. I think that in the future only 1 in several 1000 records will have an order no. So the index will be highly selective because I will have only 1 record per order no.

The code is like this:
Table.RESET;
Table.SETCURRENTKEY(“Order No.”);
Table.SETRANGE(“Order No.”,TheOrderNo);
IF Table.FINDFIRST THEN BEGIN

It should go lightening fast but it takes almost 1 second for each FINDFIRST.

Why?
The SQL plan gave me this:

Top[2,1];Nested Loops[3,2];Index Seek($2)[4,3];Clustered Index Seek(Company Name$Table$0)[6,3]
WHAT? A CLUSTERED index Seek?????
it should have been an index seek.
The select was what I expected:
SELECT TOP 1 * FROM “Company Name$Table” WITH (READUNCOMMITTED)  
HERE ((”Order No_”=?)) ORDER BY “Order No_”,”Header No_”,”Transaction No_” OPTION (OPTIMIZE FOR UNKNOWN)

But because SQL knows that the Field “Order No.” contained only blanks, it decided to do a clustered index seek because of the ORDER BY (at least this is what I think SQL decided). So how to fix it NOW (I don’t want to wait that it goes into production and it takes days or months before the problem fixes itself.

What is the code for?
The FINDFIRST serves because I want the Invoice No. of that record and it has to be a non-blank value.
So, I changed the index from “Order No.” to “Order No.”,”Invoice No.”. It makes the index a little bigger, but it shouldn’t be a big problem (or at least I hope so).
The C/AL code I changed like this:

Table.RESET;
Table.SETCURRENTKEY(“Order No.”,”Invoice No.”);”
Table.SETRANGE(“Order No.”,TheOrderNo);
Table.SETFILTER(“Invoice No.”,’<>%1’,’’);
IF Table.FINDFIRST THEN BEGIN

Even with all blank values in “Order No.”,”Invoice No.” at the moment, SQL has changed its plan to an index seek and the code has become very fast.