bionicblakey
asked on
select Distinct error 8163
insert into mydatabase..mail_queue ( mailTo, mailType, mailFrom, subject, contentType, mailAux, mailFK, mailFK2, message)
select distinct emailAddress, 'regenschirm', @fromAddress, @subject, 'text/html',
Why is this? It worked fine on SQL SRV 7 but on SQL SERVER 2000 this no longer works!
select distinct emailAddress, 'regenschirm', @fromAddress, @subject, 'text/html',
Why is this? It worked fine on SQL SRV 7 but on SQL SERVER 2000 this no longer works!
ASKER
distinct is there so that only one email is sent to a user that may appear more than once inthe db.
The code you see above is the exact same code as i used in sql 7. Also i copied the exact db to sql 2000
I am not sure what you mean, here is the whole Sp
insert into ourdb..mail_queue ( mailTo, mailType, mailFrom, subject, contentType, mailAux, mailFK, mailFK2, message)
select distinct emailAddress, 'regenschirm', @fromAddress, @subject, 'text/html',
cast(contact_emails.contac tID as varchar(8000)), @jobID, @template, null
from CONTACT_EMAILS join RESULTS on contact_emails.contactID = result
join CONTACTS on RESULTS.result = CONTACTS.ID
join GROUP_MEMBERS as GM on GM.contactID = contacts.ID
join GROUP_REQUIREMENTS as GR on GR.groupID = GM.groupID
join GROUP_TOKENS as GT on GT.tokenCode = GR.tokenCode
join GROUP_MEMBERS as U on U.groupID = GT.groupID
where contact_emails.preferred = 'Y' and contacts.useEmail = 'Y'
and RESULTS.contactUser = @contactUser
and (GR.actionCode = 'SEND' or GR.actionCode = 'SUPERSEND')
and U.contactID = @contactUser
The code you see above is the exact same code as i used in sql 7. Also i copied the exact db to sql 2000
I am not sure what you mean, here is the whole Sp
insert into ourdb..mail_queue ( mailTo, mailType, mailFrom, subject, contentType, mailAux, mailFK, mailFK2, message)
select distinct emailAddress, 'regenschirm', @fromAddress, @subject, 'text/html',
cast(contact_emails.contac
from CONTACT_EMAILS join RESULTS on contact_emails.contactID = result
join CONTACTS on RESULTS.result = CONTACTS.ID
join GROUP_MEMBERS as GM on GM.contactID = contacts.ID
join GROUP_REQUIREMENTS as GR on GR.groupID = GM.groupID
join GROUP_TOKENS as GT on GT.tokenCode = GR.tokenCode
join GROUP_MEMBERS as U on U.groupID = GT.groupID
where contact_emails.preferred = 'Y' and contacts.useEmail = 'Y'
and RESULTS.contactUser = @contactUser
and (GR.actionCode = 'SEND' or GR.actionCode = 'SUPERSEND')
and U.contactID = @contactUser
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I am not sure what you mean, here is the whole Sp<<
As SjoerdVerweij has pointed out you cannot have text, ntext or image in a DISTINCT query (in any version of SQL Server). That means that one of the columns in the Select statement is one of those data types. I am going to go out on a limb here and guess that it is "emailAddress"
As SjoerdVerweij has pointed out you cannot have text, ntext or image in a DISTINCT query (in any version of SQL Server). That means that one of the columns in the Select statement is one of those data types. I am going to go out on a limb here and guess that it is "emailAddress"
>>One of the @vars must be of Text type then. <<
Good point, I had not thought of that.
Good point, I had not thought of that.
ASKER
i wish it were the emailaddress. but no, it is not.
BSJ
BSJ
ASKER
acperkins : This exact SP worked fine for 3 years using SQL SRV 7. Only until we migrated to SQL 2000 did this error occur.
SjoerdVerweij : Thanks so much. That did the trick!
SjoerdVerweij : Thanks so much. That did the trick!
(Why is the distinct there?)