How to assign the control paramater to sqldatasource in code behind page.

I ran into an error "object data not set refererece " ( most dreaded error message) at the point where the control parameter was assigned to the sqldatasource in the aspx page.

<asp:SqlDataSource ID="SqlDataSourceAppliedDateScreenDateCheck" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1%>"
SelectCommand="PROC1"
SelectCommandType="StoredProcedure" DataSourceMode="DataReader"><SelectParameters><asp:ControlParameter ControlID="DropDownListCode" Name="NewCode"
PropertyName="SelectedValue" Type="Int32" /></SelectParameters></asp:SqlDataSource>
The DropdowListCode was not accessible because it is inside a FormView.I changed the above as follows to make it work.

Page1.aspx <asp:SqlDataSource ID="SqlDataSourceAppliedDateScreenDateCheck" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1%>"
SelectCommand="PROC1"
SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
</asp:SqlDataSource>

// removed the control parameter to add in the code behind page
Page1.aspx.vb

Protected Sub SqlDataSourceAppliedDateScreenDateCheck_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSourceAppliedDateScreenDateCheck.Selecting
'Pass the control parameter in the code behind

Dim drpNewCode As DropDownList = DirectCast(FormView1.FindControl("DropDownListCode"), DropDownList)
Dim sqlCtrlParam As SqlParameter = New SqlParameter("@NewCode", drpNewCode.SelectedValue.ToString())
e.Command.Parameters.Add(sqlCtrlParam)
End Sub

Change the order of the column in a table

I created a column and had to drop and add a column named ID with the identity value. Since I re-added the column ID after the creation of the table, this column was the last. I wanted to move the column in the first since it was an identity column for that table.
This is how I was able to change the order of the column ID

SSMS -> Tools----Options ---Designers --- Unheck Prevent saving changes that require table - recreation 

Then change the column order in the table designer.

Using default trace to capture the event in the server

We had a situation whem the database was running fine since yesterday but itwas noticable slow today. So, first thing I suspected that a developer might have altered a stored procedure that ight have aused a delay ( bad query or a missing join ????)

In order to find out I took the following step.

--Find if the default race is enabled

SELECT * FROM sys.configurations WHERE configuration_id = 1568

--if it is not enabled we can enable it by running the following
sp_configure 'show advanced options' ,1;GO RECONFIGURE ;
GO
sp_configure 'default trace enabled', 1;
GO

RECONFIGURE;GO  --find the location of the default trace in the server
select * from :: fn_trace_getinfo(default)Go  --property 1 - value 2 means the trace is set for rollover when the file  has reached the maximum size.
--property 2 shows the location of the default trce file in the server
--property 3 shows the maximum size in MB of the file
--property 4 shows if there is a stop time. NULL means the trace is not set to stop
--property  5 shows if the trace is enabled, value 1 means yes it is.

Now to find all the event since yesterday

select e.name , t.starttime , t.DatabaseName , objectname , LoginName , t.HostName , t.ApplicationName , t.spid , t.eventclass
from fn_trace_gettable( 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_469.trc' , 5 ) as t
join sys.trace_events e
on e.trace_event_id = t.EventClass
where ObjectName is not null
and t.EventClass = 164 -- object altered
 and starttime > '08/30/2012'
order by t.StartTime

This pointed me to the right direction as I started to analyze the store procedures that was rolled out.

Database Mail Executable Minimum Lifetime

We are facing some issue with Sql Server 2008 R2 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time and we get dead locks alert

I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.

Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds





.

BC30554: masterpage is ambiguous

I did not get this error on my dev machine. I did not get this compilation error in the staging site. I got this error in the produciton site.

Resolution: There was another file called masterpage2.aspx that was using the same namespace masterpage. It looked like a test file or for some reason a blank masterpage file was renamed this. Removed the masterpage2.aspx file , did a build and it worked fine.

Error handling in transactions in sql 2008

BEGIN TRANSACTION TRAN1

BEGIN TRY
--put all the valid statements here--


   CREATE TABLE #test(intREsult int);

   INSERT INTO #test Values (4/0);
   INSERT INTO #test Values (1/2);


  COMMIT TRAN TRAN1
END TRY


BEGIN CATCH
--the error handling code including the rollback

    DECLARE @errorMEssage nvarchar(1000);
   SELECT @errorMEssage = ERROR_MESSAGE();
   ROLLBACK TRAN TRAN1;
   RAISERROR(@errorMEssage,16,1);

END CATCH

Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.