Link to home
Start Free TrialLog in
Avatar of Scotto13
Scotto13

asked on

MD5 Hash in Access???

My SQL programmer developed the code below.  He runs it in SQL 2000 with no problems.  I want to pull the data into Access to manage reporting.  Is this possible?  When I run the code in a pass through query in Access, I get an error: HashBytes is not a recognized function...

select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
      from (
      select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid,HashBytes('MD5',Convert(varbinary,xml_data)) MD5Hash
      from PRODSQL1.message_center.dbo.fielded_data_transaction fd
      where date_created between @StartDate and @EndDate )
      fd_fail_hash
      GROUP BY ShiftDate,RopId,AdId,Subregionid
      HAVING COUNT(*) > 1

      union
      
      select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
      from
      (
      select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId,HashBytes('MD5',Convert(varbinary,xml_data)) MD5Hash
      from PRODSQL1.message_center.dbo.fielded_data_transaction
      where date_created between @StartDate and @EndDate
      ) fd_pass_hash
      GROUP BY ShiftDate,RopId,AdId,Subregionid
      HAVING COUNT(*) = 1
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Is HashBytes a SQL Server UDF?
Avatar of Scotto13
Scotto13

ASKER

udf? - user defined??

I don't know...
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I can't find it anywhere.  There is no dbo.HashBytes.  I'll check and recise.  

Thanks for the lead on where to look.
Scotto13
I mean wherever you have
HashBytes
replace it with
dbo.HashBytes
I got it.

There is no table called HashBytes.  When I try this, it gets an error, cannot find Hashbytes...
But it must be a function (and since it isn't an inbuilt one - therefore user defined).
With a bdo prefix - it complains that there's no such table? :-S

select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
     from (
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid, dbo.HashBytes('MD5',Convert(varbinary,xml_data)) MD5Hash
     from PRODSQL1.message_center.dbo.fielded_data_transaction fd
     where date_created between @StartDate and @EndDate )
     fd_fail_hash
     GROUP BY ShiftDate,RopId,AdId,Subregionid
     HAVING COUNT(*) > 1

     union
     
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
     from
     (
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId, dbo.HashBytes('MD5',Convert(varbinary,xml_data)) MD5Hash
     from PRODSQL1.message_center.dbo.fielded_data_transaction
     where date_created between @StartDate and @EndDate
     ) fd_pass_hash
     GROUP BY ShiftDate,RopId,AdId,Subregionid
     HAVING COUNT(*) = 1
Although - that said...
It doesn't look like that data is even being used in the posted SQL...

And I notice it seems to be using a Linked Server too - dont' know if that's an issue...

Does this make any sense as far as output you're expecting to see

select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
     from (
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid
     from PRODSQL1.message_center.dbo.fielded_data_transaction fd
     where date_created between @StartDate and @EndDate )
     fd_fail_hash
     GROUP BY ShiftDate,RopId,AdId,Subregionid
     HAVING COUNT(*) > 1

     union
     
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
     from
     (
     select CONVERT(char(12),DATEADD(day, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId
     from PRODSQL1.message_center.dbo.fielded_data_transaction
     where date_created between @StartDate and @EndDate
     ) fd_pass_hash
     GROUP BY ShiftDate,RopId,AdId,Subregionid
     HAVING COUNT(*) = 1