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(d ay, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
from (
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid,HashBytes('MD5 ',Convert( varbinary, xml_data)) MD5Hash
from PRODSQL1.message_center.db o.fielded_ data_trans action fd
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
from
(
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId,HashBytes('MD5 ',Convert( varbinary, xml_data)) MD5Hash
from PRODSQL1.message_center.db o.fielded_ data_trans action
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) = 1
select CONVERT(char(12),DATEADD(d
from (
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d
from
(
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre
HAVING COUNT(*) = 1
Is HashBytes a SQL Server UDF?
ASKER
udf? - user defined??
I don't know...
I don't know...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks for the lead on where to look.
Scotto13
I mean wherever you have
HashBytes
replace it with
dbo.HashBytes
HashBytes
replace it with
dbo.HashBytes
ASKER
I got it.
There is no table called HashBytes. When I try this, it gets an error, cannot find Hashbytes...
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(d ay, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
from (
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid, dbo.HashBytes('MD5',Conver t(varbinar y,xml_data )) MD5Hash
from PRODSQL1.message_center.db o.fielded_ data_trans action fd
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
from
(
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId, dbo.HashBytes('MD5',Conver t(varbinar y,xml_data )) MD5Hash
from PRODSQL1.message_center.db o.fielded_ data_trans action
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) = 1
With a bdo prefix - it complains that there's no such table? :-S
select CONVERT(char(12),DATEADD(d
from (
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d
from
(
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre
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(d ay, -1, GetDate()),101) ShiftDate,Ropid,Adid, SubregionId,0 as 'Pass', 1 as 'Fail'
from (
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as Subregionid
from PRODSQL1.message_center.db o.fielded_ data_trans action fd
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,Ropid, Adid, SubregionId, 1 as 'Pass', 0 as 'Fail'
from
(
select CONVERT(char(12),DATEADD(d ay, -1, GetDate()),101) ShiftDate,key3 as Ropid,key2 as Adid,key1 as SubregionId
from PRODSQL1.message_center.db o.fielded_ data_trans action
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre gionid
HAVING COUNT(*) = 1
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(d
from (
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate )
fd_fail_hash
GROUP BY ShiftDate,RopId,AdId,Subre
HAVING COUNT(*) > 1
union
select CONVERT(char(12),DATEADD(d
from
(
select CONVERT(char(12),DATEADD(d
from PRODSQL1.message_center.db
where date_created between @StartDate and @EndDate
) fd_pass_hash
GROUP BY ShiftDate,RopId,AdId,Subre
HAVING COUNT(*) = 1