Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

select Distinct  error 8163

Posted on 2004-07-30
7
543 Views
Last Modified: 2008-02-01
     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
Comment
Question by:bionicblakey
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11682077
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
 

Author Comment

by:bionicblakey
ID: 11682100
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
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 500 total points
ID: 11682127
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11682151
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11682154
>>One of the @vars must be of Text type then. <<
Good point, I had not thought of that.
0
 

Author Comment

by:bionicblakey
ID: 11682192
i wish it were the emailaddress. but no, it is not.

BSJ
0
 

Author Comment

by:bionicblakey
ID: 11682249
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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