Difference between stored procedures and functions ?

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

COM Basics - Important Points

COM
===

IUNKNOWN

1)Base interface of COM. Every interface directly or indirectly derived from this interface
2)Provides three methods AddRef, QueryInterface, Release
3)AddRef increments the reference count to the object
4)Release decrements the reference count to the object
5)QueryInterface obtains Pointer to the object.
6)QueryInterface returns E_NOINTERFACE if requested object was not found and also nullify its out parameter.

IDISPOSE

1)exposes objects, methods and properties to programming tools and other applications that support Automation.
2)The object's properties and methods can be accessed using IDispatch::GetIDsOfNames and IDispatch::Invoke.

CoCreateInstance and CoCreateInstanceEX

1)CocreateInstance is used to create object in local system and it allows only one object to create at a time.
2)CoCreateInstanceEx is used to create object in remote system and it allows to create multiple objects based on single CLSID

Aggregation

1)Is the mechanism in which outerobject exposes the methods of inner object
2)We can get a pointer to the inner interface, calling QueryInterface of the outer object with IID of the inner interface.

Moniker

1)An object that implements IMoniker interface.
2)provides information to uniquely identify a COM object.

OLE vs COM

1)OLE is build on top of COM
2)COM is a specification, while OLE is a particular implementation of this specification

COM vs DCOM

1)introduced several improvements for distributed environment, such as MULTI_QI (multiple QueryInterface()), security

contexts
2)DCOM demonstrated importance of surrogate process (you cannot run in-proc server on a remote machine).
3)DCOM introduced a load balancing
4)DCOM extends COM

Dual Interface

1) which supports both IDISPATCH and V-Table based interfaces
2)You may have two dual interfaces in one class. you cannot work with two dual interfaces at the same time.

Marshalling By Value

1) Instead of accessing an object remotely, it is possible to copy the static state of the object and create a new object with the same state information on the caller side.
2) not involve network round trips.

multi-threaded apartment (MTA) and Single-threaded apartment (STA)

1) In STA, all calls will be made by the same thread. In MTA, COM object can be accessed by many threads simultaneously
2) A process can have only one MTA, but many STAs

In-Proc means COM object that implemented as DLL and supposed to be hosted by a container. When you have to instantiate the in-proc object remotely, you may use DLLHost.exe application that was design specially for this purpose.

IDL

Interface Definition Language to describe COM interfaces

Is there a way to register in-proc server without regsvr32.exe?

Yes. Call DllRegisterServer() from the client. Do not forget to call DLLUnregisterServer() from the same client. You may also use Registrar object for the same purpose or use direct manipulation of the windows registry.

Classic ASP Basics - 1

global.asa
==========

application events
session events
object declarations
TypeLibrary declarations
#include directive

======================



You could reference the object "MyAd" from any page in the ASP application:

<%=MyAd.GetAdvertisement("/banners/adrot.txt")%>

========================

Calling stored procedure from ADO
=================================

Set cn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
cn.Open "data source name", "userid", "password"
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
' Ask the server about the parameters for the stored proc
cmd.Parameters.Refresh
' Assign a value to the 2nd parameter.
' Index of 0 represents first parameter.
cmd.Parameters(1) = 11
cmd.Execute
%>
Calling via method 1

ReturnValue = <% Response.Write cmd.Parameters(0) %>

Cursors types in ADO
====================

1) adOpenDynamic :- Additions, Changes and Deletions by other users will be visible
2) adOpenKeyset :- Only changes will be visible. Additions and Deletions are not visible.
3) adOpenStatic :- Additions, Changes and Deletions by other users are not visible. Used for disconnected recordset model.
4) adOpenForward-only :- Additions, Changes and Deletions by other users are not visible. To only scroll forward only.

Cursor Locations in ADO
=======================

adUseNone
adUseServer
adUseClient (read only)
adUseClientBatch

Cursor Lock types in ADO
=======================

adLockPessimistic - Locks the row once after any edits occur.
adLockOptimistic - Locks the row only when Update is called.
adLockBatchOptimistic - Allows Batch Updates.
adLockReadOnly - Read only. Cannot alter the data.

Disconnected Recordset
=======================

set con= server.createobject("ADODB.Connection")
set rs= server.createobject("ADODB.Recordset")
rs.CursorLocation=adUseClient
con.open sqlcon
rs.open sqlqry, con, adOpenStatic, adLockBatchOptimistic
Set oRS.ActiveConnection = Nothing
Set GetanotherRS = oRS

Clustered and nonclustered indexes
=================================

Clustered indexes contains actual data at leaf nodes
non clustered indexes contains row locators that point to actual data. 249 -> 999