Tag: SQL

Getting SQL Replication Info Programmatically from Java

winterWisteria Wisteria leaves in winter.

References

Details

SQL Server has a process called replication, whereby you can automatically copy records from one database to another, and keep them in-sync.  One use for this is to have one database as the transactional database, and another to report from, with the reporting database having indexes and constraints tailored more toward faster reporting (reads) than fast input.

Sometimes replication gets a little behind, and if you are running reports on the database that is a little behind, you might like to know.

The stored procedure sp_replcounters will tell you the count of transactions awaiting transfer to the distribution database (which lies between the transaction database and the destination database in a replication process).  You can call this stored procedure, but you want it to return the result set to Java.

One way to achieve this is to wrap your own stored  procedure around it, put the records into a temp table and query the temp table to return the result set.  This only works if you specify SET NOCOUNT  ON, otherwise the Java Persistence API (JPA) gets confused by the multiple queries and you don’t get the result set in your Java code.

CREATE PROCEDURE GetReplicationLatency

AS

--This is important.  Results from multiple queries within stored procedures throw JPA off unless SET NOCOUNT ON is used.
SET NOCOUNT ON

declare @db varchar(50)
set @db = db_name()


create table #tempTable ([database] varchar(50), [replicated transactions] int, [replication rate trans/sec] int,
 [replication latency (sec)] decimal(16,6), replbeginlsn varchar(40), replnextlsn varchar(40))
insert into #tempTable
exec sp_replcounters
select * from #tempTable where [database] = @db


drop table #tempTable

 

Then your Java code looks like this:

private static final String REPLICATION_LATENCY_QUERY = "exec GetReplicationLatency ";Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
List<Object[]> results = query.getResultList();

 

 

SQL Server datetime to int

birds

declare @currentDateTime datetime 
declare @timeStampValue int 

set @currentDateTime = SYSDATETIME(); 
print @currentDateTime 
set @timeStampValue = DATEDIFF(second, '1970-01-01', @currentDateTime) 
print @timeStampValue

 
To check the result:

https://www.epochconverter.com/

 

UTC milleseconds example:

 
declare @timeStampValue Bigint  
declare @utcDateTime datetime 

set @utcDateTime = GETUTCDATE(); 
set @timeStampValue = DATEDIFF(SECOND, '1970-01-01', @utcDateTime) 
set @timeStampValue = @timeStampValue * 1000

 

Duplicates in two columns in a table

thor-in-the-poppies-lisa-twede

That’s a painting of my cat Thor in the backyard.  Apparently he’s looking at the hose, despite the fact that to his right the yard is full of California Poppies.

Today’s blog has some code that is not my own.  Michael Powaga posted it over on Stack Overflow.  I just wanted to keep track of this little gem because I’ve needed it twice now and both times I ended up going with his solution.

I needed to know how many rows (and which ones) were duplicates but not just on one column, on two columns.  I had a situation where (I feel) there should have been a constraint to prevent duplicates in this table.  I wanted to neatly identify the offending records so I could deal with them.

This is his code.  It’s at the link above, but also copied here just in case.

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city
SQL – Find the largest number in a set of alpha-numeric values

SQL – Find the largest number in a set of alpha-numeric values

References:

The SQL:

declare @lastIntUsed int 

create table #temp (clean_number varchar(50), IntCol int) 
; with tally as (select top (100) N=ROW_NUMBER() over (order by @@spid) from sys.all_columns),
data as ( 
   select sometable.number, clean_number
   from sometable
   cross apply ( 
      select (select C + '' FROM (select N, SUBSTRING(sometable.number, N, 1) C  
          from tally
          where N<=DATALENGTH(sometable.number)) [1]
      where C between '0' and '9'
      order by N
      for xml path('')) 
   ) p (clean_number)
   where p.clean_number is not null 
) 
insert into #temp (clean_number, IntCol) 
select number, cast(clean_number as int) IntCol from data

set @lastIntUsed = (select MAX(IntCol) from #temp) 
print @lastIntUsed
drop table #temp

I couldn’t find any way to just get the max number from the CTE itself (doesn’t mean there isn’t a way), so I said screw it – I’ll just stick the results into a temp table and then I can get the max number or the min or the max number that is less than some value or whatever I want.