| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2002-01-18 : 17:21:57
|
| Hi,when I tried to execute the following statement I got an error as follows:Server: Msg 8163, Level 16, State 3, Line 1The text, ntext, or image data type cannot be selected as DISTINCT. I got this error message in my web server also running sql server 7.0.But when i executed the same query in my local machine, I didnt got any errors,But i got the result as i desired.What would be the problem?...Is there anything i need to change in settings...or something else....The candidate_id is of int datatype. For both these queries i got the same error.when i executed these queries in my server.In my local machine both worked very well.My queries are as follows:1. Select * from candidates where candidate_email='karuna@karuna.com' and password='123456'2.select distinct a.candidate_id),candidate_name,city,state_name,company_name,companyprofile from candidateinfo a,companyinfo b,state c,candidate_cities,candidate_specialisation,candidate_country where a.candidate_id=b.candidate_id and b.state=c.state_id and candidate_specialisation.spl_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) and candidate_specialisation.candidate_id=a.candidate_id and candidate_cities.city_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169) and candidate_cities.candidate_id=a.candidate_id and a.candidate_id not in(select candidate_id from ermailerslist where mailer_id=1012)Edited by - karuna on 01/19/2002 08:00:29 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-18 : 17:45:04
|
| It's saying that one of the columns in the SELECT statement is a text/ntext/image column. Are you sure the table structures are EXACTLY the same on both machines?By the way, here's a MUCH SHORTER version of your query:select distinct a.candidate_id, candidate_name, city, state_name, company_name, companyprofile from candidateinfo a join companyinfo b on (a.candidate_id=b.candidate_id)join state c on (b.state=c.state_id) join candidate_specialisation on (candidate_specialisation.candidate_id=a.candidate_id), join candidate_cities on (candidate_cities.candidate_id=a.candidate_id), candidate_country --do you even need this table???where candidate_specialisation.spl_id between 1 and 20 and candidate_cities.city_id between 1 and 169 and a.candidate_id not in (select candidate_id from ermailerslist where mailer_id=1012)I sense that this is an Oracle query converted to SQL Server. If so, I would recommend that you study Transact-SQL a little closer because it will yield some optimizations that may not have been available using Oracle (the JOIN syntax in particular). You definitely should use BETWEEN instead of listing a huge list of individual values as an IN clause.I don't know how candidate_country plays in this query; it's not joined to anything and I can't determine if it's columns are included in the SELECT list. |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-01-18 : 17:58:03
|
| Hi, Few things I like to clarify on the above query.The query is not a standard one for all users.its dynamically developed based on the selections they make from a select box in an asp page.The four option he has is as follows.CitiesCountriesspecialisation - He preferssince the specialisation_id and city_id,country_id depends on the users selection I cant use between clause.If he selects 1,3,9,10 option in the select box then the between clause wont work.apart from the candidate_id other columns are varchar type.I'am confused with one thing..when i run the same query in my local machine it worked very well with out any problems,But its not working with sql server in my web server.I dont know why.... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-18 : 18:36:16
|
| Are there any text columns in any of the tables listed, even if they're not in the SELECT clause? Check both SQL Servers, local and web. If you find a text column in a table, see if removing that table from the query fixes the problem (just for test purposes).Can you rewrite this as a stored procedure that accepts parameters, instead of having it generate dynamically? You might need some dynamic SQL anyway, but the less you have the better.What other options can the user set? Can you provide more detail on the whole process? |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-01-18 : 18:59:46
|
| Hi, The companyprofile column in the select statement is of ntext type.when i removed it from the select statement the query worked fine in the server.Even without removing companyprofile column from the select statement the query was working in the local machine.But that companyprofile column is the important column which i need to retrieve along with other columns in the select statement |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-18 : 19:56:36
|
| The last thing I can think of is that there are two different service packs installed on the machines, or there is a setting that conflicts with the DISTINCT keyword. Have you tried removing the DISTINCT? I would try that first, then run SELECT @@VERSION on each machine and see if they have different values.Do you absolutely need the DISTINCT? It seems to me that companyprofile would relate to a single company row/company id. The DISTINCT might well be redundant. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-01-18 : 20:59:45
|
| I tried removing the distinct, what happend is it retrived me almost 40,000+ records,thats is the actual result should be 14 records,but for each city_id,spl_id etc,the records got repeated and hence the total result what i got is almost 40,000+...The Sql versions are as below:Local Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Desktop Edition Evaluation on Windows NT 5.0 (Build 2195: Service Pack 2)ServerMicrosoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition Evaluation on Windows NT 4.0 (Build 1381: Service Pack 6) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-19 : 12:01:36
|
| This is the closest I could find on the problem:http://support.microsoft.com/default.aspx?scid=kb;EN-US;q162032Unfortunately it doesn't help you much. Are you sure that the remote server isn't set for 6.5 compatability mode?The fact that the local machine is running Windows 2000 is most likely the reason it works there, although I can't imagine why. You also have the base SQL 7.0 software on both machines, with no service packs installed. I would recommend installing SQL Server SP3 on both machines, this may clear up the problem. Failing that, I think you're stuck with this unless you upgrade the remote machine to Windows 2000. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-01-28 : 15:30:02
|
| When I changed the datatype of company profile to nvarchar from ntext the above query worked.So as of now I have a solution for this problem.But I think I need a better solution.If I could able to find the solution.I'll inform you guys.Thank you very much for your help Rob. |
|
|
|
|
|
|
Problem with My select distinct clause stmt..