September 14, 2015 07:33 by
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.
January 13, 2015 06:34 by
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!
January 9, 2015 06:01 by
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 ')
December 16, 2014 07:30 by
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
November 20, 2014 05:35 by
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.
October 30, 2014 08:43 by
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
.