Database Discussion Boards

thank you very much.

hi friend i want to rollback all the transaction of today when there is some Exception thrown while executing today Query how i can do this
in C#.

wasim khan

I'm not quite sure what yuo are trying to achieve, but you enclose your sql in a try catch with a transaction:

<pre>try
{
SQLConnection conn = new SQLConnection(connectionString);
conn.begintran():
...do your sql
conn.committran();
}
catch (Exception)
{
conn.rollbacktran();
}
finally
{
conn.close();
conn.dispose();
}</pre>

I may have the syntax slightly wrong, I don't have c# on this laptop to check, but yuo should get the idea.

Bob
Ashfield Consultants Ltd

Do you mean you want to roll back an earlier transaction if a later transaction fails?

---------------------------
Blogging about SQL, Technology and many other things

Hi!

I´m new here, and as i was in doubt about the right place to publish a question that involves asp.net and ado.net, i published in asp.net but here is the link for it.

http://www.codeproject.com/script/Forums/View.aspx?fid=12076&select=2597234#xx2597234xx[^]

Really thanks for the help.

Fernando

Hi,

I have a stored procedure that I can not get to work. I am passing a value for the sort order in the GROUP BY clause, but code does not want to create the stored procedure. What am I doing wrong:

create PROCEDURE [dbo].[nsp_SearchManager_716]
(
@RequestedNB VARCHAR(50),
@Sort VARCHAR(50),
@SortOrder VARCHAR(50)
)

AS

SELECT
DISTINCT *
FROM
tblResult WITH (NOLOCK)
WHERE
sRequestedNB = @RequestedNB
ORDER BY
sSalary @SortOrder;

I would appreciate any sort of help.

Thanks
Brendan

I'm fairly certain that it wont evaulate the variable @SortOrder to get the direction. however you can do

EXEC ('SELECT DISTINCT * '+
'FROM tblResult WITH (NOLOCK) '+
'WHERE sRequestedNB = ' + @RequestedNB +
'ORDER BY sSalary ' + @SortOrder)

which will create then execute the string including evaluating the varibles. you could also put the sring into a varchar varible so that you can do SELECT or EXEC on it for testing purposes. limiting the size of your sort order varible to 3 (ASC, DSC) would help prevent someproblems from wherever this gets called. Hope this works for you.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

The syntax won't work unfortunately - I wish it would. there are two real options, create some dynamic sql or

if @SortOrder = 'ASC'
begin
SELECT
   DISTINCT *
FROM
   tblResult WITH (NOLOCK)
WHERE
   sRequestedNB = @RequestedNB
ORDER BY
   sSalary ASC
end 
else
begin
SELECT
   DISTINCT *
FROM
   tblResult WITH (NOLOCK)
WHERE
   sRequestedNB = @RequestedNB
ORDER BY
   sSalary DESC
end

Alternatively, would it be possible to do the order by back in your code?

Bob
Ashfield Consultants Ltd

If salary is a number simply multiply -1 or 1 on it for desc and asc sorting.
It is an easy way to do dynamic sorting of number fields (and dates).

---------------------------
Blogging about SQL, Technology and many other things

Good idea, never thought about doing that.

Bob
Ashfield Consultants Ltd

hi..
can anyone help in merging two sqlreports into one report.the one report has landscape orientation and other report has portrait mode.
if it is not possible to merge then how can we use the subreport method to do the same..

Hi Friends,

I am using SQL Server 2005. I am facing the following problem. I need to know how to apply if condition in query.

I am having one table called Test1 with two fields c_name, average.

CC 7
CC 6
CC 8
IGC 2
IGC 4
IGC 6
HC 4
HC 5
NM 9
NM 6
NC 2
NC 5
NC 9
JC 4
JC 3
JC 1

The above are the values in that table.

Now I am using the following query to display the c_name, count(college) with average >5 group by c_name

select c_name,count(c_name) as Total from test1
where average>5 group by c_name

Result :

CC 3
IGC 1
NC 1
NM 2

Based on my query I am getting the above result. In the above result the colleges HC and JC are not coming because it has average only less than 5. But for my case, I want to display the college name with the total as 0 if that college doesnt have average >5. I need to modify the above query to get the result like below.

CC 3
IGC 1
NC 1
NM 2
HC 0
JC 0

Please help for this.

Thanks in Advance,

Regards,

Try this:

select c_name, sum(case when average > 5 then 1 else 0 end) as Total
from Test1
group by c_name

Bob
Ashfield Consultants Ltd

Hi Bob,

Thanks lot for ur help.

Its working well.

Regards,

No problem

Bob
Ashfield Consultants Ltd

Hi,

I have database backup from sql server 2005, i want to restore that backup file to sql server 2000. I have tried attach database and restore database but i am not able to do that. Is there any way to achieve this? please help me.

Thanks
Warm Regards
Prakash-B

Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server.

With the help of Export/Import OR some third party Tool you can transfer your data from 2005 to 2000 and this is the way only.

Parwej Ahamad
R & D with IIS 5.0/6.0

modified on Friday, June 13, 2008 11:57 AM

Seems reasonable to me, what makes you beleive you will be able to restore a 05 DB on 2000. I would have thought this would be a futile effort.

You will be able to DTS, or select the data across. You may even be able to use replication but restore, not a chance.

Never underestimate the power of human stupidity
RAH

i had a problem with sending mail using trigger when Update data from a table. Procedure is executed successfully. but when i update record from a particular table. I had a following error

xp_sendmail: Either there is no default mail client or the current mail

client cannot fulfill the messaging request. Please run Microsoft Outlook

and set it as the default mail client.

(0 row(s) affected)

Can any one explain what's my problem and give me a solution for this.

it's Urgent !!!!!!!

Thanks in Advance!!!
Sabarees

modified on Thursday, June 12, 2008 6:01 PM

I hav gone through google around your problem and found there some resonale solution that would help you.

http://www.smartcomputing.com/techsupport/detail.aspx?guid=&ErrorID=22240[^]

http://kbalertz.com/905421/receive-there-default-client-current-client-cannot-fulfill-messaging-error-message-Address.aspx[^]

http://kbalertz.com/905421/receive-there-default-client-current-client-cannot-fulfill-messaging-error-message-Address.aspx[^]

Parwej Ahamad
R & D with IIS 5.0/6.0

Every morning I have a temporary table that gets updated with Customer Sales data (year to date, month to day, last year to date, etc), from another database. One problem I have is that I get every single customer from that database, even if a customer's MTD, YTD,LYTD figures have no changed, resulting in getting 625,000 records. Another is that I need update Existing customer with the data in my temp table. I tried using a while loop and looping through my table to update records. I also tried select the records from the temp table and comparing the values to what is in the existing, the placing those records in a table variable and using a cursor on the temp table. Both of these take entirely too much time. Here is the T-sql for reference...

Declare @ARbal money,
@CustTreeNodeID uniqueidentifier,
@CustomerNumber varchar(50),
@CustomerUpdateID int,
@LYSales money,
@LYTDSales money,
@YTDgm money,
@YTDSales money,
@MTDSales money,
@LMTDSales money

Declare @t table
(
CustomerUpdateID int,
CustomerNumber varchar(50),
YTDSales money,
LYSales money,
ARBal money,
LYTDSales money,
MTDSales money,
LMTDSales money
)

insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales)

select CustomerUpdateID,
CustomerNumber,
YTDSales,
LYSales,
ARBal,
LYTDSales,
MTDSales,
LMTDSales
from _CustomerUpdate u

where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD ))

declare mycursor cursor forward_only
for select CustomerUpdateID from @t

open mycursor
while (1=1)
begin
fetch next from mycursor into @CustomerUpdateID
if @@Fetch_status <>0
break;

set @CustomerNumber = (Select CustomerNumber from _CustomerUpdate where CustomerUpdateID = @CustomerUpdateID)

if isnull(@CustomerNumber,'')<>''
begin
set @CustTreeNodeID =(select CustTreeNodeID from CustTreeNode where CustomerNumber = @CustomerNumber)
end

select top 1 @YTDSales = YTDSales,
@LYSales = LYSales,
@ARbal = ARbal,
@LYTDSales = LYTDSAles,
@MTDSales = MTDSales,
@LMTDSales = LMTDSales
from @t where CustomerUpdateID = @CustomerUpdateID

update CustTreeNodeAccountAssoc set YTD = @YTDSales, LYTD= @LYTDSales,Balance = @ARbal where CustTreeNodeID = @CustTreeNodeID

end
close mycursor
deallocate mycursor

Can you re-write the cursor to not use cursors? I think cursors are slow in SQL.

Like I said I tried using a loop using a status field on the temp table: something like this

while(select top 1 from _customerUpdate where [Status] ='INC') = 'INC'
begin
Begin Try
perform customer update logic here...

End try
Begin Catch

End Catch

Update _CustomerUpdate set Status ='CMP' where CustomerUpdateID = @CustomerUpdateID.
End

Either way both of these ran incredibily slow...

I don't know your schema so consider this as psudocode, but this will be faster due to the fact that it isn't using a cursor (as suggested previously). Then, if possible, I would add datetime columns which you can use to compare the date your _CustomerUpdate table was changed with the date your CustTreeNodeAccountAssoc table was updated. This will prevent the need to retrieve all 600K+ records, that will also considerably speed things up and it will scale better as the number of records increases.

<br />
Declare @ARbal money,<br />
@CustTreeNodeID uniqueidentifier,<br />
@CustomerNumber varchar(50),<br />
@CustomerUpdateID int,<br />
@LYSales money,<br />
@LYTDSales money,<br />
@YTDgm money,<br />
@YTDSales money,<br />
@MTDSales money,<br />
@LMTDSales money<br />
<br />
Declare @t table<br />
(<br />
CustomerUpdateID int,<br />
CustomerNumber varchar(50),<br />
YTDSales money,<br />
LYSales money,<br />
ARBal money,<br />
LYTDSales money,<br />
MTDSales money,<br />
LMTDSales money<br />
)<br />
<br />
insert into @t(CustomerUpdateID, CustomerNumber,YTDSales, LYSales,ARBal, LYTDsales,MTDSales,LMTDSales)<br />
<br />
select CustomerUpdateID,<br />
CustomerNumber,<br />
YTDSales,<br />
LYSales,<br />
ARBal,<br />
LYTDSales,<br />
MTDSales,<br />
LMTDSales<br />
from _CustomerUpdate u <br />
<br />
where exists (select 1 from CustTreeNode c join CustTreeNodeaccountAssoc ca on ca.CustTreeNodeID = c.CustTreeNodeID and (u.LYTDSales != ca.LYTD or u.YTDSales != ca.YTD or u.MTDSales!= ca.MTD or u.LMTDSales !=ca.LMTD ))<br />
<br />
-- REPLACE YOUR CURSOR WITH THIS CODE BELOW (or something similar that better fits your logic)<br />
UPDATE A<br />
  YTD = T.YTDSales, <br />
  LYTD = T.LYSales,<br />
  Balance = T.ARBal<br />
FROM @t T<br />
INNER JOIN CustTreeNode N ON N.CustomerNumber = T.CustomerNumber<br />
INNER JOIN CustTreeNodeAccountAssoc A ON A.CustTreeNodeID = N.CustTreeNodeID<br />

Mark's blog: developMENTALmadness.blogspot.com

Am developing a multi user VB.NET app and i would welcome suggestions on how to code my connection code.
How do i put it such that i only write it once(maybe a module -like i used to do in VB 6) or put it in an XML config file