Another SelectCommand Distinct problem

why i type the select command like below the disctinct doesnt work? the query stil show all the Category i hav so how do i fix it??

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC">

<SelectParameters>

<asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

Hi

You may want to try

"SELECT DISTINCT Category FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC"

The SELECT shown, viz., "Category, ID" brings all the distinct combinations of Category AND ID

Fouwaaz

|||

it work using singel feild.lets say if i select category, Title and weblink but i oni wan DISTINCT Category how do i change the code??

SELECT DISTINCT Category, Title,Weblink FROM Bookmarks WHERE (UserName = @.username) AND (Category = 'Top1' OR Category = 'Top2' OR Category = 'Top3' OR Category = 'Top4' OR Category = 'Top5')

now the distinct is handle 3 feild how to i make the distinct handle 1 feild?? bcoz i was trying to the output like this:

Original Data

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Output is the title become weblink...

|||

Hi

Sorry, it looks like I did not understand your question. This is the output you have shown

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Now, could you show the output that you would like to get?

Thanks

Fouwaaz

Another Question: Replication

Just another quick question:
When you have published a article is it possible to change a column data type without drop the replication, thanks (Again)Yes, but you have to drop the article first before you change it. After you have made the change, then add the article back in the publication.|||Originally posted by joejcheng
Yes, but you have to drop the article first before you change it. After you have made the change, then add the article back in the publication.

Thanks again. Ed

another query question

Hi,
How do you do this in Pubs?
Select the types of books that had an average price > $13.50
I can only go so far:
select type, avg(price) from titles group by typenospam
select type, avg(price) from titles
group by type
HAVING avg(price) >13
"nospam" <hello@.hotmail.com> wrote in message
news:TuOdnavPvLGCLciiRVn-hA@.giganews.com...
> Hi,
> How do you do this in Pubs?
> Select the types of books that had an average price > $13.50
>
> I can only go so far:
> select type, avg(price) from titles group by type
>

Another Date Picker question

I created a report with two parameters, StartDate and EndDate.

They are specified as datetime type.

When I went to the Preview tab, there were date pickers for both values.

After a week or so of using the report, I thought it would be better if I gave the parameters default values. I spent some time reading what others had done in this forum and used the following as the expressions for the parameters:

Code Snippet

=IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2)))))))

=IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2)))))))

and now, there are no date pickers. I am curious. Why are they gone? Can I get them back and still have my default values?

Thanks,

IanO

Try removing the FormatDateTime function.|||

Thanks for your reply. Allow me to explain why the Format is there.

When I had not specified default values for the report parameters, the date pickers provided values that were dates only - no time values. After I put in the IIFs for the default values, the defaults included 12:00:00 AM with the date.

I hunted for quite a while and the solution that I chose was the Format. Perhaps you have another idea of how I can have a default Sunday and a default Saturday for the current week, without the time?

Thanks,

IanO

|||It's odd that the default values gave you a time. I used it here and had only date values. Hmmmm.|||If you want to keep the format text you should be able to add the cdate function.|||

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2))))))))

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2))))))))

Then make sure your datatype is date because using just your original expression you get an error if the datatype is not a string.

Simone

Annoying Error Message

Hello,

I recently migrated from 2000 to 2005 database. On the new server, I kept getting this type of error message when there is some sort of join involved and they are not complex join either.

Error - "A severe error occurred on the current command. The results, if any, should be discarded."

ie

select A.Cookie1, A.Cookie2
into BookersLookers_Cookie
from BookersLookers_DataSet A
LEFT JOIN ActionSetup B on A.ActionID = B.ActionID
group by A.Cookie1, A.Cookie2

Is there something in the code where I could prevent this error from happening?

Any insight is appreciated,

-Lawrence

Moving to engine forum|||

Hi SQLChamp,

What is the build of your SQL server?

What is the exact error message you are getting?

Any errors in theerror log?

Jag

|||

Hello Jag Sandhu,

What is the build of your SQL server? 9.00.3042 (X64)

What is the exact error message you are getting? "A severe error occurred on the current command. The results, if any, should be discarded."

Any errors in theerror log? no error log set up

-Lawrence

|||

You encounter an old bug which should have been fixed if you're running sp2.

http://support.microsoft.com/default.aspx/kb/910416

The error log we're interested in is the sql log. You can get it by executing the following sproc.

exec sp_readerrorlog

Please post the result of "select @.@.version" while you're at it.

|||

I posted select @.@.version earlier - 9.00.3042 (X64)

I would find out any logs from sp_readerrorlog

If all else fails, I guess this is a big bug, and hopefully SP2 will fix it.

-Lawrence

|||

Hi

From your buildnumber it seems like you are running SP2 already.

Let us know what you get from error logs.

Jag

|||

Hello,

I received the following error message from sp_readerrorlop

2007-04-30 12:51:18.620 Server Error: 17310, Severity: 20, State: 1.

2007-04-30 12:51:18.620 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

|||

You should probably do what the message says.

Contact Product Support Services with the dump produced in the log directory.

Or possibly post the .mdmp file on Connect for analysis.

|||

I was wondering if anyone had a fix for this type of error/bug? Any hotfx or work-around?

Our IT Support contacted MS, but MS Production Support is not very responsive in this case. "Severe Error" still occurs in simple join. Any additional help would be much appreciated.

Sincerely,

-Lawrence

|||

SP1 works fine, so I would NOT recommend applying SP2.

-Lawrence

Annoying Error Message

Hello,

I recently migrated from 2000 to 2005 database. On the new server, I kept getting this type of error message when there is some sort of join involved and they are not complex join either.

Error - "A severe error occurred on the current command. The results, if any, should be discarded."

ie

select A.Cookie1, A.Cookie2
into BookersLookers_Cookie
from BookersLookers_DataSet A
LEFT JOIN ActionSetup B on A.ActionID = B.ActionID
group by A.Cookie1, A.Cookie2

Is there something in the code where I could prevent this error from happening?

Any insight is appreciated,

-Lawrence

Moving to engine forum|||

Hi SQLChamp,

What is the build of your SQL server?

What is the exact error message you are getting?

Any errors in theerror log?

Jag

|||

Hello Jag Sandhu,

What is the build of your SQL server? 9.00.3042 (X64)

What is the exact error message you are getting? "A severe error occurred on the current command. The results, if any, should be discarded."

Any errors in theerror log? no error log set up

-Lawrence

|||

You encounter an old bug which should have been fixed if you're running sp2.

http://support.microsoft.com/default.aspx/kb/910416

The error log we're interested in is the sql log. You can get it by executing the following sproc.

exec sp_readerrorlog

Please post the result of "select @.@.version" while you're at it.

|||

I posted select @.@.version earlier - 9.00.3042 (X64)

I would find out any logs from sp_readerrorlog

If all else fails, I guess this is a big bug, and hopefully SP2 will fix it.

-Lawrence

|||

Hi

From your buildnumber it seems like you are running SP2 already.

Let us know what you get from error logs.

Jag

|||

Hello,

I received the following error message from sp_readerrorlop

2007-04-30 12:51:18.620 Server Error: 17310, Severity: 20, State: 1.

2007-04-30 12:51:18.620 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

|||

You should probably do what the message says.

Contact Product Support Services with the dump produced in the log directory.

Or possibly post the .mdmp file on Connect for analysis.

|||

I was wondering if anyone had a fix for this type of error/bug? Any hotfx or work-around?

Our IT Support contacted MS, but MS Production Support is not very responsive in this case. "Severe Error" still occurs in simple join. Any additional help would be much appreciated.

Sincerely,

-Lawrence

|||

SP1 works fine, so I would NOT recommend applying SP2.

-Lawrence