Memory Problems

Following links provide valuable information for sql server memory problems:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://support.microsoft.com/kb/271624

EXECUTE AS Clause (SQL Server 2005)

In sql server 2005, it is possible to run a proc/function with the credentials of an other user by using "execute as". However it has some limitations (http://msdn.microsoft.com/en-us/library/ms188354.aspx)


create table tbl_test (id int)
go
create proc proc_test
WITH execute as 'm7805'
as
insert into tbl_test values(8)
go
grant execute on proc_test to m7805_2


with user m7805_2:
insert into tbl_test values(8)
The INSERT permission was denied on the object 'tbl_test', database 'pubs', schema 'dbo'.
select * from tbl_test
The SELECT permission was denied on the object 'tbl_test', database 'pubs', schema 'dbo'.
exec proc_test --inserts the record

Sql Server 2008 upcoming

What is new and in sql server?
1-) It is possible to set restriction on users using resource governor. However restrictions can be defined on only CPU, not disc IO or anything else.
2-) On the fly data encryption is supported now.
3-) Since sql server 2005, online restore is available and there are some improvements in 2008
4-) You are no more required to take server trace in order to audit in detail. In 2008 audit supports auditing session activities.
5-) There is a new tool for tuning: Performance studio
6-) Like ADDM (AWR) in oracle, sql server 2008 has its own way of workload repository.
7-) Mirroring is able to compress transaction data before sending it to destination which benefits network bandwidth
8-) Compression is available at table level.
9-) Compression is available for backup operations.
10-) New change data capture (CDC) implementation
10-) There are some improvements on SSIS. For example it uses ADO.NET drivers instead of ODBC.

Actually there are many more changes and improvements in sql server 2008. These are the significant ones for me.

SQL Server Table Hint

SELECT
r.rlai_status,r.rlai_rowidno,r.rlai_recorddatetime,
c.cust_no,c.cust_parentrowidno,c.cust_salaryincome,
c.cust_extraincome
FROM dbo.tbl_customers c with(nolock,index(IDX_cust_parentrowid)),dbo.rloan_appinfo r with(nolock, index(IDX_status))
WHERE
c.cust_parentrowidno = r.rlai_rowidno
AND r.rlai_status='3'
AND c.cust_parentprocessid=7
AND c.cust_type = 'B'

VBScript "Microsoft OLE DB Provider for SQL Server: Timeout expired" problem

Can be solved by setting:

Set cmd = CreateObject("ADODB.Command")
cmd.CommandTimeout = 300

300 is 5 mins, in seconds