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

Managing Session Timeouts in .NET

If you are using ASP.NET 2.0, use have to check in the IIS. Open the IIS, click on the Application Pools, Select the Application pool for your application. Right Click on that, Select Properties. In the Performance tab, Set the idle timeout as your desired minutes for "shutdown worker processes after being idle for ..... minutes". Apart from this you have to set the timeout in web.config as said by the other friends.