Select data with valid emails

While working on a large database having customers data from 1980, I found a requirement to extract only customers with valid email addresses, I used the following query:


SELECT * FROM tblCustomers WHERE ( CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 AND LEFT(LTRIM([Email]),1) <> '@' AND RIGHT(RTRIM([Email]),1) <> '.' AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1 AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3 AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) )

Retrieve Current Time in SQL

A line is compose for retreieving current time only from date in sql

(SELECT RTRIM(LTRIM(SUBSTRING(CAST(GETDATE() as varchar),12,len(CAST(GETDATE() as varchar))))))

Thanks,

Retrieve Nth Highest Value

I often ask this question during interviews to test candidates and even some experienced persons are unable to give proper answer. Here's the optimized query i decided to share:

SELECT section_id
FROM
(SELECT DISTINCT section_id
, ROW_NUMBER() OVER (ORDER BY section_id DESC) AS rnk
FROM Sections_Detail
WHERE section_id IS NOT NULL) AS s
WHERE rnk = n ;

Where n could be any value to retrieve. Thanks!

Create Backup/Restore for SQL Server Database

Following sp can be used to take backup of database

CREATE PROCEDURE [dbo].[sp_backupDaily]
(
@backupPath varchar(300)
)
AS
DECLARE @DBName varchar(255)
set @DBName = 'DatabaseName'
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + '-' +
replace(replace(@DBName,':','_'),'\','_') + '.bak'
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''' + @backupPath + ' ' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

Value in @backuppath could be like '\\MyServer\Ayaz_Zaidi\DBFromProg\'

Similarly Database can be restored by using:

CREATE PROCEDURE [dbo].[sp_restoreDaily]
(
@backupPath varchar(300),
@MdfFileLoc varchar(300),
@LdfFileLoc varchar(300)
)
AS
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DatabaseName
FROM DISK = @backupPath
WITH REPLACE,
MOVE N'DatabaseName' TO @MdfFileLoc,
MOVE N'DatabaseName_log' TO @LdfFileLoc
ALTER DATABASE DatabaseName set multi_user with rollback immediate

Values in variables can be:
@backupPath = '\\spckhi001\Everyone_old\Safia\BackupFileName.bak'
@MdfFileLoc = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MdfFileName.mdf'
@LdfFileLoc = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LdfFileName.ldf'

Database is first altered to support single user before running restore's script as it cannot restore if other useres will be querying. In end it is revert back to support multiple users.

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine

When I try to install the SQL SERVER version installed on my machine the error came

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine 'MachinName'

So after a search I found a script which solved the issue.

----------------------------------------------------------
@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End

:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer

:SkipSrv
goto End

:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End
----------------------------------------------------------

1. open a notepad
2. Copy and paste the above scripts to that
3. save as FIXWMI.CMD (name can be anything, extension should be cmd)
4. Run the script (double click will do)
5. it will ask for a confirmation, allow that.
6. it will take some time to finish.
7. After finishing try, the error might have gone.

It worked for me, so thought of sharing this.

Regards,

Running scripts in SQL Server from Visual Studio

I needed to run an sql server script from visual studio for deployment purpose at client end, which later i realized receiving extra exceptional errors which wasn’t found running them directly on SQL Server.

One way is to run each script individually through Visual Studio as in procedure below

Public Sub LoopDirectory()
Dim strFileSize As String = ""
Dim di As New IO.DirectoryInfo("C:\PInventoryScripts")
Dim aryFi As IO.FileInfo() = di.GetFiles("*.sql")
Dim fi As IO.FileInfo
For Each fi In aryFi
Dim con As New SqlConnection("Data Source = " + ConfigurationSettings.AppSettings.Get("Server") & ";Initial Catalog=" & _
ConfigurationSettings.AppSettings.Get("Database") & "; User Id=" & ConfigurationSettings.AppSettings.Get("UserId") & ";Password=" & ConfigurationSettings.AppSettings.Get("Password") & ";")

Dim command As New SqlCommand(fi.OpenText.ReadToEnd(), con)
command.Connection.Open()
Dim a As Integer = command.ExecuteScalar()
con.Close()
command.Dispose()
con.Dispose()
Next
End Sub

But this may take time making each object`s script individually and keeping it in a separate file. A better option I found is by referencing Sqldmo.dll file which is in C:\Program Files\Microsoft SQL Server\80\Tools\Binn folder

Public Sub ExecuteDMOScript()
Try
Dim sServer As SQLDMO.SQLServer = New SQLDMO.SQLServer
sServer.Connect(ConfigurationSettings.AppSettings.Get("Server"), ConfigurationSettings.AppSettings.Get("UserId"), ConfigurationSettings.AppSettings.Get("Password"))
Dim oDataBase As SQLDMO.Database
Dim strSQL As String
strSQL = File.OpenText(ConfigurationSettings.AppSettings.Get("FileLoc").ToString).ReadToEnd()
sServer.Databases().Item("PINV").ExecuteImmediate(strSQL)
Catch ex As Exception
lblError.Text = ex.Message
End Try
End Sub

Creating a Split() function in Sql Server

There are few functions which we miss as a high level language programmer in databases. One of them is a popular function of dotnet called Split(), which is used to return substrings in a string delimited by a specified character. I used a function to work as Split() in my queries which is shared below:

Alter FUNCTION [dbo].[SplitDelimiter]
(
@String varchar(2000) = null,
@Seperator char(1) = null
)
RETURNS @SeperatedValues Table (
ValueId int identity(1,1),
[Value] varchar(100)
)
AS
BEGIN
While (Charindex(@Seperator,@String)>0)
Begin
Insert Into @SeperatedValues (value)
Select
[Value] = Substring(@String,1,Charindex(@Seperator,@String)-1)
Set @String = Substring(@String,Charindex(@Seperator,@String)+len(@Seperator),len(@String))
End
Insert Into @SeperatedValues (Value)
Select Value = ltrim(rtrim(@String))
Return
END

Using this function
Following query will result in 5 rows containing delimited values in each
[Select ValueId, [Value] from dbo.SplitDelimiter('This: is: a: split: sample', ':')]

ValueId Value
----------- -------------------------------------------
1 This
2 is
3 a
4 split
5 sample

(5 row(s) affected)