European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2012 Hosting - HostForLIFE.eu :: SQL Server 2012 Database Fixing

clock April 26, 2016 00:35 by author Anthony

In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO

  • db_owner
  • db_accessadmin
  • db_securityadmin
  • db_ddladmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_denydatareader
  • db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles


db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

 


HostForLIFE.eu SQL Server 2012 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.

 



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Empty and Deleter All SQL Database?

clock February 9, 2016 23:06 by author Peter

Today, let me show you how to empty and delete all SQL Database. Now write the following code snippet for Clear Blank SQL Database:

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254) 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 

    WHILE @name is not null 

    BEGIN 

        SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Procedure: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all views */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)

 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped View: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all functions */ 

    DECLARE @name VARCHAR(128)  

    DECLARE @SQL VARCHAR(254)     

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Function: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO      

    /* Drop all Foreign Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)      

    WHILE @name is not null 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint IS NOT NULL 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' 

            EXEC (@SQL) 

            PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all Primary Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint is not null 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' 

            EXEC (@SQL) 

            PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all tables */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Table: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

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.

 



SQL Server 2012 Hosting - HostForLIFE.eu :: Managing Number of SQL Server ErrorLog

clock January 5, 2016 21:36 by author Peter

Microsoft SQL Server saves 7 errorlog files by default. When new errorlog is made, the recent one will be deleted at identical time. If you wish to keep a lot of errorlog, you'll follow 2 ways below to manage number of SQL Server ErrorLog, including both increasing and decreasing ErrorLog number.
Method 1: configure SQL Server ErrorLog number in SSMS
Step 1: Open SQL Server Management Studio. connect with SQL Server with SQL Server Authentication.

Tips: If user account password forgot, you'll only reset user password or change user forgotten password. So login to SQL Server with SQL Server Authentication and new user password. Otherwise, even though you'll successfully connect with SQL Server with Windows Authentication, the following error still occurs as soon as you want to configure SQL Server logs.

Step 2: Navigate to Management > SQL Server Logs. Right-click on SQL Server Logs and choose Configure.

Step 3: In pop-up window Configure SQL Server Error Logs, tick the box "Limit the number of error log files before they are recycling". And set "Maximum number of error log" with willing number. Save changes at last before you close this window.

Method 2: Change Number of SQL Server ErrorLog in Registry Editor

Step 1: First, type regedit in Start box, and press Enter to run Registry Editor.

Step 2: Now, Locate to the following path(1 or 2) and create a new entry in registry editor.
1. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer
2. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\MSSQLServer

Right-click in the right blank pane of registry editor, and tap New > QWORD (64-bit) Value button in pop-up options.


Step 3: Rename the entry as NumErrorLogs and double-click it to edit its value. Type a number that you want to save SQL Server ErrorLog file. Tick Decimal under Base and click OK.

Close Registry Editor and finish setting on increasing or decreasing number of SQL Server Errorog file.

HostForLIFE.eu SQL Server 2012 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.



SQL Server 2012 Hosting - HostForLIFE.eu:: How to Configuring a Server Side Trace in SQL Server 2012?

clock November 24, 2015 19:37 by author Peter

When I’m after SQL Server performance problems, SQL Server Profiler is still my beloved tool. Allthough i do know that extended events offer a mor light-weight answer, those are still a bit cumbersome to use (but I’ve seen that we are able to expect some enhancements with SQL Server 2012).

When I’m using profiler to isolate performance problems, I attempt to tack server side traces, whenever doable. luckily, SQL Server Profiler can assist you making a script for a server side trace (File/Export/Script Trace Definition), so you don’t have to figure out all the event- and column-codes. As i used to be doing a similar configuration again and again, i made a decision to separate the TSQL code for the configuration within a stored procedure.

And here comes dbo.configureServerSideTrace:
if object_id('dbo.configureServerSideTrace', 'P') is not null
drop procedure dbo.configureServerSideTrace
go

-- Example for:
-- Start Trace
--   declare @traceID int
--   exec dbo.configureServerSideTrace @traceStatus = 1
--                                    ,@traceID = @traceID output
--                                    ,@maxFileSize = 10000
--                                    ,@traceFileName = N'e:\VMITrace\Undo'
--                                    ,@spId = @@spid
--
-- End Trace
--   exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

create procedure dbo.configureServerSideTrace
             (@traceStatus   bit                  -- 1 => Start Trace
                                                  -- 0 => Stop Trace
             ,@traceID       int output           -- If the Trace is started, this param will return the TraceID
                                                  -- For stopping the trace, the param has to be provided
             ,@spId          int           = null -- provide the @@spid, if you want to filter only events for this conection
                                                  -- Optional. If not provided => no filter. Not needed for stopping the trace
             ,@maxFileSize   bigint        = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.
             ,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)
                                                  -- Optional. Not neded for stoping the trace
                                                  -- Attention! If the file already exists, the SP will yield an error
                                                  -- and no trace is started.
             ) as
begin

if (@traceStatus = 0)
begin
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
return;
end

-- Create a Queue
declare @rc int

exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
-- XML Statistics Profile
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 146, 4, @on
exec sp_trace_setevent @TraceID, 146, 12, @on

-- Filter: Log only events for the provided @@spid
if @spId is not null
exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

goto finish

error:
select ErrorCode=@rc

finish:
end
go


Some annotations:

  • For the parameters, see the comments.
  • Don’t specify a string for the trace file. .TRC will be added automatically.
  • ensure that the output file doesn't already exist. Otherwise you’ll get an error.
  • very often I replace the code for beginning and stopping the trace inside “interesting code” inside a stored procedure. That is, I’m wrapping some more or less awkward code by starting and stopping a trace like this:

declare @traceID int
exec dbo.configureServerSideTrace @traceStatus = 1
                                 ,@traceID = @traceID output
                                 ,@maxFileSize = 10000
                                 ,@traceFileName = N'e:\MyTrace\Test'
                                 ,@spId = @@spid

--
-- Code of interest
--

exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

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.



SQL Server 2012 Hosting - HostForLIFE.eu :: How to Create a User Defined Error Message in SQL Server?

clock September 14, 2015 07:33 by author Peter

In this article I describe the way to produce a User defined error message in SQL Server 2012. we use SP_addmessage to add a custom message and after that we have a tendency to use a RAISERROR Statement to invoke the custom message. We use the SP_admessage stored Procedure to define a User defined Custom Error Message. This stored Procedure adds a record to the sys.message system view. A User defined message should have a message number of 50000 or higher with a severity of 1 to 25. And here is the code:
sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]

Here mcg_id is that the id of the message which might be between 50000 and 2147483647. The severity is the level of the message which might be between one and twenty five. For User defined messages we are able to use it a value of 0 to 19. The severity level between 20 to 25 may be set by the administrator. Severity levels from 20 through 25 are considered fatal.

The actual error message is "msg", that uses a data type of nvarchar(255). the maximum characters limit is two,047. any more than that will be truncated. The language is used if you wish to specify any language. Replace is used once a similar message number already exists, however you wish to switch the string for that ID, you've got to use this parameter.

RAISERROR:
The RAISERROR statement generates an error message by either retrieving the message from the sys.messages catalog read or constructing the message string at runtime. it's used to invoke the the User defined error message. first we produce a User defined error message using SP_addmessage and after that we invoke that by the use of RAISERROR.
RAISERROR ( { msg_id  }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]


Example:
EXEC sp_addmessage
500021,
10,
'THis is error message'
go
RAISERROR (500021, 10, 1)


Replacement of Message.
EXEC sp_addmessage
500021,
10,
'Previous error message is replaced',
@lang='us_english',
@with_log='false',
@replace='replace'
GO
RAISERROR (500021, 10, 1)


Altering the message:
exec sp_altermessage 500021,@parameter='with_log', @parameter_value='true'

Droping the message:
exec sp_dropmessage 500021

HostForLIFE.eu SQL Server 2012 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.



SQL Server 2012 Hosting Spain - HostForLIFE.eu :: How to to check Recovery Model of a database in SQL Server ?

clock January 13, 2015 06:34 by author Peter

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!

 



SQL Server 2012 Hosting UK - HostForLIFE.eu :: Number of words in a string on SQL Server

clock January 9, 2015 06:01 by author Peter

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 ')

 



SQL Server 2012 Hosting UK - HostForLIFE.eu :: Remove the Special Characters in a String

clock December 16, 2014 07:30 by author Peter

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 



SQL Server 2012 Hosting UK - HostForLIFE.eu :: Moving a Table to Another Schema

clock November 20, 2014 05:35 by author Peter

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.



SQL Server 2012 Hosting - HostForLIFE.eu :: 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

clock October 30, 2014 08:43 by author Peter

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

.



About HostForLIFE.eu

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 offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in