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
Scotto13Asked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
Yeah - a User Defined Function that he's written himself.
Have you tried replacing it with

dbo.HashBytes
0
 
Leigh PurvisDatabase DeveloperCommented:
Is HashBytes a SQL Server UDF?
0
 
Scotto13Author Commented:
udf? - user defined??

I don't know...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scotto13Author Commented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
I mean wherever you have
HashBytes
replace it with
dbo.HashBytes
0
 
Scotto13Author Commented:
I got it.

There is no table called HashBytes.  When I try this, it gets an error, cannot find Hashbytes...
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.