Tag Archives: SQL

SQL Server restore database – the backup set holds a backup of a database other than the existing database

I couldn’t use SQL Server Management Studio and restore database because it was crashing for some reason.
So, I had to use query

I’ve found helpful code on Stack Overflow and tried to use it.
Nothing was special about it until I got error ‘The backup set holds a backup of a database other than the existing ‘xx’ database.’

The solution was to use WITH REPLACE clause. For example

USE Master

RESTORE FILELISTONLY FROM DISK = 'C:\sqldata_backups\MyDB.bak'
RESTORE DATABASE LEOImport FROM DISK = 'C:\sqldata_backups\MyDB.bak'
WITH 
	MOVE 'MyDB' TO 'C:\sqldata\MyDB.mdf',
	MOVE 'MyDB_log' TO 'C:\sqldata\MyDB_log.ldf',
	REPLACE

To get logical names and locations of the files use following query

SELECT name, physical_name AS current_file_location
FROM sys.master_files 

SQL Server – script to backup all SQL Server databases

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

source: MSSQLTips

SQL Server Stopwords and Stoplists for Full-Text Search

By default FTS index uses system stoplist. If you want to have custom one, you need to create new first. To create a full-text stoplist in Management Studio refer to Configure and Manage Stopwords and Stoplists for Full-Text Search
In my case I used either “Create from the system stoplist” (and did modifications) or “Create an empty stoplist”.

REMEMBER after creating new stoplist:
– you have to make sure your full-text index uses this stop list and rebuild your catalog.
– Right click on your table, select full-text index then properties, and in the general tab click on the full-text stop list drop down and pick your stop list.
http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/91774a49-7362-41ad-ac91-7bd453cc94c6/

SQL Server 2008 provides three catalog views that are useful for retrieving the contents of system and custom stoplists:

SELECT * FROM sys.fulltext_system_stopwords;
SELECT * FROM sys.fulltext_stoplists;
SELECT * FROM sys.fulltext_stopwords;

For example get all available language names

SELECT DISTINCT slg.name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id;

To do modifications on custom stoplist you can either use Management Studio (right click on the list then Properties) or SQL queries.

Digits or single characters problem

For example you want to search in your table ‘7 seats’ in your cars description table. It will find all having just ‘seats’ keyword because by default single digits and characters are stopwords.
To make sure you can use variations of queries below

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id

stoplist_01

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE slg.lcid = 1033

stoplist_02

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE stopword = '7'

stoplist_03

Stoplist modifications

To fix ‘7 seats’ all I had to do is remove ‘7’ single digit from the list using query

ALTER FULLTEXT STOPLIST MyList DROP '7' LANGUAGE 'English';

(Notice ‘;’ at the end)

When I wanted to do bulk update I used query below to generate bunch of other ones.

SELECT 'ALTER FULLTEXT STOPLIST MyList DROP ''' + stopword + ''' LANGUAGE ''' + name COLLATE DATABASE_DEFAULT + ''';'
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE stopword = '7'
  • pay attention to COLLATE DATABASE_DEFAULT added to query – name field is conflicting in this query
  • remember about ‘;’ at the end
  • if you did ok, all of it will run with no problem

Sometimes you get an error like this

The stoplist 'StopList' does not contain fulltext stopword '7' with locale ID 1093. Specify a valid stopword and locale identifier (LCID) in the Transact-SQL statement.

When you have ALTER statements in separate lines, double click on error and SSMS will show you exact line. Then remove it.
Remember, if ALTER was successful in a first run, it will error out if ALTER again.

Remember

Once you are done, you will need to repopulate the affected indexes so that they will include the formerly excluded words in the index(right click on your table, full text index, ‘start full population’)
Sometimes it does not work. You query the table and results as they were before.
What helped me is setting action Repopulate index in Full Text properties or reassigning Full Text Index to System, Actions to Repopulate and assigning back (again, Actions Repopulate Index was set)

stoplist_04

stoplist_05

01

SQL string occurrences

Function

CREATE FUNCTION [dbo].[StringOccurrence]( @StringInput VARCHAR(8000), @StringSearch VARCHAR(100) )RETURNS INTBEGIN    RETURN (LEN(@StringInput) -             LEN(REPLACE(@StringInput, @StringSearch, ''))) /            LEN(@StringSearch)ENDGO

Usage

SELECT * FROM CTE_EmailsWHERE  dbo.StringOccurrence(Email, '@') = 1

 

SQL Server: Calculate Summary and Column Summary

This post shows how to show a summary of similar rows as well as column summary for SQL Server table data. Suppose you want to generate the total of a column and display it at the end of the result set. You can use at least two methods as shown below:

Consider the following data:

sql-server-summary

declare @t table(name varchar(100), amount decimal(12,2))
insert into @t
select ‘test1’, 1000 union all
select ‘test1’, 2000 union all
select ‘test1’, 3000 union all
select ‘test2’, 1500 union all
select ‘test3’, 600 union all
select ‘test3’, 1800
If you want to want to sum the amount for each name and also show the total of all names
at the end, use any of the following methods
METHOD 1: Using ROLLUP
select coalesce(name,’Total’) as name, SUM(amount) as amount from @t
group by name
with rollup

METHOD 2: Using UNIONALL
select name, SUM(amount) as amount from @t
group by name
union all
select ‘Total’,SUM(amount) from @t

OUTPUT
sql server total subtotal

SQL Selecting Domain from Email Address

Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.

SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM   dbo.email
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC

Above script will select the domain after @ character. Please note, if there is more than one @ character in the email, this script will not work as that email address is already invalid.

SQL Searching within comma delimited string

Split string UDF example you can find here

DECLARE @ChannelNumbers TABLE(Id INT, Channel VARCHAR(255))DECLARE @SearchString VARCHAR(255)SET @SearchString = '24'INSERT INTO @ChannelNumbers        ( Id, Channel )SELECT 1, '23,24' UNION ALLSELECT 3, '23' UNION ALLSELECT 3, '24' UNION ALLSELECT 5, '123' UNION ALLSELECT 6, '124'SELECT * FROM @ChannelNumbersWHERE @SearchString IN (SELECT ChannelId FROM dbo.SplitString(Id, Channel))

Notice that query does not return 124

SQL Split comma delimited string User Defined Function

CREATE FUNCTION [dbo].[SplitStringTest](    @CommaDelimitedString varchar(2000))RETURNS @ParsedList table(    StringValue varchar(50))ASBEGIN    DECLARE @OrderID varchar(50), @Pos int    SET @CommaDelimitedString = LTRIM(RTRIM(@CommaDelimitedString))+ ','    SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)    IF REPLACE(@CommaDelimitedString, ',', '') <> ''    BEGIN        WHILE @Pos > 0        BEGIN            SET @OrderID = LTRIM(RTRIM(LEFT(@CommaDelimitedString, @Pos - 1)))            IF @OrderID <> ''            BEGIN                INSERT INTO @ParsedList (StringValue)                 VALUES (CAST(@OrderID AS varchar(50)))            END            SET @CommaDelimitedString = RIGHT(@CommaDelimitedString, LEN(@CommaDelimitedString) - @Pos)            SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)        END    END        RETURNEND

Usage

SELECT StringValue FROM  dbo.SplitStringTest('A, B, C, D')

UDF version with extra values

CREATE FUNCTION [dbo].[SplitString](    @ID INT, @CommaDelimitedString varchar(2000))RETURNS @ParsedList table(    ID INT,    ChannelID varchar(50))ASBEGIN    DECLARE @OrderID varchar(50), @Pos int    SET @CommaDelimitedString = LTRIM(RTRIM(@CommaDelimitedString))+ ','    SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)    IF REPLACE(@CommaDelimitedString, ',', '') <> ''    BEGIN        WHILE @Pos > 0        BEGIN            SET @OrderID = LTRIM(RTRIM(LEFT(@CommaDelimitedString, @Pos - 1)))            IF @OrderID <> ''            BEGIN                INSERT INTO @ParsedList (ID, ChannelID)                 VALUES (@ID, CAST(@OrderID AS varchar(50)))            END            SET @CommaDelimitedString = RIGHT(@CommaDelimitedString, LEN(@CommaDelimitedString) - @Pos)            SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)                                END    END        RETURNEND