Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------

• In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
• Repace the space and token in the string result from step 1 with  an empty string.' ~'
• Replace the result of the above atring with a token and the space

DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

Counting the occurence of a substring within a string

Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
 Logic
--------------
Find the substring ball and replace it with ''   e.g.abcdmnopqrst
Find the length of the new string                   12
Subtract the new length from the lenth of the original string  24-12 = 12
Divide the new number with the length of the substring   12/4 = 3

Here is the script - taken form the November issue of SQL server magazine

DECLARE
@str       AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr  AS  VARCHAR(1000) = 'ball';


SELECT  LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)

This is so awesome!

COALESCE - Selecting each record with all not nul values

I had three rows of data that had the same acount number but differnt variation in the three columns. I had to select each account with not null column . This was achieved by the following...

My data
create table #tmpcol(c219 int, c220 int,c221 int)
INSERT INTO #tmpcol
SELECT null,220,null
INSERT INTO #tmpcol
SELECT 219,null,null
INSERT INTO #tmpcol
SELECT null,null,221


select top 1 * from #tmpcolorderby coalesce(100*c219,10*c220,c221) desc

Shrink the transaction log file

The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.

--check the log file name
select * from sys.database_files

--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)  

ALTER DATABASE ARCHIVEhcfsdatabase
 SET RECOVERY SIMPLE;
 GO

-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO

--reset the log file size to 64MB

ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
 -- Reset the database recovery model.
ALTER  DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;
GO

--Do a full Back up - Until a full back up is done the database is not set to full recovery mode--

Drop a database

1. You have to be in the master database.
2. Take the database to the single user mode.
3. Drop the database.

use master;GO
alter database [databaseName]
set single_user with rollback immediate;
drop database [databaseName];

Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON

Searching for a string in the filename and moving, renaming it using SSIS

I had to create a project ot navigate through a folder and look for a file that has  EXE string in the file name ( not as the extension) of the file. e.g. NormanEXE1234.txt. Then once the file was found it had to be copied to the archive folder and the origianl file ahd to be reamed as hcfsnorman.txt.

After almost two days of trying to figure out how to do it, I have completed the task successfully. There were not much write up on this on the internet to my suprise. I could find tutorials only on coppying and moving of the file but not searching for a particular string to match in the filename. Finally, I thought I would put this steps together and here it is....

The following screen( on the left of the screen) shows the different variables declaration. The control flow has two file system task One for archiving the files and the other for renaming the original file.Both the tasks are encapsulated in a For each loop container as we will be navigating through all the files in the folder to find the match for the files we want to move to the archive folder.
The variables userd:

• vArchiveFile : Stores the path where the file will be moved to archive ( destination folder)
• vFileName : Name of the file ( source fie name)
• vFileSourcePath :  The path of the original file where it is found
• vFilsSpec : The string to search in the filename
• vWorkingFileName : The name of the file to be renamed

Double click on the For each loop container and fill up as follows. The filespec property of  the expression of the container should be the filename since we will be searching for the filenames that has EXE string in it.

Double click the FileSystem Task editor and change the destination path variable, the source connection and the operation. The source connection called ExtendedServices is a flat file connection.

The flat file connection is created as follows where in file name we have  file://sanftps/FTPUsers/354norman/ToHCFS/*EXE*.txt

Next, the most important part of the project is to define building the expression for the connection string of the file connection to look for a certain string ( EXE - in this case) in the source folder.
Click on the (..) next to the expression in the proeprty page of the connection manager.
Select the property Conenction string and then click on the expression to open up a screen as shown in the picture below.

This is where we specify the expression for the connection string.

FINDSTRING( @[User::vFileSourcePath] + @[User::vFileName] , @[User::vFileSpec] ,1)  > 0? @[User::vFileSourcePath] + @[User::vFileName] :""

Basically, we are using FINDSTRING string function and tying it to the Boolean Operand to build the connection string. That's all.