rschandrastechblog

The confusing "Invalid postback or callback argument" error in an .aspx page.

2011-11-13T22:41:00.000-08:00

Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.

Here is one possible condition, in which I could simulate this error :

You populate a GridView in Page_Load always. Instead normally you should do it only in case of post bacK :

if ( !Page.IsPostBack)
{
//.... code to populate the gridview
pui.PopulateGrid(CustomerGridView, scmbl_cust.GetCustomerDataTable());

}

If you comment the !Page.IsPostBack, you will get the above error.

GridView Vertical text in headers

2011-09-15T23:56:00.000-07:00

http://forums.asp.net/t/1049758.aspx/1

.verticaltext

{

font:bold 10px Tahoma;

color: #0000FF;

writing-mode: tb-rl;

filter: flipH() flipV();

}

So what I am doing is loading my gridview like usual, and then adding a _DataBound event for the gridview. So here is the code to rotate the header row.

Protected Sub gvMatrix_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvMatrix.DataBound

Dim style As New Web.UI.WebControls.Style

Dim row As GridViewRow = gvMatrix.HeaderRow

style.CssClass = "verticaltext"

For Each cell As TableCell In row.Cells

cell.ApplyStyle(style)

Next

end sub

Paging at DAL

2011-09-02T01:46:00.001-07:00

PAGING AT DAL

1. http://www.codeproject.com/KB/webforms/GridViewCustomPaging.aspx
====================================================================
CREATE PROCEDURE [usp_GetProducts]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = ProductID FROM Products ORDER BY ProductID

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT ProductID, ProductName FROM Products WHERE
ProductID >= @first_id
ORDER BY ProductID

SET ROWCOUNT 0

-- GEt the total rows

SELECT @totalRows = COUNT(ProductID) FROM Products
GO
====================================================================

2. http://www.aspsnippets.com/Articles/Custom-Paging-in-ASP.Net-GridView-using-SQL-Server-Stored-Procedure.aspx

====================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
INTO #Results
FROM [Customers]

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results
END
GO
====================================================================

Difference between abstract classes and interfaces

2011-08-27T04:50:00.000-07:00

What is the difference between an interface and an abstract class ?

Interface	Abstract Class
Interfaces are implemented i.e. instances of interfaces cannot be created by derived classes.	Classes are inherited i.e. derived classes do create instances of abstract classes
You cannot use access specifiers with members of an interface. All interface members are implicitly public and cannot have any other access specifier.	Members of abstract class can have all access specifiers except private i.e. they can be internal, public, protected and protected internal but not private.
All the members of an interface are implicitly abstract (although you cannot explicitly use abstract keyword because it not allowed to use access specifiers in interface).	An abstract class can have non-abstract members.
Interface cannot have static members.	Abstract classes can have non-abstract static members.
A class can inherit one or more interfaces.	A class can inherit from only one abstract (or non-abstract ) class.
Modifying an interface results in breaking the existing clients. This is because clients must implement all interface members, and interface members are only declarations , no code or values are allowed.	Not all modifications in an abstract class break existing clients. E.g. adding a new non-abstract method to the base abstract class will not break existing clients.
Interfaces CANNOT have constructors and destructors.	Constructors and destructors ARE ALLOWED in abstract classes. The abstract class constructors cannot be abstract or private.

When will an abstract class become equivalent to an interface ?
To equivalent to an interface , the abstract class must meet following conditions :
•    All of its members must be abstract.
•    It should not have members other than methods,properties,events and indexers.
•    Access modifiers to all of its members must be public.

On DbCommand, ExecuteReader, ExecuteScalar, etc and creating database through ADO.NET

2011-08-23T00:19:00.000-07:00

Ideally we run a DML query in ExecuteNonQuery.
What will happen when a DML query is run in ExecuteReader,ExecuteScalar,or ExecuteXmlReader ?

ExecuteReader
No Exception is thrown, RecordsAffected contains the number of records affected.
HasRows returns false, .Read returns false, RecordsAffected contains number of records afftected.
When read is attempted, error "Invalid attempt to read when no data is present" is thrown

ExecuteScalar
No Exception is thrown, query is executed, however NULL object is returned,
Returns the first column of first row, is not found returns null ( null object).
When the return value is actually null, DBNull.Value is returned.

ExecuteXmlReader
InvalidOperationException is thrown , however, query is executed before throwing an exception.
"Invalid command sent to ExecuteXmlReader. The command must return an Xml result."

Another interesting question :
Can you create database and other objects using the DbCommand objects?
YES.
But you cannot immediaately switch to the database because you cannot use GO statements in
DbCommand command texts, so you need to change the connection string to change 'Initial Catalog' name
and then issue rest of commands.

What are the attributes of connection string ?

Data Source : Name/IP of the server
Initial Catalog : Name of the database
User ID : User id
Password : user password
Persist Secutiry Info : True by default.

Can you use JOIN in UPDATE ?

2011-07-20T23:36:00.000-07:00

YES.

e.g.
update
set
from

where

Remember the syntax :
UPDATE TableA
SET colname=TableB.colname ( or anything else)
FROM TableA, TableB

WHERE <join condition>

Have a look at following links for more information :

http://www.tek-tips.com/faqs.cfm?fid=1958

http://www.bennadel.com/blog/938-Using-A-SQL-JOIN-In-A-SQL-UPDATE-Statement-Thanks-John-Eric-.htm

SUMMARTIMELISTSENSUMMER2011.id >= #tempRSC.id and SUMMARTIMELISTSENSUMMER2011.id <= #tempRSC.laststoprowid SUMMARTIMELISTSENSUMMER2011 ,#tempRSC routename = #tempRSC.f33 SUMMARTIMELISTSENSUMMER2011

OOP fundamentals : Explicite Interface Implementation and Structures can contain class

2011-07-15T04:38:00.001-07:00

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;public
{

{

Label1.Text = abcd1.ret().ToString();

Label1.Text +=
}
}
partial class _Default : System.Web.UI.Page protected void Page_Load(object sender, EventArgs e)abcd abcd1 = new abcd(30);ZZZZ z = new ZZZZ();aa a = new ZZZZ();bb b = new ZZZZ();" " + z.meth() + " " + a.meth().ToString() + " " + b.meth();class ZZZZ : aa, bb{

{

}public int meth()return 55;#region

{

} aa Membersint aa.meth()return 66;#endregion
#region

{

} bb Membersint bb.meth()return 77;#endregion}interface aa{

}int meth();interface bb{

}
int meth();struct abcd{

{
x =
}
public aaaaaa x;public abcd(int z)new aaaaaa();public class aaaaaa{

{
a = 10;
}
}

{

}
} public int a;public aaaaaa()public int ret()return x.a;

http://ramanisandeep.wordpress.com

2011-07-12T02:23:00.001-07:00

http://ramanisandeep.wordpress.com/

How to create Sr No in GridView

2011-07-12T00:11:00.000-07:00

Add a TemplateField and inside ItemTemplate put

<%# Container.DataItemIndex + 1 %>

SQL Server Date Time : What will be the output of cast(0 as datetime) ?

2011-07-11T23:50:00.000-07:00

SQL DATE TIME
1. What will be the output of following ?
select CAST (0 as datetime)
'1900-01-01 00:00:00.000'
returns epoch
2. How to create a date from given integers for year , month,date
Declare @DayOfMonth TinyInt
Set @DayOfMonth = 20
Declare @Month TinyInt
Set @Month = 6
Declare @Year Integer
Set @Year = 2006
Select DateAdd(day, @DayOfMonth - 1,DateAdd(month, @Month - 1,DateAdd(Year, @Year-1900, 0)))
3. How to get first date of current month ?
select convert(datetime ,
cast(DATEPART(year , getdate()) as nvarchar(10)) + '-' +
cast (DATEPART(month , getdate()) as nvarchar(5)) +
'-01'
)

static

2011-07-08T04:58:00.001-07:00

1. Which operators cannot be overloaded ?
=, ., ?:, ->, new, is, sizeof, typeof
2. Comparsion operator
==, !=, <, >, <=, >=
They must be overloaded in pairs.
3. How do you overload operators ?
By defining static member functions using the operator keyword.
4. Can non-static classes contain static members ?
YES.
5. Can static classes contain non-static members ?
NO.
6. Can you access static members of non-static classes using instances of non-static members ?
NO. Static members must be accessed using thier class names, not using instances
7. What all can be static ?
method, field, property, or event
constructors can be static.
const behave like static fields , but static keyword cannot be used with them
operator overloading functions are always a static
Indexers cannot be static, the allowed access specifiers for indexers are only

It is mutually exclusive to mark a method with the new and override keywords.

2011-07-06T04:28:00.000-07:00

http://msdn.microsoft.com/en-us/library/cstb4x06(v=vs.80).aspx
Compiler Error CS0113

Classes , Structs and Interfaces

2011-07-06T03:32:00.001-07:00

Classes

What can be the members of class, struct and interfaces?

Class: (http://msdn.microsoft.com/en-us/library/0b0thckt.aspx)

· Constructors

· Destructors

· Constants

· Fields

· Methods

· Properties

· Indexers

· Operators

· Events

· Delegates

· Classes

· Interfaces

· Structs

Struct: (http://msdn.microsoft.com/en-us/library/ah19swz4.aspx)

constructors, constants, fields, methods, properties, indexers, operators, events, and nested types,

Interfaces : methods, properties, events, indexers (http://msdn.microsoft.com/en-us/library/ms173156.aspx)

	Class	Struct	Interface
Constructors	Y	Y	N
Destructors	Y	N	N
Constants	Y	Y	N
Fields	Y	Y	N
Methods	Y	Y	Y
Properties	Y	Y	Y
Indexers	Y	Y	Y
Operators	Y	Y	N
Events	Y	Y	Y

Can a static class contain non-static members?

NO. (http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx).

The main features of a static class are:

· They only contain static members.

· They cannot be instantiated.

· They are sealed.

· They cannot contain Instance Constructors.

Constructors:
- Constructors cannot be “virtual”.
- They cannot be inherited.
- Constructors are called in the order of inheritance.
- Constructors can be overloaded.
- special function that is called when class is instantiated.
- same name as the class.
- does not have a return type.
- used to initialize values.
- can have parameters.
- compiler will supply one if not declared.

Can you define constructors for struct?

We cannot define default constructor for structs, because one is compulsorily provided by compiler.

However, we can define constructors for structs that take parameters.

Remember that the default constructor is called when we use new with struct. However, objects based on structs can be initialized or assigned and then used.

e.g. int I = new int(); //valid ; can be used

int i ; I = 33; //valid ; can be used

int I; // cannot be directly used without assigning a value, because new is not given, hence value is not initialized.

Can you define a default constructor for a strut ?

NO. It is compulsorily provided by compiler.

In how many type value types can be created?

By calling new : int I = new int();

By assigning a value: int I = 44; //using new is not a requirement.

A base class has no default constructor but has some parametered constructors. Will the derived class have to do something?

If a base class does not offer a default constructor, the derived class must make an explicit call to a base constructor by using base. (MSDN http://msdn.microsoft.com/en-us/library/ms173115.aspx#Y1200)

Can constructors be inherited? NO.

What is the order of calling constructors and destructors in an inheritance chain ?

Constructors : Least derived (base) to Most Derived.

Destructors : Most Derived to Least Derived. (http://msdn.microsoft.com/en-us/library/66x5fx1b(v=vs.80).aspx)

Consider the following :

Class A {

Public A() {}

~A(){}

}

Class B : A {

Public B() {}

~B(){}

}

What will be the order of ctor/dtor in following cases ?

B b = new A(); //INVALID

A a = new B(); // ctor A, ctor B , dtor B , dtor A

In a nested class, can you access members of container class ?

NOT DIRECTLY, you have to pass an instance of container to nested class either in nested class’s ctor or some other method.

Destructors :

· Destructors cannot be defined in structs. They are only used with classes.

· A class can only have one destructor.

· Destructors cannot be inherited or overloaded.

· Destructors cannot be called. They are invoked automatically.

· A destructor does not take modifiers or have parameters.

(MSDN http://msdn.microsoft.com/en-us/library/66x5fx1b.aspx)

What are the differences between classes and structs ?

Class	Struct
Classes are reference type	Struct is a value type
Classes can inherit from another class or interface but not from a class (struct cannot be a base of a class)	Structs cannot inherit from other structs or classes (http://msdn.microsoft.com/en-us/library/ms173149(v=vs.80).aspx) A struct cannot inherit from another struct or class, and it cannot be the base of a class http://msdn.microsoft.com/en-us/library/aa288471(v=vs.71).aspx
Classes can have destructors	Structs cannot have destructors. http://msdn.microsoft.com/en-us/library/66x5fx1b.aspx

Encapsulation

What is the default access level of members in a class?

Private. This is applicable to constructor also, if a constructor is provided and no access specifiers are provided for it.

However, if you do not provide a default constructor to a class, the default parameterless constructor provided by the compiler is public.

Can you inherit from a class which has only private constructors ?

No. It is not possible to inherit a class that has only a private constructor.

Can you inherit from a static class ?

NO. Static classes are sealed and cannot be inherited. http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx

Can a static class contain not static methods?

Inheritance

What is class inheritance and interface inheritance?

Can you inherit from a class which has only private constructors ?

No. It is not possible to inherit a class that has only a private constructor.

What is the difference in virtual and abstract methods?

When talking about the base class, a virtual method may or may not contain implementation. But, an abstract method cannot contain implementation.

When talking about the (non-abstract) derived class, a virtual method may or may not be overridden, but an abstract method must be overridden ( or actually implemented).

Polymorphism

How many types of overloading are supported?

Method overloads and operator overloading.

What is operator overloading?

OOP

2011-07-06T03:31:00.000-07:00

Classes

What can be the members of class, struct and interfaces?

Class: (http://msdn.microsoft.com/en-us/library/0b0thckt.aspx)

· Constructors

· Destructors

· Constants

· Fields

· Methods

· Properties

· Indexers

· Operators

· Events

· Delegates

· Classes

· Interfaces

· Structs

Struct: (http://msdn.microsoft.com/en-us/library/ah19swz4.aspx)

constructors, constants, fields, methods, properties, indexers, operators, events, and nested types,

Interfaces : methods, properties, events, indexers (http://msdn.microsoft.com/en-us/library/ms173156.aspx)

	Class	Struct	Interface
Constructors	Y	Y	N
Destructors	Y	N	N
Constants	Y	Y	N
Fields	Y	Y	N
Methods	Y	Y	Y
Properties	Y	Y	Y
Indexers	Y	Y	Y
Operators	Y	Y	N
Events	Y	Y	Y

Can a static class contain non-static members?

NO. (http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx).

The main features of a static class are:

· They only contain static members.

· They cannot be instantiated.

· They are sealed.

· They cannot contain Instance Constructors.

Can you define constructors for struct?

We cannot define default constructor for structs, because one is compulsorily provided by compiler.

However, we can define constructors for structs that take parameters.

Remember that the default constructor is called when we use new with struct. However, objects based on structs can be initialized or assigned and then used.

e.g. int I = new int(); //valid ; can be used

int i ; I = 33; //valid ; can be used

int I; // cannot be directly used without assigning a value, because new is not given, hence value is not initialized.

Can you define a default constructor for a strut ?

NO. It is compulsorily provided by compiler.

In how many type value types can be created?

By calling new : int I = new int();

By assigning a value: int I = 44; //using new is not a requirement.

A base class has no default constructor but has some parametered constructors. Will the derived class have to do something?

Can constructors be inherited? NO.

What is the order of calling constructors and destructors in an inheritance chain ?

Constructors : Least derived (base) to Most Derived.

Destructors : Most Derived to Least Derived. (http://msdn.microsoft.com/en-us/library/66x5fx1b(v=vs.80).aspx)

In a nested class, can you access members of container class ?

NOT DIRECTLY, you have to pass an instance of container to nested class either in nested class’s ctor or some other method.

Destructors :

· Destructors cannot be defined in structs. They are only used with classes.

· A class can only have one destructor.

· Destructors cannot be inherited or overloaded.

· Destructors cannot be called. They are invoked automatically.

· A destructor does not take modifiers or have parameters.

(MSDN http://msdn.microsoft.com/en-us/library/66x5fx1b.aspx)

What are the differences between classes and structs ?

Class	Struct
Classes are reference type	Struct is a value type
Classes can inherit from another class or interface but not from a class (struct cannot be a base of a class)	Structs cannot inherit from other structs or classes (http://msdn.microsoft.com/en-us/library/ms173149(v=vs.80).aspx) A struct cannot inherit from another struct or class, and it cannot be the base of a class http://msdn.microsoft.com/en-us/library/aa288471(v=vs.71).aspx
Classes can have destructors	Structs cannot have destructors. http://msdn.microsoft.com/en-us/library/66x5fx1b.aspx

Encapsulation

What is the default access level of members in a class?

Private. This is applicable to constructor also, if a constructor is provided and no access specifiers are provided for it.

However, if you do not provide a default constructor to a class, the default parameterless constructor provided by the compiler is public.

Can you inherit from a class which has only private constructors ?

No. It is not possible to inherit a class that has only a private constructor.

Can you inherit from a static class ?

NO. Static classes are sealed and cannot be inherited. http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx

Can a static class contain not static methods?

Inheritance

What is class inheritance and interface inheritance?

Can you inherit from a class which has only private constructors ?

No. It is not possible to inherit a class that has only a private constructor.

What is the difference in virtual and abstract methods?

When talking about the base class, a virtual method may or may not contain implementation. But, an abstract method cannot contain implementation.

When talking about the (non-abstract) derived class, a virtual method may or may not be overridden, but an abstract method must be overridden ( or actually implemented).

Polymorphism

How many types of overloading are supported?

Method overloads and operator overloading.

What is operator overloading?

SQL COUNT() function (*) includes null, rest do not

2011-07-05T04:09:00.000-07:00

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead.

SQL Server : You can use @table variables in udf, but not # or ## temp tables

2011-07-05T02:45:00.000-07:00

The following code will throw error :

select * into ##tempaaa from LiveData

alter
returns
as
begindeclare @a bigintselect @a = max(id) from ##tempaaa
return @inp
end

The error will be :
Cannot access temporary tables from within a function. int function test(@inp int )

Cannot use the OUTPUT option in a DECLARE, CREATE AGGREGATE or CREATE FUNCTION statement.

2011-07-05T02:32:00.001-07:00

what is create aggregate ?

SQL Server : Can a user-defined function return a text value ?

2011-07-05T02:20:00.000-07:00

NO.

SQL user-defined functions cannot return text,ntext or image.

http://msdn.microsoft.com/en-us/library/aa258261(v=SQL.80).aspx
can be any of the scalar data types supported by SQL Server, except text, ntext, image, and timestamp.

Can you use try..catch block in user defined functions ?

2011-07-05T02:13:00.000-07:00

NO.

http://msdn.microsoft.com/en-us/library/ms175976.aspx
The TRY…CATCH construct cannot be used in a user-defined function.

Inline Functions : What is wrong with the following code ?

2011-07-05T00:01:00.000-07:00

What is wrong with the following code :

create function test
( @inp int)
returns int as
return ( @inp)

[http://msdn.microsoft.com/en-us/library/aa214762(v=sql.80).aspx
Inline user-defined functions are a subset of user-defined functions that return a table]

Inline functions cannot return any other data type than table.

http://msdn.microsoft.com/en-us/library/ms189294.aspx

Inline User-defined Function Rules

The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.

There is no function_body delimited by BEGIN and END.

The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

The table-valued function accepts only constants or @local_variable arguments

Inline user-defined functions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.

Stored Procedures and User Defined Functions

2011-07-04T23:31:00.000-07:00

Stored Procedures and User Defined Functions

What is the maximum number of parameters supported by a stored procedure or user defined function?

2100 for both

In how many ways a stored procedure can return results?

· Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).

· Return codes, which are always an integer value.

· A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.

· A global cursor that can be referenced outside the stored procedure.

Suppose a stored procedure declares a local temp table #temp. If it calls another stored procedure, will the #temp available to nested proc ?
YES.

What is procedure cache in SQL Server ?

2011-07-04T21:19:00.000-07:00

The part of SQL server memory pool that is used to store execution plans is refered to as procedure cache in SQL Server.

DBCC FREEPROCCACHE
OR
DBCC FREEPROCCACHE WITH NO_INFOMSGS

is used to free procedure caches.

SQL Server : Null values do not match other null values.

2011-07-04T03:37:00.000-07:00

http://msdn.microsoft.com/en-us/library/aa258291(v=sql.80).aspx

Note that NULL = NULL is NOT TRUE Also NULL <> NULL is NOT TRUE

INNER JOIN DOES NOT CONTAI N NULL VALUES IN OUTPUT

if ( NULL = NULL ) print 'yes' else
print 'no'

output
-----------
no

if ( NULL <> NULL ) print 'yes' else
print 'no'

output
-----------
no

What you should know about Crystal Reports ?

2011-07-03T02:28:00.000-07:00

1. How do add a parameter field (Selection Formula)
First add a parameter field Parameter Fields ->New ->Give name and value .
Right Click anywhere on the report, Report ->Selection Formula ->Record.
The selection formula will be something like
{StoredProcedure3;1.Product_name} = {?paramField}
where paramField is the parameter field you have given and LHS is the field from datasource
Put the condition and save.

2. How to use a SQL Stored Procedure ?
It will appear in the OLE DB (ADO) connection, simple can be used as a table.

3. How to add a subreport ?
Right click on the report anywhere , Insert->Subreport

Choose Report

If you want OnDemand sub report , check the On-Demand sup-report check box.

4. How to add a summary field on a section ?

Simply right click on the section, Insert->Summary, choose appropriate fields , and press OK.

Remember that you can add
Parameter Field, Running Total Field, Formula field ,special fields and group name fields

PArameter - > report parameter
running total-> total of a column groupwise/pagewise/total
formula-> to apply a formula, e.g. {Product.Product_price} * {OrderDetails.OrderDetails_qty}
special fields are like page numer, total page count, date , print time , etc

Crystal Report Concepts

2011-07-03T01:17:00.001-07:00

Suppose I want to produce a report something like following

Order details for each order, grouped by customers, sorted by date ASC

Each row will contail OrderId, ProductName, Qty, UnitPrice , ItemPrice

After each group end , there will be a total of customers orders.

1. Insert a group on CustomerID or name

2. Add a formula field called ItemTotal with the formula

{Product.Product_price} *{OrderDetails.OrderDetails_qty}

3. Add a Running Total Field say Rtotal0 with following properties :

Field To Summarize : @ItemTotal
Evaluate : For each record
Reset : On Change of group

Place this field on GroupFooterSection

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

I want to find Maximum and minimum of group values, say
max itemprice and min itemprice.

You need to define two formula fields and place them on the
GroupFooterSection.
Maximum ({@ItemTotal})

==============================================================
In formulas, field names are enclosed within "{}" curly brackets
and non-table fields are preceded with "@" symbol
==============================================================

How to limit the number of records in a crystal report ?

2011-07-03T00:38:00.000-07:00

For this you need to count records in the details section and reset the count after every nth records. The count can be reset in the header section. NOTE that the header section will be executed, if you break the page every nth record.

So what you need is :

1. A count of records placed in details section ,
and a formula in details section which will increment count for every record displayed on the details section
2. A formula in the header section which will reset this count to 0
3. A page break after the nth value of counter.

The details can be found at many places on the web, e,g,
LIMITING THE NUMBER OF RECORDS PER PAGE ON A CRYSTAL REPORT
http://www.accuvar.com/tips/crw_records.htm

Header formula : called HeaderReset here
WhilePrintingRecords;
NumberVar counter:=0

Details section formula called CountDetails here
WhilePrintingRecords;
NumberVar counter;
counter:=counter+1

And a pagebreak after {@CountDetails}= n

NOTE that placement of formulae in the correct section (header or detail) is very important here

to get the correct result

Fabulous Adventures In Coding

2011-07-02T23:51:00.001-07:00

Eric Lippert's Blog

http://blogs.msdn.com/b/ericlippert/

How do I use SQLExpress with Crystal Reports ?

2011-07-02T10:49:00.000-07:00

There are two ways to do this: Using "OLE DB(ADO)" connection or "ADO.NET Dataset"

Let's explore :

1.Create a database in the SQLExpress instance using the

Server Explorer->Data Connections->Right Click->Create New SQL Server Database

Database created this way will be directly available in the

Database Expert->Create New Connection->OLE DB(ADO) option.

The "OLE DB(ADO)" wizard will start. If it does not
start, click on "Make New Connection" to start it.

In the "OLE DB Provider" box, select "OLE DB Provider for SQL Server"
(Even selecting "SQL Server Native Client 10.0" is OK) and click next.

If your express server does not appear in the
"Connection Information" box, type ".\SQLExpress" in the "Server" combo.

When you check "Integrated Security" , credentials will not be asked.

The database you created from Server Explorer will be available in the
"Database" combo.

2. If you do not want to create a database in the SQLExpress instance,
but instead want to work with .mdf file, you need to take a different
approach :

Create the mdf file.
Then add a new item to the project, "Dataset". This
will add "Dataset1.xsd" to the project and open the Dataset designer.

In the dataset designer, drag all the required tables from server
explorer.

Now when you open Database Expert, "Dataset1" will appear in
"Project Data"->"ADO.NET Datasets".

crystal report db

2011-07-02T05:34:00.001-07:00

CREATE TABLE [dbo].[OrderMaster] (
[OrderMaster_id] [int] NOT NULL ,
[OrderMaster_date] [datetime] NULL ,
[OrderMaster_customername] [varchar] (50),
[OrderMaster_createduser] [varchar] (50)
)

CREATE TABLE [dbo].[OrderDetails] (
[OrderDetails_id] [int] NOT NULL ,
[OrderDetails_masterid] [int] NULL ,
[OrderDetails_productid] [int] NULL ,
[OrderDetails_qty] [int] NULL
)

CREATE TABLE [dbo].[Product] (
[Product_id] [int] NOT NULL ,
[Product_name] [varchar] (50) ,
[Product_price] [numeric](18, 2) NULL
)

insert product (product_id, product_name,product_price)
select 1 , 'Product1' , 10.50
union all
select 2 , 'Product2' , 20.25
union all
select 3 , 'Product3' , 30.40
union all
select 4 , 'Product4' , 5.50
union all
select 5 , 'Product5' , 76.50
union all
select 6 , 'Product6' , 45.50
union all
select 7 , 'Product7' , 10.50

insert ordermaster (
OrderMaster_id,
OrderMaster_date,
OrderMaster_customername,
OrderMaster_createduser)
select 1 , getdate() , 'customer1' , 'user'
union all
select 2 , dateadd(day , -1 , getdate()) , 'customer1' , 'user'
union all
select 3 , dateadd(day , -2 ,getdate()) , 'customer2' , 'user'
union all
select 4 , getdate() , 'customer4' , 'user'
union all
select 5 , dateadd(day , -5,getdate()) , 'customer4' , 'user'
union all
select 6 , getdate() , 'customer3' , 'user'

insert orderdetails (
OrderDetails_id,OrderDetails_masterid,OrderDetails_productid,
OrderDetails_qty)
select 1, 1, 2, 10
union all
select 1, 1, 6, 20
union all
select 1, 1, 4, 30
union all
select 1, 1, 2, 60
union all
select 1, 1, 7, 45
union all
select 1, 2, 7, 58
union all
select 1, 2, 4, 22
union all
select 1, 3, 4, 30
union all
select 1, 3, 2, 60
union all
select 1, 3, 7, 45

--DROP TABLE Product
--DROP Table OrderMAster
--DROP Table orderdetails

How will you check all the checkboxes in a Grid ?

2011-06-30T23:54:00.000-07:00

Can be done using javascript or code-behind(server code)

1. in javascript, access the gridview and then iterate through all child elements of type "checkbox".
then set the check if not already set using item.click.
http://www.codedigest.com/Articles/ASPNET/132_GridView_with_CheckBox_%e2%80%93_Select_All_and_Highlight_Selected_Row.aspx
using jquery this is simpler :
http://www.codedigest.com/CodeDigest/62-Check-All-Checkboxes-in-GridView-using-JQuery.aspx

2. in the code behind use the DataRowBind method to check all the check boxes, accessing cells of the row, then identifying cell type of checkbox and then call clickon each item
(to be verified)

jQuery Tutorials Link : http://www.codedigest.com/Tutorials/jQuery_Tutorials_For_ASPNET_Developers.aspx

2011-06-30T23:39:00.000-07:00

jQuery Tutorials For ASP.Net Developers

Beginning jQuery Development

What is jQuery? How to use it in ASP.Net Pages?

How to enable jQuery intellisense in Visual Studio 2008?

How to use jQuery intellisense in an external JavaScript file?

Introduction to jQuery in ASP.Net and 10 Advantages to Choose jQuery

Using jQuery in ASP.Net Application

Master-Detail View with Selectable GridView and DetailsView using jQuery in ASP.Net

Handling or Attaching Events to Dynamic HTML using jQuery in ASP.Net

Creating Accordion Panel using jQuery in ASP.Net

Building Collapsible Panel Control using jQuery in ASP.Net Page

http://www.codedigest.com/Tutorials/jQuery_Tutorials_For_ASPNET_Developers.aspx

Building Ajax Application using jQuery

Doing AJAX with jQuery in ASP.Net

Calling a WebService using jQuery in ASP.Net

Ajax Application using jQuery and JSON

Building Cascading DropDownList in ASP.Net Using jQuery and JSON

Creating a Simple AJAX Master-Details View Using jQuery, JSON and jTemplates in ASP.Net

Populating RadioButtonList Using jQuery, JSON in ASP.Net

Lazy Loading jQuery Collapsible Panel in ASP.Net Using JSON

UserName or Login Availability Check Using jQuery in ASP.Net

Rotate Text Link Advertisements Using JQuery in ASP.Net

Lazy Loading jquery Accordion Panel in ASP.Net

Creating a Simple Rating control using jQuery and AJAX in ASP.Net

UserName or Login Availability Check Using jQuery in ASP.Net

GridView Style Edit Update in Repeater Control Using jQuery and Ajax

GOOD JAVASCRIPT SITES

2011-06-29T02:51:00.000-07:00

http://www.w3schools.com/js/js_ex_dom.asp

http://www.quackit.com/javascript/examples/

http://www.comptechdoc.org/independent/web/cgi/javamanual/javadocument.html

CAST CONVERT PINAL

2011-06-29T02:28:00.001-07:00

http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.

How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

What are the online references for CAST and CONVERT?
CAST and CONVERT

Which one are better ? scalar , Table , multiline , inline UDFs

2011-06-28T04:30:00.000-07:00

Inline UDFs offer a SET based operation where as multi-line and scalar udfs offer row-based operation.

Bottom Line : Avoid CURSORs , WHILE LOOPs , multi-line and scalar udfs

http://www.sqlservercentral.com/articles/T-SQL/73887/
http://jahaines.blogspot.com/2009/10/converting-scalar-user-defined-function.html

1 indicate least recommended and 3 indicates most recommended UDFs

1. Scalar UDF
1. Multilne UDF
2. Table UDF
3. Inline Table UDF
3. Inline UDF

Page Split DBCC SQL Code

2011-06-28T04:13:00.000-07:00

This is from QOD40. Remember that even a ROLLBack causes a page split.

create table test ( c1 int , c2 varchar(1000))create

insert into test values (1, REPLICATE('a' , 900))insert into test values (2, REPLICATE('b' , 900))insert into test values (3, REPLICATE('c' , 900))insert into test values (4, REPLICATE('d' , 900))insert into test values (6, REPLICATE('f' , 900))insert into test values (7, REPLICATE('g' , 900))insert into test values (8, REPLICATE('h' , 900))insert into test values (9, REPLICATE('i' , 900))dbcc
begin
insert tran into test values (5, REPLICATE('e' , 900))rollback tran ind(0, 'test' , 1) clustered index pk on test(c1)

SQL Server Indexes Fact Sheet

2011-06-28T03:38:00.000-07:00

MS SQL Server

1. What is the maximum allowed size of index key in an index ( total size of columns used in the index definition) ?

900 bytes

2. How many indexes a table can have ?

1 Clustered and
999 non-clustered

3. How many columns can be combined in a composite index key ?
16

4. What is a heap ?
If there is no clustered index on a table, the table is called a heap.

5. How do I identify heaps and clustered indexed tables in my SQL Server database ?
The following query gives listing of clustered , non-clustered indexes and heaps :
select
(
join
(
on
order by b.type_desc ,a.name a.id = b.object_id select object_id , name , type_desc from sys.indexes ) B select id , name from sysobjects where xtype = 'U' ) A a.name , b.type_desc from

A column may have null values. But I want to ensure that all not null values are UNIQUE.

2011-06-28T00:17:00.000-07:00

A UNIQUE constraint is not useful here because, it will allow a single NULL value in the column, no DUPLICATE NULLs.

Use a filtered index here :
CREATE UNIQUE INDEX indexName ON tableName(columnNames) WHERE columnName IS NOT NULL

http://msdn.microsoft.com/en-us/library/cc280372.aspx

Difference between PRIMARY KEY and UNIQUE KEY

2011-06-28T00:14:00.001-07:00

PRIMARY KEY	UNIQUE KEY
NULL value is not allowed	NULL is allowed, subject to the condition that only a single NULL is allowed. Inserting more null throws “Cannot insert duplicate key” error.
Table can have only one PRIMARY KEY constraint defined	Table can have multiple UNIQUE KEY constraints Defined
By default, if a clustered index is not defined already on the table, defining a PRIMARY KEY constraint results in a clustered index with same name as PRIMARY KEY	By default non-clustered index is create when a UNIQUE KEY constraint is added.
Purpose is to provide a unique identity to each row	Purpose is to avoid duplication

A Good Link on "Generics, Reference Types, Value Types, and Interview Questions " http://geekswithblogs.net/michelotti/archive/2010/08/06/generics-reference-types-value-types-and-interview-questions.aspx

2011-06-27T10:47:00.001-07:00

Another good resume tips : http://ofb.net/~niniane/resume_howto.html http://www.detrition.net/resume.html

2011-06-26T22:45:00.001-07:00

http://ofb.net/~niniane/resume_howto.html

http://www.detrition.net/resume.html

Which can be rolled back - DELETE or TRUNCATE ?

2011-06-25T03:11:00.000-07:00

Refer http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

If DELETE or TRUNCATE are enclosed within a TRANSACTION, then both can be rolled back.

DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

because : In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files.

SQL : What is going under the hood ? Very good articles to get info about current SQL activities

2011-06-25T03:03:00.000-07:00

1. http://dotnetslackers.com/articles/sql/Identifying-currently-running-SQL-queries.aspx

Identifying currently running SQL queries

SELECT

es.session_id, es.host_name, es.login_name

, er.status, DB_NAME(database_id) AS DatabaseName

, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,

((CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1) AS [Individual Query]

, qt.text AS [Parent Query]

, es.program_name, er.start_time, qp.query_plan

, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads

, er.blocking_session_id, er.open_transaction_count, er.last_wait_type

, er.percent_complete

FROM sys.dm_exec_requests AS er

INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id

CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt

CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

WHERE es.is_user_process=1

AND es.session_Id NOT IN (@@SPID)

ORDER BY es.session_id

2. What's blocking my running SQL? http://dotnetslackers.com/articles/sql/Whats-blocking-my-running-SQL.aspx

SELECT
Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, BlockingSQL.text AS BlockingSQL
, Waits.wait_type WhyBlocked
, Blocked.session_id AS BlockedSessionId
, USER_NAME(Blocked.user_id) AS BlockedUser
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

Criterias for Primary Key

2011-06-25T02:52:00.001-07:00

Which key will you choose as a primary key ?
1. Primary key should not contain null values (should be declared as NOT NULL)
2. Should be unique in the domain ( current and future expected values)
3. Should not change over time

Can a foreign Key refer to same column in a table : YES

As per http://dotnetslackers.com/articles/sql/Difference-Between-Candidate-Keys-and-Primary-Key.aspx :

"A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind."

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType) Why WCF returns array representation on wire by default

2011-06-24T04:07:00.000-07:00

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType)

Why WCF returns array representation on wire by default
http://creamcode.wordpress.com/2011/05/15/why-wcf-returns-array-representation-on-wire-by-default/

The difference between <%= and <%# in ASP.NET

2011-06-23T22:18:00.000-07:00

http://blogs.msdn.com/b/dancre/archive/2007/02/13/the-difference-between-lt-and-lt-in-asp-net.aspx

•The <%= expressions are evaluated at render time
•The <%# expressions are evaluated at DataBind() time and are not evaluated at all if DataBind() is not called.
•<%# expressions can be used as properties in server-side controls. <%= expressions cannot.

Do you need to use ResolveUrl for href ?

2011-06-23T22:09:00.001-07:00

ALMOST NO.

WCF : IMPORTANT : BE CAREFUL WHEN RETURNING A DATATABLE : ALWAYS NAME THE TABLE

2011-06-23T04:22:00.000-07:00

If a web method in a wcf service is returning a DataTable, the DataTable must be given a name :

Public Function ValidateUser(ByVal UserId As String, ByVal Password As String) As DataTable Implements ILoginService.ValidateUserDim retval As Boolean = FalseDim dtTemp As New DataTable("ReturnTable")Dim cmd As New SqlCommandcmd.CommandType =
cmd.CommandText = CommandType.StoredProcedure"ValidateUser"cmd.Parameters.AddRange(
{

})
cmd.Connection =
cmd.Connection.Open()
dtTemp.Load(cmd.ExecuteReader())
cmd.Connection.Close()

New SqlParameter() _New SqlParameter("UserId", UserId), _New SqlParameter("Password", Password) _New SqlConnection(connstr)Return dtTempEnd Function

Other wise you may get an error something like this :

An existing connection was forcibly closed by the remote host

Exception Details: System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
Dim dtTemp As New DataTable("ReturnTable")

OPEN QUESTION : Are static classes provided default static constructor by compiler ?

2011-06-23T04:17:00.000-07:00

ALMOST "NO".

http://msdn.microsoft.com/en-us/library/ms173115(v=vs.80).aspx
Unless the class is static, classes without constructors are given a public default constructor by the C# compiler in order to enable class instantiation.

http://msdn.microsoft.com/en-us/library/79b3xss3(v=vs.80).aspx
Static classes cannot contain a constructor, although it is still possible to declare a static constructor to assign initial values or set up some static state.

The first statements almost makes it clear, although it is not mentioned clearly anywhere that default static constructors are NOT provided by compiler to static classes.

Another intersesting thing, what if both a derived and base class have static constructors ?
see this : http://msdn.microsoft.com/en-us/library/k9x6w0hc(v=vs.80).aspx

Static constructor of derived class is called before base class's static constructor

When base class and derived class both have static contructors, then first derived class static constructor is called and then base class static constructor is called. (In C#.NET 2.0)
This is exactly opoosite to the instance constructor hierarchy.

Method Overloading is done only by parameter types, not by return types

2011-06-23T00:20:00.000-07:00

{

}

int Meth(int a, int b)return 0;//compile time error //Error 1 Type 'Class1' already defines a member called 'Meth' with the same parameter types //long Meth(int a, int b)//{// return 0;

//VALID
{

}

//VALID//NOTE : EVEN A RETURN TYPE OF LONG IS ALSO VALID
{

}int Meth(string b, int a)return 0;int Meth(int a, string b)return 0; //}

Links FOR SQL SERVER PERFORMANCE TUNING

2011-06-22T22:58:00.000-07:00

1. CODEPROJECT

http://www.codeproject.com/KB/database/SQLServerTipsGuidelines.aspx

2. DOTNETHEAVEN
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

When all the columns from a table are selected, a PK clustered index is clearly the best option.

2011-06-22T22:21:00.000-07:00

This has reference to this article http://sqlserverpedia.com/wiki/Using_the_Right_Indexes_for_Optimal_Performance

dont know what the following makes sense :

LIVE_DATA_301 nonclustered located on PRIMARY Timestamp

count : 1062425

select * from LiveData301 select * from LiveData301 WITH (INDEX(LIVE_DATA_301))select

GPSString from LiveData301 WITH (INDEX(LIVE_DATA_301))

How to find list of indexes in a database ?

2011-06-22T21:46:00.000-07:00

Found very good options at
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db

The ones which I liked most are :

1.
DECLARE @IndexInfo TABLE (index_name         varchar(250)
                          ,index_description varchar(250)
                          ,index_keys         varchar(250))
INSERT INTO @IndexInfo exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

This does not return table name, but if you are sure you can makeout table name from
index name then this is sufficient, short and sweet.

NOTICE this sp : sp_msforeachtable

2. Following one is also good, but uses a loop and less readable than above.
But it returns table name also.

DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname
INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE
SELECT * from @IndexInfo

SQL Server - Which are the database recovery modes ?

2011-06-22T00:03:00.001-07:00

full
simple
bulk-logged

How to clear SQL Server Cache ?

2011-06-21T23:39:00.000-07:00

CONCLUSION :
1. Normally, use following two when you do not want query compilation also to
come into picture.
CHECKPOINT

DBCC DROPCLEANBUFFERS 
2. When you want query compilation also to come into picture,  free the proccache also : 

CHECKPOINT
DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

3. The most comprehensive cleaning strategy would be :

DBCC FREESYSTEMCACHE(All)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )

CHECKPOINT
DBCC DROPCLEANBUFFERS

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

From : http://sqlserverpedia.com/wiki/SELECT_DISTINCT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server

MSDN : DBCC DROPCLEANBUFFERS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187762.aspx

Removes ALL CLEAN BUFFERS from the buffer pool.

MSDN recommends to use CHECKPOINT before DROPCLEANBUFFERS.  AS you can see, DROPCLEANBUFFERS will drop only clean buffers, what about dirty buffers ?

clean buffers - memory and disk data is matching

dirty buffers - updated data present in memory, not written to disk

CHECKPOINT forces data of dirty pages to be written to disk, and makes it clean.

REMEMBER that CHECKPOINT only works on CURRENT DATABASE.

So next question : What is the difference between DROPCLEANBUFFERS and

FREEPROCCACHE ?

See http://mssqlonline.blogspot.com/2007/03/stored-procedure-clean-cache-and-clean.html for answer.

It says "DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run."

FREEPROCCACHE clears cached execution plans of SPs.

MSDN says http://msdn.microsoft.com/en-us/library/ms174283.aspx

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache.

Now another question : What are clean , dirty and cold buffer caches ?

CLEAN and DIRTY are well explained above. Not much clear about cold,

refer here for some explanation :

http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx

It says :

The cold buffer cache has a couple of variants within SQL Server.    Typically it refers to the buffer pool right after restart.   The data cache is not loaded (cold) and requires physical reads to populate the cache.   This is often termed the RAMPUP phase.     Internally it can also refer to the free list activity.   SQL Server understands the time of last access to buffers and when appropriate takes this into account when handling the free buffer lists.   Buffers on the free list that have some age to them may be termed cold because the age increases the chance that they accessible with a near memory transfer from a location such as CPU cache.

As per Pinal Dave http://blog.sqlauthority.com/2008/07/22/sql-server-clear-sql-server-memory-caches/

The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )

To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
For more information visit to : http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

How to disable WCF security ?

2011-06-21T04:49:00.000-07:00

In the host web.config, include a binding like following and assign it to the service :

Note the "CustomBinding" below, and note the "None"s : security, proxyCredential, clientcredential. Also note that the binding is assigned using the bindingConfiguration attribute.

<system.serviceModel>
    <bindings>
      <wsHttpBinding >
        <binding name="CustomBinding" >
          <security mode="None">
            <transport proxyCredentialType="None" clientCredentialType="None"></transport>
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>

      <service name="BusinessServiceLayer.LoginService">
        <endpoint address="" binding="wsHttpBinding" contract="BusinessServiceLayer.ILoginService" bindingConfiguration="CustomBinding" >
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://RelayTrack_Services.retser.com/BusinessServiceLayer/LoginService/" />
          </baseAddresses>
        </host>
      </service>
    </services>

How to Host a WCF Class Library Project ?

2011-06-21T03:38:00.000-07:00

When we create a WCF class library project, it is not ready to be hosted on IIS directly.
It needs web.config and .svc file.
Notice that the WCF class library project has a app.config file, but no web.config or .svc file.

There are two steps :
  Create web.cofig
  Create .svc file

1. To create web.config, add a blank text file to the class library project. Rename it to web.config.
Add the following to it and save it:
<?xml version="1.0" encoding="UTF-8"?>
  <configuration>
  </configuration>

Then copy the entire <system.serviceModel> tag from the existing app.config file of the class library peoject.

2. Next add a blank file to the wcf class library project and name it as a .svc file, say xyz.svc.
In the svc file add two statements :
  <%@ ServiceHost Service="BusinessServiceLayer.LoginService" %>
  <%@ Assembly Name="BusinessServiceLayer" %>

Note that the service value in the ServiceHost directive is the name appearing in the <service> tag in app.config/web.cofig

Assembly name is the name of the dll. By default this dll is created in bin\Debug of bin\Release folder of the
class library project. This must be copied out of the \Debug or \Release folder into bin folder for it to
be accessible to the IIS.

The last step is to set .svc as the default page in the IIS web site settings.

How to Store output of SQL Server EXEC into a table

2011-06-16T02:16:00.001-07:00

http://it.toolbox.com/wiki/index.php/Store_output_of_SQL_Server_EXEC_into_a_table

how to produce 0 without using any number in sql server ?

2011-06-16T02:10:00.000-07:00

1.
select $
2.
select £
3.
Declare @num int
Set @num = ''
Select @num

this is more useful. see what happens when a set an integer to null

Note that the following does NOT produce 0, it will produce NULL

Declare
Select @num int @num

How to share data between stored procedures ? Good Link

2011-06-15T22:35:00.000-07:00

http://www.sommarskog.se/share_data.html

Discusses in detail various methods :

This table summarises the methods:

Method	Input/ Output	SQL Server versions	Comment
Using OUTPUT Parameters	Output	All	Not generally applicable, but sometimes overlooked.
Table-valued Functions	Output	SQL 2000	Probably the best method for output, but has some restrictions.
Inline Functions			Use this when you want to reuse a single SELECT.
Multi-statement Functions			When you need to encapsulate more complex logic.
Using a Table	In/Out	All	Most general methods with no restrictions, but a little more complex to use.
Sharing a Temp Table			Mainly for single pair of caller/callee.
Process-keyed Table			Best choice for many callers to same callee.
Global Temp Tables			A variation of Process-Keyed.
INSERT-EXEC	Output	SQL 6.5	Does not require rewrite. Has some gotchas.
Table Parameters and Table Types	In/(Out)	SQL 2008	Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLR	Output	SQL 2005	Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERY	Output	SQL 7	Does not require rewrite. Tricky with many pitfalls.
Using XML	In/Out	SQL 2005	A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor Variables	Output	SQL 7	Not recommendable.

Have a look at other articles also on the home page :

http://www.sommarskog.se/index.html

The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not. (Added June 2001, Completely revised: April 2006. Most recent update: December 2008.) German translations of an old version available.

Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. (Added March 2003. Version for SQL 2005 added March 2007, major revision and addition of article for SQL 2008: January 2010. Most recent update October 2010.)

Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. (Added May 2003. Most recent update: November 2009. French and German translations of the SQL 2005 version available.)

How to share data between stored procedures. Different ways of passing sets of data between stored procedures. (Added July 2001, Most recent update: January 2010)

Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. (Added February 2006)

Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. (Added February 2011.)

Build Your Own Index. This is a teaser for.. Well, follow the link and read on! (Added November 2009.)

Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000. (Added July 2003. Most recent update: November 2009 )

Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. (Added Jan 1999, Latest update: June 2008)

How find the difference between two consecutive rows in a table ?

2011-06-15T22:10:00.000-07:00

By using a self join , as below.
Note that this query assumes the table has an autoincrement column like ID . If it is not, then possibly it will have to generated using ROW_NUMBER. Note that using autoincrement is better than using something like TOP.

create table test ( ID int, val int) insert test( ID , val) select 1, 10union all select 2, 12union all select 3, 9union all select 4, 15union all select 5, 8union all select

select

select

ID val
1 10
2 12
3 9
4 15
5 8
6 20

select a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on

ID ID val val diff
1 NULL 10 NULL NULL
2 1 12 10 2
3 2 9 12 -3
4 3 15 9 6
5 4 8 15 -7
6 5 20 8 12 b.ID = a.ID -1 * from test a.ID , b.ID , a.val , b.val , a.val - b.val as diff from test a left join test b on b.ID = a.ID -1 6, 20