גרי רשף

Exec msdb..sp_start_job 'Job001';

Use msdb;

Go

Grant Execute On Object::sp_start_job To Try001;

Go

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE --RECONFIGURE WITH OVERRIDE אם זה לא עובד אז לנסות

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE --RECONFIGURE WITH OVERRIDE אם זה לא עובד אז לנסות

GO

Exec xp_CmdShell 'Dir C:\ > C:\Try001.txt';

Go

Use master;

Go

Create User Try001 For Login Try001;

Go

Grant Execute On Object::xp_CmdShell To Try001;

Go

Exec master..sp_xp_CmdShell_Proxy_Account 'MyLogin','MyPassword';

Go

Exec master..sp_xp_CmdShell_Proxy_Account Null;

Go

Create Table #Tmp(ID Int Identity Primary Key,

                S Varchar(Max) Sparse Null,

                I Int Sparse Null,

                B Bit Sparse Null,

                D DateTime Sparse Null,

                SIBD XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

Go

Insert Into #Tmp(S) Values('Try it');

Insert Into #Tmp(I) Values(123);

Insert Into #Tmp(B) Values(1);

Insert Into #Tmp(D) Values(GETDATE());

Insert Into #Tmp(SIBD) Values(Cast('<S>Another try</S><I>456</I><B>0</B><D>2010-04-01T12:00:00</D>' As Xml));

Select    * ,

        S,

        I,

        B,

        D

From    #Tmp;

CREATE EVENT SESSION MyEventSession ON SERVER

ADD EVENT sqlserver.sql_statement_completed(ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,sqlserver.tsql_stack, sqlserver.username, sqlserver.client_hostname) WHERE sqlserver.database_id=2),

ADD EVENT sqlserver.sql_statement_starting(ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,sqlserver.tsql_stack, sqlserver.username, sqlserver.client_hostname) WHERE sqlserver.database_id=2)

ADD TARGET package0.asynchronous_file_target(set FileName = 'c:\tmp\EventSession.xel',MetaDataFile = 'c:\tmp\EventSession.xem')

GO

Select    *

From    sys.dm_xe_objects 

Where    object_type='event'

Order By name

Select    *

From    sys.dm_xe_objects 

Where    object_type='action'

Order By name

Select    *

From    sys.dm_xe_objects 

Where    object_type='pred_source'

Order By name

Select    *

From    sys.dm_xe_objects 

Where    object_type='target'

Order By name

ALTER EVENT SESSION MyEventSession ON SERVER STATE = Start

GO

Select * From sys.objects

Go

Select * From sys.tables

Go

ALTER EVENT SESSION MyEventSession ON SERVER STATE = Stop

GO

DROP EVENT SESSION MyEventSession ON SERVER

GO

Select    *,

        CAST(event_data as XML) as event_data_xml

From    sys.fn_xe_file_target_read_file('c:\tmp\EventSession*.xel','c:\tmp\EventSession*.xem', null, null)

Go

With T As

(SELECT    X.value('(./@name)', 'varchar(1000)') as event_name,

        X.value('(./data[@name="source_database_id"]/value)[1]', 'int') as source_database_id,

        X.value('(./data[@name="object_id"]/value)[1]', 'int') as object_id,

        X.value('(./data[@name="object_type"]/value)[1]', 'varchar(25)') as object_type,

        X.value('(./data[@name="duration"]/value)[1]', 'bigint') as duration,

        X.value('(./data[@name="cpu"]/value)[1]', 'bigint') as cpu,

        X.value('(./data[@name="reads"]/value)[1]', 'bigint') as reads,

        X.value('(./data[@name="writes"]/value)[1]', 'bigint') as writes,

        X.value('(./action[@name="sql_text"]/value)[1]', 'varchar(Max)') as sql_text,

        X.value('(./action[@name="database_id"]/value)[1]', 'int') as database_id,

        X.value('(./action[@name="session_id"]/value)[1]', 'int') as session_id,

        X.value('(./action[@name="username"]/value)[1]', 'varchar(Max)') as username,

        X.value('(./action[@name="client_hostname"]/value)[1]', 'varchar(Max)') as client_hostname

FROM    (SELECT CAST(event_data AS XML) xml_event_data 

        FROM sys.fn_xe_file_target_read_file('c:\tmp\data2*.xel','c:\tmp\data2*.xem', NULL, NULL)) AS event_table

        CROSS APPLY xml_event_data.nodes('//event') (X))

Select    event_name,

        source_database_id,

        DB_NAME(source_database_id) source_database_name,

        object_id,

        object_type,

        duration,

        cpu,

        reads,

        writes,

        sql_text,

        database_id,

        DB_NAME(database_id) database_name,

        session_id,

        username,

        client_hostname

From    T;

Go

XP_CMDSHELL 'del c:\tmp\EventSession.*'

GO

Declare    @ID1 Int;

Set        @ID1=3;

Select    *

From    sys.objects

Where    object_id=IsNull(@ID1,object_id);

Declare    @ID2 Int;

Select    *

From    sys.objects

Where    object_id=IsNull(@ID2,object_id);

Create Proc P_Objects(@ID Int=-1) As

Select    *

From    sys.objects

Where    object_id=Case When @ID=-1 Then object_id Else @ID End;

Exec P_Objects 3;

Exec P_Objects;

Create Table #Ovdim(ID Int Not Null Primary Key,

                    Shem VarChar(50),

                    Heara NVarChar(Max));

Go

Insert Into #Ovdim Values(1,'Ada','העובדת הראשונה');

Insert Into #Ovdim Values(2,'Beni','הוא 100% בסדר');

Insert Into #Ovdim Values(3,'Galit','גם היא בסדר');

Insert Into #Ovdim Values(4,'Dan','יש לו 100 במתימטיקה');

Insert Into #Ovdim Values(5,'Hila',Null);

Select    *

From    #Ovdim

Where    Heara Like '%100%%'

Select    *

From    #Ovdim

Where    Heara Like '%100~%%' ESCAPE '~'

Select    *

From    #Ovdim

Where    Len(Heara)<>Len(Replace(Heara,'100%',''))

Select    *

From    AdventureWorks.Person.Address

Where    AddressLine1 Like '%100~%%' ESCAPE '~'

Select    *

From    AdventureWorks.Person.Address

Where    Len(AddressLine1)<>Len(Replace(AddressLine1,'100%',''))

Use AdventureWorks;

Go

Create Table #TmpTbl(MyInt Int, MyString Varchar(10));

Go

Select * From tempdb.sys.columns Where object_id=OBJECT_ID('tempdb.dbo.#TmpTbl');

Use Master

Go

xp_cmdshell 'md C:\MyAudit\'

Go

CREATE SERVER AUDIT My_Server_Audit

    TO FILE (FILEPATH='C:\MyAudit\', MAXSIZE = 5MB,

    MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON);

Go

ALTER SERVER AUDIT My_Server_Audit with (STATE = ON);

Go

Use AdventureWorks

Go

CREATE DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec

    FOR SERVER AUDIT My_Server_Audit

    ADD (SELECT,DELETE,INSERT,UPDATE ON DATABASE::[AdventureWorks] BY [public])

    WITH (STATE = Off)

GO

ALTER  DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec

    FOR SERVER AUDIT My_Server_Audit

    ADD (SCHEMA_OBJECT_CHANGE_GROUP)

Go

ALTER DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec

    FOR SERVER AUDIT My_Server_Audit WITH (STATE = ON)

Go

SELECT    *

FROM    sys.fn_get_audit_file ('C:\MyAudit\My_Server_Audit*.sqlaudit',default,default)

Order By event_time;

Use AdventureWorks

Go

Create Table dbo.Try001(I Int);WaitFor Delay '00:00:01';

Insert Into Try001 Select 1;WaitFor Delay '00:00:01';

Select * From Try001;WaitFor Delay '00:00:01';

Update Try001 Set I=2;WaitFor Delay '00:00:01';

Begin Tran

Delete From Try001;WaitFor Delay '00:00:01';

RollBack;

Truncate Table Try001;WaitFor Delay '00:00:01';

Drop Table Try001;WaitFor Delay '00:00:01';

Go

Create View dbo.Try002 As Select 2 D;

Go

WaitFor Delay '00:00:01';

Go

Alter View dbo.Try002 As Select 3 D;

Go

WaitFor Delay '00:00:01';

Go

Select * From Try002;WaitFor Delay '00:00:01';

Drop View dbo.Try002;

Go

SELECT    *

FROM    sys.fn_get_audit_file ('C:\MyAudit\My_Server_Audit*.sqlaudit',default,default)

Order By event_time;

Use Master

ALTER SERVER AUDIT My_Server_Audit with (STATE = ON)

Go

Use Master

If (Select Count(*) From sys.server_audits Where name='My_Server_Audit')>0

    Begin

    ALTER SERVER AUDIT My_Server_Audit with (STATE = OFF);

    Drop SERVER AUDIT My_Server_Audit;

    End

Go

Use AdventureWorks

Go

If (Select Count(*) From sys.Database_audit_specifications Where name='My_DB_Audit_Spec')>0

    Begin

    ALTER DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec with (STATE = OFF);

    Drop DATABASE AUDIT SPECIFICATION My_DB_Audit_Spec;

    End

Go

xp_cmdshell 'del C:\MyAudit\/q'

Go

xp_cmdshell 'rd C:\MyAudit\'

Go

Use tempdb

Go

Create Database PartDB;

Go

Use PartDB;

Go

CREATE PARTITION FUNCTION PART_FN (datetime)

AS RANGE LEFT FOR VALUES ('20021231');

Go

CREATE PARTITION SCHEME PART_SCH

AS PARTITION PART_FN TO ([Primary],[Primary]);

Go

CREATE TABLE CurrencyRate(CurrencyRateID int,

                        CurrencyRateDate datetime,

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime) ON PART_SCH (CurrencyRateDate);

GO

Insert Into CurrencyRate

Select    * 

From    AdventureWorks.Sales.CurrencyRate T 

Order By T.CurrencyRateDate;

Go

SELECT    $PARTITION.PART_FN(CurrencyRateDate) Partition,

        MIN(CurrencyRateDate) [Min],

        MAX(CurrencyRateDate) [Max],

        COUNT(CurrencyRateDate) [Count]

FROM    CurrencyRate

Group By $PARTITION.PART_FN(CurrencyRateDate)

Order By $PARTITION.PART_FN(CurrencyRateDate);

Go

ALTER DATABASE PartDB

ADD FILEGROUP FGCurrFile

Go

ALTER DATABASE PartDB

ADD FILE(NAME = CurrFile,FILENAME = 'E:\CurrFile.ndf',SIZE = 1MB)

TO FILEGROUP FGCurrFile

GO

ALTER PARTITION SCHEME PART_SCH

NEXT USED [FGCurrFile]

GO

ALTER PARTITION FUNCTION PART_FN() 

SPLIT RANGE ('20031231')

GO 

ALTER PARTITION FUNCTION PART_FN()

MERGE RANGE ('20031231')

GO

ALTER DATABASE PartDB Remove FILE CurrFile;

Go

ALTER DATABASE PartDB Remove FILEGROUP FGCurrFile

Go

CREATE TABLE CurrencyRate_Out(CurrencyRateID int,

                        CurrencyRateDate datetime,

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime);

Go

ALTER TABLE dbo.CurrencyRate

SWITCH PARTITION 1

TO dbo.CurrencyRate_Out;

Go

Select COUNT(*) [Count] From CurrencyRate;

Go

Select COUNT(*) [Count] From CurrencyRate_Out;

CREATE TABLE CurrencyRate_In(CurrencyRateID int,

                        CurrencyRateDate datetime CHECK (CurrencyRateDate >= '20050101' AND CurrencyRateDate IS NOT NULL),

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime);

Go

Insert Into CurrencyRate_In Values(15000,'20050101','USD','ARS',0.99,0.999,'20050101');

Go

ALTER PARTITION SCHEME PART_SCH

NEXT USED [Primary]

Go

ALTER PARTITION FUNCTION PART_FN() 

SPLIT RANGE ('20041231');

Go

ALTER TABLE CurrencyRate_In switch TO CurrencyRate PARTITION 3;

Go

Select * From CurrencyRate_In;

Select * From CurrencyRate Where $PARTITION.PART_FN(CurrencyRateDate)=3

Declare    @S1 Varchar(Max),

        @S2 Varchar(Max);

Select    @S1='Geri Reshef',

        @S2='e';

Select DataLength(@S1)-DataLength(Replace(@S1,@S2,'')) Occurences;

Declare    @S1 Varchar(Max),

        @S2 Varchar(Max);

Select    @S1='Count appearance of a particular string within the outer string',

        @S2='String';

Select (DataLength(@S1)-DataLength(Replace(@S1,@S2,'')))/DataLength(@S2) Occurences;

With T1 As    --3 posible signs ("-" = empty)

(Select    '-' IMD

Union All

Select    'X' IMD

Union All

Select    'O' IMD),

T2 As

(Select    1 N,

        CAST(IMD As Varchar(9)) IMD,

        IMD IMD1,

        '-' IMD2,

        '-' IMD3,

        '-' IMD4,

        '-' IMD5,

        '-' IMD6,

        '-' IMD7,

        '-' IMD8,

        '-' IMD9,

        CAST(Null As SmallInt) As Tozaa

From    T1

Union All

Select    *,

        Case When 'XXX' In (IMD1+IMD2+IMD3,IMD4+IMD5+IMD6,IMD7+IMD8+IMD9,IMD1+IMD4+IMD7,IMD2+IMD5+IMD8,IMD3+IMD6+IMD9,IMD1+IMD5+IMD9,IMD3+IMD5+IMD7)

                Then Cast(1 As SmallInt) Else Cast(0 As SmallInt) End --X wins

        +Case When 'OOO' In (IMD1+IMD2+IMD3,IMD4+IMD5+IMD6,IMD7+IMD8+IMD9,IMD1+IMD4+IMD7,IMD2+IMD5+IMD8,IMD3+IMD6+IMD9,IMD1+IMD5+IMD9,IMD3+IMD5+IMD7)

                Then Cast(3 As SmallInt) Else Cast(0 As SmallInt) End --O wins

        +Case When IMD1+IMD2+IMD3 Like '%X%' And IMD4+IMD5+IMD6 Like '%X%' And IMD7+IMD8+IMD9 Like '%X%' And IMD1+IMD4+IMD7 Like '%X%' And IMD2+IMD5+IMD8 Like '%X%' And IMD3+IMD6+IMD9 Like '%X%' And IMD1+IMD5+IMD9 Like '%X%' And IMD3+IMD5+IMD7 Like '%X%'

                And IMD1+IMD2+IMD3 Like '%O%' And IMD4+IMD5+IMD6 Like '%O%' And IMD7+IMD8+IMD9 Like '%O%' And IMD1+IMD4+IMD7 Like '%O%' And IMD2+IMD5+IMD8 Like '%O%' And IMD3+IMD6+IMD9 Like '%O%' And IMD1+IMD5+IMD9 Like '%O%' And IMD3+IMD5+IMD7 Like '%O%'

                Then Cast(2 As SmallInt) Else Cast(0 As SmallInt) End --Equal

        Tozaa

From    (Select    T2.N+1 N,

                Cast(T2.IMD+T1.IMD As Varchar(9)) IMD,

                T2.IMD1,

                Case When N+1=2 Then T1.IMD When N+1>2 Then T2.IMD2 Else '-' End IMD2,

                Case When N+1=3 Then T1.IMD When N+1>3 Then T2.IMD3 Else '-' End IMD3,

                Case When N+1=4 Then T1.IMD When N+1>4 Then T2.IMD4 Else '-' End IMD4,

                Case When N+1=5 Then T1.IMD When N+1>5 Then T2.IMD5 Else '-' End IMD5,

                Case When N+1=6 Then T1.IMD When N+1>6 Then T2.IMD6 Else '-' End IMD6,

                Case When N+1=7 Then T1.IMD When N+1>7 Then T2.IMD7 Else '-' End IMD7,

                Case When N+1=8 Then T1.IMD When N+1>8 Then T2.IMD8 Else '-' End IMD8,

                Case When N+1=9 Then T1.IMD When N+1>9 Then T2.IMD9 Else '-' End IMD9

        From    T2,

                T1

        Where    N<9

                And Len(T2.IMD+T1.IMD)-Len(Replace(T2.IMD+T1.IMD,'X',''))<=5

                And Len(T2.IMD+T1.IMD)-Len(Replace(T2.IMD+T1.IMD,'O',''))<=4

                And IsNull(Tozaa,2) In (0,1,2,3)) T),

T3 As

(Select    *

From    T2

Where    IsNull(Tozaa,0) In (0,1,2,3)

        And Len(IMD)=9

        And (9-Len(Replace(IMD,'X','')))-(9-Len(Replace(IMD,'O',''))) In (0,1))

Select    *

Into    T_Mazavim

From    T3;

Select    Mb.*,

        Ma.IMD IMDp

Into    T_Ksharim

From    T_Mazavim Ma

Inner Join T_Mazavim Mb

        On Case When Ma.IMD1=Mb.IMD1 Then 1 When Ma.IMD1='-' Then 0 Else 999 End

            +Case When Ma.IMD2=Mb.IMD2 Then 1 When Ma.IMD2='-' Then 0 Else 999 End

            +Case When Ma.IMD3=Mb.IMD3 Then 1 When Ma.IMD3='-' Then 0 Else 999 End

            +Case When Ma.IMD4=Mb.IMD4 Then 1 When Ma.IMD4='-' Then 0 Else 999 End

            +Case When Ma.IMD5=Mb.IMD5 Then 1 When Ma.IMD5='-' Then 0 Else 999 End

            +Case When Ma.IMD6=Mb.IMD6 Then 1 When Ma.IMD6='-' Then 0 Else 999 End

            +Case When Ma.IMD7=Mb.IMD7 Then 1 When Ma.IMD7='-' Then 0 Else 999 End

            +Case When Ma.IMD8=Mb.IMD8 Then 1 When Ma.IMD8='-' Then 0 Else 999 End

            +Case When Ma.IMD9=Mb.IMD9 Then 1 When Ma.IMD9='-' Then 0 Else 999 End=8

        And Ma.Tozaa=0;

Declare    @RC Int;

Set        @RC=1;

While    @RC>0

        Begin

        Update T_Ksharim

        Set        Tozaa=Case When (9-LEN(Replace(IMD,'-',''))) In (1,3,5,7,9) Then Mn Else Mx End --MinMax algorithm

        From    T_Ksharim K

        Inner Join (Select    IMDp,

                            MIN(Tozaa) Mn,

                            MAX(Tozaa) Mx

                    From    T_Ksharim

                    Group By IMDp

                    Having    MIN(Tozaa)<>0) T

                On K.IMD=T.IMDp

        Where    K.Tozaa=0;

        Set        @RC=@@ROWCOUNT;

        End;

Select    *

From    T_Ksharim

Where    IMDp='---------'

Select    *

From    T_Ksharim

Where    IMDp='X--O-----'

Select    *

From    T_Ksharim

Where    IMDp='X--OX---O'