Separate Domain from email address in Excel

=RIGHT(A4,LEN(A4)-SEARCH("@",A4,1))

List of database Constraints

USE GSPrime4;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint
, SCHEMA_NAME(schema_id) AS SchemaName
, OBJECT_NAME(parent_object_id) AS TableName
, type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%Constraint'
--and NameofConstraint like '%PK_%'
order by OBJECT_NAME(OBJECT_ID)

GO

T-SQL Error Handling

BEGIN Transaction

BEGIN TRY


PRINT 'STEP 1 COMPLETE'

PRINT 'STEP 2 COMPLETE'

PRINT 'STEP 3 COMPLETE'
--delete from vendorcompanyvendor where vendorcompanyid > @MaxVendorCompanyID
--truncate table vendorcompanyfyenddate

--select @MaxCompanyID = @MaxCompanyID + 1
--select @MaxVendorCompanyID = @MaxVendorCompanyID + 1
--select @MaxVendorCompanyVendorID = @MaxVendorCompanyVendorID + 1
--DBCC CHECKIDENT ('company', RESEED, @MaxCompanyID)
--DBCC CHECKIDENT ('vendorcompany', RESEED, @MaxVendorCompanyID)
--DBCC CHECKIDENT ('vendorcompanyvendor', RESEED, @MaxVendorCompanyVendorID)

-- Log this rollback so the Audit log makes sense afterwards
EXEC StatusUpdate @MaxDataPeriodID, 3, '[RollBackDataPeriod]', '...completed', null


END TRY

BEGIN CATCH
 SELECT
          ERROR_NUMBER() AS ErrorNumber
        , ERROR_SEVERITY() AS ErrorSeverity
        , ERROR_STATE() AS ErrorState
        , ERROR_PROCEDURE() AS ErrorProcedure
        , ERROR_LINE() AS ErrorLine
        , ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Select text to left of @

SELECT userID, emailID, left(emailId, charindex('@',emailId)-1)
  FROM [TaxFormsDelivery].[dbo].[TFDP_Client_Users]
  where UserID  = left(emailId, charindex('@',emailId)-1)

Excel CONCATENATE and TEXT functions

=CONCATENATE(A1," ",TEXT(B1,"0000"))

Fragmentation of Views

SELECT DB_NAME() [database], OBJECT_NAME(a.object_id) [table], idx.[name], a.object_id, a.index_id, a.index_type_desc, a.alloc_unit_type_desc, avg_fragmentation_in_percent, a.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') a
JOIN sys.indexes idx ON idx.object_id = a.object_id AND idx.index_id = a.index_id
WHERE page_count > 1000
AND avg_fragmentation_in_percent > 10.0
ORDER BY avg_fragmentation_in_percent DESC

SQL Server Transaction example

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Calculating execution frequency

METHOD #1

USE [databasename]
GO
CREATE PROCEDURE procExecutionFrequency  @Database sysnameASBEGIN SELECT cp.objtype 'ObjectType' , min(DB_NAME(st.dbid)) +'.'+OBJECT_SCHEMA_NAME(st.objectid,dbid) +'.' +OBJECT_NAME(st.objectid,dbid) 'ObjectName' , max(cp.usecounts) 'ExecutionFrequency' FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE cp.objtype in ( 'proc', 'view')  AND DB_NAME(st.dbid) = @Database GROUP BY   cp.objtype,   cp.plan_handle,   OBJECT_SCHEMA_NAME(objectid,st.dbid),   OBJECT_NAME(objectid,st.dbid)  ORDER BY ObjectType, ExecutionFrequency descENDGO
EXEC procExecutionFrequency 'GSPrime4'
GO

---------------------------------------------------------------------

METHOD #2

CREATE TABLE dbo.tblRTPVrequests (
rtpvID int IDENTITY (1, 1) NOT NULL ,
rtpvDateTime datetime ,

CONSTRAINT PK_RTPVrequests PRIMARY KEY CLUSTERED ( rtpvID )
)
GO

--update the counter

insert into dbo.tblRTPVrequests values(getdate())
select * from dbo.tblRTPVrequests

memory footprint of views

SELECT * FROM sys.views

select [name]
, type_desc
, space_used_in_kb = (page_count * 8.0)
, space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I inner join sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P on I.[object_id] = P.[object_id]
and I.[index_id] = P.[index_id]

Create Date of SQL Server Index

select crdate, i.name, object_name(o.id)
from sysindexes i join sysobjects o ON o.id = i.id
where i.name IN ('ndx_move','ndx_2')
order by crdate desc

SQL Server 2005 Activity Monitor

http://www.dedicatedsqlserver.com/HowTo/SQLServer_Activity_Monitor.aspx

Showing persistently hidden columns in Excel

Sometimes......rare occasions, columns get hidden and won't unhide.

Sub Unhide_cols()
    On Error GoTo err_Unhide_cols

    Dim oWS As Excel.Worksheet
    Set oWS = Application.Worksheets("2013 EOD Account Watch List")
 
    With Application.Worksheets(oWS.Name)
        '.Range("A2").Activate
        .Columns("K").Select
        Selection.EntireColumn.Hidden = False
     
        .Columns("A:I").Select
        Selection.EntireColumn.Hidden = True
     
        Selection.EntireColumn.Hidden = False
                     
        .Columns("A:I").AutoFit
        .Columns("B").ColumnWidth = 20
     
    End With

exit_Unhide_cols:
    Set oWS = Nothing
    Exit Sub
err_Unhide_cols:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Unhide_cols
End Sub

CAST vs CONVERT

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SQL Query to find missing rows between two related tables

SELECT A.ABC_ID, A.VAL WHERE NOT EXISTS 
   (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL)

or

SELECT A.ABC_ID, A.VAL WHERE VAL NOT IN 
    (SELECT VAL FROM B WHERE B.ABC_ID = A.ABC_ID)

or

SELECT A.ABC_ID, A.VAL LEFT OUTER JOIN B 
    ON A.ABC_ID = B.ABC_ID AND A.VAL = B.VAL WHERE B.VAL IS NULL

T-SQL check for Integer value

declare @string as varchar(20)
set @string = '1.01'

select 'test' as col1,
case
WHEN ISNUMERIC(@string) = 0 THEN 0
WHEN @string LIKE '%[^-+ 0-9]%' THEN 0
WHEN CAST(@string AS NUMERIC(38, 0))
 NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE 1
end as intCol

sp_executesql using cursor

truncate table SQLtoExec

drop table SQLtoExec

create table SQLtoExec (

sqlTEXT varchar(120) NOT NULL

-- , VendorCompanyId int NOT NULL

-- , fyenddate varchar(20) NOT NULL

)

INSERT INTO SQLtoEXEC

SELECT 'DELETE FROM vendorfinancecolumnar WHERE vendorcompanyid = '+ ltrim(str(vendorcompanyid)) + ' AND fyenddate = '''+ ltrim(cast(fyenddate as varchar(100))) + '''' 

FROM vendorfinancecolumnar 

WHERE vendordataperiodid = (

select vendordataperiodid 

from vendordataperiod

where vendorproviderid = 3 

and dataperiodid = 51)

GROUP BY vendorcompanyid, fyenddate

HAVING count(vendorcompanyid) > 1

DECLARE @sqlText nvarchar(120)

-- ########################################################################

DECLARE myCursor CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT top 1000 sqlText

FROM sqlToEXEC

OPEN myCursor

     FETCH NEXT FROM myCursor

     INTO @sqlText

    

     WHILE @@FETCH_STATUS = 0

     BEGIN    

         

 EXECUTE sp_executesql @sqlText

                                               

          -- Get the next record.

          FETCH NEXT FROM myCursor

          INTO @sqlText

     END

CLOSE myCursor

DEALLOCATE myCursor

--drop table #myTable

Linked Server

/*
EXEC sp_addlinkedserver
   @server = '[type Linked Server Name]',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\servername\myAccessDBlocation\my.mdb'
GO
*/

Excel formulas: increments months or years or days

Incrementing Months in Excel

Excel Formulas: Separating String Values between spaces

place these formulas side by side in two columns..

=LEFT($A1,FIND(" ",$A1)-1)

=RIGHT($A1,FIND(" ",$A1)-1)

Performing a Batch Delete

hi,
use a batch DELETE, e.g. something like

DECLARE @batchSize INT
SET @batchSize = 10000

WHILE (SELECT COUNT(*) FROM yourTable WHERE yourCondition) > 0
BEGIN
  DELETE  FROM yourTable
  WHERE   primaryKey IN ( SELECT TOP ( @batchSize )
                                  primaryKey
                        FROM      yourTable
                        WHERE     yourCondition ) ;
END ;

of course using EXISTS is faster:
WHILE EXISTS(SELECT * FROM yourTable WHERE yourCondition)

Kill SQL Server database connections

USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid)
 from master.dbo.sysprocesses
 where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid)
FROM master.dbo.sysprocesses
WHERE dbid = db_id(@dbname)
 AND spid > @spid
END