use datalength function in mssqlserver


DECLARE @TestVariable AS VARCHAR
DECLARE @NextTestVariable AS CHAR
SET @TestVariable = ‘abc’
SET @NextTestVariable = ‘abc’
SELECT DATALENGTH(@TestVariable), DATALENGTH(@NextTestVariable)

Correct answer:
1,1
Explanation:
When length is not specified in a variable declaration statement in VARCHAR/CHAR, the default length is 1.

Use of Datalength in MSsqlserver


DECLARE @TestVariable AS VARCHAR
DECLARE @NextTestVariable AS CHAR
SET @TestVariable = ‘abc’
SET @NextTestVariable = ‘abc’
SELECT DATALENGTH(@TestVariable), DATALENGTH(@NextTestVariable)

Correct answer:
1,1
Explanation:
When length is not specified in a variable declaration statement in VARCHAR/CHAR, the default length is 1.

Trigger with encryption in SqlServer


The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.
–How can create trigger with encryption
CREATE TRIGGER TempTriggerfordelete
ON tbl_Employee WITH ENCRYPTION
INSTEAD OF DELETE

AS
BEGIN
PRINT ‘delete trigger fired. Cannot insert values in the table TempTable.’
END

What are Triggers in MSSqlserver


A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database.
The following are major features of database triggers and their effects:

* triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
* triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
* triggers can cancel a requested operation
* triggers can cause mutating table errors

Triggers are of 3 types in SQL Server 2005:
1. DML Triggers
. AFTER Triggers
. INSTEAD OF Triggers
2. DDL Triggers
3. CLR Triggers
Note:DDL and CLR Triggers cannot work in SQL Server 2000
DML Trigger:-These Trigger is fired only when INSERT, UPDATE, and DELETE Statement occurs in table.
Explanation on DML Trigger:
Let us create a Table and insert some records in that Table.
1) After Triggers:
After Triggers can be created in 3 ways.
1) After INSERT
2) After UPDATE
3) After DELETE

1) creating After INSERT Trigger:-

Syntax:
create trigger triggername
on tablename
AFTER INSERT
As
[SQL Statement/PRINT command]
GO

Eg:
create trigger afterinsert_trigger
on emp
AFTER INSERT
as
PRINT ‘AFTER TRIGGER EXECUTED SUCESSFULLY’
GO

Creating AFTER UPDATE TRIGGER:-
create trigger afterupdate_trigger

on emp

AFTER UPDATE

as

PRINT ‘AFTER UPDATE TRIGGER EXECUTED SUCESSFULLY’

GO

Creating AFTER DELETE TRIGGER:

Create trigger afterdelete_trigger

On emp

AFTER DELETE

as

PRINT ‘AFTER DELETE TRIGGER EXECUTED SUCESSFULLY’
GO
Instead Of Update Trigger

Creating INSTEAD OF UPDATE TRIGGER:-

create trigger insteadofupdate_trigger
on emp
INSTEAD OF UPDATE
as
PRINT ‘INSTEAD OF UPDATE TRIGGER EXECUTED SUCESSFULLY’
GO
Instead of Delete Trigger

Creating INSTEAD OF DELETE TRIGGER:-

create trigger insteadofdelete_trigger
on emp
INSTEAD OF DELETE
as
PRINT ‘INSTEAD OF DELETE TRIGGER EXECUTED SUCESSFULLY’
GO

For practice run given below queries
Create database dbTester

USe dbTester

CREATE TABLE tbl_Employee(IntId Int Not Null Identity Primary Key,
strEmpname varchar(20) Not Null Default ‘Unkown’,
strEmpPhone varchar(10) Not Null Default ’00-000-000′
)
SELECT *FROM tbl_Employee

SELECT *FROM tbl_Employee

select *from sys.objects where type_desc=’USER_Table’

INSERT INTO tbl_Employee(strEmpname,strEmpPhone)VALUES (‘abc’,’9988745′)

alter trigger afterinsert_trigger
on tbl_Employee
AFTER INSERT
as
UPDATE dbo.tbl_employee set strempname=’Ritesh’
WHERE dbo.tbl_employee.intId=(SELECT max(dbo.tbl_employee.intId) from dbo.tbl_employee)
PRINT ‘AFTER TRIGGER EXECUTED SUCESSFULLY’
GO

alter trigger afterupdate_trigger
on tbl_Employee
AFTER UPDATE
as
/*UPDATE dbo.tbl_employee set strempname=’Ritesh’
WHERE dbo.tbl_employee.intId=(SELECT max(dbo.tbl_employee.intId) from dbo.tbl_employee)*/
PRINT ‘AFTER UPDATE TRIGGER EXECUTED SUCESSFULLY’
GO

CREATE TRIGGER TempTrigger
ON tbl_Employee
INSTEAD OF INSERT
AS
BEGIN
PRINT ‘Insert trigger fired. Cannot insert values in the table TempTable.’
END

–after execute this command run insert command you will get new puzzle answer
–and also remember to insert recor into renamed table
sp_rename ‘tbemp’ ,’tbl_employee’

USe of Row Number


CREATE TABLE TableOfShame
(
ShameCode varchar(4) NULL,
ShameType varchar(15) NULL,
ShamePriority varchar(10) NULL
);

SELECT *FROM TABLEOFSHAME

INSERT INTO TableOfShame(ShameCode,ShameType,ShamePriority)
SELECT ’01’, ‘Chagrin’, ‘Low’ UNION aLL
SELECT ’01’, ‘Chagrin’, ‘Low’ UNION aLL
SELECT ’02’, ‘Disgust’, ‘High’ UNION aLL
SELECT ’03’, ‘Abashment’,’Medium’ UNION aLL
SELECT ’04’, ‘Embarassment’,’Low’ UNION ALL
SELECT ’05’, ‘Humiliation’, ‘Medium’

these record insert again

WITH cte_FindDuplicateShame as
(
SELECT ShameCode, ShameType, ShamePriority,
ROW_NUMBER() over(PARTITION BY ShameCode, ShameType ORDER BY ShameCode DESC) as RowNum
FROM dbo.TableOfShame
)
SELECT ShameCode, ShameType, ShamePriority, RowNum
FROM cte_FindDuplicateShame
ORDER BY ShameCode, ShameType, RowNum;

WITH cte_FindDuplicateShame as
(
SELECT ShameCode, ShameType, ShamePriority,
ROW_NUMBER() over(PARTITION BY ShameCode, ShameType ORDER BY ShameCode DESC) RowNum
FROM dbo.TableOfShame
)
DELETE cte_FindDuplicateShame
WHERE RowNum 1;

SELECT * FROM TableOfShame ORDER BY ShameCode;

Be happy and enjoy in the programing world

URL Rewriting In asp.net


UrlRewriter.NET is an open-source, light-weight, highly configurable URL rewriting component for ASP.NET 1.1 and 2.0. UrlRewriter.NET provides similar IIS Rewrite capabilities that the Apache web server provides with mod_rewrite and .htaccess. You don’t need to install an ISAPI Rewrite filter to use the component. Best of all, UrlRewriter.NET is free and licensed with a very permissive MIT-style licence.

UrlRewriter.NET is a great Search Engine Optimization (SEO) tool. Using UrlRewriter.NET, you can create URL’s containing your target keywords, boosting your rankings.

With UrlRewriter.NET, you can:

* Rewrite URL’s from “user and Search Engine” friendly urls to the actual .aspx pages (also known as URL Masking, IIS Rewrite or ASP Rewrite)
* Redirect from old URL patterns to the new ones, ensuring the Search Engine spiders continue to follow your links (also known as URL Replace)
* Block certain visitors based on the User-Agent – very helpful for blocking crawlers that don’t obey the robots.txt protocol
* Ban users based on IP range (provides the capabilities of mod_rewrite on IIS)
* And much more…

UrlRewriter.NET is a pure .NET component written in C#, and does not require any ISAPI rewrite dll’s to be installed in IIS. You configure rules in a very readable XML format, either in your web.config file or an external rewriter configuration file.

UrlRewriter.NET is in use in many websites large and small such as DotNetKicks, and is embedded in several open source packages, such as the fantastic Yet Another Forum.NET.
Anyway here is how the project is constructed and how to get it working.

1. Create a new web project – I am using C# but if you really want to you can use VB
2. Download the urlrewriting library from the official site http://urlrewriter.net
3. Add a reference to the UrlRewritingNet.UrlRewriter.dll to your project
4. Open up the web.config file and replace the content with the following code:

<!–

–>

* Create the aspx pages

1. blog.aspx
2. contact.aspx
3. default.aspx (this should already exist)
4. longpage.aspx
5. search.aspx
6. submit_company.aspx

* In Default.aspx add the following code:

You will notice that the href links are defined in the web.config file.
* In search.aspx, add the following html code:

This is the region page – if you click on product12345.aspx, you will be redirected here but the code will show the arguments of the page i.e. 12345 – confused? You will be!

* Now open up the code behind file for search.aspx and add in the Page_Load event:

if(Request.QueryString[“id”] != null)
Response.Write(“querystring passed in: ” + Request.QueryString[“id”]);
else
Response.Write(“No query string passed in”);

* Run the project. If you click on the search link, you will be taken to the search page but notice the URL in the browser window as it should be reading Product-Search-UK. Same for the other url’s as well except for the last one, if you click on that, a product id will be passed into the page as a querystring that you can use to display specific products on a single page
If Want to download sample then Paste this link in address bar
http://blog.xploiter.com/wp-content/uploads/2009/04/urlrewriting.zip