SQL server side traces handy commands

select * from ::fn_trace_getinfo(0)    //list all the traces--where property = 5
--stops the tracet of raceid 2 EXEC sp_trace_setstatus 2 ,0

---restart the trace of traceid 2
EXEC sp_trace_setstatus 2 ,1
--deletes the trace of traceid 2 from the server EXEC sp_trace_setstatus 2 ,2

Resending the failed mails in DBmail

We had several failed mail items that were supposed to be sent out overnight by some SQL Jobs.
The following query was run to find the failed items

use msdb
GO

declare @StartDate datetime,
            @EndDate datetime


select * from dbo.sysmail_faileditems where last_mod_date >=@StartDate  and last_mod_date < @EndDate

--all the returned rows had sent_status as failed.

Here is a very cool script that will resend the failed items that I found in sqlservercentral.com. It worked like a charm!!!

declare @mailitem_id int, @sendmailxml
varchar(max), @rc
int,@StartDate datetime, @EndDate
datetime-- get the dates to use in the queryset @StartDate = convert(datetime, convert(char(8), GetDate(), 112))set @EndDate = @StartDate + 1declare cFailedMail cursor forSELECT
mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDateopen cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0
begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'-- Send the send request on queue.EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0BEGIN
 RAISERROR(14627, 16, 1, @rc, 'send mail')END

fetch next from cFailedMail into @mailitem_idend
close cFailedMail
deallocate cFailedMail

Using table valued parameters to insert multiple rows in VB.NET

Dim Table1 As DataTable
'create a table named tmptblTable1 = New DataTable("tmptbl")
Dim Row1, Row2, Row3, Row4, Row5 As DataRow
'establishing connection. you need to provide password for SQL serverDim sConn As New SqlConnection()
Dim sSqlcommand As New SqlCommand()

Try'declare a column named Ptno Dim intPtno As DataColumn = New DataColumn("Ptno")
'setting the datatype for the columnintPtno.DataType = System.Type.GetType("System.Int32")
'adding the column to tableTable1.Columns.Add(intPtno)
Dim intUserId As DataColumn = New DataColumn("UserId")intUserId.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intUserId)

Dim intDocType As DataColumn = New DataColumn("DocTypeId")intDocType.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intDocType)

Dim strSessionId As DataColumn = New DataColumn("UserSessionId")strSessionId.DataType = System.Type.GetType(
"System.String")Table1.Columns.Add(strSessionId)


If Request.QueryString("Doc1") <> "0" Then'declaring a new rowRow1 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row1.Item("Ptno") = ptnoRow1.Item(
"UserId") = userIdRow1.Item(
"DocTypeId") = Doc1Row1.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row1)

End IfIf Request.QueryString("Doc2") <> "0" Then'declaring a new rowRow2 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row2.Item("Ptno") = ptnoRow2.Item(
"UserId") = userIdRow2.Item(
"DocTypeId") = Doc2Row2.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row2)

End IfIf Request.QueryString("Doc3") <> "0" Then'declaring a new rowRow3 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row3.Item("Ptno") = ptnoRow3.Item(
"UserId") = userIdRow3.Item(
"DocTypeId") = Doc3Row3.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row3)

End IfIf Request.QueryString("Doc4") <> "0" Then'declaring a new rowRow4 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row4.Item("Ptno") = ptnoRow4.Item(
"UserId") = userIdRow4.Item(
"DocTypeId") = Doc4Row4.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row4)

End IfIf Request.QueryString("Doc5") <> "0" Then'declaring a new rowRow5 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row5.Item("Ptno") = ptnoRow5.Item(
"UserId") = userIdRow5.Item(
"DocTypeId") = Doc5Row5.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row5)

End If

sConn.ConnectionString = ConfigurationManager.ConnectionStrings("StatusUpdatesConnectionString").ToString()sConn.Open()
sSqlcommand = sConn.CreateCommand()
sSqlcommand.CommandType = CommandType.StoredProcedure
sSqlcommand.CommandText =
"PROC_PV_ACCOUNT_DOCTYPE_INSERT"sSqlcommand.CommandTimeout = 3600
sSqlcommand.Connection = sConn
sSqlcommand.Parameters.Add("@DocTypeTbl", SqlDbType.Structured).Value = Table1sSqlcommand.ExecuteNonQuery()



Catch ex As Exception
Throw New Exception(ex.Message)
FinallysSqlcommand.Dispose()
If sConn.State = ConnectionState.Open ThensConn.Close()
sConn = NothingEnd IfEnd Try

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.