How to unzip file?

Function Unzip(FileName as String) Dim Proc As System.Diagnostics.Process
Dim strArguments As String =
“”
Dim strDestination As String = String
.Empty
strDestination = “Path where you want to unzip your file on your computer”  &  FileName
strArguments =
“-o “ & strDestination

Proc = System.Diagnostics.Process.Start(
“C:\WinZip\wzunzip”
, strArguments)
‘Note give the path of zip software installed in your computer.
‘Suppose if it is install at “C:\Program files\Winzip\wzunzip” then replace above this path.
Proc.WaitForExit()

End Function

SQL SERVER 2005 – APPLY clause

The APPLY clause
 
The APPLY clause in SQL Server 2005 can be used on a table-valued function to be invoked for each row returned by the outer table expression.  Unlike SQL Server 2000, the order of the operation does matter with the APPLY clause in SQL Server 2005.
The simplified syntax of APPLY clause (T-SQL) is as follows.

SELECT column1, column2…
FROM outer_table_expression ote
CROSS APPLY|OUTER APPLY table_valued_function (ote.join_column)
From the above syntax we can see there are two types of APPLY clause available in SQL Server 2005: CROSS APPLY and OUTER APPLY.

CROSS APPLY is similar to INNER JOIN.  It returns only rows from the outer table that produce a result set from the table-valued function.  The table valued function in the CROSS APPLY clause also acts the correlated sub-query where the sub-query is executed repeatedly for each row of the outer query.

Listing 4

SELECT DeptName, EmpName, EmpSalary
FROM Departments d
CROSS APPLY fn_DepartmentEmployees (d.DeptId)
This query will list the employees’ names and salaries in all the departments that have one or more employees.  If a department does not have any employees allocated then that department will not appear in the result set.  To include such departments requires use of OUTER APPLY clause.

OUTER APPLY is similar to LEFT OUTER JOIN.  It returns all rows from the outer result set with NULL values in the columns of the table-valued function that do not produce a result set for the outer row.

Listing 5

SELECT DeptName, EmpName, EmpSalary
FROM Departments d
OUTER APPLY fn_DepartmentEmployees (d.DeptId)
 
 
Although this is a good feature, there is a performance issue associated with this enhancement.  If the outer table expression of the APPLY clause returns a huge result set, performance of the query may degrade due to the number of calls to the table-valued function.
 
Conclusion :
 
In SQL Server 2005 we can apply the APPLY operator if a table-valued function needs to be invoked for each row retuned by the outer table expression.

———————
SAMPLES
———————

— Start environemt setup : To be executed in SQL Server 2000 and 2005
— Create a test database
CREATE DATABASE MyTestDatabase
GO

USE MyTestDatabase
GO

— Create Departments table
CREATE TABLE Departments
(
 DeptId int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
 DeptName varchar(50)
)
GO

— Create Employees table
CREATE TABLE Employees
(
 EmpId int IDENTITY(1,1) NOT NULL PRIMARY KEY ,
 EmpName varchar(50) ,
 DeptId int CONSTRAINT FOREIGN_KEY REFERENCES Departments (DeptId) ,
 EmpSalary money
)
GO

SET NOCOUNT ON

— Populate Departments table with test data
INSERT INTO Departments (DeptName)
VALUES (‘HR’)

INSERT INTO Departments (DeptName)
VALUES (‘Payroll’)

INSERT INTO Departments (DeptName)
VALUES (‘Admin’)

— Populate Employees table with test data
INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘John’, 1, 5000)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Albert’, 1, 4500)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Crain’, 2, 6000)

INSERT INTO Employees (EmpName, DeptId, EmpSalary)
VALUES (‘Micheal’, 2, 5000)

SET NOCOUNT OFF

GO

— Create udf fn_DepartmentEmployees that accepts department id and
— returns the list of employee’s name and salary along with the department id.
CREATE FUNCTION fn_DepartmentEmployees (@DeptId int)
RETURNS @DeptEmp TABLE (DeptId int, EmpName varchar(50), EmpSalary money)
BEGIN
 INSERT INTO @DeptEmp (DeptId, EmpName, EmpSalary)
 SELECT DeptId, EmpName, EmpSalary
 FROM Employees
 WHERE DeptId = @DeptId

 RETURN
END
GO

— End  environemt setup

/*
— Execute listing 1, 2 and 3 in SQL Server 2000 for understanding table-valued function limitation

— Listing 1
SELECT DeptId, EmpName, EmpSalary
FROM fn_DepartmentEmployees (1)
GO

— Listing 2
SELECT DeptName, EmpName, EmpSalary
FROM fn_DepartmentEmployees (1) de
JOIN Departments d ON d.DeptId = de.DeptId
GO

— Listing 3
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
JOIN fn_DepartmentEmployees (d.DeptId) de ON d.DeptId = de.DeptId
GO
*/
/*
— Execute listings 4 & 5 in SQL Server 2005 to understand the new APPLY construct
— that overcomes the SQL Server 2000 limitation of table-valued function

— Listing 4
— CROSS APPLY query
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
CROSS APPLY fn_DepartmentEmployees (d.DeptId)
GO

— Listing 5
— OUTER APPLY query
SELECT DeptName, EmpName, EmpSalary
FROM Departments d
OUTER APPLY fn_DepartmentEmployees (d.DeptId)
GO
*/ 

SQL Server 2005 – How to shrink the truncated log file.

To shrink the Trucated Log file.

Following code always shrinks the Trucated Log File to minimum size possible.

USE Master
--You can replace with the database name but if it doesnt 
--works then replace with master databas)
GO DBCC SHRINKFILE(<TransactionLogName>, 1) 
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY 
DBCC SHRINKFILE(<TransactionLogName>, 1)

VB.NET – How to export excel data into text file.

I am writing two solution to acheive this task the second one is most recommended.

First: 

In first example i am using Excel.Application object

You need to import a reference of Microsoft.Office.Interof.excel

Most important thing, dont forget to close Excel Object otherwise it will be retain in the memory and that cause CONTEXTSWITCHDEADLOCK.

It only occurs when there is a delay in the processing or there is a huge excel file to read.

to resolve this problem follow these steps.

1 . Debug

2 . Exception

3. Managed Debugging Assistants

4. Disable ContextSwitchDeadLock.

——————————————————————————–

Public Sub ExportExcelIntoText(ByVal FullFileName As String)
Dim oXL As New Excel.Application
Dim oWBK As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim TextFilename As String = FullFileNameTextFilename = Left(TextFilename, Len(TextFilename) – 4)TextFilename += “.txt”
‘deleting a existing file
If File.Exists(TextFilename) Then
File.Delete(TextFilename)
End If
Dim fs As New FileStream(TextFilename, FileMode.Create, FileAccess.Write)
Dim sWrtier As New StreamWriter(fs)oWBK = oXL.Workbooks.Open(FullFileName)
oWS = oXL.Worksheets(1)
Try
Dim rowIndex As Int16 = 1
Dim colIndex As Int16 =1sWrtier.BaseStream.Seek(0, SeekOrigin.End)

For
rowIndex = 1 To 50
colIndex = 1
If Not (CType(oWS.Cells(rowIndex, colIndex), Excel.Range)).Value2 Is Nothing Then
For colIndex = 1 To 15
If (CType(oXL.Cells(rowIndex, colIndex), Excel.Range)).Value2 Is Nothing Then
sWrtier.Write(“” + vbTab)
Else
sWrtier.Write((CType(oXL.Cells(rowIndex, colIndex), Excel.Range)).Value2.ToString() + vbTab)
End If
Next
End If
sWrtier.WriteLine()
Next‘closing the file

Catch ex As Exception
Console.Write(ex.Message)
Finally
oWBK.Close()
oWBK = Nothing
oXL = Nothing
sWrtier.Close()
sWrtier.Dispose()
sWrtier = Nothing
End Try
End Sub
——————————————————————–

Second: In a below example i am reading excel file using OleDB and insert first into dataset and later writing into text file.Public Sub ExportExcelIntoText(ByVal FullFileName As String)
Dim TextFilename As String 
TextFilename = Left(TextFilename, Len(TextFilename) – 4) TextFilename += “.txt”
If File.Exists(TextFilename) Then
File.Delete(TextFilename)
End IfDim fs As FileStream
Dim sWrtier As StreamWriter
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSetds = Nothing
da = Nothing
sWrtier = Nothing
Try
fs = New FileStream(TextFilename, FileMode.Create, FileAccess.Write)
sWrtier = New StreamWriter(fs)
Dim cnn As New OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + FullFileName + “;Extended Properties=””Excel 8.0;HDR=YES;”””)
da = New OleDb.OleDbDataAdapter(“Select * from [Sheet1$]”, cnn)
ds =
New DataSet(“ExcelFile”)da.Fill(ds)
Dim rowIndex As DataRow
Dim colIndex As DataColumnsWrtier.BaseStream.Seek(0, SeekOrigin.End)
For Each rowIndex In ds.Tables(0).Rows
For Each colIndex In ds.Tables(0).Columns
If rowIndex(colIndex) Is Nothing Then
sWrtier.Write(“” + vbTab)
Elses
Wrtier.Write(rowIndex(colIndex).ToString + vbTab)
End If
Next
sWrtier.WriteLine()
Next
‘closing the file
Catch ex As Exception
Console.Write(ex.Message)
Finally
ds.Dispose()
ds = Nothing
da.Dispose()
da = Nothing
sWrtier.Close()
sWrtier.Dispose()
sWrtier = Nothing
End Try
End Sub
—————————————

I would appreciate your comments.

regards,

Rana