[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MD5 Hash in Access???

Posted on 2006-03-21
8
Medium Priority
?
1,817 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Scotto13
  • 5
  • 3
8 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16253090
Is HashBytes a SQL Server UDF?
0
 

Author Comment

by:Scotto13
ID: 16253106
udf? - user defined??

I don't know...
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1600 total points
ID: 16253161
Yeah - a User Defined Function that he's written himself.
Have you tried replacing it with

dbo.HashBytes
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Scotto13
ID: 16253210
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16253229
I mean wherever you have
HashBytes
replace it with
dbo.HashBytes
0
 

Author Comment

by:Scotto13
ID: 16253244
I got it.

There is no table called HashBytes.  When I try this, it gets an error, cannot find Hashbytes...
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16253267
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16253282
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question