Insert multiple rows of Data in SQL server using XML

The XML must be like:

<surveys>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
</surveys>

This XML must be passed as argument to stored procedure. Create a stored procedure and pass the XML document as a parameter. In the example below @XMLDOC is a string variable with the actual XML passed to it.

DECLARE @xml_hndl INT

--prepare the XML Document by executing a system stored procedure
EXEC SP_XML_PREPAREDOCUMENT @xml_hndl OUTPUT, @XMLDOC

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO SurveyResponse

        SELECT SurveyId, 
               QuestionId, 
               Answer, 
               UserId, 
               UserName, 
               GETDATE(), 
               GETDATE()
        FROM
        OPENXML(@xml_hndl, '/surveys/survey', 1)
        WITH
         (
             SurveyId BIGINT '@SurveyId',
             QuestionId BIGINT '@QuestionId',
             Answer INT '@Answer',
             UserId VARCHAR(100) '@UserId',
             UserName VARCHAR(200) '@UserName'
         )

        SET @return = 1
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    SET @return = 0
END CATCH

Omniture Tutorial

Good tutorial for understanding how Omniture works.

http://www.slideshare.net/nablerwa/an-introduction-to-omniture

Attach excel to SQL Server DB as linked server

Query to attach excel to DB as linked server.

EXEC sp_addlinkedserver 'ImportData',
                        'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
                        'D:\Working\Web_Analytics_Requirements.xls',
                        NULL,
                        'Excel 8.0'
GO
--ImportData is the name of new linked server
--D:\Working\Web_Analytics_Requirements.xls is the path to excel
--Excel 8.0 is for MS office 2003

To insert the spreadsheet as a table in database

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Summary$]')
--ImportData is the name of linked server
--Summary is the name the spreadsheet

To directly fetch values from linked server (excel file)

SELECT * FROM ImportData...Summary$

Header& Footer in a web page

The body tag should be like:

<body>
    <form id="master" runat="server">
        <div id="pageheader"></div>
        <div id="pagebody">
            <div>CONTENT</div>
        </div>
        <div id="pagefooter"></div>
    </form>
</body>

CSS for this tag should be:

body
{
    margin:0px;
    font-family:Calibri;
    font-size:15px;
    padding: 40px 0 25px 0;
    _padding-top:0px; /*for ie 6*/
    overflow:auto;
}
#pagebody
{
    overflow:auto;
    min-width:100%;
    vertical-align:middle;
    text-align:center;
}
#pageheader
{
    position:fixed;
    top:0px;
    left:0px;
    width:100%;
    height:40px;
    border-bottom:1px solid #FF6633;
    background-color:#FFFFFF;
    vertical-align:bottom;
}
#pagefooter
{
    position:fixed;
    _position:absolute;/*for ie 6*/
    bottom:0px;
    left:0px;
    width:100%;
    height:25px;
    border-top:1px solid #FF6633;
    text-align:left;
    vertical-align:bottom;
    background-color:#FFFFFF;
}

Add this section to head:

<!--[if !IE 7]>
    <style type="text/css">
        #pagebody {display:table;height:100%}
    </style>
<![endif]-->

ASP.Net Fundamentals

Important documents:

1. Design Patterns
2. Page Related Events
3. Garbage Collection & Dispose
4. Microsoft Application Blocks
5. ASP.Net Application Life Cycle Overview
6. ASP.Net Session state and State Management
7. OOPS Fundamentals
8. Java Script
9. jQuery

SQL Server cursor example

DECLARE db_cursor CURSOR FOR
SELECT name
FROM table
WHERE --some condition--

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

--sql query using @name goes here--
FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

SQL server query to drop default constraint and a column

declare @default sysname, @sql nvarchar(max)

select @default = name
from sys.default_constraints
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
select column_id
from sys.columns
where object_id = object_id('MYTABLENAME')
and name = 'MYCOLUMNNAME'
)

set @sql = N'alter table MYTABLENAME drop constraint ' + @default
exec sp_executesql @sql

alter table MYTABLENAME drop MYCOLUMNNAME

go