Search This Blog

Sunday, December 9, 2012

OBJECT_ID SQL Server

SQL Server > Built-in FunctionsOBJECT_ID

OBJECT_ID returns the database object identification number of a schema-scoped object.

Example:

IF OBJECT_ID (N'dbo.tbl1', N'U') IS NOT NULL
  DROP TABLE dbo.tbl;
GO




SET XACT_ABORT SQL Server

SQL > Set Statements > XACT_ABORT

Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.






Syntax:
SET XACT_ABORT { ON | OFF }

Example

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL
DROP TABLE tbl1;
GO
IF OBJECT_ID(N'tbl2', N'U') IS NOT NULL
DROP TABLE tbl2;
GO

CREATE TABLE tbl1
(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE tbl2
(fld2 INT NOT NULL REFERENCES tbl1(fld1));
GO
INSERT INTO tbl1 VALUES (1);
INSERT INTO tbl1 VALUES (2);
INSERT INTO tbl1 VALUES (3);
INSERT INTO tbl1 VALUES (4);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO tbl2 VALUES (1);
INSERT INTO tbl2 VALUES (2);
INSERT INTO tbl2 VALUES (5); -- Foreign key error. SELECT shows only values 1 and 2 inserted. -- Values 5 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.

COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO tbl2 VALUES (3);
INSERT INTO tbl2 VALUES (5); -- Foreign key error.
-- Values 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
INSERT INTO tbl2 VALUES (4);
COMMIT TRANSACTION;
GO

SELECT * FROM tbl2;
GO






Saturday, December 1, 2012

date datatype SQL Server

SQL Server > Data Types > Date

Default format: YYYY-MM-DD
Range: 0001-01-01 through 9999-12-31

Example

DECLARE @date1 date
CREATE TABLE Table1 ( datefield date )

-- difference between date and datetime

DECLARE @date date= '12-10-21';
DECLARE @datetime datetime= @date;

SELECT @date AS 'date', @datetime AS 'datetime';

--Result
--date              datetime
------------       -----------------------
--2021-12-10  2021-12-10 00:00:00.000

Other Examples






Friday, November 30, 2012

How to change colums color to RadGridView (VB.NET)

Telerik > RadGridView > Cell Formatting

CellFormatting event is used to add formatting to grid cells

Because of UI virtualization in RadGridView, cell elements are created only for currently visible cells.
To prevent applying the formatting to other columns' cell elements all customization should be reset for the rest of the cell elements.
          
Example:
Change Forecolor in CellFormating Events
 

Imports Telerik.WinControls.UI
Imports Telerik.WinControls

Private Sub grd_CellFormatting(sender As System.Object, e As Telerik.WinControls.UI.CellFormattingEventArgs) Handles grd.CellFormatting
 
 If e.CellElement.ColumnInfo.Name = "total" Then
  e.CellElement.ForeColor = Color.Blue
 Else
  e.CellElement.ResetValue(LightVisualElement.ForeColorProperty, ValueResetFlags.Local)
 End If

End Sub






Wednesday, November 28, 2012

Cursor SQL Server

SQL Server > Data Types > Cursor

SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

Permissions default to any user that has SELECT permissions;

Syntax:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Example:

create table #temp
(id int)
declare @p int = 0
while @p < 10
begin
       insert into #temp
       values(@p)
       set @p = @p + 1
end

select * from #temp -- return all rows
DECLARE @id int
DECLARE c CURSOR FOR
select
   id
from
  #temp
OPEN c
FETCH NEXT FROM c INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
   print @id -- scroll row by row
    FETCH NEXT FROM c INTO @id
END
CLOSE c;
DEALLOCATE c;
drop table #temp






Find Last Day of Previous Month SQL Server

SQL Server > Built-in Functions > DATEADD Find Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))






Google’s Romanian Domain Gets Taken Down By Algerian Hacker MCA-CRB



That’s not an empty threat, it seems. MCA-DRB, according to Zone-h’s registry of hacked sites, has been responsible for 5,530 site hacks and defacements to date, with many of them appearing to cover government and public services sites from countries across Asia, Africa, Europe, Australia and the Americas

By MCA-CRB



Algerian Hacker




S thanks = Mr-AdeL & i-Hmx & Lagripe-Dz All Members Sec

To Be Continued ....