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.

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

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.