Executing stored procedure with input parameters using sp_executesql

Stored procedure name proc1
Parameters are @param1, @param2, @param3
Values are 1,2,3

declare @SqlString nvarchar(2000)
declare @ParamDef nvarchar(2000)
set @SqlString = N'exec proc1 @param1, @param2, @param3'
set @ParamDef = N'@param1 bit, @param2 bit, @param3 bit'

EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = 0, @param2 = 1, @param3 = 1

Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---

• Connect to SSMS
• Go to Tools
• Go to SQL Profiler
• Chose the appropriate trace template according to requirements ( or create your own)
• Check enable trace stop time and specify the date and time that you want it to stop
• Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime

 

Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

Giving execute permission to all stored procedures through a databaserole

I had a situation where I had to give a user( AD group) execution permission to all the sps.

- Created a role called db_executor data user role

USE pubs
GO

exec sp_addrole  'db_executor'

GO

--added memebers to the role

USE pubs
GO

exec sp_addrolemember 'db_executor', 'GRP_ProdSupport'

GO

--gave permission to execute the stored procedures to the role.

USE pubs
GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
        -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO

(code source : http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ )

Permission to run a specific SQL job to a user

I added a user to the msdb database and gave him the SQLAgentUserRole but that gave him access to all the jobs, which is not what I wanted. This is the work around I followed....


USE msdb
GO
CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
-- Executes as the database owner, for msdb that is 'sa'
WITH EXECUTE AS OWNER
AS
-- Hardcoding the job name limits this procedure's control
EXEC dbo.sp_start_job @job_name = 'MyProductionJobName'
GO
-- Grant Execute rights to your selected users
GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
GO


This way the user will not have direct access to the job MyProductionJobName but through this wrapper sp it can run the job.

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