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)

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

Windows: Configuring Local Users: lusrmgr.msc

lusrmgr.msc

SQL Server backup results

SELECT a.name, b.type, DATEDIFF(MINUTE,b.backup_start_date , b.backup_finish_date),
b.backup_start_date ,
b.backup_finish_date
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
WHERE DATEPART(DW,b.backup_start_date) IN (6, 7)
AND b.type NOT IN ( 'L', 'I')

system.cpl = workstation properties

system.cpl = workstation properties