Using table valued parameters to insert multiple rows into a database table

Before Table-Valued Parameters were introduced in SQL Server 2008, programmers had to pass multi-row data as serialized, delimited, strings, long parameter lists, or XML. Now using TVP ( table Value parameters we can send all the rows in one transactio to the Sql server without having to make round trips, this will boost the performance.
For example let's say we have to insert the following data to the table

Ptno, DocTypeId, UserId,UserSessionId
34343,1,614,354354545
45554,2,614,456566666
14563,3,614,456366336

The above data needs to be inserted into the table
CREATE TABLE [dbo].[PV_Account_DocType](
 [PVId] [int] IDENTITY(1,1) NOT NULL,
 [Ptno] [int] NOT NULL,
 [DocTypeId] [int] NOT NULL,
 [UserId] [int] NOT NULL,
 [DateEntered] [datetime] NOT NULL,
 [UserSessionId] [varchar](100) NULL
) ON [PRIMARY]

Where PVID is the identity value and dateentered has the default value of getdate()

The first step is we create the Table Type  "DocType" in the User Defined table types section

CREATE TYPE [dbo].[DocType] AS TABLE(
 [Ptno] [int] NOT NULL,
 [UserId] [int] NULL,
 [DocTypeId] [int] NULL,
 [UserSessionId] [varchar](100) NULL
)
GO

Now that the TPV is defined we need to make it usable. For example if we want to insert data or use any other T-SQL, we have to create a variable of the new table type and populate it. For those familiar with Object Oriented Programming, this is a bit like instantiating a class or object. Once this new variable is created it will inherit the table structure of the new type. This is how it is done:
First of all we declare a variable of table type:

DECLARE @Mytabletype DocType
/* The variable name @Mtabletype can be called anything you want but the table type has to reference an existing table definition type. Now we insert the data: */INSERT INTO @Mytabletype (ptno, UserId,DocTypeId,UserSessionId)VALUES (34343,1,614,'354354545'),
(45554,2,614,'456566666'),
(14563,3,614,'456366336');

/* Once the table is populated, we can manipulate the data like any other table, provided the variable is in scope: */SELECT * FROM @Mytabletype
  We get the following result

So what does the code above do? Well, first of all, we define the stored procedure by adding our table type DocType then we add a READONLY qualifier. This qualifier is very important and MUST be included when a stored procedure is created. This is mainly because Transact-SQL passes table-valued parameters to routines by reference. Basically, a pointer is passed to the stored procedure and not as we would have expected, the user defined table variable. It therefore isn’t possible to do DML operations that update a table-valued parameter, such as UPDATE, DELETE, or INSERT within a routine. Table-valued parameters are read-only in Transact-SQL code. If you need to modify the data that is passed to a stored procedure or parameterized statement in a table-valued parameter, you will need to insert the data into a temporary table or into a table variable first. It isn’t actually much of a problem to do this, and takes little time.

We now use the stored procedure to insert the data
exec PROC_PV_ACCOUNT_DOCTYPE_INSERT @DocTypeTbl = @Mytabletype
The sp is coded as follows

CREATE PROC PROC_PV_ACCOUNT_DOCTYPE_INSERT(@DocTypeTbl DocType READONLY )AS
INSERT INTO PV_Account_DocType(Ptno, DocTypeId, UserId,UserSessionId)
SELECT Ptno, DocTypeId, UserId,UserSessionId
FROM @DocTypeTblGO

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