SQL Server Restrict Memory Usage

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

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

RECONFIGURE WITH OVERRIDE

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

 Sqlteam.com/forums/topic.asp?topic_ID=63292

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
Northwind..Orders

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

http://www.codinghorror.com/blog/archives/000976.html

Powered by ScribeFire.

SQL: Truncate vs Delete

Truncate:

- 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
object)

- 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

Delete:

- 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

[ref]

 

SQL: Notify Client about progress in long running process

RAISERROR (N'After 2', -- Message text           10, -- Severity           1) -- State       WITH NOWAIT

The thing to note here is the use of NOWAIT option.

This flushes all waiting resultsets and messages back to the client.

Also note the use of non fatal severity and state, since we don’t
want to cancel our script but only inform the user of the progress.

It comes in very handy when running script that can take a while indeed.
ref: link

How does SQL Server really store Nulls

Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it’s 0.

For variable size datatypes the acctual size is 0 bytes.

For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, ” for chars).

[link]

Powered by ScribeFire.

Deploying ReportViewer web control inside Reporting Services 2005 website

if your web application is using ReportViewer web control, it will throw the exception:The type Microsoft.ReportingServices.UI.WebControlConnection, ReportingServicesWebUserInterface, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 does not implement IReportServerConnection or could not be found

its your application inheriting appSettings from parent’s site web.config.
To resolve this, modify your applications’ web.config as

<appSettings>
<remove key ="ReportViewerServerConnection"/>
<remove key ="ReportViewerTemporaryStorage"/>
</appSettings>