Sleep Function in SQL Server
T-SQL Statement WaitFor Delay can be used to set the sleep time in SQL batch. The following example sets one-minute sleep time
print getdate()
waitfor delay '00:01'
print getdate()
Here is the output:
Sep 15 2009 6:43AM
Sep 15 2009 6:44AM
While loop in T-SQL
Here is a simple example of While loop
Declare @temp tinyint = 1
while (@temp < 11)
begin
print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)
set @temp += 1
end
The above code will be having the following output:
Output
1 X 2 = 2
2 X 2 = 4
3 X 2 = 6
4 X 2 = 8
5 X 2 = 10
6 X 2 = 12
7 X 2 = 14
8 X 2 = 16
9 X 2 = 18
10 X 2 = 20
This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table
declare #myi int
Should be replaced by
declare @myi int
Compound operators execute some operation and set an original value to the result of the operation
Declare @temp tinyint
Select @temp = 1
Set @temp += @temp
print @temp
The following is a list of compound operators
Operator | Link to more information | Action |
+= | += (Add EQUALS) (Transact-SQL) | Adds some amount to the original value and sets the original value to the result. |
-= | -= (Subtract EQUALS) (Transact-SQL) | Subtracts some amount from the original value and sets the original value to the result. |
*= | *= (Multiply EQUALS) (Transact-SQL) | Multiplies by an amount and sets the original value to the result. |
/= | /= (Divide EQUALS) (Transact-SQL) | Divides by an amount and sets the original value to the result. |
%= | %= (Modulo EQUALS) (Transact-SQL) | Divides by an amount and sets the original value to the modulo. |
&= | &= (Bitwise AND EQUALS) (Transact-SQL) | Performs a bitwise AND and sets the original value to the result. |
^= | ^= (Bitwise Exclusive OR EQUALS) (Transact-SQL) | Performs a bitwise exclusive OR and sets the original value to the result. |
= | = (Bitwise OR EQUALS) (Transact-SQL) | Performs a bitwise OR and sets the original value to the result. |
Some of the new data that are introduced in Microsoft SQL Server 2008 are :
Here is a simple self-explanatory example:
declare @date date = getdate()
declare @time time = getdate()
declare @dt2 datetime2 = getdate()
declare @dt datetime = getdate()
declare @sdt smalldatetime = getdate()
print 'Date ' + cast(@date as char)
print 'Time ' + cast(@time as char)
print 'SmallDateTime ' + cast(@sdt as char)
print 'DateTime ' + cast(@dt as char)
print 'DateTime2 ' + cast(@dt2 as char)
Here is the output of the same:
Date 2009-09-04
Time 16:18:54.5730000
SmallDateTime Sep 4 2009 4:19PM
DateTime Sep 4 2009 4:18PM
DateTime2 2009-09-04 16:18:54.5730000
Modulo provides the integer remainder after dividing the first numeric expression by the second one.
print 'remainder ' + cast(112 % 17 as char)
ADO Connection String for Access 2007 Database
Here is the ADO connection string example for Access 2007 database.
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"
If the database needs password then use the following string:
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb; Jet OLEDB:Database Password=MyDbPassword;"
The project that uses the code should have reference to ActiveX data objects library
The following code reads the CSV file to an array. LINQ is used to loop through the array and the contents are written as XML using XElement (System.XML.Linq).
public void ConvertCSVToXML()
{
String[] FileContent = File.ReadAllLines(@"C:\Temp\vba.csv");
String XMLNS = "";
XElement Inv = new XElement("Invoice",
from items in FileContent
let fields = items.Split(',')
select new XElement("Item",
new XElement("ID", fields[0]),
new XElement("Name", fields[1]),
new XElement("Price", fields[2]),
new XElement("Availability", fields[3]),
new XElement("TotalPrice", fields[4])
)
);
File.WriteAllText(@"C:\Temp\vba.xml", XMLNS + Inv.ToString() );
}
C# and LINQ can be combined to do wonders; here is a sample of that. The following snippet extracts the files that are more thsn 345 KB
private static void GetFilesFromDirectory(string DirPath)
{
try
{
DirectoryInfo Dir = new DirectoryInfo(DirPath);
FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );
var query = from FI in FileList
where FI.Length > 340000
select FI.FullName + " " + FI.Length.ToString() ;
foreach (string s1 in query )
{
Console.WriteLine(s1);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message );
}
}
Extract files based on Size in C#, Extract files greater than 1GB using C#, C# List files based on Size, Filter by FileSize using C#, Extract files based on Size in .NET, Extract files greater than 1GB using .NET, .NET List files based on Size,
Here is a snippet using C# and LINQ, which will retrieve the files from a directory for a specific date
private static void GetFilesFromDirectory(string DirPath)
{
try
{
DirectoryInfo Dir = new DirectoryInfo(DirPath);
FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );
var query = from FI in FileList
where FI.LastWriteTime.Date == DateTime.Now.Date
select FI.FullName + " " + FI.LastWriteTime;
foreach (string s1 in query )
{
Console.WriteLine(s1);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message );
}
}
The following code will retrieve the read-only files using LINQ and C#
private static void GetFilesFromDirectory(string DirPath)
{
try
{
DirectoryInfo Dir = new DirectoryInfo(DirPath);
FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );
var query = from FI in FileList
where FI.IsReadOnly == true
select FI.FullName + " " + FI.LastWriteTime;
foreach (string s1 in query )
{
Console.WriteLine(s1);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message );
}
}
The above can (and mostly is) done by the following way
FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );
foreach (FileInfo F1 in FileList)
{
if (F1.IsReadOnly == true)
{
Console.WriteLine(F1.FullName );
}
}
List ReadOnly files using C#, Retrieve Read-Only files using C#, Get Read Only Files using C#, List ReadOnly files using .NET, Retrieve Read-Only files using .NET, Get Read Only Files using .NET
