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 for MS-Access

/*
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
*/

EXEC sp_addlinkedserver
   @server = 'sibr',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\tpadev01\[folder name]\[folder name]\[db name].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)