A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.
The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.
SET NOCOUNT ON
CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')
-- Results --Apr 28 2001 9:56AM
When to Use Triggers
There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.
Thursday, September 24, 2009
Wednesday, September 23, 2009
SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function
/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSPGO
/* SELECT from TempTable */
SELECT *FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue
INTINSERT INTO @retTestFnSELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSPGO
/* SELECT from TempTable */
SELECT *FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue
INTINSERT INTO @retTestFnSELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO
Wednesday, July 8, 2009
Date Format using CONVERT function in SQL Server
Today, i would like to list out various styles of date display formats. These are very helpful for the Front-end application programmers where they want to display different date formats.
Various date style formats can be produced with CONVERT function.
CONVERT function need 3 parameters.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The first parameter is return data type, second parameter is expression, third parameter which is optional parameter defines the style.
NOTE: Do not apply CONVERT function on date column when it is Indexed.
with out waiting lets look at the various styles of date values.
The below query produces the output in
MON DD YYYY HH:MIAMPM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),100)
--Output (MON DD YYYY HH:MIAMPM)
--Jul 7 2009 2:19PM
The below query produces the output in MM/DD/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),101)
--Output (MM/DD/YYYY)
--07/07/2009
The below query produces the output in YYYY.MM.DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),102)
--Output (YYYY.MM.DD)
--2009.07.07
The below query produces the output in DD/MM/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),103)
--Output (DD/MM/YYYY)
--06/07/2009
The below query produces the output in DD.MM.YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),104)
--Output (DD.MM.YYYY)
--06.07.2009
The below query produces the output in DD-MM-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),105)
--Output (DD-MM-YYYY)
--06-07-2009
The below query produces the output in DD MON YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),106)
--Output (DD MON YYYY)
--06 Jul 2009
The below query produces the output in MON DD,YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),107)
--Output (MON DD,YYYY)
--Jul 06, 2009
The below query produces the output in HH24:MI:SS format.
SELECT CONVERT(VARCHAR(30),GETDATE(),108)
--Output (HH24:MI:SS)
--14:24:20
The below query produces the output in MON DD YYYY HH:MI:SS:NNN AMPM format.
Use 113 style to get date and time with nano seconds in AM/PM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
--Output (MON DD YYYY HH:MI:SS:NNN AMPM)
--Jul 7 2009 2:24:35:490PM
The below query produces the output in MM-DD-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),110)
--Output (MM-DD-YYYY)
-- 07-07-2009
The below query produces the output in YYYY/MM/DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),111)
--Output (YYYY/MM/DD)
--2009/07/07
The below query produces the output in YYYYMMDD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),112)
--Output (YYYYMMDD)
--20090707
The below query produces the output in MON DD YYYY HH24:MI:SS:NNN format.
Use 113 style to get date and time with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
--Output (MON DD YYYY HH24:MI:SS:NNN)
--07 Jul 2009 14:26:24:617
The below query produces the output in HH24:MI:SS:NNN format.
Use 114 Style to extract Time part with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),114)
--Output (HH24:MI:SS:NNN)
--14:26:48:953
Various date style formats can be produced with CONVERT function.
CONVERT function need 3 parameters.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The first parameter is return data type, second parameter is expression, third parameter which is optional parameter defines the style.
NOTE: Do not apply CONVERT function on date column when it is Indexed.
with out waiting lets look at the various styles of date values.
The below query produces the output in
MON DD YYYY HH:MIAMPM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),100)
--Output (MON DD YYYY HH:MIAMPM)
--Jul 7 2009 2:19PM
The below query produces the output in MM/DD/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),101)
--Output (MM/DD/YYYY)
--07/07/2009
The below query produces the output in YYYY.MM.DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),102)
--Output (YYYY.MM.DD)
--2009.07.07
The below query produces the output in DD/MM/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),103)
--Output (DD/MM/YYYY)
--06/07/2009
The below query produces the output in DD.MM.YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),104)
--Output (DD.MM.YYYY)
--06.07.2009
The below query produces the output in DD-MM-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),105)
--Output (DD-MM-YYYY)
--06-07-2009
The below query produces the output in DD MON YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),106)
--Output (DD MON YYYY)
--06 Jul 2009
The below query produces the output in MON DD,YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),107)
--Output (MON DD,YYYY)
--Jul 06, 2009
The below query produces the output in HH24:MI:SS format.
SELECT CONVERT(VARCHAR(30),GETDATE(),108)
--Output (HH24:MI:SS)
--14:24:20
The below query produces the output in MON DD YYYY HH:MI:SS:NNN AMPM format.
Use 113 style to get date and time with nano seconds in AM/PM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
--Output (MON DD YYYY HH:MI:SS:NNN AMPM)
--Jul 7 2009 2:24:35:490PM
The below query produces the output in MM-DD-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),110)
--Output (MM-DD-YYYY)
-- 07-07-2009
The below query produces the output in YYYY/MM/DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),111)
--Output (YYYY/MM/DD)
--2009/07/07
The below query produces the output in YYYYMMDD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),112)
--Output (YYYYMMDD)
--20090707
The below query produces the output in MON DD YYYY HH24:MI:SS:NNN format.
Use 113 style to get date and time with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
--Output (MON DD YYYY HH24:MI:SS:NNN)
--07 Jul 2009 14:26:24:617
The below query produces the output in HH24:MI:SS:NNN format.
Use 114 Style to extract Time part with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),114)
--Output (HH24:MI:SS:NNN)
--14:26:48:953
Tuesday, July 7, 2009
Information Schema Views in SQL Server
In this article, I would like to list out the SQL Server metadata tables which we need in our day-to-day work.
SELECT * FROM Information_Schema.CHECK_CONSTRAINTS
-- Contains one row for each CHECK constraint in the current database.
SELECT * FROM Information_Schema.COLUMN_DOMAIN_USAGE
-- Contains one row for each column, in the current database, that has a user-defined data type.
SELECT * FROM Information_Schema.COLUMN_PRIVILEGES
--Contains one row for each column with a privilege either granted to or by the current user in the current database
SELECT * FROM Information_Schema.COLUMNS
--Contains one row for each column accessible to the current user in the current database.
SELECT * FROM Information_Schema.CONSTRAINT_COLUMN_USAGE
--Contains one row for each column, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.CONSTRAINT_TABLE_USAGE
--Contains one row for each table, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.DOMAIN_CONSTRAINTS
--Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it.
SELECT * FROM Information_Schema.DOMAINS
--Contains one row for each user-defined data type accessible to the current user in the current database.
SELECT * FROM Information_Schema.KEY_COLUMN_USAGE
--Contains one row for each column, in the current database, that is constrained as a key.
SELECT * FROM Information_Schema.PARAMETERS
--Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
SELECT * FROM Information_Schema.REFERENTIAL_CONSTRAINTS
--Contains one row for each foreign constraint in the current database.
SELECT * FROM Information_Schema.ROUTINE_COLUMNS
--Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
SELECT * FROM Information_Schema.ROUTINES
--Contains one row for each stored procedure and function accessible to the current user in the current database.
SELECT * FROM Information_Schema.SCHEMATA
--Contains one row for each database that has permissions for the current user.
SELECT * FROM Information_Schema.TABLE_CONSTRAINTS
--Contains one row for each table constraint in the current database.
SELECT * FROM Information_Schema.TABLE_PRIVILEGES
--Contains one row for each table privilege granted to or by the current user in the current database.
SELECT * FROM Information_Schema.TABLES
--Contains one row for each table in the current database for which the current user has permissions.
SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE
--Contains one row for each column, in the current database, used in a view definition.
SELECT * FROM Information_Schema.VIEW_TABLE_USAGE
--Contains one row for each table, in the current database, used in a view.
SELECT * FROM Information_Schema.VIEWS
--Contains one row for views accessible to the current user in the current database.
SELECT * FROM Information_Schema.CHECK_CONSTRAINTS
-- Contains one row for each CHECK constraint in the current database.
SELECT * FROM Information_Schema.COLUMN_DOMAIN_USAGE
-- Contains one row for each column, in the current database, that has a user-defined data type.
SELECT * FROM Information_Schema.COLUMN_PRIVILEGES
--Contains one row for each column with a privilege either granted to or by the current user in the current database
SELECT * FROM Information_Schema.COLUMNS
--Contains one row for each column accessible to the current user in the current database.
SELECT * FROM Information_Schema.CONSTRAINT_COLUMN_USAGE
--Contains one row for each column, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.CONSTRAINT_TABLE_USAGE
--Contains one row for each table, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.DOMAIN_CONSTRAINTS
--Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it.
SELECT * FROM Information_Schema.DOMAINS
--Contains one row for each user-defined data type accessible to the current user in the current database.
SELECT * FROM Information_Schema.KEY_COLUMN_USAGE
--Contains one row for each column, in the current database, that is constrained as a key.
SELECT * FROM Information_Schema.PARAMETERS
--Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
SELECT * FROM Information_Schema.REFERENTIAL_CONSTRAINTS
--Contains one row for each foreign constraint in the current database.
SELECT * FROM Information_Schema.ROUTINE_COLUMNS
--Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
SELECT * FROM Information_Schema.ROUTINES
--Contains one row for each stored procedure and function accessible to the current user in the current database.
SELECT * FROM Information_Schema.SCHEMATA
--Contains one row for each database that has permissions for the current user.
SELECT * FROM Information_Schema.TABLE_CONSTRAINTS
--Contains one row for each table constraint in the current database.
SELECT * FROM Information_Schema.TABLE_PRIVILEGES
--Contains one row for each table privilege granted to or by the current user in the current database.
SELECT * FROM Information_Schema.TABLES
--Contains one row for each table in the current database for which the current user has permissions.
SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE
--Contains one row for each column, in the current database, used in a view definition.
SELECT * FROM Information_Schema.VIEW_TABLE_USAGE
--Contains one row for each table, in the current database, used in a view.
SELECT * FROM Information_Schema.VIEWS
--Contains one row for views accessible to the current user in the current database.
Thursday, July 2, 2009
Interview Questions
.Net Interview Questions
ASP.NETASP.Net Interview Questions - Part 1
ASP.Net Interview Questions - Part 2
ASP.Net Interview Questions - Part 3
ASP.Net Interview Questions - Part 4
ASP.Net Interview Questions - Part 5
ASP.Net Interview Questions - Part 6
ASP.Net Interview Questions - Part 7
ASP.Net Interview Questions - Part 8
ASP.Net Interview Questions - Part 9
ASP.Net Interview Questions - Part 10
ASP.Net Interview Questions - Part 11
ASP.Net Interview Questions - Part 12
C# C# Interview Questions
Tuesday, June 30, 2009
Delete Duplicate records in sql server
WITH CTE (SecondCol,ThirdCol, DuplicateCount)
AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
how to remove the LOGFILE and perform some operations from cmd prompt
SELECT name,recovery_model_desc FROM sys.databases
and check whether the database is 'FULL' mode or 'SIMPLE'..if it s FULL we have to remove the LOGFILE of database.
go to database ...> detach.. next goto logfile and rename it..of that database
again we have to attach the only .mdf file (instead of .mdf and .ldf files to database)
=====================
sqlcmd -?
we can change the sqlserver sa password from cmd prompt very easily
EXEC SP_PASSWORD NULL,'venki','sa'
GO
and check whether the database is 'FULL' mode or 'SIMPLE'..if it s FULL we have to remove the LOGFILE of database.
go to database ...> detach.. next goto logfile and rename it..of that database
again we have to attach the only .mdf file (instead of .mdf and .ldf files to database)
=====================
sqlcmd -?
we can change the sqlserver sa password from cmd prompt very easily
EXEC SP_PASSWORD NULL,'venki','sa'
GO
Friday, June 26, 2009
Bind the Help To Project and Binding datasource to report viewver
Dim hlppath As String = Application.StartupPath & "\Ayuda\Espiga.chm"
System.Windows.Forms.Help.ShowHelp(Me, hlppath)
Private Sub InitReports(ByVal ReportName As String, ByVal DS As DataSet, ByVal ParameterName() As String, ByVal ParameterValue() As String)
Try
Dim ParamList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
Dim reportDatasource As New Microsoft.Reporting.WinForms.ReportDataSource
ReportViewer1.Clear()
ReportViewer1.Reset()
'Add Tables to LocalReport's DataSource
For Each table As DataTable In DS.Tables
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource(DS.DataSetName & "_" & table.TableName, table))
Next
'Load parameters
For i As Integer = 0 To ParameterName.Length - 1
ParamList.Add(New Microsoft.Reporting.WinForms.ReportParameter(ParameterName(i).Trim, ParameterValue(i).Trim))
Next i
'Embed the LocalReport to the ReportViewer
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = Me.GetType().Namespace & "." & ReportName
'Set the parameters to the LocalReport
Me.ReportViewer1.LocalReport.SetParameters(ParamList)
Me.ReportViewer1.RefreshReport()
Catch ex As Exception
End Try
End Sub
System.Windows.Forms.Help.ShowHelp(Me, hlppath)
Private Sub InitReports(ByVal ReportName As String, ByVal DS As DataSet, ByVal ParameterName() As String, ByVal ParameterValue() As String)
Try
Dim ParamList As New Generic.List(Of Microsoft.Reporting.WinForms.ReportParameter)
Dim reportDatasource As New Microsoft.Reporting.WinForms.ReportDataSource
ReportViewer1.Clear()
ReportViewer1.Reset()
'Add Tables to LocalReport's DataSource
For Each table As DataTable In DS.Tables
Me.ReportViewer1.LocalReport.DataSources.Clear()
Me.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource(DS.DataSetName & "_" & table.TableName, table))
Next
'Load parameters
For i As Integer = 0 To ParameterName.Length - 1
ParamList.Add(New Microsoft.Reporting.WinForms.ReportParameter(ParameterName(i).Trim, ParameterValue(i).Trim))
Next i
'Embed the LocalReport to the ReportViewer
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = Me.GetType().Namespace & "." & ReportName
'Set the parameters to the LocalReport
Me.ReportViewer1.LocalReport.SetParameters(ParamList)
Me.ReportViewer1.RefreshReport()
Catch ex As Exception
End Try
End Sub
Export the Grid data to Excel
Public Sub Export2Excel(ByVal grdView As DataGridView, ByVal fileName As String)
Dim myFile As String = fileName
Dim subtractBy As Integer, cellText As String
Dim fs As IO.StreamWriter = Nothing
Try
' Open the file and write the headers
fs = New IO.StreamWriter(myFile, False)
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("")
' Create the styles for the worksheet
fs.WriteLine("")
' Style for the column headers
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("
fs.WriteLine("")
fs.WriteLine(" ")
' Style for the column information
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine(" ")
fs.WriteLine(" ")
' Write the worksheet contents
fs.WriteLine("")
fs.WriteLine("")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine("")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("" & _
"{0} ", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" ")
' Check for an empty row at the end due to Adding allowed on the DataGridView
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format("", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = ""
If grdView.Item(intCol, i).Value IsNot DBNull.Value Then cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format("" & _
"{0} ", _
cellText.ToString))
Next
fs.WriteLine(" ")
Next
' Close up the document
fs.WriteLine(" ")
fs.WriteLine(" ")
fs.WriteLine(" ")
Catch ex As Exception
Throw ex
Finally
If fs IsNot Nothing Then fs.Close()
End Try
End Sub
Dim myFile As String = fileName
Dim subtractBy As Integer, cellText As String
Dim fs As IO.StreamWriter = Nothing
Try
' Open the file and write the headers
fs = New IO.StreamWriter(myFile, False)
fs.WriteLine("")
fs.WriteLine("")
fs.WriteLine("
' Create the styles for the worksheet
fs.WriteLine("
' Style for the column headers
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
' Style for the column information
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
' Write the worksheet contents
fs.WriteLine("
fs.WriteLine("
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("
grdView.Columns.Item(i).Width))
Next
fs.WriteLine("
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("
"
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine("
' Check for an empty row at the end due to Adding allowed on the DataGridView
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format("
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = ""
If grdView.Item(intCol, i).Value IsNot DBNull.Value Then cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format("
"
cellText.ToString))
Next
fs.WriteLine("
Next
' Close up the document
fs.WriteLine("
fs.WriteLine("
fs.WriteLine("
Catch ex As Exception
Throw ex
Finally
If fs IsNot Nothing Then fs.Close()
End Try
End Sub
Wednesday, June 10, 2009
Date Time patters converting in datagridview
Private Sub UcdgvAuxiliary_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles UcdgvAuxiliary.DataError
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception
Date Time patters converting in datagridview
Private Sub UcdgvAuxiliary_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles UcdgvAuxiliary.DataError
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception
Try
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).ValueType.Name = "DateTime" Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim myDT As New System.Globalization.DateTimeFormatInfo
myDT.ShortDatePattern = "ddMMyyyy"
Dim dt As DateTime = Date.Parse(UcdgvAuxiliary.EditingControl.Text, myDT)
UcdgvAuxiliary.EditingControl.Text = dt
UcdgvAuxiliary.EndEdit()
End If
End If
End If
Catch ex As Exception
Friday, June 5, 2009
Binding Source- and Binding navigator
Public Sub New(ByVal NotesSrc As DataSet)
' This call is required by the Windows Form Designer.
InitializeComponent()
Try
dsNotesSrc = NotesSrc
BS = New BindingSource(dsNotesSrc, dsNotesSrc.Tables(0).TableName)
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)
DefaultView = dsNotesSrc.Tables(0).DefaultView
Catch ex As Exception
ex.Message.ToString()
End Try
End Sub
Binding Navigator
Private Sub FrmViewNotes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblfrm.Text = "Administrador del sistema"
dsUsers = conFacade.GetUsers()
lblDte.Text = DateTime.Now.ToString()
Me.MdiParent = FrmMDI
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(DefaultView).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveNextItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveNextItem.Click
BS.MoveNext()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveLastItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveLastItem.Click
BS.MoveLast()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveFirstItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveFirstItem.Click
BS.MoveFirst()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMovePreviousItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMovePreviousItem.Click
BS.MovePrevious()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub ShowUserTo()
If txtNote.Text <> "" Then
Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
Dim drUsers() As DataRow = dsUsers.Tables(0).Select("UserId='" + drNotesSrc(0).Item("UserTo").ToString() + "'")
lblTo3.Text = drUsers(0).Item("Name").ToString
Else
lblTo3.Text = String.Empty
End If
End Sub
Private Sub btnDeleteNote_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteNote.Click
Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
ParentClass.DeleteNote(drNotesSrc(0).Item("Note").ToString())
dsNotesSrc = BS.DataSource
dsNotesSrc.Tables(0).Rows.RemoveAt(txtNote.BindingContext(BS).Position)
txtNote.DataBindings.Clear()
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)
' BindingNavigatorMovePreviousItem_Click(sender, e)
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
If dsNotesSrc.Tables(0).Rows.Count = 0 Then
ParentClass.MyParent.ViewNoteItemEnabled = False
ParentClass.ImgNotes.Visible = False
Me.Close()
End If--
End Sub
Private Sub btnDeleteall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteall.Click
If dsNotesSrc.Tables(0).Rows.Count <> 0 Then
For i As Integer = 1 To dsNotesSrc.Tables(0).Rows.Count
btnDeleteNote_Click(sender, e)
Next
End If
End Sub
' This call is required by the Windows Form Designer.
InitializeComponent()
Try
dsNotesSrc = NotesSrc
BS = New BindingSource(dsNotesSrc, dsNotesSrc.Tables(0).TableName)
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)
DefaultView = dsNotesSrc.Tables(0).DefaultView
Catch ex As Exception
ex.Message.ToString()
End Try
End Sub
Binding Navigator
Private Sub FrmViewNotes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblfrm.Text = "Administrador del sistema"
dsUsers = conFacade.GetUsers()
lblDte.Text = DateTime.Now.ToString()
Me.MdiParent = FrmMDI
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(DefaultView).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveNextItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveNextItem.Click
BS.MoveNext()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveLastItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveLastItem.Click
BS.MoveLast()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMoveFirstItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveFirstItem.Click
BS.MoveFirst()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub BindingNavigatorMovePreviousItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMovePreviousItem.Click
BS.MovePrevious()
ShowUserTo()
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
End Sub
Private Sub ShowUserTo()
If txtNote.Text <> "" Then
Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
Dim drUsers() As DataRow = dsUsers.Tables(0).Select("UserId='" + drNotesSrc(0).Item("UserTo").ToString() + "'")
lblTo3.Text = drUsers(0).Item("Name").ToString
Else
lblTo3.Text = String.Empty
End If
End Sub
Private Sub btnDeleteNote_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteNote.Click
Dim drNotesSrc() As DataRow = dsNotesSrc.Tables(0).Select("NoteText='" + txtNote.Text + "'")
ParentClass.DeleteNote(drNotesSrc(0).Item("Note").ToString())
dsNotesSrc = BS.DataSource
dsNotesSrc.Tables(0).Rows.RemoveAt(txtNote.BindingContext(BS).Position)
txtNote.DataBindings.Clear()
txtNote.DataBindings.Add("Text", BS, dsNotesSrc.Tables(0).Columns("NoteText").ColumnName)
' BindingNavigatorMovePreviousItem_Click(sender, e)
BindingNavigatorPositionItem.Text = "Note " & txtNote.BindingContext(BS).Position + 1 & " Of " & txtNote.BindingContext(DefaultView).Count
If dsNotesSrc.Tables(0).Rows.Count = 0 Then
ParentClass.MyParent.ViewNoteItemEnabled = False
ParentClass.ImgNotes.Visible = False
Me.Close()
End If--
End Sub
Private Sub btnDeleteall_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteall.Click
If dsNotesSrc.Tables(0).Rows.Count <> 0 Then
For i As Integer = 1 To dsNotesSrc.Tables(0).Rows.Count
btnDeleteNote_Click(sender, e)
Next
End If
End Sub
Thursday, June 4, 2009
Standard code for Getting Data- VB.Net- Raides
Public Function GetData(Optional ByVal Filter As String = "") As DataSet
Try
Me.OpenConnection()
Dim TableParts() As String = Nothing
If Regex.IsMatch(Table, "\w+(?:\.\w+)+", RegexOptions.Singleline) Then
TableParts = Table.Split(".")
Else
ReDim TableParts(0)
TableParts(0) = Table
End If
Dim sqlstr As String = "SELECT * FROM "
For i As Integer = 0 To TableParts.Length - 1
sqlstr &= "[" & TableParts(i) & "]."
Next
sqlstr = sqlstr.TrimEnd(".") & IIf(Filter <> "", " WHERE " & Filter, "")
Dim ds As New DataSet
Dim cmd As New SqlCommand(sqlstr, Me.connection)
Dim Da As New SqlDataAdapter(cmd)
Da.Fill(ds, Table)
Return ds
Catch ex As Exception
'Throw New Exception(ex.Message)
Return Nothing
Finally
Me.CloseConnection()
End Try
End Function
Try
Me.OpenConnection()
Dim TableParts() As String = Nothing
If Regex.IsMatch(Table, "\w+(?:\.\w+)+", RegexOptions.Singleline) Then
TableParts = Table.Split(".")
Else
ReDim TableParts(0)
TableParts(0) = Table
End If
Dim sqlstr As String = "SELECT * FROM "
For i As Integer = 0 To TableParts.Length - 1
sqlstr &= "[" & TableParts(i) & "]."
Next
sqlstr = sqlstr.TrimEnd(".") & IIf(Filter <> "", " WHERE " & Filter, "")
Dim ds As New DataSet
Dim cmd As New SqlCommand(sqlstr, Me.connection)
Dim Da As New SqlDataAdapter(cmd)
Da.Fill(ds, Table)
Return ds
Catch ex As Exception
'Throw New Exception(ex.Message)
Return Nothing
Finally
Me.CloseConnection()
End Try
End Function
Wednesday, May 13, 2009
Cell Validating in Datagridview
Private Sub UcdgvAuxiliary_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles UcdgvAuxiliary.CellValidating
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).Name = "StartDate" AndAlso UcdgvAuxiliary.CurrentCell.IsInEditMode = True Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim ss As String = System.Text.RegularExpressions.Regex.Replace(UcdgvAuxiliary.EditingControl.Text, "^(((0[1-9][12]\d3[01])\/(0[13578]1[02])\/((1[6-9][2-9]\d)\d{2}))((0[1-9][12]\d30)\/(0[13456789]1[012])\/((1[6-9][2-9]\d)\d{2}))((0[1-9]1\d2[0-8])\/02\/((1[6-9][2-9]\d)\d{2}))(29\/02\/((1[6-9][2-9]\d)(0[48][2468][048][13579][26])((16[2468][048][3579][26])00))))$", "")
UcdgvAuxiliary.CurrentCell.Value = ss
UcdgvAuxiliary.EndEdit()
End If
End If
End If
End Sub
If UcdgvAuxiliary.Columns(UcdgvAuxiliary.CurrentCell.ColumnIndex).Name = "StartDate" AndAlso UcdgvAuxiliary.CurrentCell.IsInEditMode = True Then
If Not UcdgvAuxiliary.EditingControl Is Nothing Then
If Not UcdgvAuxiliary.EditingControl.Text = "" Then
Dim ss As String = System.Text.RegularExpressions.Regex.Replace(UcdgvAuxiliary.EditingControl.Text, "^(((0[1-9][12]\d3[01])\/(0[13578]1[02])\/((1[6-9][2-9]\d)\d{2}))((0[1-9][12]\d30)\/(0[13456789]1[012])\/((1[6-9][2-9]\d)\d{2}))((0[1-9]1\d2[0-8])\/02\/((1[6-9][2-9]\d)\d{2}))(29\/02\/((1[6-9][2-9]\d)(0[48][2468][048][13579][26])((16[2468][048][3579][26])00))))$", "")
UcdgvAuxiliary.CurrentCell.Value = ss
UcdgvAuxiliary.EndEdit()
End If
End If
End If
End Sub
Tuesday, May 5, 2009
To commit the cahnges in DataGridView
to commit the cahnges in DataGridView
UcdgvAuxiliary.BeginEdit(True)
UcdgvAuxiliary.EndEdit(CType(True, DataGridViewDataErrorContexts))
UcdgvAuxiliary.CommitEdit(DataGridViewDataErrorContexts.Commit)
UcdgvAuxiliary.BeginEdit(True)
UcdgvAuxiliary.EndEdit(CType(True, DataGridViewDataErrorContexts))
UcdgvAuxiliary.CommitEdit(DataGridViewDataErrorContexts.Commit)
to commit the cahnges in DataGridView and DataBindings
to commit the cahnges in DataGridView
UcdgvAuxiliary.BeginEdit(True)
UcdgvAuxiliary.EndEdit(CType(True, DataGridViewDataErrorContexts))
UcdgvAuxiliary.CommitEdit(DataGridViewDataErrorContexts.Commit)
=================================================
ucbtnAuxiliary.BindingContext(dsAuxilKeys, dsAuxilKeys.Tables(0).TableName).EndCurrentEdit()
ucbtnsConcept.BindingContext(dsAuxilKeys, dsAuxilKeys.Tables(0).TableName).EndCurrentEdit()
UcdgvAuxiliary.BeginEdit(True)
UcdgvAuxiliary.EndEdit(CType(True, DataGridViewDataErrorContexts))
UcdgvAuxiliary.CommitEdit(DataGridViewDataErrorContexts.Commit)
=================================================
ucbtnAuxiliary.BindingContext(dsAuxilKeys, dsAuxilKeys.Tables(0).TableName).EndCurrentEdit()
ucbtnsConcept.BindingContext(dsAuxilKeys, dsAuxilKeys.Tables(0).TableName).EndCurrentEdit()
Sql Server material downloads
Hi
Here these are Sql Server Marerial download links
http://www.ziddu.com/download/4552118/MsSQLServer2000.rar.html
http://www.ziddu.com/download/4351815/SQLServer2005_SSMSEE.rar.html
http://www.ziddu.com/download/4277299/DatabaseSQLServer.rar.html
http://www.ziddu.com/download/4277299/DatabaseSQLServer.rar.html
http://www.ziddu.com/download/4235316/AdministratorsGuideToMicrosoftSQLServer20052006.rar.html
http://www.ziddu.com/download/4235316/AdministratorsGuideToMicrosoftSQLServer20052006.rar.html
Here these are Sql Server Marerial download links
http://www.ziddu.com/download/4552118/MsSQLServer2000.rar.html
http://www.ziddu.com/download/4351815/SQLServer2005_SSMSEE.rar.html
http://www.ziddu.com/download/4277299/DatabaseSQLServer.rar.html
http://www.ziddu.com/download/4277299/DatabaseSQLServer.rar.html
http://www.ziddu.com/download/4235316/AdministratorsGuideToMicrosoftSQLServer20052006.rar.html
http://www.ziddu.com/download/4235316/AdministratorsGuideToMicrosoftSQLServer20052006.rar.html
Monday, April 13, 2009
Regarding joins in sql server
can visit http://blog.sqlauthority.com/
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOINThis join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
RIGHT OUTER JOINThis join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
FULL OUTER JOINThis join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.
Additional Notes related to JOIN:
The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*FROM Table1 t1WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)GO
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.
/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.ID IS NULL
The above example can also be created using Right Outer Join.
NOT INNER JOINRemember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.
USE AdventureWorksGOCREATE TABLE table1(ID INT, Value VARCHAR(10))INSERT INTO Table1 (ID, Value)SELECT 1,‘First’UNION ALLSELECT 2,‘Second’UNION ALLSELECT 3,‘Third’UNION ALLSELECT 4,‘Fourth’UNION ALLSELECT 5,‘Fifth’GOCREATE TABLE table2(ID INT, Value VARCHAR(10))INSERT INTO Table2 (ID, Value)SELECT 1,‘First’UNION ALLSELECT 2,‘Second’UNION ALLSELECT 3,‘Third’UNION ALLSELECT 6,‘Sixth’UNION ALLSELECT 7,‘Seventh’UNION ALLSELECT 8,‘Eighth’
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOINThis join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
RIGHT OUTER JOINThis join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
FULL OUTER JOINThis join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.
Additional Notes related to JOIN:
The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*FROM Table1 t1WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)GO
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.
/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.ID IS NULL
The above example can also be created using Right Outer Join.
NOT INNER JOINRemember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.
USE AdventureWorksGOCREATE TABLE table1(ID INT, Value VARCHAR(10))INSERT INTO Table1 (ID, Value)SELECT 1,‘First’UNION ALLSELECT 2,‘Second’UNION ALLSELECT 3,‘Third’UNION ALLSELECT 4,‘Fourth’UNION ALLSELECT 5,‘Fifth’GOCREATE TABLE table2(ID INT, Value VARCHAR(10))INSERT INTO Table2 (ID, Value)SELECT 1,‘First’UNION ALLSELECT 2,‘Second’UNION ALLSELECT 3,‘Third’UNION ALLSELECT 6,‘Sixth’UNION ALLSELECT 7,‘Seventh’UNION ALLSELECT 8,‘Eighth’
Monday, March 23, 2009
sql server existing instance change (warning while installing sql server)
If the warning is like to change the sql server instance and to upgrade to sql server sp2....
The easy way i found was ......
Go to Visual studio>Tools>Options>DataBase Tools>DataConnections>SqlServerInstanceName
Change that name to what ever u want to....It ll fix the problem...
The easy way i found was ......
Go to Visual studio>Tools>Options>DataBase Tools>DataConnections>SqlServerInstanceName
Change that name to what ever u want to....It ll fix the problem...
Wednesday, February 25, 2009
How to retrieve the column Information of table -sql server
select column_name, data_type, character_maximum_length from information_schema.columnswhere table_name = 'myTable'
Tuesday, February 24, 2009
How to Delete all the data from the Database with simple Query- Sql Server
use practice
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Friday, February 20, 2009
Copy the Schema of database in sql server and Data
Copy the Schema of database in sql server
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
Copy the Whole database data but not Constraints and SPs and Triggers and Views
use the DTSWIZARD of sql server ...
goto Run -> cmd-> dtswizard
If you have a table in a database and you would like to copy the table to another database, use this query:
SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers
Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.If you want to transfer all the objects from one database to another,
open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts.
Then run these scripts against the new database.Transfer both schema and dataTo copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
Copy the Whole database data but not Constraints and SPs and Triggers and Views
use the DTSWIZARD of sql server ...
goto Run -> cmd-> dtswizard
If you have a table in a database and you would like to copy the table to another database, use this query:
SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers
Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.If you want to transfer all the objects from one database to another,
open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts.
Then run these scripts against the new database.Transfer both schema and dataTo copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).
Wednesday, February 18, 2009
How to import data from Excel to Sql server through DTSWizard
http://www.sql-server-performance.com/articles/biz/How_to_Import_Data_From_Excel_2007_p1.aspx
Monday, February 16, 2009
Import the data from Excel to Sql Server
OPENROWSET ( 'provider_name',
{ 'datasource'; 'user_id'; 'password' 'provider_string' },
{ [ catalog.][schema.]object 'query' }
)
In this article, we will only be using the following syntax of the OPENROWSET:OPENROWSET ( 'provider_name', 'provider_string', 'query' )
The provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry. In the case of Excel, the provider_name that we will use is "Microsoft.Jet.OLEDB.4.0".
The provider_string is a provider-specific connection string that is passed to initialize the OLE DB provider. provider_string typically encapsulates all the connection information needed to initialize the provider. For Excel, the provider_string that we will use is "Excel 8.0;Database=Excel File Name".
The query is a string constant sent to and executed by the provider. SQL Server does not process the query but processes query results returned by the provider (a pass-through query).
A B C D E F
------------------------------------------------------------------------------------------------
1 FirstName LastName Address City State ZIP
2 Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
3 Donald Duck Walt Disney World Lake Buena Vista FL 32830
4 George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
5 George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
6 Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
To query this data from the Excel file without using DTS to import it to a table in SQL Server, you can do the following SELECT statement with OPENROWSET:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
Sheet1 is the name of the worksheet in the Excel file. Make sure that the name of the worksheet is followed by the dollar sign ($) when specifying in the query. If the dollar sign is not included, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message:
The Microsoft Jet database engine could not find the object 'Sheet1'.
Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace
[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
The result of this query is as follows:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL NULL
Donald Duck Walt Disney World Lake Buena Vista FL NULL
George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
By default, the first row in the Excel file is assumed to be the column header, as can be seen from the output.
It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine. The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive. If the Excel file specified in the provider_string parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error.].
Just like any SELECT statement, conditions can be included in the query to filter out records from the Excel worksheet by simply specifying it in the WHERE clause. As an example, to display only the addresses of people from Florida you can do the following:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
WHERE [State] = 'FL'
This query gives the following result:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
Donald Duck Walt Disney World Lake Buena Vista FL 32830
{ 'datasource'; 'user_id'; 'password' 'provider_string' },
{ [ catalog.][schema.]object 'query' }
)
In this article, we will only be using the following syntax of the OPENROWSET:OPENROWSET ( 'provider_name', 'provider_string', 'query' )
The provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry. In the case of Excel, the provider_name that we will use is "Microsoft.Jet.OLEDB.4.0".
The provider_string is a provider-specific connection string that is passed to initialize the OLE DB provider. provider_string typically encapsulates all the connection information needed to initialize the provider. For Excel, the provider_string that we will use is "Excel 8.0;Database=Excel File Name".
The query is a string constant sent to and executed by the provider. SQL Server does not process the query but processes query results returned by the provider (a pass-through query).
A B C D E F
------------------------------------------------------------------------------------------------
1 FirstName LastName Address City State ZIP
2 Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
3 Donald Duck Walt Disney World Lake Buena Vista FL 32830
4 George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
5 George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
6 Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
To query this data from the Excel file without using DTS to import it to a table in SQL Server, you can do the following SELECT statement with OPENROWSET:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
Sheet1 is the name of the worksheet in the Excel file. Make sure that the name of the worksheet is followed by the dollar sign ($) when specifying in the query. If the dollar sign is not included, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message:
The Microsoft Jet database engine could not find the object 'Sheet1'.
Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace
[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
The result of this query is as follows:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL NULL
Donald Duck Walt Disney World Lake Buena Vista FL NULL
George Bush 1600 Pennsylvania Avenue NW Washington DC 20500-0003
George Clooney 151 El Camino Drive Beverly Hills CA 90212-2704
Tom Cruise 9830 Wilshire Boulevard Beverly Hills CA 90212-1804
By default, the first row in the Excel file is assumed to be the column header, as can be seen from the output.
It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine. The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive. If the Excel file specified in the provider_string parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error.].
Just like any SELECT statement, conditions can be included in the query to filter out records from the Excel worksheet by simply specifying it in the WHERE clause. As an example, to display only the addresses of people from Florida you can do the following:SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')
WHERE [State] = 'FL'
This query gives the following result:FirstName LastName Address City State ZIP
---------- ---------- ------------------------------ -------------------- ------ ----------
Mickey Mouse Walt Disney World Lake Buena Vista FL 32830
Donald Duck Walt Disney World Lake Buena Vista FL 32830
Thursday, January 8, 2009
Convert datetime and date formats
SELECT GETDATE()
ResuleSet:2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions.
The method he suggested was to useSELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed http://msdn.microsoft.com/en-us/library/ms187928.aspx
ResuleSet:2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions.
The method he suggested was to useSELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed http://msdn.microsoft.com/en-us/library/ms187928.aspx
Subscribe to:
Posts (Atom)
