Re: Sql cursor problem... - Database Discussion Boards

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

Use the config file. If its .NET2 or higher there is a app setting of connectionstring specifically for this.

Bob
Ashfield Consultants Ltd

Hello,

I have a product table.
I have a ProdIn table with fields (ProdIn,ProdOut,Cost)
ProdIn,ProdOut are the fields from Product Table.

Now I want to fill up ProdIn table with all the combinations of Products.i.e If I have 2 products ,I have 2^2 records in ProdIn.

I don't know how to do this.

I have done this which is wrong
Select Product as ProdIn ,Product as ProdOut from Product

Prithaa

This will probably do it -

select a.product as prodin,b.product as prodout
from product a, product b

Bob
Ashfield Consultants Ltd

Hello,

Thanks
It has worked.

Please give sites which offers such query types.

Prithaa

No problem.

prithaa wrote:
Please give sites which offers such query types

I don't know of any specifically but sqlservercentral is quite good.

Bob
Ashfield Consultants Ltd

How to write a trigger on imported data?
Suppose we import the data on an existing table and we want some updation on imported data automatically by the use of trigger. How should I do?
I am working with sql 2000. I have tried google but no sucess found........ please help me soon...

modified on Thursday, June 12, 2008 2:20 AM

Just create an on insert trigger, use the special inserted table to get the data you are inserting and update the real table as required.

Bob
Ashfield Consultants Ltd

Hi Bob,
Do you have any other Method?

Not really. From your original post I would use a trigger, or if it was a one off load maybe run some SQL to do the update after the load, but it all depends on what you are trying to do.

Bob
Ashfield Consultants Ltd

Hi all,

I m working on VC++ 2005, MFC-SDI type application, I m using ODBC for connection of Access Database.

CRecordset use as Derived class of Database.

Here i want to get the index of Currently save Record.

Please tell me how can i do this.

I m waiting for your valuable suggestions.

Thanks in advance.

IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH

Cross poster.

Holidays! (June 13th)
http://craptasticnation.blogspot.com/[^]

I am currently using a sproc like the one below to get both a dataset and a count of the rows returned.

Is there a better way to do this?

	@Filter		int,
	@theRows	int output
AS
BEGIN
	SELECT
		Field1,
		Field2
	FROM
		MyTable
	WHERE
		FilterField=@Filter

	SET @theRows=(select count(*) from MyTable WHERE FilterField=@Filter)

END

Thanks.

Try this

	@Filter		int,	@theRows	int output
AS
BEGIN	
SELECT		Field1,		Field2	
FROM		MyTable	
WHERE		FilterField=@Filter	

SELECT @theRows = @@ROWCOUNT
END

Bob
Ashfield Consultants Ltd

I knew there was a better way than doing another select/from

Thanks Bob.

No problem.

Bob
Ashfield Consultants Ltd

Hi..
Sure You Can Have This One....

@Filter		int,	
@theRows	int output
AS
BEGIN	
	SET @theRows=(SELECT	Count(Field)	
                      FROM	MyTable	
                      WHERE	FilterField=@Filter)
END

Hope It Will work...
do Reply..
Have Nice Day..
Big Grin | :-D