SQL Server Restrict Memory Usage

EXEC sp_configure ‘show advanced options’, 1


EXEC sp_configure ‘max server memory (MB)’, 200


SQL: Get Comma Seperated Values


Select @CSV = isnull(@TRX_REF + ‘,’,”) + seqID from table

Calling webservice from SQL 2000

 Sending  XML data over HTTP to a web service, using XMLHTTP object


SQL: Group data by weeks

– Group by weeks
starting on Mondays

select count(OrderID) as numOrders,

min(dateadd(day, datediff(day,’19000101′,OrderDate)/7*7, ‘19000101’)) as WeekStarting,

min(OrderDate) as EarliestOrder, max(OrderDate) as LatestOrder from

group by datediff(day,’19000101′,OrderDate)/7

order by WeekStarting


via [SQLTeam]


SQL Server: Data Source name not found

Recently, I had a problem when SQL Server 2000 Enterprise Manager fails to register (local)database; the error message was “Data Source name not found and no default driver specified”



This was a problem with faulty installation of SQL Server or somehow ODBC/Data Access Components configurationgone bad. The solution thatworked in my case is to export and import HKLM\Software\ODBC\ODBC.INST\SQL Server registry key from a normal system with ODBC drivers installed to the faulty SQL machine’s registry


A Visual Explanation of SQL Joins

SQL: Truncate vs Delete


– deallocates the data pages in a table and only this deallocation is stored in transaction log

– aquires only table and page locks for the whole table. since no
row locks are used less memory is required (lock is a pure memory

– resets identity column if there is one

– removes ALL pages. NO empty pages are left behind in a table

– fast(er)

– doesn’t fire delete triggers


– removes one row at the time and every deleted row is stored in the transaction log

– aquires table and/or page and row locks for the whole table

– leaves identity column alone

– can leave empty pages in a table since empty page removal requires a table lock which doesn’t necessarily happen

– slow(er)

– fires delete triggers