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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

I don't know...
Leigh PurvisDatabase DeveloperCommented:
Yeah - a User Defined Function that he's written himself.
Have you tried replacing it with

dbo.HashBytes

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
Leigh PurvisDatabase DeveloperCommented:
I mean wherever you have
HashBytes
replace it with
dbo.HashBytes
Scotto13Author Commented:
I got it.

There is no table called HashBytes.  When I try this, it gets an error, cannot find Hashbytes...
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.