SQL Server timestamp

Getting timestamp as yyyymmddhhmmSSsss

DECLARE @sTime VARCHAR(255)
SET @sTime=REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), GETDATE(), 121),'-',''),' ',''),':',''),'.','')
SELECT @stime

How to attach a SQL Server database (.mdf file) with a missing log (.ldf) file.

Sometimes we receive .mdf file only no log file.

If we use Attach command it gives error (something like "no log file found ")
We can attach a SQL Server database (.mdf file) with a missing log (.ldf) file.

Procedure:
Open SQL Studio
Right click on any datatbase and click "New Query"

Run the following System Stored Procedure

sp_attach_single_file_db @dbname= 'NewDatabaseName', @physname= 'C:\Users\AIqbal\Documents\Project1\northwind.mdf'

this will create new database and new log file under same folder

Delimited Text file To DataSet

Imports System
Imports System.Data
Imports System.IO


Public Class TextToDataSet

Public Sub New()
End Sub

'''

''' Converts a given delimited file into a dataset.
''' Assumes that the first line
''' of the text file contains the column names.
'''

''' The name of the file to open
''' The name of the
''' Table to be made within the DataSet returned
''' The string to delimit by
'''
Public Shared Function Convert(ByVal File As String, ByVal TableName As String, ByVal delimiter As String) As DataSet
'The DataSet to Return
Dim result As New DataSet()

'Open the file in a stream reader.
Dim s As New StreamReader(File)

'Split the first line into the columns
Dim columns As String() = s.ReadLine().Split(delimiter.ToCharArray())

'Add the new DataTable to the RecordSet
result.Tables.Add(TableName)

'Cycle the colums, adding those that don't exist yet
'and sequencing the one that do.
For Each col As String In columns
Dim added As Boolean = False
Dim [next] As String = ""
Dim i As Integer = 0
While Not added
'Build the column name and remove any unwanted characters.
Dim columnname As String = col + [next]
columnname = columnname.Replace("#", "")
columnname = columnname.Replace("'", "")
columnname = columnname.Replace("&", "")

'See if the column already exists
If Not result.Tables(TableName).Columns.Contains(columnname) Then
'if it doesn't then we add it here and mark it as added
result.Tables(TableName).Columns.Add(columnname)
added = True
Else
'if it did exist then we increment the sequencer and try again.
System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
[next] = "_" + i.ToString()
End If
End While
Next

'Read the rest of the data in the file.
Dim AllData As String = s.ReadToEnd()

'Split off each row at the Carriage Return/Line Feed
'Default line ending in most windows exports.
'You may have to edit this to match your particular file.
'This will work for Excel, Access, etc. default exports.
Dim rows As String() = AllData.Split(vbCr & vbLf.ToCharArray())

'Now add each row to the DataSet
Dim iCount As Integer = 0
For Each r As String In rows
iCount = iCount + 1
If iCount < rows.Length Then
'Split the row at the delimiter.
Dim items As String() = r.Split(delimiter.ToCharArray())
items = FixEmptyInArray(items)
'Add the item
result.Tables(TableName).Rows.Add(items)
End If
Next

'Return the imported data.
Return result
End Function


Public Shared Function FixEmptyInArray(ByVal TheArray As Object) As String()
'This function gives max value of int array without sorting an array
For i As Integer = 0 To UBound(TheArray) - 1
If Trim(TheArray(i)) = "" Then
TheArray(i) = Nothing
End If
Next
Return TheArray
End Function
End Class


''Source www.codeproject.com

Stored Procedure to search a string in
1. Tables like
2. In Columns (upto 9)

CREATEPROCEDURE [dbo].[zz_spSearchForValueInColumns_And_TablesLike]
@SearchString AS VARCHAR(255)
,@TableNameLike nvarchar(256)
,@Column1 nvarchar(256)
,@Column2 nvarchar(256) = NULL
,@Column3 nvarchar(256)=NULL
,@Column4 nvarchar(256)=NULL
,@Column5 nvarchar(256)=NULL
,@Column6 nvarchar(256)=NULL
,@Column7 nvarchar(256)=NULL
,@Column8 nvarchar(256)=NULL
,@Column9 nvarchar(256)=NULL
AS

/*variables*/
DECLARE @error int
Declare @SQuery varchar(MAX)

SET @TableNameLike='''%' + @TableNameLike+''''
--PRINT @TableNameLike

DECLARE @curBuildQuery CURSOR
DECLARE @MainTablename varchar(255)

DECLARE @sMainTableQuery nVARCHAR (MAX)
SET @sMainTableQuery=N' SET @curBuildQuery = CURSOR LOCAL READ_ONLY FOR '
SET @sMainTableQuery=@sMainTableQuery + ' SELECT [name] FROM SYS.TABLES WHERE [name] like '+@TableNameLike
SET @sMainTableQuery=@sMainTableQuery + ' AND [name] NOT like ''zz%'''
SET @sMainTableQuery=@sMainTableQuery + ' ORDER BY [name]'
SET @sMainTableQuery=@sMainTableQuery + ' ;OPEN @curBuildQuery'
--PRINT @sMainTableQuery;return
--DELETE FROM zzResults_main

EXEC sp_executesql
@sMainTableQuery,
N'@curBuildQuery cursor OUTPUT', @curBuildQuery OUTPUT

FETCH NEXT FROM @curBuildQuery INTO @MainTablename
--PRINT @MainTablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @MainTablename
-- DECLARE @SearchString AS VARCHAR(255)
-- SET @SearchCountryName='aa'
SET @SearchString=ISNULL(@SearchString,'')
SET @SearchString=REPLACE(@SearchString,'''','''''')
SET @sMainTableQuery ='INSERT INTO zzResults_main'
SET @sMainTableQuery =@sMainTableQuery + ' ('
SET @sMainTableQuery =@sMainTableQuery + '[Import_Table],[ERS_UID],[File_Name],[Run_Number],[File_seqno],[Seqno],[Job_code],[Council_Name],[S_code] '
SET @sMainTableQuery =@sMainTableQuery + ',[EA_code 1],[EA_code 2],[EA_code 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[PD_code],[poll_no],[barcode],[name],[on behalf of],[add1],[add2],[add3],[add4],[add5],[postcode]'
SET @sMainTableQuery =@sMainTableQuery + ',[UIM 1],[UIM 2],[UIM 3],[EA_text 1],[EA_text 2],[EA_text 3],[bpno 1],[bpno 2],[bpno 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[bpno_text],[papercount],[Election1_link],[Election2_link],[Election3_link]'
SET @sMainTableQuery =@sMainTableQuery + ',[BRE_Address1],[BRE_Address2],[BRE_Address3],[BRE_Address4],[BRE_Address5],[BRE_Address6],[BRE_Postcode],[BRE_Licence]'
SET @sMainTableQuery =@sMainTableQuery + ',[polling_day],[waiver],[Overseas_Address],[Anonymous],[Proxy],[Restricted_Franchise] '
SET @sMainTableQuery =@sMainTableQuery + ',Main_Table'
SET @sMainTableQuery =@sMainTableQuery + ')'
SET @sMainTableQuery =@sMainTableQuery + ' SELECT '
SET @sMainTableQuery =@sMainTableQuery + '[Import_Table],[ERS_UID],[File_Name],[Run_Number],[File_seqno],[Seqno],[Job_code],[Council_Name],[S_code] '
SET @sMainTableQuery =@sMainTableQuery + ',[EA_code 1],[EA_code 2],[EA_code 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[PD_code],[poll_no],[barcode],[name],[on behalf of],[add1],[add2],[add3],[add4],[add5],[postcode]'
SET @sMainTableQuery =@sMainTableQuery + ',[UIM 1],[UIM 2],[UIM 3],[EA_text 1],[EA_text 2],[EA_text 3],[bpno 1],[bpno 2],[bpno 3]'
SET @sMainTableQuery =@sMainTableQuery + ',[bpno_text],[papercount],[Election1_link],[Election2_link],[Election3_link]'
SET @sMainTableQuery =@sMainTableQuery + ',[BRE_Address1],[BRE_Address2],[BRE_Address3],[BRE_Address4],[BRE_Address5],[BRE_Address6],[BRE_Postcode],[BRE_Licence]'
SET @sMainTableQuery =@sMainTableQuery + ',[polling_day],[waiver],[Overseas_Address],[Anonymous],[Proxy],[Restricted_Franchise] '
SET @sMainTableQuery =@sMainTableQuery + ',''' + @MainTablename + ''''
SET @sMainTableQuery =@sMainTableQuery + ' FROM ' + @MainTablename
SET @sMainTableQuery=@sMainTableQuery+ ' WHERE '

if @Column1 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' CHARINDEX('''+ @SearchString +''','+@Column1+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column1+')>0 '
END


if @Column2 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column2+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column2+')>0 '
END

if @Column3 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column3+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column3+')>0 '
END

if @Column4 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column4+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column4+')>0 '
END

if @Column5 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column5+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column5+')>0 '
END

if @Column6 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column6+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column6+')>0 '
END

if @Column7 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column7+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column7+')>0 '
END

if @Column8 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column8+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column8+')>0 '
END

if @Column9 is not null
BEGIN
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ @SearchString +''','+@Column9+')>0 '
SET @sMainTableQuery=@sMainTableQuery+ ' OR CHARINDEX('''+ REPLACE(@SearchString,' ','') +''','+@Column9+')>0 '
END

--PRINT @MainTablename
PRINT @sMainTableQuery
EXEC sp_executesql @sMainTableQuery
FETCH NEXT FROM @curBuildQuery INTO @MainTablename
END

CLOSE @curBuildQuery
DEALLOCATE @curBuildQuery

--Drop table zzResults_main
--select * into zzResults_main from R350_main where 1<>1
--ALTER TABLE zzResults_main ADD main_table VARCHAR(255)


--/*** use temp table 'zzResults_main_temp'
--select * from zzResults_main
--delete from zzResults_main_temp
--select * from zzResults_main_temp
--select * into zzResults_main_temp from zzResults_main where 1<>1
--insert into zzResults_main_temp select * from zzResults_main

--Delete from zzResults_main
--zz_spSearchForValueInColumns_And_TablesLike 'BFPO 715','_main','add1','add2','add3','add4','add5','postcode'


SELECT * FROM zzResults_main
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

Web Site Accessibility XHTML Validation

Web Site Accessibility:
Guidelines
http://www.w3.org/TR/WCAG10/full-checklist.html
PDF version: http://www.w3.org/TR/WCAG10/checkpoint-list.pdf

XHTML Validation:
http://validator.w3.org/#validate_by_uri

extract email address from a string

Little quirky but worked fine.

below call will return email address.

dim email=getEmailAddress('Aslam M Iqbal , new read watson st, aslam.iqbal-test@certainweb.com 02004 425577')

Imports System.Text.RegularExpressions
Shared Function getEmailAddress(ByVal mystring As String) As String
Dim pattern As String
pattern = "[_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+"
Dim RegularExpressionObject As Match = Regex.Match(mystring, pattern)
getEmailAddress = RegularExpressionObject.ToString()
End Function

simple search for column name all tables in db

select *
from information_schema.columns
where column_name like '%column_name%'