• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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!

0
bionicblakey
Asked:
bionicblakey
  • 3
  • 2
  • 2
1 Solution
 
SjoerdVerweijCommented:
You probably had message as VarChar on SQL7 and as Text on SQL2000, or did not use distinct. Try replacing message with cast(message as varchar(8000)) in your select. If that does not work (messages longer than 8000 characters), you will have to use an intermediate step.

(Why is the distinct there?)

0
 
bionicblakeyAuthor Commented:
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.contactID 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
      
0
 
SjoerdVerweijCommented:
Hmm. One of the @vars must be of Text type then. But, there's a way around that:

insert into ourdb..mail_queue ( mailTo, mailType, mailFrom, subject, contentType, mailAux, mailFK, mailFK2, message)
     select emailAddress, 'regenschirm', @fromAddress, @subject, 'text/html',
          x.contactID, @jobID, @template, null
     from (select distinct emailaddress, contact_emails.contactID
     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) as x
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
>>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"
0
 
Anthony PerkinsCommented:
>>One of the @vars must be of Text type then. <<
Good point, I had not thought of that.
0
 
bionicblakeyAuthor Commented:
i wish it were the emailaddress. but no, it is not.

BSJ
0
 
bionicblakeyAuthor Commented:
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!
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now