We need to include the NORESEED option when running DBCC CHECKIDENT to find the current identity value

Without NORESEED option if we run DBCC CHECKIDENT, then it checks the current identity value and if the identity and current value don’t match, it reseeds the identity.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’);

GO

If your intent is to find the current identity value, then we will have to include NORESEED option.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’, NORESEED);

GO

SQL Server job step failed with SQLSTATE 01000] (Message 7989) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Checking identity information: current identity value ‘-1’.

Today suddenly SQL server job step failed with message:

[SQLSTATE 01000] (Message 7989)  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[SQLSTATE 01000] (Message 2528)  Checking identity information: current identity value ‘-1’.

Same step was running with success till yesterday with the above message as info type message.

What is equivalent of Oracle ROWNUM in SQL Server?

We are executing openquery from SQL server to Oracle:

select * from emp where rownum<5

Its equivalent is:

with cte as 
(
select *, row_number() over(order by <column>) as rownum 
from emp
)
select * from cte
where rownum < 5

SQL server does not have rownum pseudo column.
So if we would like to fetch row number along with select statement we have to use a ranking function ROW_NUMBER or Top 1.

 

 

SQL OVER and PARTITION BY T-SQL

Jen McCown blog explains so nicely Over and Partition By usage of SQL functions.

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost    OrderNum
10.00     345
10.00     346
10.00     347
10.00     348

Quick translation:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo

FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00   345          1
8.00   346          1
8.00   347          1
2.00   348          2

The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.

Using the OVER clause with aggregate functions

The following example uses the OVER clause with aggregate functions over all rows returned by the query. In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Avg”
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Count”
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Min”
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Max”
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

Here is the result set.

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max  
------------ ----------- -------- ----------- ----------- ----------- ------ ------  
43659        776         1        26          2           12          1      6  
43659        777         3        26          2           12          1      6  
43659        778         1        26          2           12          1      6  
43659        771         1        26          2           12          1      6  
43659        772         1        26          2           12          1      6  
43659        773         2        26          2           12          1      6  
43659        774         1        26          2           12          1      6  
43659        714         3        26          2           12          1      6  
43659        716         1        26          2           12          1      6  
43659        709         6        26          2           12          1      6  
43659        712         2        26          2           12          1      6  
43659        711         4        26          2           12          1      6  
43664        772         1        14          1           8           1      4  
43664        775         4        14          1           8           1      4  
43664        714         1        14          1           8           1      4  
43664        716         1        14          1           8           1      4  
43664        777         2        14          1           8           1      4  
43664        771         3        14          1           8           1      4  
43664        773         1        14          1           8           1      4  
43664        778         1        14          1           8           1      4  

In SQL Server How to create job and stored procedure to check and notify any long running jobs every 5 hours

You want the server to notify you that there is a job which is running for more than 5 hours.

Create a SQL server recurring job to run every 5 hours and run the following stored procedure:

create PROCEDURE [dbo].[NotifyLongRunningJobs]

AS
BEGIN
SET NOCOUNT ON;
Declare @jobcount int = 0;
Declare @message nvarchar (max);
Declare @joblist nvarchar (max);
SELECT @jobcount = count(distinct (j.name ))
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

select @jobcount

if @jobcount>0
Begin
–Create Email text
select @joblist = STUFF(
(

SELECT ‘ , ‘ + CONVERT(NVARCHAR(1000), j.name)
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

FOR xml path(”)

)
, 1
, 1
, ”)
select @message = N'<FONT face=”Calibri” size=”3″><H3>Long Running Job List</H3>’ +
‘<BR>’ +
@joblist +
‘<P></FONT>’;
–Send Email

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘EMAIL PROFILE’,
@recipients = ‘abc@xyz.com’,
@subject = ‘Long Running Job List’,
@body = @message,
@body_format = ‘HTML’ ;

End
END

How to grant access to couple of tables in sql server database

The best way is to create role and grant the needed access to the needed tables using that role:

CREATE ROLE CoupleOfTablesAccessRole
GRANT SELECT ON [dbo].[Table1] TO CoupleOfTablesAccessRole;

GRANT SELECT,INSERT,UPDATE,DELETE ON [dbo].[Table2] TO CoupleOfTablesAccessRole;

USE [Database_Name]
GO
CREATE USER [ABC\xyz] FOR LOGIN [ABC\xyz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Database_Name]
GO
EXEC sp_addrolemember ‘CoupleOfTablesAccessRole’, N’ABC\xyz’