Sandy Zone

EXEC SP_XML_PREPAREDOCUMENT

2009-02-19T03:10:00.000-08:00

PREPARE XML DOCUMENT

While handling using bulk inserts / updates ( using XMl in Stored Procedures )
care must be taken to handle to below characters.

1. &
2. <
3. >
4. '
5. "

XMl cannot process these symbols.

We need to pass them as html codes so that they get inserted properly.

1. &
2. <
3. >
4. "
5. '

As shown below.

strRemarks = Textbox.Replace("&", "&")
strRemarks = strRemarks.Replace("<", "<")
strRemarks = strRemarks.Replace(">", ">")
strRemarks = strRemarks.Replace("""", """)
strRemarks = strRemarks.Replace("'""'", """)
strRemarks = strRemarks.Replace("'", "'")

My Learnings...

2009-01-12T23:07:00.001-08:00

The deadlines must not be streched.

UNIT TESTING IS VERY IMPORTANT..

When some resource tells, i've completed the module, they must show unit testing as well as functional...

Provide complete information for the resources. Regarding their logic..

Make Sure they are not hard coding any things in the Application..

Before starting coding, do a brain storm session and ensure what reusable code and design can be help us out...

DATE FORMATS ARE Always a headache.

Use Convert.Datetime instead of Date.parse...

ASP TEXTBOXES made as Readonly fields wont return any value to the server, So use always HTML input boxes for readonly (Calendar)

Ensure that WBS is updated if necessary before start of coding.

ALWAYS HAVE A PROPER TIME ESTIMATE..

FROM THE NEXT PROJECT, JUST RECORD THE TIME REQUIRED TO COMPLETE A USP/ FORM OR WHATEVER, SO THAT WE CAN USE THAT INFORMATION FOR FURTHER TIME ESTIMTATION.

ASK FOR THE TEAM'S LEAVE PLAN...

ALWAYS BE PREPARED FOR ANY KINDA RISKS..

production issues.. needs to be addresses...

first ensure the design is proper in the stage of designing only.. Once done, we must not tinker with it always...

---------------------------------------------
My Observations and learning’s while working on MER

Requirement Gathering

I. Don’t Assume Any thing, get information properly

II. If we are gathering requirements for a particular page, then make sure all fields are covered, don’t miss even a single field.

III. MOMz are a must. Also specify as much details as possible in the documents so that in future it wont affect the functionality

IV. In SDD, Database level details must be specified very clearly.

V. If there are some conditional constraints that are very decisive, then it must be clearly specified.

VI. If there is any interaction with a third party system or a different system, then make sure, an exactly similar environment is available at the development end. Or else, while integrating both, problems are meant to rise and time also won’t be on our side.

VII. Once the SRS and SDD are done, just go through the same and do as it says. Don’t assume or remember things and do. SRS / SDD must be the source for coding. Just Open it and type the code.

VIII. It’s a very bad practice to start development without the SRS and SDD being signed off or else except more headaches in the later phases of the project where in the user may demand for requirements which you have not even heard of.

IX. If something can be done technically in more than one way, Keep all the options in front of them and let them come back with the option feasible for them.

a. For Example, Consider Client Side Validations, This can be done in the following ways

i. Javascript (Which we follow religiously)
ii. VB Script
iii. Field Validators.

So, instead of suffering last minute changes, we can avoid this at the requirement gathering stage itself.

Database Design

I. Do not have unnecessary table and Stored Procedures, which will just confuse us needlessly.

II. Make sure for a particular field A, the data type and the size is correctly defined.

III. Follow one Case, Either all BIG CAPS or all small caps. NeVeR BOTH

a. The above statement refers to the naming conventions of tables / views / stored procedures as well as Script statements

IV. If there is interaction with another database, just make sure views are created to access the same instead of directly querying onto it.

V. Creating VIEWS is very much important and it will reduce a lot of statement in the Where clause.

VI. If we are inserting / updating to more than one table in a Stored Procedure, Sql Transaction statements are a MUST.

VII. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.

Coding

I. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.

II. If there is some functionality which is common across the entire layer / Application, then use a common class file; write that piece of code here.

a. If there are changes, then it can be easily dealt with.

b. For example, if there is an ACTIVITYLOG functionality to be implemented, then

III. Add one line of comment for every method and if there is some complex code written, a comment is always necessary there. All comments must be short and live up to the complexity of the code.

IV. Make sure the pages cannot be directly accessed. Use Session Variables to ensure Direct Access to the application will not happen.

V. All datasets / Sessions or other disposable resources must be disposed where ever applicable.

VI. Try to user Data Reader where ever applicable, since the performance of the application will take a huge hike.

Testing

I. Try to see the application as a USER instead of a developer. There can be a chance wherein we can think of some changes that can help the user, do his work more effectively

II. Give the code to someone very new to the application and so

Implementation

VII. G

UAT

I. G

II. Give more importance to Design as well, CSS and alignment details.

III. Try

IV.

---------------------------------------------

2009-01-10T01:38:00.000-08:00

Been busy these days working with hectic Schedules.
Been discovering many new ways and enjoying working with Sql Server.
Creating Complex Stored Procedures and making Fun of

Here is , which i created recently.
Dynamically Create Columns and Rows from Two independent tables.
This was for one fin Report and i spent 3 hrs for this.

Try if you can digest it or else Move On...

-------------------------------------------

CREATE PROCEDURE USP_AUTO_MONTHLY_CONSOLIDATED_REPORT@PRODUCT_TYPE INT,@DATE_FROM DATETIME,@DATE_TO DATETIMEAS
/*DECLARE @PRODUCT_TYPE INTSET @PRODUCT_TYPE = 141DECLARE @DATE_FROM DATETIMEDECLARE @DATE_TO DATETIMESET @DATE_FROM = '1/01/2008'SET @DATE_TO = '1/07/2009'*/
BEGIN
SET NOCOUNT ON
DECLARE @TMP_DATE_FROM DATETIME DECLARE @MONTHNAME NVARCHAR(20) DECLARE @MONTHNO INT DECLARE @YEARNO INT
SET @TMP_DATE_FROM = @DATE_FROM
CREATE TABLE #TEMP_MONTH_YEAR_DETAILS ( MONTH_NAME NVARCHAR(20), MONTH_NO INT, YEAR_NO INT )
WHILE @TMP_DATE_FROM <= @DATE_TO BEGIN SELECT @MONTHNAME = CASE MONTH(@TMP_DATE_FROM) WHEN 1 THEN 'January (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 2 THEN 'February (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 3 THEN 'March (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 4 THEN 'April (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 5 THEN 'May (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 6 THEN 'June (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 7 THEN 'July (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 8 THEN 'August (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 9 THEN 'September (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 10 THEN 'October (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 11 THEN 'November (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 12 THEN 'December (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' END
--SELECT @MONTHNAME SET @MONTHNO = MONTH(@TMP_DATE_FROM) SET @YEARNO = YEAR(@TMP_DATE_FROM)
IF NOT EXISTS ( SELECT MONTH_NO FROM #TEMP_MONTH_YEAR_DETAILS WHERE MONTH_NO = @MONTHNO AND YEAR_NO = @YEARNO ) INSERT INTO #TEMP_MONTH_YEAR_DETAILS (MONTH_NAME , MONTH_NO , YEAR_NO) VALUES (@MONTHNAME,@MONTHNO,@YEARNO)
SET @TMP_DATE_FROM = DATEADD(MM,1,@TMP_DATE_FROM) END
--SELECT * FROM #TEMP_MONTH_YEAR_DETAILS
IF @PRODUCT_TYPE <> -1BEGIN
SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR
ENDELSEBEGIN-- SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR--END DROP TABLE #TEMP_MONTH_YEAR_DETAILSEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/************************************************************************** SP
************************************************************************/

CREATE PROCEDURE USP_PRODUCTWISE_DEVIATION_REASON_REPORT@DATE_FROM DATETIME,@DATE_TO DATETIMEASBEGIN /*DECLARE @DATE_FROM DATETIME DECLARE @DATE_TO DATETIME --- ONLY FOR TESTING PURPOSE. SET @DATE_FROM = '1/1/1900' SET @DATE_TO = '1/1/2100'*/
DECLARE @TEMP_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT FOR CREATING A NEW TABLE "TEMP_OUTPUT" DECLARE @TEMP_DATATYPE NVARCHAR(100) -- A SQL SCRIPT WHICH SPECIFIES DATATYPE OF THE COLUMNS IN "TEMP_OUTPUT" DECLARE @TEMP_PROD_TYPES NVARCHAR(100) -- THIS WILL STORE PRODCUTNAME WHICH WILL BE THE COLUMNS OF THE "TEMP_OUTPUT"
DECLARE @TEMP_FINAL_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO UPDATE "TEMP_OUTPUT" WITH COUNTS. DECLARE @TEMP_SQL_COLUMN_NAME_QUERY NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO IDENTIFY WHICH COLUMN NEEDS TO BE UPDATED IN "TEMP_OUTPUT" DECLARE @TEMP_SQL_UPDATE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT DECLARE @TEMP_SQL_UPDATE_WHERE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT WHERE CLAUSE
DECLARE @TEMP_PROD NUMERIC(13,0) -- THIS WILL STORE THE PRODUCTS DECLARE @TEMP_PROD_TWO NVARCHAR(1000) -- THIS WILL ALSO STORE THE PRODUCTS, BUT AS A NVARCHAR DECLARE @TEMP_PROD_DESC NVARCHAR(1000) -- THIS WILL STORE THE PRODUCT DESCRIPTION, SOMETHING LIKE PRODNAME_PRODID DECLARE @TEMP_DEVIATION_ID NUMERIC(13,0) -- THIS WILL STORE THE DEVIATION ID DECLARE @TEMP_DEVIATION_REASON NVARCHAR(100) -- THIS WILL STORE THE DEVIATION REASON DECLARE @PROD_COUNT_VALUE INT -- THIS WILL STORE THE REQUIRED COUNTS OF EACH PRODUCT FOR EACH DEVIATION
-- --BELOW THREE DECLARED VARIABLE, ARE USED TO GET THE UPDATE COLUMN. DECLARE @TARGET_COL NVARCHAR(100) DECLARE @UPDATE_COL NVARCHAR(100) DECLARE @TEMP_COL_OUTPUT NVARCHAR(100) --
--BASIC TABLE CREATION QUERY SET @TEMP_SQL = ' CREATE TABLE TEMP_OUTPUT ([DEVIATION_REASON_SG_KEY] [numeric](13, 0) NOT NULL , DEVIATION_REASON NVARCHAR(200) ' SET @TEMP_DATATYPE = ' NVARCHAR(300) NULL ' --
--BASIC TABLE UPDATE QUERY SET @UPDATE_COL = N'@TARGET_COL varchar(5000) OUTPUT' SET @TEMP_SQL_UPDATE_QUERY = 'UPDATE TEMP_OUTPUT SET ' -- COLUMN NAME = VALUE SET @TEMP_SQL_UPDATE_WHERE_QUERY = ' WHERE DEVIATION_REASON_SG_KEY =' --
-- BEGIN "TEMP_OUTPUT" TABLE CREATION
DECLARE CUR_FETCH_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC OPEN CUR_FETCH_PROD_NAMES
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES
WHILE @@FETCH_STATUS = 0 BEGIN --SELECT @TEMP_PROD_TYPES SET @TEMP_SQL = @TEMP_SQL + ', [' +@TEMP_PROD_TYPES + ']'+ @TEMP_DATATYPE
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES END
SET @TEMP_SQL = @TEMP_SQL + ' ) ' EXEC (@TEMP_SQL) -- THE TABLE "TEMP_OUTPUT" IS CREATED, NOW ITS TIME TO DUMP IN RECORDS.
CLOSE CUR_FETCH_PROD_NAMES DEALLOCATE CUR_FETCH_PROD_NAMES -- CLEAR THE CURSOR CUR_FETCH_PROD_NAMES
---------------------------------------------------------------------------------------------------------
-- START TO INPUT DATA TO THE NEWLY CREATED "TEMP_OUTPUT" TABLE.
DECLARE CUR_LOOP_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) , TYPE_CODE FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC
OPEN CUR_LOOP_PROD_NAMES
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD -- FETCH PROD DESCRIPTION AND PRODUCT ID
WHILE @@FETCH_STATUS = 0 BEGIN SET @TEMP_PROD_TWO = '' SET @TEMP_PROD_TWO = @TEMP_PROD -- @TEMP_PROD_TWO WILL BE USED FOR THE DYNAMIC QUERY. --PRINT @TEMP_PROD_TWO
DECLARE CUR_LOOP_DEVIATION_REASON CURSOR FOR SELECT DESCRIPTION,SG_KEY FROM AT_DEVIATION_MASTER
OPEN CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID -- FETCH DEVIATION REASON AND DEVIATION ID
WHILE @@FETCH_STATUS = 0 BEGIN --- CHECK IF DEVIATION SG_KEY IS PRESENT IN "TEMP_OUTPUT" TABLE.
IF NOT EXISTS(SELECT DEVIATION_REASON_SG_KEY FROM TEMP_OUTPUT WHERE DEVIATION_REASON_SG_KEY = @TEMP_DEVIATION_ID) BEGIN -- DEVIATION_REASON_SG_KEY IS NOT PRESENT, SO INSERT. INSERT INTO TEMP_OUTPUT(DEVIATION_REASON_SG_KEY,DEVIATION_REASON) VALUES(@TEMP_DEVIATION_ID,@TEMP_DEVIATION_REASON) END ELSE BEGIN -- DEVIATION_REASON_SG_KEY IS PRESENT --UPDATE "TEMP_OUTPUT" TABLE.
-- FETCH CORRESPONDING COUNT VALUES. SET @PROD_COUNT_VALUE = ( SELECT ISNULL(COUNT(FK_PRODUCT_TYPE),0) FROM AT_AUTO_CE WHERE AT_AUTO_CE.CE_DEVTN = 1 AND AT_AUTO_CE.CE_DEVTN_REASON = @TEMP_DEVIATION_ID --AT_DEVIATION_MASTER.SG_KEY AND AT_AUTO_CE.FK_PRODUCT_TYPE = @TEMP_PROD-- FK_PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY FK_PRODUCT_TYPE ) -- ONLY UPDATE THE TABLE IF @PROD_COUNT_VALUE <> 0 -- OR ELSE THE VALUE WILL BE NULL, BT DEFAULT. IF @PROD_COUNT_VALUE <> 0 BEGIN --SELECT @PROD_COUNT_VALUE --IDENTIFY THE COLUMN TO UPDATE THE VALUE -- SET @TEMP_SQL_COLUMN_NAME_QUERY = 'SELECT @TARGET_COL = column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name= ''TEMP_OUTPUT'' AND column_name LIKE''%' SET @TEMP_SQL_COLUMN_NAME_QUERY = @TEMP_SQL_COLUMN_NAME_QUERY + @TEMP_PROD_TWO + '''' --PRINT @TEMP_SQL_COLUMN_NAME_QUERY EXECUTE sp_executesql @TEMP_SQL_COLUMN_NAME_QUERY, @UPDATE_COL, @TARGET_COL = @TEMP_COL_OUTPUT OUTPUT --SELECT @TEMP_COL_OUTPUT AS OUTT -- NOW THE REQ COLUMN NAME WILL BE STORED IN @TEMP_COL_OUTPUT VARIABLE SET @TEMP_FINAL_SQL = @TEMP_SQL_UPDATE_QUERY + '['+ @TEMP_COL_OUTPUT + '] =' + CAST(@PROD_COUNT_VALUE AS NVARCHAR) + @TEMP_SQL_UPDATE_WHERE_QUERY + CAST(@TEMP_DEVIATION_ID AS NVARCHAR) --PRINT @TEMP_FINAL_SQL -- @TEMP_FINAL_SQL WILL HOLD THE FINAL UPDATE QUERY. EXEC(@TEMP_FINAL_SQL) -- UPDATED THE "TEMP_OUTPUT" TABLE. END-- END FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID END
CLOSE CUR_LOOP_DEVIATION_REASON DEALLOCATE CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD END
CLOSE CUR_LOOP_PROD_NAMES DEALLOCATE CUR_LOOP_PROD_NAMES
--------------------------------------------------------------------------------------------------------
SELECT * FROM TEMP_OUTPUT DROP TABLE TEMP_OUTPUTEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Molloys r us : Set page title dynamically in asp.net

2008-05-14T03:37:00.000-07:00

Molloys r us : Set page title dynamically in asp.net: "Set page title dynamically in asp.net

There is no page title web control within asp.net so how would you set a page title Programmatically?

You can add an ID and Runat attribute to the existing title tag:

Within your code behind file add a html generic control with the other control declarations:

protected System.Web.UI.HtmlControls.HtmlGenericControl pageTitle;

The title can now be accessed programmatically just like any other web control:"

"No Records Found" Message in Reporting Services - Brian Mains Blog

2008-04-25T04:57:00.000-07:00

"No Records Found" Message in Reporting Services - Brian Mains Blog: "I was trying to display a message in my report, that when no records were found from the underlying query, a message would be displayed like 'no records found' or something like that. I couldn't find the answer until I came across a forum post, and so I include it in case it helps someone else: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1659610&SiteID=1"

Return False.. :)

2008-03-20T10:17:00.000-07:00

I had written a j.s for onclick event and even though i had clicked it.

This was the code that i used to call the j.s

chk.Attributes.Add("onclick", "javascript:Temp('" & CType(dgi.FindControl("ddMapToColumn"), DropDownList).ClientID & "'); return false")

But, to my surprise, even after checking the checkbox, the tick mark on the check box wasn't coming.

The reason for which is the "return false;" statement.

This was something that i learned after googling

DataGrid + On Click of Check box, Enable or Disable a Drop Down

2008-03-20T10:15:00.000-07:00

This is how it goes..

Add Attributes to the checkbox in item data bound

For Each dgi In dgCol.Items
Dim chk As CheckBox = CType(dgi.FindControl("chkSelect"), CheckBox)
chk.Attributes.Add("onclick", "javascript:Temp('" & CType(dgi.FindControl("ddMapToColumn"), DropDownList).ClientID & "');")
Next

The Java script that does the Enabling / Disabling is as follows..

function Temp(drpdown)
{
document.getElementById(drpdown).disabled = !document.getElementById(drpdown).disabled;
}

Nothing much is required apart from this and the job is done !!

Retrieve Query String Parameters using Javascripts

2008-03-20T10:11:00.000-07:00

Retrieve Query String Parameters using Javascripts

Parameters are coming, but its giving an obj not found error, Need to Check that out

""

Data Grid + Checkboxes...

2008-03-20T10:08:00.001-07:00

This is Useful when you have a Grid and u need to use javascripts for Each element , in this ex its Check Box

Here is the ASP code

//
// this html tag adds checkbox to header
//

onclick="DGSelectOrUnselectAll('DataGrid1',this,'chkDel')" >

//
// this html tag adds checkbox to datagrid

Below is the Java Script

//-------------------------------------------------------
//this is to select or unselect the datagrid check boxes

function DGSelectOrUnselectAll(grdid,obj,objlist){
//this function decides whether to check or uncheck all
if(obj.checked)
DGSelectAll(grdid,objlist)
else
DGUnselectAll(grdid,objlist)
}
//----------

function DGSelectAll(grdid,objid){
//.this function is to check all the items
var chkbox;
var i=2;

chkbox=document.getElementById(grdid +
'__ctl' + i + '_' + objid);

while(chkbox!=null){
chkbox.checked=true;
i=i+1;
chkbox=document.getElementById(grdid +
'__ctl' + i + '_' + objid);
}

}//--------------

function DGUnselectAll(grdid,objid){
//.this function is to uncheckcheck all the items
var chkbox;
var i=2;

chkbox=document.getElementById(grdid +
'__ctl' + i + '_' + objid);

while(chkbox!=null){
chkbox.checked=false;
i=i+1;
chkbox=document.getElementById(grdid +
'__ctl' + i + '_' + objid);
}
}
//-------------------------------------------------------

THIS IS USEFUL TO CHECK OR UNCHECK CHECKBOXES IN A GRID.

PS: THIS ONLY AFFECTS THE CURRENT PAGE CONTENTS OF THE GRID.
IF YOU NEED THE SAME TO AFFECT ALL OTHER PAGES, THEN WE NEED TO CUSTOMISE THE ABOVE CODE
BY ADDING A HIDDEN VARIABLE AND SETTING IT TO SOME VALUE WHICH IF SET, THEN CHECKS ALL CHECK BOXES
WHEN WE LOAD THE PAGE.

Little Animation on the web page!

2008-02-22T09:59:00.000-08:00

I was googling something and suddenly i found this particular technical web site.. http://nishant.hopto.org/wordpress/

There is nothing much in the site but i found this interting though !

And found this Cool thing which we can add to a ASPX page, meta content part and check out the amazing way to ignore the postback.

Just paste the below TAG in your HTML, it will cause IE to use DirectX transformation to render your page. This gives a very cool and pleasing look.

""<"meta equiv="”Page-Exit”" content="”progid:DXImageTransform.Microsoft.Fade(duration=".5)”">""

The way I understand this works is that IE sends a request for the new page but does not clear the page you have on your screen right now. When IE has the second page ready in the buffer, it just uses DirectX to transform the image of the previous page into the image of the new page.

You can try these other effects too :

content=”progid:DXImageTransform.Microsoft.Wheel(duration=0.5,spokes=5)”
content=”progid:DXImageTransform.Microsoft.Barn(duration=0.5,orientation=horizontal)”
content=”progid:DXImageTransform.Microsoft.Blinds(duration=0.5,bands=5)”
content=”progid:DXImageTransform.Microsoft.CheckerBoard(duration=0.5)”
content=”progid:DXImageTransform.Microsoft.Fade(duration=0.5)”
content=”progid:DXImageTransform.Microsoft.GradientWipe(duration=0.5,wipeStyle=0)”
content=”progid:DXImageTransform.Microsoft.Iris(duration=0.5,irisStyle=STAR)”
content=”progid:DXImageTransform.Microsoft.Iris(duration=0.5,irisStyle=CIRCLE)”
content=”progid:DXImageTransform.Microsoft.Pixelate(duration=0.5,maxSquare=40)”
content=”progid:DXImageTransform.Microsoft.Wheel(duration=0.5,spokes=5)”
content=”progid:DXImageTransform.Microsoft.RandomDissolve(duration=0.5)”
content=”progid:DXImageTransform.Microsoft.Spiral(duration=0.5)”
content=”progid:DXImageTransform.Microsoft.Stretch(duration=0.5,stretchStyle=push)”
content=”progid:DXImageTransform.Microsoft.Strips(duration=0.5,motion=rightdown)”

Plus, you can apply these affects at various points in your site or web pages :

meta http-equiv=”Page-Enter”
meta http-equiv=”Page-Exit”
meta http-equiv=”Site-Enter”
meta http-equiv=”Site-Exit“

Clearing Session Variables

2008-02-22T09:56:00.001-08:00

Session.Abandon and Session.Clear() is the two methods that gave me a HUGE headache for the day.

Logout isnt working as i wanted. Only after a postback, its clearing the controls.

What to do, I Donno.. If i get the solution, ill try to add it here.

This was the Code That i was using in the LOG OUT Page LOAD

Session.Abandon()
Response.Cache.SetCacheability(HttpCacheability.No Cache)
Response.Cache.SetExpires(Now().AddSeconds(-1))
Response.Cache.SetNoStore()
Response.AddHeader("Pragma", "no-cache")

This also didnt help much. Later i Added this particular line in all the ASPX pages.

<%@ OutputCache Location = "none" %>

FINALLY IT WORKED. NOW ITS FINE, BUT STILL UNSURE, WHY IT WAS NOT WORKING.

THIS IS ONE OF THE SIMPLEST REASON, WHY WEB DEVELOPMENT IS "NOT SO EASY"

Important Sql Server 2000 Queries !!

2008-02-09T00:26:00.000-08:00

I'll try to add some important Sql Server Queries in the below post.
This will be handy for me some day.

Q1 : To select Column Names from a table:

SELECT column_name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name= 'YOUR TABLE NAME'

Modified Date of a DB object - Sql Server 2000

2008-02-09T00:21:00.003-08:00

Some days Back i had a particular requirement like.. i wanted to identify the modified datetime of a Database object in Sql Server 2000.

We have the table called sysobjects in every database which is supposed to store all these information.

There are two columns which is of interest for the above scenario

1. crdate Created Date
2. refdate - ( This also stored the Created Date only )

After i saw refdate, i was really excited and thought that this was the column which stores the modified datetime of a Database object.
But my excitement came down even more quickly as it also stored the date of Creation of the object.

Thought Google Is God and started Googling.

After googling for some time, Guess what i discoverd, There is no way u can get the Modified datetime of a Database object in Sql Server 2000.

The day after, i was just googling around for the same.

I knew all Changes to the database are recorded in the log files, So if i was able to read / Query the Sql Log files, i was able to meet my requirement.

I tried to open the Sql Log in a notapad, after stopping the server, but there was nothing in a readable format.

I Googled Again....... ;( "How to read Sql Log files"

And i finally found some thing interesting, DBCC..

DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logical consistency of a database, although they are also used for a variety of miscellaneous tasks that can be done.

These are not popular things which are known to everyone...

There is a command called dbcc log

whose signature looks like dbcc log(databasename/dbid, type)

Ex : DBCC log(temp,3)

I thought this will provide information which we cant digest...

The quest for my requirement continues....

For the time being....
Chao....

Happy Coding

----------------------------------------------------------------------------------------------------------------------------

Sql Server 2000 - Providing Permissions

2008-02-09T00:20:00.000-08:00

Providing Permissions for a New database Login User.

Some days back, we had a new requirement, where in we need to create a separate database user to access the application.
This user must not be the DB owner.

So, we created the user in the Sql Server Enterprise Manager --> Security --> Logins --> New Login.

Now, we had provide the New Login name, and choose the type of authentication required for the user
A. Windows Authentication
B. Sql Server Authentication.

As per our requirement, we choose B, and provided the credentials.

The next step is to select the Database and the Language.

Now, Goto Database Access Tab and we need to CHECK the databases that this new user need to access.

After checking, we need to assign the roles for this user

db_datareader and db_datawriter will ensure this user to select or Insert into tables / views.

After this, we need to provide the execute permission for the Stored Procedures which need to done manually for each and every SP.

This is a tiresome process if we've more SP.

After Googling around, i found a script which will output a Script which if run, will provide the permissions for the user to execute SPs.

This script really came Handy !!

select 'Grant Execute on ' + name + ' to ' + 'SQLServerUSERName'
from sysobjects where xtype in ('P')

Just Change "SQLServerUSERName" to the New DB Login Name and Magic... The output of this script gives u a set of select statements which u need to execute and Thatz it, The permissions are provided for the user.

Happy Coding

----------------------------------------------------------------------------------------------------------------------------

Windows Authentication

2008-02-09T00:18:00.000-08:00

We are working on an Intranet Application which has Windows Authentication.

Following piece of code, gets you the name of the user who has logged in.

---

Imports System.Globalization

Dim GetUser As String
Dim strUID() As String
Dim User As System.Security.Principal.IPrincipal
User = System.Web.HttpContext.Current.User
GetUser = User.Identity.Name
strUID = GetUser.Split("\")
GetUser = strUID(1)

---

Once we put this code, we should get the logged in User's Name in the GetUser var.

But, we are facing some problems in a few systems where this name is getting populated incorrectly.

Instead of the Logged in user's name, we are getting the server's login name.

Googling around to solve this problem, ,no luck yet.

Once i solve this, ill put the solution here.

Happy Coding

----------------------------------------------------------------------------------------------------------------------------

2008-01-17T21:49:00.000-08:00

Long time posting on the Blogger

Since yesterday i'm having problems with CSS.

I've googled down the hidden treasures that protect the secret of a writing a Valid CSS.

Following links are handy

W3C HTML Validator: http://validator.w3.org/
WDG HTML Validator: http://www.htmlhelp.org/tools/validator/
CSS Validator: http://jigsaw.w3.org/css-validator/
WDG's CSScheck: http://www.htmlhelp.org/tools/csscheck/

Got em from a web site
http://meyerweb.com/

Connection String......

2007-07-03T00:00:00.000-07:00

Did u ever had a tough time writing a connection string to a Database...

Here is the easiest method of all...

Its Soo simple and easy and u can just have ur Connection string just using only a notepad.

All u gotta do is to Create a New Notepad Docuement and save it as FileName.UDL file

Now double click on the same file and use the wizard to configure ur connection with the DataBase..

Now once all the confoigurations are done, Close the file and reopen it using Note pad or any text editor...

There u go, Ur Connection String is ready.

Ur String may looks like this

.
.
.
[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Persist Security Info=False;User ID=dbuser;Initial Catalog=INDUB;Data Source=NESSQLSRV
.
.
.

Now u knw how easy it is to write connection strings without a need to remember many things.....

Happy Coding...

Sql Server : Under the HOod

2007-06-27T01:14:00.000-07:00

Just experimenting with a few things in sql Server 2000

Rules
raiserror
User Defined Data Types
SYSOBJECTS
Check constraints

A Couple of Stored Procedure
SP_BINDRULE
SP_UNBINDRULE

Lets take up one by one...

Rule : A constraint that can be applied to a particular column or User Defined Data Type (UDT)

Syntax : create rule RuleName
as
Condition_Expression

Example : Create Rule RuleOne
as
@EMPNO <> 5

So now, to apply this newly created rule to a Table Column or UDT, We use the Stored Procedure, SP_BINDRULE.

Syntax
SP_BINDRULE [ @rulename = ] 'rule' ,
[ @objname = ] 'object_name'
[ , [ @futureonly = ] 'futureonly_flag' ]

This Procedure takes 3 arguments

1) Rulename : The Name of the rule which we want to apply
2) ObjectName : Name of the Table Column or UDT.
3) Futureonly : If this field is set, it means the contraints applies in future and not for information already existing in the database.

Example1 : sp_bindrule RuleOne, 'Sandy'

Here, RuleOne is the Name of the Rule and 'Sandy' is a UDT

Example2 : sp_bindrule RuleOne, 'TableName.ColumnName'

NOTE : ONLY ONE RULE CAN BE APPLIED TO A COLUMN OR A UDT.

The Exising Rule , If any willl be overwritten.

SP_UNBINDRULE is used to remove a rule from a Column or UDT.

Syntax

Sp_UNBINDRULE
[@objname =] 'object_name'
[, [@futureonly =] 'futureonly_flag']

Arguments,

1) Object Name
Name of the Table Column or UDT.
2) futureonly
If it is set , then Existing columns of the type 'UDT' wil not loose the Constraint, but future UDT columns will not have this Constraint.

DROP RULE RuleName : is used to drop the Rule.

RAISERROR :
Returns a user-defined error message and sets a system flag to record that an error has occurred. The client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information.

Will post more information about the same in my next post....

Playing with OOPS

2007-06-19T05:29:00.000-07:00

I have 3 classes

class A, B and C.

In class A and class B, i've a data member : public int i;

ill inherit class B from A.

class B : A

After this, i've two variables "i" having the same name.

Now , can i access both the values just one using one object ??

I can use base in C# and mybase in vb.Net, but i dont want to use them in constructor..

If, i have one more class like this..

class C : B

Then, in class C, can i create a single object, using which i can access both the base class A and Derived Class B's data member "i" ?

Stilll looking for an answer/solution.

Hope i find one soooner...........

Sql Server 2000 : Maximum Capacity Specifications

2007-06-11T00:27:00.000-07:00

Found this piece of info in MSDN !

The Maximum Capacity Specifications in Sql Server 2000/97

Columns per SELECT statement : 4096

Columns per INSERT statement : 1024

Nested stored procedure levels : 32

Nested subqueries : 32

Nested trigger levels : 32

Parameters per stored procedure 1024

Tables per SELECT statement 256

The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Check out more information @

http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

Cheerz !

Test Blog !

2007-03-23T03:19:00.001-07:00

Test blog !

EHAB Continued....

2007-03-14T06:56:00.000-07:00

Whole day was working on EHAB and finally implemented a custom publisher !

Create the Custom Publisher Class

1) In the Exception Application block, i added a VB class called "CustomPublisher"
2) Code for the class file would go like this...

Imports Microsoft.ApplicationBlocks.ExceptionManagement
Imports Microsoft.ApplicationBlocks.Data
Imports System.Web
Imports System.Data.SqlClient
Imports System.Data

Public Class CustomPublisher
Implements IExceptionPublisher

Public Sub Publish(ByVal exception As System.Exception, ByVal additionalInfo As NameValueCollection, ByVal configSettings As NameValueCollection) Implements IExceptionPublisher.Publish

Try
Dim strConn As String
strConn = ConfigurationSettings.AppSettings.Get("ConnStr").ToString()
-- Some how get the Conn string
SqlHelper.ExecuteNonQuery(strConn,CommandType.StoredProcedure,"usp_InsertException")
-- Some Stored proc to insert ur exception...
Catch ex As exception
-- Do something to catch the exception here, outta scope of EHAB
End Try
End Sub
End Class

3) Configure the Web.config file...

the contents shud go like this....

Now, note that the assembly name must be the Name of the Assembly where the project is deployed and type should be the class name which overrides the Publish method.

Now, in the project, where ever therez an exception, all we need to do is call the method

ExceptionManager.Publish(ex)

The rest is taken care by EHAB.

Wasted a couple of hours working on App.config file..

Realized that App.Config file is for Windows Applications and
Web.config file is for Web Application...

More to Come on the same...

2007-03-13T02:51:00.000-07:00

I was playing around with Exception Handling Application Block ( EHAB) !

I created a dummy ASP.Net Web Application.

I added the Reference of EHAB to the Project.

I build the project, i got an error saying

"type IExceptionPublisher is not defined "

I build the EHAB and added its reference again.

Now, i build the project and the error was gone.

Now, i wrote a code that would rise an exception and i wanted that to be recorded using EHAB.

I ran the project and i got an un expected exception saying

"Requested registry access is not allowed"

I googled this exception and found some info about

Thanks to Mr. Brono from UK..

This is what he had posted in his blog...

Requested Registry Access is not allowed
SOLUTION IS AS FOLLOW (THIS SOLUTION IS FOR DEVELOPMENT ENVIRONMENT ONLY):

1. Give the ASPNET user permission to read the Security eventlog registry entry.Run regedt32 Navigate to the following key:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Security Right click on this entry and select Permissions Add the ASPNET user Give it Read permission

2. Change settings in machine.config fileRun ExplorerNavigate to WINDOWS or WINNT folderOpen Microsoft.NET folderOpen Framework folderOpen v1.1.4322 folder (folder name may be different, depending on what dotnet version is installed)Open CONFIG folderOpen machine.config file using notepad (make a backup of this file first)Locate processmodel tag (approx. at line 441)Locate userName="machine" (approx. at line 452)Change it to userName="SYSTEM"Save and close the fileClose Explorer

3. Restart IIS Run IISReset

Production Solution

Go to the application pool and select IDENTITY leave it Network ServiceGo to the registry it is accessing and select the permissionAdd the NETWORK SERVICE user to it

After making the above changes... I ran the project again and found one more exception sayin...

"Event Log Full"
I had to make the event log empty... Googled again.

Run the command "eventvwr.msc" from Windows Start

Right Click on the Application and say clear all events.

Now the event log was empty.

I ran the application again and found the exception Published in the event log.

More to come on EHAB.

Check this one out....

2007-03-01T04:01:00.000-08:00

http://www.tiddlywiki.com/

A new era of Web Pages...

Can it change the definition of Dynamic Web Pages....

Gotta wait n seee. :)