Problem with DECLARE CURSOR

Dear Sir,

I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data 
Object 2.5.

If I run following query through SQL Server 2005 Management Studio through 
QUERY only then it works FINE. But if I run from application using Execute 
method of Command Object of ADO then it process maximum 49 records in CURSOR 
and query completes without any errors.

I had checked by inserting records in a Temporary Table. If I do Not Use 
UPDATE statement then it INSERT maximum 65 records in Table and if I use 
UPDATE statement then it INSERT & UPDATE maximum 49 records.

PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.

Please reply me after proper reading of this query.

Sanjay Shah


--- Declare Scalar Variables
DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME, 
@cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS 
nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode AS 
nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15), 
@nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS 
nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6), 
@nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS 
FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3), 
@nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;

--- Define Cursor with Transaction Ledger
DECLARE Ledger_Cursor CURSOR FOR
SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode, ItemSno, 
Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear, RBranchCode, 
RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, Quantity1, 
Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM FaItemLedger WHERE 
DocumentDate >= ('04/01/2003') AND DocumentDate <= ('04/30/2003') ORDER BY 
DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 > 0 OR Amount > 0 THEN 1 
ELSE  2 END ;

--- Open Cursor Rows
OPEN Ledger_Cursor;

--- Get Values of Cursor Columns into Scalar Variables
FETCH NEXT FROM Ledger_Cursor INTO
@cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode, 
@nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo, 
@nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode, 
@cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer, @nAmount, 
@cTaxCode, @nTaxableAmount, @nTaxAmount;

-- Perform till Fatch Status is 0 means (Not EOF)
WHILE @@FETCH_STATUS = 0
    BEGIN
        --- Initialise RDocumentNo if Valuation is Not Bill Wise
        IF @cValuation NOT IN ('B','P')
            BEGIN
                SET @nRDocumentYear = 0;
                SET @cRBranchCode = '';
                SET @cRDocumentType = '';
                SET @cRSeriesCode = '';
                SET @cRDocumentNo = '';
                SET @nRItemSno = 0;
            END

        --- Update Stock Master with Values of Scalar Variables
        UPDATE FaStockMaster SET
               FaStockMaster.StockQuantity = 
Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
               FaStockMaster.StockQuantity1 = 
Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
               FaStockMaster.Used = 'True',
               FaStockMaster.Month1TotalQuantity = 
ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
               FaStockMaster.Month1TotalQuantity1 = 
ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
               FaStockMaster.Rate = CASE WHEN 
Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  > 0 AND CASE 
WHEN @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount 
* 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * 
CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity > 
0 AND @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 
0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END 
, 2) END > 0 AND @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
AND @cVa
luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1, 2) 
ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE 
WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity > 
0 AND @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 
0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END 
, 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE 
WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND 
FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END , 6) 
WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity > 
0 AND @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate / 
CASE WHEN @cValuation IN ('B','P') AND
@nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
FaStockMaster.Per END  * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate END ,
               FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND 
@nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
FaStockMaster.Per END ,
               FaStockMaster.StockValue = CASE WHEN @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount 
* 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * 
CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity > 
0 AND @cDocumentType IN 
('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 
0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END 
, 2) END,
               FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P') 
AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
               FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN 
('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE 
FaStockMaster.TaxableAmount END ,
               FaStockMaster.TaxAmount = CASE WHEN @cValuation IN ('B','P') 
AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
               FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN 
('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity > 0 
AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate ELSE 
FaStockMaster.LastReceiptDate END
               WHERE
               FaStockMaster.ItemCode = @cItemCode AND
               FaStockMaster.StoreCode = @cStoreCode AND
               FaStockMaster.ColorCode = @cColorCode AND
               FaStockMaster.SizeCode = @cSizeCode AND
               FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
               FaStockMaster.DocumentYear = @nRDocumentYear AND
               FaStockMaster.BranchCode = @cRBranchCode AND
               FaStockMaster.DocumentType = @cRDocumentType AND
               FaStockMaster.SeriesCode = @cRSeriesCode AND
               FaStockMaster.DocumentNo = @cRDocumentNo AND
               FaStockMaster.ItemSno = @nRItemSno ;

        --- Get Next Values of Cursor Columns into Scalar Variables
        FETCH NEXT FROM Ledger_Cursor INTO
        @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, 
@cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode, 
@cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType, 
@cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, 
@nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
    END

--- Close Cursor
CLOSE Ledger_Cursor;

--- Release Cursor
DEALLOCATE Ledger_Cursor;

--- *** End of Procedure


0
SANJAY
1/16/2010 10:35:58 AM
sqlserver.programming 1873 articles. 0 followers. Follow

16 Replies
813 Views

Similar Articles

[PageSpeed] 18

Sanjay,

There are three things I'd like to note. Feel free to ignore whatever
doesn't help you:

1. You are using an unsafe date format in the cursor definition. The
interpretation of ('04/01/2003') depends entirely on the connection
settings at the time you run the query.

2. You might consider specifying the cursor with as FAST_FORWARD, since
it is not your intension to change the table that is used in the cursor,
and you only navigate the cursor with FETCH NEXT.

3. There is no need for a cursor. You can write one UPDATE statement to
do all this.

-- 
Gert-Jan



"SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." wrote:
> 
> Dear Sir,
> 
> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data
> Object 2.5.
> 
> If I run following query through SQL Server 2005 Management Studio through
> QUERY only then it works FINE. But if I run from application using Execute
> method of Command Object of ADO then it process maximum 49 records in CURSOR
> and query completes without any errors.
> 
> I had checked by inserting records in a Temporary Table. If I do Not Use
> UPDATE statement then it INSERT maximum 65 records in Table and if I use
> UPDATE statement then it INSERT & UPDATE maximum 49 records.
> 
> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
> 
> Please reply me after proper reading of this query.
> 
> Sanjay Shah
> 
> --- Declare Scalar Variables
> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME,
> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS
> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode AS
> nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15),
> @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS
> nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6),
> @nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS
> FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3),
> @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
> 
> --- Define Cursor with Transaction Ledger
> DECLARE Ledger_Cursor CURSOR FOR
> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode, ItemSno,
> Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear, RBranchCode,
> RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, Quantity1,
> Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM FaItemLedger WHERE
> DocumentDate >= ('04/01/2003') AND DocumentDate <= ('04/30/2003') ORDER BY
> DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 > 0 OR Amount > 0 THEN 1
> ELSE  2 END ;
> 
> --- Open Cursor Rows
> OPEN Ledger_Cursor;
> 
> --- Get Values of Cursor Columns into Scalar Variables
> FETCH NEXT FROM Ledger_Cursor INTO
> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode,
> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo,
> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode,
> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer, @nAmount,
> @cTaxCode, @nTaxableAmount, @nTaxAmount;
> 
> -- Perform till Fatch Status is 0 means (Not EOF)
> WHILE @@FETCH_STATUS = 0
>     BEGIN
>         --- Initialise RDocumentNo if Valuation is Not Bill Wise
>         IF @cValuation NOT IN ('B','P')
>             BEGIN
>                 SET @nRDocumentYear = 0;
>                 SET @cRBranchCode = '';
>                 SET @cRDocumentType = '';
>                 SET @cRSeriesCode = '';
>                 SET @cRDocumentNo = '';
>                 SET @nRItemSno = 0;
>             END
> 
>         --- Update Stock Master with Values of Scalar Variables
>         UPDATE FaStockMaster SET
>                FaStockMaster.StockQuantity =
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>                FaStockMaster.StockQuantity1 =
> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>                FaStockMaster.Used = 'True',
>                FaStockMaster.Month1TotalQuantity =
> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>                FaStockMaster.Month1TotalQuantity1 =
> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>                FaStockMaster.Rate = CASE WHEN
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  > 0 AND CASE
> WHEN @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount
> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  *
> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity >
> 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity >
> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END
> , 2) END > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cVa
> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1, 2)
> ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE
> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity >
> 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity >
> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END
> , 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE
> WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND
> FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END , 6)
> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity >
> 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate /
> CASE WHEN @cValuation IN ('B','P') AND
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
> FaStockMaster.Per END  * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate END ,
>                FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
> FaStockMaster.Per END ,
>                FaStockMaster.StockValue = CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount
> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  *
> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity >
> 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity >
> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END
> , 2) END,
>                FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P')
> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>                FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN
> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE
> FaStockMaster.TaxableAmount END ,
>                FaStockMaster.TaxAmount = CASE WHEN @cValuation IN ('B','P')
> AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
>                FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity > 0
> AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate ELSE
> FaStockMaster.LastReceiptDate END
>                WHERE
>                FaStockMaster.ItemCode = @cItemCode AND
>                FaStockMaster.StoreCode = @cStoreCode AND
>                FaStockMaster.ColorCode = @cColorCode AND
>                FaStockMaster.SizeCode = @cSizeCode AND
>                FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>                FaStockMaster.DocumentYear = @nRDocumentYear AND
>                FaStockMaster.BranchCode = @cRBranchCode AND
>                FaStockMaster.DocumentType = @cRDocumentType AND
>                FaStockMaster.SeriesCode = @cRSeriesCode AND
>                FaStockMaster.DocumentNo = @cRDocumentNo AND
>                FaStockMaster.ItemSno = @nRItemSno ;
> 
>         --- Get Next Values of Cursor Columns into Scalar Variables
>         FETCH NEXT FROM Ledger_Cursor INTO
>         @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode,
> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode,
> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType,
> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate,
> @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>     END
> 
> --- Close Cursor
> CLOSE Ledger_Cursor;
> 
> --- Release Cursor
> DEALLOCATE Ledger_Cursor;
> 
> --- *** End of Procedure
0
Gert
1/16/2010 12:40:33 PM
"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> a �crit dans le 
message de groupe de discussion : 4B51B3C1.CA367EC3@xs4all.nl...
> Sanjay,
>
> There are three things I'd like to note. Feel free to ignore whatever
> doesn't help you:
>
> 1. You are using an unsafe date format in the cursor definition. The
> interpretation of ('04/01/2003') depends entirely on the connection
> settings at the time you run the query.
>
> 2. You might consider specifying the cursor with as FAST_FORWARD, 
> since
> it is not your intension to change the table that is used in the 
> cursor,
> and you only navigate the cursor with FETCH NEXT.
>
> 3. There is no need for a cursor. You can write one UPDATE statement 
> to
> do all this.

And 4 : Perhaps a time out on VB code side. Check the command object 
properties.

-- 
Fred
foleide@free.fr 

0
Fred
1/16/2010 12:55:44 PM
> I had checked by inserting records in a Temporary Table. If I do Not Use 
> UPDATE statement then it INSERT maximum 65 records in Table and if I use 
> UPDATE statement then it INSERT & UPDATE maximum 49 records.

Add SET NOCOUNT ON to the beginning of the script.  This is a Best Practice 
for ADO applications because it will suppress the DONE_IN_PROC messages 
(rowcounts) that can interfere expected behavior with the ADO API. 
Alternatively, you can invoke the ADODB.Command MoveNext and NextRecordset 
methods from within nested loops in your application code to consume all the 
resultsets.

Also, to add on to Gert-Jan's recommendations, I suggest you add LOCAL to 
the CURSOR declaration like the example below.

DECLARE Ledger_Cursor CURSOR LOCAL FAST_FORWARD FOR ...

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
 

0
Dan
1/16/2010 4:00:43 PM
Try the temp table again, and put a PK constraint on (ItemCode, StoreCode, 
ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode, DocumentType, 
SeriesCode, DocumentNo, ItemSno) columns in the temp table.  If it errors 
out this means you are generating duplicates, which means your update 
statement is updating some rows two or more times.

If you want to see exactly which rows are being updated twice you can create 
the temp table without the PK constraint and run a query like this:

SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno, 
COUNT(*)
FROM #MyTempTable
GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
HAVING COUNT(*) > 1;

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <microbrain@vsnl.com> wrote in 
message news:uHIfDgplKHA.5520@TK2MSFTNGP06.phx.gbl...
> Dear Sir,
>
> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data 
> Object 2.5.
>
> If I run following query through SQL Server 2005 Management Studio through 
> QUERY only then it works FINE. But if I run from application using Execute 
> method of Command Object of ADO then it process maximum 49 records in 
> CURSOR and query completes without any errors.
>
> I had checked by inserting records in a Temporary Table. If I do Not Use 
> UPDATE statement then it INSERT maximum 65 records in Table and if I use 
> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>
> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>
> Please reply me after proper reading of this query.
>
> Sanjay Shah
>
>
> --- Declare Scalar Variables
> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME, 
> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS 
> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode AS 
> nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15), 
> @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS 
> nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6), 
> @nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS 
> FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3), 
> @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>
> --- Define Cursor with Transaction Ledger
> DECLARE Ledger_Cursor CURSOR FOR
> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode, 
> ItemSno, Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear, 
> RBranchCode, RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, 
> Quantity1, Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM 
> FaItemLedger WHERE DocumentDate >= ('04/01/2003') AND DocumentDate <= 
> ('04/30/2003') ORDER BY DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 
>  > 0 OR Amount > 0 THEN 1 ELSE  2 END ;
>
> --- Open Cursor Rows
> OPEN Ledger_Cursor;
>
> --- Get Values of Cursor Columns into Scalar Variables
> FETCH NEXT FROM Ledger_Cursor INTO
> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode, 
> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo, 
> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode, 
> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer, 
> @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>
> -- Perform till Fatch Status is 0 means (Not EOF)
> WHILE @@FETCH_STATUS = 0
>    BEGIN
>        --- Initialise RDocumentNo if Valuation is Not Bill Wise
>        IF @cValuation NOT IN ('B','P')
>            BEGIN
>                SET @nRDocumentYear = 0;
>                SET @cRBranchCode = '';
>                SET @cRDocumentType = '';
>                SET @cRSeriesCode = '';
>                SET @cRDocumentNo = '';
>                SET @nRItemSno = 0;
>            END
>
>        --- Update Stock Master with Values of Scalar Variables
>        UPDATE FaStockMaster SET
>               FaStockMaster.StockQuantity = 
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>               FaStockMaster.StockQuantity1 = 
> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>               FaStockMaster.Used = 'True',
>               FaStockMaster.Month1TotalQuantity = 
> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>               FaStockMaster.Month1TotalQuantity1 = 
> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>               FaStockMaster.Rate = CASE WHEN 
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  > 0 AND CASE WHEN 
> @cDocumentType IN ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') 
> AND @nQuantity > 0 AND @cValuation IN ('W','F') THEN 
> Round(FaStockMaster.StockValue + @nAmount * 1, 2) ELSE 
> Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE WHEN 
> @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>  > 0 AND @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity 
>  > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
> END , 2) END > 0 AND @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
> AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
> AND @cVa
> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1, 
> 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * 
> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>  > 0 AND @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity 
>  > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
> END , 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * 
> CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND 
> FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END , 6) 
> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>  > 0 AND @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate 
> / CASE WHEN @cValuation IN ('B','P') AND
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
> FaStockMaster.Per END  * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate END 
> ,
>               FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND 
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
> FaStockMaster.Per END ,
>               FaStockMaster.StockValue = CASE WHEN @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0 
> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + 
> @nAmount * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity 
> * 1, 3)  * CASE WHEN @cValuation IN ('L','B','P','A','N') AND 
> (@dDocumentDate >= FaStockMaster.LastReceiptDate OR @cValuation IN 
> ('B','P')) AND @nQuantity > 0 AND @cDocumentType IN 
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity 
>  > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
> END , 2) END,
>               FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P') 
> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>               FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN 
> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE 
> FaStockMaster.TaxableAmount END ,
>               FaStockMaster.TaxAmount = CASE WHEN @cValuation IN ('B','P') 
> AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
>               FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN 
> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity > 
> 0 AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate 
> ELSE FaStockMaster.LastReceiptDate END
>               WHERE
>               FaStockMaster.ItemCode = @cItemCode AND
>               FaStockMaster.StoreCode = @cStoreCode AND
>               FaStockMaster.ColorCode = @cColorCode AND
>               FaStockMaster.SizeCode = @cSizeCode AND
>               FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>               FaStockMaster.DocumentYear = @nRDocumentYear AND
>               FaStockMaster.BranchCode = @cRBranchCode AND
>               FaStockMaster.DocumentType = @cRDocumentType AND
>               FaStockMaster.SeriesCode = @cRSeriesCode AND
>               FaStockMaster.DocumentNo = @cRDocumentNo AND
>               FaStockMaster.ItemSno = @nRItemSno ;
>
>        --- Get Next Values of Cursor Columns into Scalar Variables
>        FETCH NEXT FROM Ledger_Cursor INTO
>        @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, 
> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode, 
> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType, 
> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, 
> @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>    END
>
> --- Close Cursor
> CLOSE Ledger_Cursor;
>
> --- Release Cursor
> DEALLOCATE Ledger_Cursor;
>
> --- *** End of Procedure
>
> 

0
Michael
1/16/2010 4:15:45 PM
Hugo did interesting tests and concluded using STATIC achieves best performance:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/16/2010 4:16:00 PM
Plamen Ratchev wrote:
> 
> Hugo did interesting tests and concluded using STATIC achieves best performance:
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
> 
> --
> Plamen Ratchev
> http://www.SQLStudio.com

I guess that both STATIC and FAST_FORWARD are good choices.

You may also have seen the final note of Hugo himself in the comments at
the end, that says "I found that there are cases where the FAST_FORWARD
option is faster than the STATIC option", which conforms with the
"wisdom" about cursors from 2000 to 2009.

-- 
Gert-Jan
0
Gert
1/16/2010 4:37:26 PM
Yes, and in his last comment Hugo explains:

"In situations where all the data to be processed by the cursor fits into the cache, STATIC always wins. In cases where 
the amount of data is way too large to fit into cache, FAST_FORWARD has the edge."

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/16/2010 4:46:20 PM
Michael Coles (admin@geocodenet.com) writes:
> Try the temp table again, and put a PK constraint on (ItemCode,
> StoreCode, ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode,
> DocumentType, SeriesCode, DocumentNo, ItemSno) columns in the temp
> table.  If it errors out this means you are generating duplicates, which
> means your update statement is updating some rows two or more times. 
> 
> If you want to see exactly which rows are being updated twice you can
> create the temp table without the PK constraint and run a query like
> this: 
> 
> SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno, 
> COUNT(*)
> FROM #MyTempTable
> GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
> HAVING COUNT(*) > 1;
 
He could also use the OUTPUT clause of the UPDATE statements to see
which rows he updates multiple times.

But what he really should to is to scrap the cursor entirely, and write
the whole thing as a single UPDATE statement.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/16/2010 8:01:42 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D02D5EB65A48Yazorman@127.0.0.1...
>
> He could also use the OUTPUT clause of the UPDATE statements to see
> which rows he updates multiple times.
>
> But what he really should to is to scrap the cursor entirely, and write
> the whole thing as a single UPDATE statement.
>

True, but after spending 10 mins trying to edit his query to make it 
readable I scrapped the idea and went hunting through it for a primary key 
:)

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

0
Michael
1/17/2010 5:32:12 AM
Hi Gert-Jan,

I Thank you very much for your reply & suggestion.

Yes, I know that Only UPDATE statement can do this. But did you know that 
UPDATE Statement will not update one record month than once in one UPDATE 
statement ?

Means if Transaction Table (FaItemLedger) is having more than one record of 
master then it will update only first record. (i.e. In transaction there 
more than one record for item code 101 then it will update only first 
record.)

Sanjay Shah

"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B51B3C1.CA367EC3@xs4all.nl...
> Sanjay,
>
> There are three things I'd like to note. Feel free to ignore whatever
> doesn't help you:
>
> 1. You are using an unsafe date format in the cursor definition. The
> interpretation of ('04/01/2003') depends entirely on the connection
> settings at the time you run the query.
>
> 2. You might consider specifying the cursor with as FAST_FORWARD, since
> it is not your intension to change the table that is used in the cursor,
> and you only navigate the cursor with FETCH NEXT.
>
> 3. There is no need for a cursor. You can write one UPDATE statement to
> do all this.
>
> -- 
> Gert-Jan
>
>
>
> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." wrote:
>>
>> Dear Sir,
>>
>> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data
>> Object 2.5.
>>
>> If I run following query through SQL Server 2005 Management Studio 
>> through
>> QUERY only then it works FINE. But if I run from application using 
>> Execute
>> method of Command Object of ADO then it process maximum 49 records in 
>> CURSOR
>> and query completes without any errors.
>>
>> I had checked by inserting records in a Temporary Table. If I do Not Use
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>>
>> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>>
>> Please reply me after proper reading of this query.
>>
>> Sanjay Shah
>>
>> --- Declare Scalar Variables
>> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME,
>> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS
>> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode 
>> AS
>> nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15),
>> @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS
>> nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6),
>> @nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS
>> FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3),
>> @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>>
>> --- Define Cursor with Transaction Ledger
>> DECLARE Ledger_Cursor CURSOR FOR
>> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode, 
>> ItemSno,
>> Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear, 
>> RBranchCode,
>> RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, Quantity1,
>> Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM FaItemLedger 
>> WHERE
>> DocumentDate >= ('04/01/2003') AND DocumentDate <= ('04/30/2003') ORDER 
>> BY
>> DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 > 0 OR Amount > 0 THEN 
>> 1
>> ELSE  2 END ;
>>
>> --- Open Cursor Rows
>> OPEN Ledger_Cursor;
>>
>> --- Get Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode,
>> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo,
>> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode,
>> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer, 
>> @nAmount,
>> @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>
>> -- Perform till Fatch Status is 0 means (Not EOF)
>> WHILE @@FETCH_STATUS = 0
>>     BEGIN
>>         --- Initialise RDocumentNo if Valuation is Not Bill Wise
>>         IF @cValuation NOT IN ('B','P')
>>             BEGIN
>>                 SET @nRDocumentYear = 0;
>>                 SET @cRBranchCode = '';
>>                 SET @cRDocumentType = '';
>>                 SET @cRSeriesCode = '';
>>                 SET @cRDocumentNo = '';
>>                 SET @nRItemSno = 0;
>>             END
>>
>>         --- Update Stock Master with Values of Scalar Variables
>>         UPDATE FaStockMaster SET
>>                FaStockMaster.StockQuantity =
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>>                FaStockMaster.StockQuantity1 =
>> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>>                FaStockMaster.Used = 'True',
>>                FaStockMaster.Month1TotalQuantity =
>> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>>                FaStockMaster.Month1TotalQuantity1 =
>> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>>                FaStockMaster.Rate = CASE WHEN
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  > 0 AND CASE
>> WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0
>> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + 
>> @nAmount
>> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) 
>> *
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
>> END
>> , 2) END > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0
>> AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0
>> AND @cVa
>> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1, 
>> 2)
>> ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE
>> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
>> END
>> , 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE
>> WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND
>> FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END , 
>> 6)
>> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate 
>> /
>> CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END  * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate 
>> END ,
>>                FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END ,
>>                FaStockMaster.StockValue = CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0
>> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + 
>> @nAmount
>> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) 
>> *
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
>> END
>> , 2) END,
>>                FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P')
>> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>>                FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN
>> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE
>> FaStockMaster.TaxableAmount END ,
>>                FaStockMaster.TaxAmount = CASE WHEN @cValuation IN 
>> ('B','P')
>> AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
>>                FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType 
>> IN
>> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity > 
>> 0
>> AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate 
>> ELSE
>> FaStockMaster.LastReceiptDate END
>>                WHERE
>>                FaStockMaster.ItemCode = @cItemCode AND
>>                FaStockMaster.StoreCode = @cStoreCode AND
>>                FaStockMaster.ColorCode = @cColorCode AND
>>                FaStockMaster.SizeCode = @cSizeCode AND
>>                FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>>                FaStockMaster.DocumentYear = @nRDocumentYear AND
>>                FaStockMaster.BranchCode = @cRBranchCode AND
>>                FaStockMaster.DocumentType = @cRDocumentType AND
>>                FaStockMaster.SeriesCode = @cRSeriesCode AND
>>                FaStockMaster.DocumentNo = @cRDocumentNo AND
>>                FaStockMaster.ItemSno = @nRItemSno ;
>>
>>         --- Get Next Values of Cursor Columns into Scalar Variables
>>         FETCH NEXT FROM Ledger_Cursor INTO
>>         @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode,
>> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode,
>> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType,
>> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, 
>> @nRate,
>> @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>     END
>>
>> --- Close Cursor
>> CLOSE Ledger_Cursor;
>>
>> --- Release Cursor
>> DEALLOCATE Ledger_Cursor;
>>
>> --- *** End of Procedure 


0
SANJAY
1/19/2010 12:52:46 PM
Hi Fred,

Which property ?

Sanjay Shah

"Fred" <foleide@free.fr.invalid> wrote in message 
news:eOUS5sqlKHA.2132@TK2MSFTNGP05.phx.gbl...
> "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> a �crit dans le 
> message de groupe de discussion : 4B51B3C1.CA367EC3@xs4all.nl...
>> Sanjay,
>>
>> There are three things I'd like to note. Feel free to ignore whatever
>> doesn't help you:
>>
>> 1. You are using an unsafe date format in the cursor definition. The
>> interpretation of ('04/01/2003') depends entirely on the connection
>> settings at the time you run the query.
>>
>> 2. You might consider specifying the cursor with as FAST_FORWARD, since
>> it is not your intension to change the table that is used in the cursor,
>> and you only navigate the cursor with FETCH NEXT.
>>
>> 3. There is no need for a cursor. You can write one UPDATE statement to
>> do all this.
>
> And 4 : Perhaps a time out on VB code side. Check the command object 
> properties.
>
> -- 
> Fred
> foleide@free.fr 


0
SANJAY
1/19/2010 12:53:57 PM
Hi Dan,

I tried all parameters of Cursor Object.

Sanjay Shah

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message 
news:AD7EA5E3-4737-4BDB-908F-C98BB29A645E@microsoft.com...
>> I had checked by inserting records in a Temporary Table. If I do Not Use 
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use 
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>
> Add SET NOCOUNT ON to the beginning of the script.  This is a Best 
> Practice for ADO applications because it will suppress the DONE_IN_PROC 
> messages (rowcounts) that can interfere expected behavior with the ADO 
> API. Alternatively, you can invoke the ADODB.Command MoveNext and 
> NextRecordset methods from within nested loops in your application code to 
> consume all the resultsets.
>
> Also, to add on to Gert-Jan's recommendations, I suggest you add LOCAL to 
> the CURSOR declaration like the example below.
>
> DECLARE Ledger_Cursor CURSOR LOCAL FAST_FORWARD FOR ...
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> 


0
SANJAY
1/19/2010 12:55:42 PM
Hi Gert,

This is not problem of STATIC and FAST_FORWARD. Because I had tried all 
parameters.

This might be a problem of provider.

Sanjay Shah

"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B51EB46.21E53262@xs4all.nl...
> Plamen Ratchev wrote:
>>
>> Hugo did interesting tests and concluded using STATIC achieves best 
>> performance:
>> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
>>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com
>
> I guess that both STATIC and FAST_FORWARD are good choices.
>
> You may also have seen the final note of Hugo himself in the comments at
> the end, that says "I found that there are cases where the FAST_FORWARD
> option is faster than the STATIC option", which conforms with the
> "wisdom" about cursors from 2000 to 2009.
>
> -- 
> Gert-Jan 


0
SANJAY
1/19/2010 12:56:54 PM
Hi Michael,

I am not updating one record more than one in one UPDATE statement. 
Therefore I am using CURSOR.

Thanks,

Sanjay Shah

"Michael Coles" <admin@geocodenet.com> wrote in message 
news:D5BF8CE8-74A7-4279-BCAA-544402C3F06F@microsoft.com...
> Try the temp table again, and put a PK constraint on (ItemCode, StoreCode, 
> ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode, 
> DocumentType, SeriesCode, DocumentNo, ItemSno) columns in the temp table. 
> If it errors out this means you are generating duplicates, which means 
> your update statement is updating some rows two or more times.
>
> If you want to see exactly which rows are being updated twice you can 
> create the temp table without the PK constraint and run a query like this:
>
> SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno, 
> COUNT(*)
> FROM #MyTempTable
> GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo, 
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
> HAVING COUNT(*) > 1;
>
> -- 
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption" 
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <microbrain@vsnl.com> wrote 
> in message news:uHIfDgplKHA.5520@TK2MSFTNGP06.phx.gbl...
>> Dear Sir,
>>
>> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data 
>> Object 2.5.
>>
>> If I run following query through SQL Server 2005 Management Studio 
>> through QUERY only then it works FINE. But if I run from application 
>> using Execute method of Command Object of ADO then it process maximum 49 
>> records in CURSOR and query completes without any errors.
>>
>> I had checked by inserting records in a Temporary Table. If I do Not Use 
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use 
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>>
>> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>>
>> Please reply me after proper reading of this query.
>>
>> Sanjay Shah
>>
>>
>> --- Declare Scalar Variables
>> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME, 
>> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS 
>> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode 
>> AS nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS 
>> nVarChar(15), @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), 
>> @cRDocumentType AS nVarChar(3), @cRSeriesCode AS nVarChar(4), 
>> @cRDocumentNo AS nVarChar(6), @nRItemSno AS INT, @nQuantity AS FLOAT, 
>> @nQuantity1 AS FLOAT, @nRate AS FLOAT, @nPer AS INT, @nAmount AS FLOAT, 
>> @cTaxCode AS nVarChar(3), @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>>
>> --- Define Cursor with Transaction Ledger
>> DECLARE Ledger_Cursor CURSOR FOR
>> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode, 
>> ItemSno, Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear, 
>> RBranchCode, RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, 
>> Quantity1, Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM 
>> FaItemLedger WHERE DocumentDate >= ('04/01/2003') AND DocumentDate <= 
>> ('04/30/2003') ORDER BY DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 
>>  > 0 OR Amount > 0 THEN 1 ELSE  2 END ;
>>
>> --- Open Cursor Rows
>> OPEN Ledger_Cursor;
>>
>> --- Get Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode, 
>> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo, 
>> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode, 
>> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer, 
>> @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>
>> -- Perform till Fatch Status is 0 means (Not EOF)
>> WHILE @@FETCH_STATUS = 0
>>    BEGIN
>>        --- Initialise RDocumentNo if Valuation is Not Bill Wise
>>        IF @cValuation NOT IN ('B','P')
>>            BEGIN
>>                SET @nRDocumentYear = 0;
>>                SET @cRBranchCode = '';
>>                SET @cRDocumentType = '';
>>                SET @cRSeriesCode = '';
>>                SET @cRDocumentNo = '';
>>                SET @nRItemSno = 0;
>>            END
>>
>>        --- Update Stock Master with Values of Scalar Variables
>>        UPDATE FaStockMaster SET
>>               FaStockMaster.StockQuantity = 
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>>               FaStockMaster.StockQuantity1 = 
>> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>>               FaStockMaster.Used = 'True',
>>               FaStockMaster.Month1TotalQuantity = 
>> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>>               FaStockMaster.Month1TotalQuantity1 = 
>> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>>               FaStockMaster.Rate = CASE WHEN 
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  > 0 AND CASE WHEN 
>> @cDocumentType IN ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') 
>> AND @nQuantity > 0 AND @cValuation IN ('W','F') THEN 
>> Round(FaStockMaster.StockValue + @nAmount * 1, 2) ELSE 
>> Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * CASE WHEN 
>> @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  > 0 AND @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
>> FaStockMaster.Per END , 2) END > 0 AND @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0 AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0 AND @cVa
>> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1, 
>> 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)  * 
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  > 0 AND @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
>> FaStockMaster.Per END , 2) END / Round(FaStockMaster.StockQuantity + 
>> @nQuantity * 1, 3)  * CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per 
>> END , 6) WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >= 
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity 
>>  > 0 AND @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate 
>> / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
>> FaStockMaster.Per END  * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate 
>> END ,
>>               FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
>> FaStockMaster.Per END ,
>>               FaStockMaster.StockValue = CASE WHEN @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 
>> 0 AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue + 
>> @nAmount * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + 
>> @nQuantity * 1, 3)  * CASE WHEN @cValuation IN ('L','B','P','A','N') AND 
>> (@dDocumentDate >= FaStockMaster.LastReceiptDate OR @cValuation IN 
>> ('B','P')) AND @nQuantity > 0 AND @cDocumentType IN 
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE 
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND 
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE 
>> FaStockMaster.Per END , 2) END,
>>               FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P') 
>> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>>               FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN 
>> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE 
>> FaStockMaster.TaxableAmount END ,
>>               FaStockMaster.TaxAmount = CASE WHEN @cValuation IN 
>> ('B','P') AND @nQuantity > 0 THEN @nTaxAmount ELSE 
>> FaStockMaster.TaxAmount END ,
>>               FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN 
>> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity > 
>> 0 AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate 
>> ELSE FaStockMaster.LastReceiptDate END
>>               WHERE
>>               FaStockMaster.ItemCode = @cItemCode AND
>>               FaStockMaster.StoreCode = @cStoreCode AND
>>               FaStockMaster.ColorCode = @cColorCode AND
>>               FaStockMaster.SizeCode = @cSizeCode AND
>>               FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>>               FaStockMaster.DocumentYear = @nRDocumentYear AND
>>               FaStockMaster.BranchCode = @cRBranchCode AND
>>               FaStockMaster.DocumentType = @cRDocumentType AND
>>               FaStockMaster.SeriesCode = @cRSeriesCode AND
>>               FaStockMaster.DocumentNo = @cRDocumentNo AND
>>               FaStockMaster.ItemSno = @nRItemSno ;
>>
>>        --- Get Next Values of Cursor Columns into Scalar Variables
>>        FETCH NEXT FROM Ledger_Cursor INTO
>>        @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, 
>> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode, 
>> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType, 
>> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, 
>> @nRate, @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>    END
>>
>> --- Close Cursor
>> CLOSE Ledger_Cursor;
>>
>> --- Release Cursor
>> DEALLOCATE Ledger_Cursor;
>>
>> --- *** End of Procedure
>>
>>
> 


0
SANJAY
1/19/2010 12:58:37 PM
Hi Erland,

The Cursor doen't take more than 45 rows.

Sanjay Shah

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D02D5EB65A48Yazorman@127.0.0.1...
> Michael Coles (admin@geocodenet.com) writes:
>> Try the temp table again, and put a PK constraint on (ItemCode,
>> StoreCode, ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode,
>> DocumentType, SeriesCode, DocumentNo, ItemSno) columns in the temp
>> table.  If it errors out this means you are generating duplicates, which
>> means your update statement is updating some rows two or more times.
>>
>> If you want to see exactly which rows are being updated twice you can
>> create the temp table without the PK constraint and run a query like
>> this:
>>
>> SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
>> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno,
>> COUNT(*)
>> FROM #MyTempTable
>> GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
>> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
>> HAVING COUNT(*) > 1;
>
> He could also use the OUTPUT clause of the UPDATE statements to see
> which rows he updates multiple times.
>
> But what he really should to is to scrap the cursor entirely, and write
> the whole thing as a single UPDATE statement.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
SANJAY
1/19/2010 12:59:32 PM
Maybe a problem with your datetime-format.

This is a way to avoid datetime format problems:

- put your code in a Stored Procedure.
- use parameters from type DATETIME to tell the SP the dates to compare

CREATE PROCEDURE schema.YourTestProcedure
(
  @dtCompare1 DATETIME,
  @dtCompare2 DATETIME,
)
AS
BEGIN
  ...

  RETURN 1;
END

- change your statementsthis way
SELECT ...
WHERE DocumentDate>=@dtCompare1  AND DocumentDate<=@dtCompare2
ORDER BY DocumentDate;

- call the Stored Procedure this way an use an VB6 Datetime datatype

Set myCmd = New ADODB.Command
With myCmd
  .ActiveConnection = YourAdoConnection
  .CommandText = "schema.YourTestProcedure"
  .CommandType = adCmdStoredProc
  .CommandTimeout = 60

  .Parameters.Refresh
  '.Parameters(0) = ""
  .Parameters("@dtCompare1 ") = dtBegin
  .Parameters("@dtCompare2 ") = dtEnd
  .Execute
  lReturn = .Parameters(0).Value
End With
Set myCmd = Nothing


In addition try to use UPDATE-statements instead of cursors.
0
Lutz
1/19/2010 2:41:45 PM
Reply:

Similar Artilces:

encoding problem in Outlook 2007 importing Outlook 2000 personal folders
I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese characters in the subject became unreadable but it's ok to display in message body. In Outlook 2000, there's no problem to display the Chinese characters in the subject. What's wrong with Outlook 2007? What setttings should I change to fix the problem? Thanks. cyl <u8526505@gmail.com> wrote: > I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese > characters in the subject became unreadable but it's ok to display in > message body. In Outlook 2000, there's no problem t...

Problem shortcuts in Word
Hello, I work with Word 2008. In the beginning there is no problem, but suddenly i have problem with shortcuts. When i want to do "cmd c" it make an other shortcuts same thing for "cmd s", "cmd v" and surly some other. But when i write there is no problem "c" is "c"... If somebody have an idea please help me. Thank you. Unless you indicate your specific update level of Office as well as OS X & describe what the keystrokes *are* doing contrary to what you expect there isn't much we can offer. -- Regards |:>)...

Outlook 2003 PST problems
I just installed Office 2003 professional on a new computer & am having problems with outlook. I copied my Outlook.pst file into the appropriate directory & although it is recognized the calendar is not working properly. I cannot attach any of the colored labels to my appointments - so everything is pretty much in black & white. I totally uninstalled & reinstalled office... first removing my pst file... but there appears to be an ini file... or something that is retaining some information... does anyone know of a fix for the labels... or how to TOTALLY uninstall office (so I...

Problem with Authentication
The CRM application was running smooth. But now I can't logging. It ask my credentials and than return a 401.1 error. I had already reinstalled the CRM server but it won't work. I'm accessing it in the same local network. I'm running CRM 3.0 in SBS. Best regards, Erico Hi When you open a Internet explorer and type the name of the CRM server, it prompts for user and password ? Are you logged in with domain credentials ( AD user ) ? If you are, so add url of the crm server in Local intranet. Enable "Automatic logon only in intranet", this is under security,c...

Vista blue screen problem...please help!
I've seen some other threads, here are the dump files: 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' (http://rapidshare.com/files/347103149/Minidump.rar.html) Please, help me out here. -- ceVil It might be better to know at what point the bsod occurs and what the actual err.msg given is "ceVil" <guest@unknown-email.com> wrote in message news:0b9a0c60b90bf6c0be88bda30f2820ab@nntp-gateway.com... > > I've seen some other threads, here are the dump files: > 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' > (...

calendar problem
Dear all, I am using both mail and calendar function in outlook 2002. Everything is ok until recently, when I try to make a new appointment or checking any old appointments, an error message "out of memory or resource, try to close some windows..." pop up. The mail function is still ok. Can anyone tell me how can I solve this? I have already installed sp2. The version of office is the professional one with frontpage. Thanks. tp ...

problem #4
Hi, I am facing this new problem... i.e a user has installed Office 2003 on win2k prof platform.. whenever she is working on a outlook.. she gets a Microsoft error message... saying to send the report or don't send the report... Usually I have seen this problem in IE... But for outlook it is new to me... after clicking on send or don't send report.. the outlook closes. And there are no IE open..... and if atall it is open.. the IE does not close... Need your help regards, KAH What is logged to the Event Viewer regarding this? Try one of the following already; http://www.howt...

95 and 97 problem
For some unknown reason there is Excel 95 and 97 on this pc.When i double click on an Excel file, the pc defaults to the 95 program (Which incidently doesn't work properly). So my question is how do i get the pc to default to 97. Cheers Craig Craig, Have a look at Windows Help. From Windows 2000 Help (Index under Programs Associating with File Types) To change which program starts when you open a file In My Computer or Windows Explorer, on the View menu, click Folder Options. Click the File Types tab. In the list of file types, click the one you want to change. Click Edit. In Act...

sync problems
I have all of my info on an HP Ipac. I had to reformat the hard drive and I did not make a back-up file. I am trying to move everything back to the computer but it says there is an error with syncing it. How can I move everything from the PDA back to Outlook again? Please help- thanks ...

Date problem
I can't believe there's no post on this, but I can't find it. I've tried the solutions I could find (see below) and still get the error: "The expression is typed incorrectly or it is too complex to be evaluated. For example....Try simplifying...." I have a date field, formatted as Date/Time, General Date, default Value = Now(). I like having the date and the time - in case we need it. I want to run a report on calls taken just for one day. I have CallDate: CallDateAndTime in my query, with "criteria" as Between ([Forms]![frmReports]![txtStartDate]) A...

Installation Problem!!
I am having a problem installing MSCRM 1.2. My configuration consists of a Domain Controller running Win 2000 Server SP4 . I am trying to install MSCRM on a member of the domain running Windows 2000 Server SP4. There is a DNS Server on the DC which is integrated with AD, and a SQL Server 2000 running on the Win 2K machine. The install errors out when CRM creates the root business with the message: Setup was unable to install Microsoft CRM Server. Setup was unable to provision your organization. Setup was unable to create the root business. The configuration of ASP.NET seems to be ok, the pr...

Office 2007 - HELP
I have used Word for many years and love it - not too thrilled with the changes in Office 2007, but my bigger issue is with Publisher 2007. I have an image that I have used in Word just fine - meaning, the on screen colors are correct and when printed the colors are correct. Someone sent me a Publisher file and asked me to add the image - I did this - now the image is messed up - the colors are correct on screen, but when printed FROM MY computer the image colors are all wrong - but if I transport the file to a different computer it prints fine I have reinstalled Office - no good Any id...

Resource editor problems
Hello all, Under VC++ 7.1... Please consider these two lines: IDC_ARROW_ADD_CANCEL DISCARDABLE "res\\cur00004.cur" IDC_ARROW_ADD_CANCEL CURSOR DISCARDABLE "res\\cur00004.cur" The second is a hand-edited change to the first. Both will compile just fine in the resource compiler. However, after I use a text editor to make this change, I cannot open the .rc file in the resource editor. The resource editor issues the following error open attempting to open the .rc file: error RC2135 : file not found: CURSOR Can anyone shed light on what's going on here? Thanks, Dave ...

Directory Replication Problem #2
Hi, recently my Exchange Server directory database receives changes from other servers but does not send out its own changes. Check from the knowledge base, to correct the USN discrepancy need to use Authrest.exe (need to amke changes for about 100+ users). Does anybody know where to get this file 'Authrest.exe' for exchange server 5.5? Exchange server 5.5 CD only provide this file for ver.4.0. Have anyone use it before? Regards, "Sharon Tan" <sharon_tansk@yahoo.com.sg> wrote: >Hi, recently my Exchange Server directory database >receives changes from ot...

Problems Creating a disclaimer in Exchange 2003
I am trying to create a server based disclaimer that will stamp all of my smtp emails going to the internet. I have been following KB article 317680 with no luck. I get an error like the one below. Binding Display Name Specified: smtpscriptinghost ** Registration Failed ** Err.Number (HRESULT) = 0x1AD Err.Description = ActiveX component can't create object ProgID = cdo.ss_smtponarrival COM Category = {FF3CAA23-00B9-11d2-9DFB-00C04FA322BA} Corresponding Event = onarrival ** Have you registered your sink COM class on this machine? I am puttin...

User Defined Variables in MS query cause a problem to import data
If I use user defined variables in MS query to import data into excel sheet, the query executes but no data gets imported into the excel sheet. If I get rid of the variable the results can be imported into the Excel sheet. ...

Cell Format
I have a spreadsheet with cells that I'm trying to type the date into. I type in the date exactly like this: 09/26/03. When I hit "enter" MS Excel displays "37890" in the cell. No matter what I do I can't the cell to show: 09/26/03. I think that somehow I need to "strip" the cell of what it's original format is, but I can't figure out how. I try to use the Date option in the format cell screen but it doesn't work. Curtis I tried this and it didn't work. I also tried the "Text to columns" feature. No success. >...

outlook web access redirect problem
hi, I have been trying to redirect outlook web access (OWA) from the IIS root folder to the /exchange folder. I have followed the instructions on going into the web site and redirecting to a folder but I just get the following URL when I browse to the root of my webserver : http://webmail/exchange/exchange - it's like it's doubling up somehow. I have it set to : A redirection to a URL redirect to: /exchange client will be sent to: A directory below URL entered I just can't work it out... any ideas... cheers Baronne Which instructions did you follow? I wrote the below articl...

Problem after sorting
In my Excel 2007 workbook I have two sheets The "Master Names" sheet has columns: (A) First|(B) Last (C) First (B) Last (concatenated) The "Selected Names" sheet contains (A) cells which link to selected (C) First Last (concatenated) cells in the Master Names sheet. Everything links and displays fine except when I add names to the bottom of the Master Names sheet and then sort using (B) Last name column. When I do this I get a 0 (zero) in the (A) First Second cells in the Selected names sheet and other cells in this sheet have the wrong name. Obviously I am doin...

IF AND problem
Need to isolate problems on 1600 rows Worksheet Row Dept Hrs. Dollars A5 120500 600 9000 A6 120600 400 8000 A7 130600 240 A8 130400 160 A9 140600 320 6400 A10 140200 100 2000 A11 140600 240 (Found out the dept has 2 leading blanks) Isolate problems on 0600 criteria pointing out when b has value and c = 0. OK if both are 0. A7 and A11 should stand out.. =IF((MID(A1,5,4)="0600")=AND(b1>=0=AND(c1>=0)),"NO","ok") Does this work for you: =IF(RIGHT(A...

Problem SHAppBarMessage
Hi, I am creating an application bar which shall be displayed always at the top of the screen to show the user certain alerts. I am using the commands SHAppBarMessage(ABM_NEW, &abd); SHAppBarMessage(ABM_QUERYPOS, &abd); SHAppBarMessage(ABM_SETPOS, &abd); SetWindowPos(NULL, abd.rc.left, abd.rc.top, abd.rc.right - abd.rc.left, iClientHeight, SWP_NOACTIVATE); Works fine. The problem begins, when I start moving the taskbar. I am catching the event OnSettingChange and my application bar is resizing/moving correctly. BUT: If the taskbar moves to the top and my application bar ...

Lookup() problem
I set up two lists, 200 employees names and 200 employee numbers. A couple of the numbers match the correct names but the rest of the numbers all pull the same employee name. =LOOKUP(C4,List!C4:C204,List!B4:B204) The first time I tried it seemed to pull the correct matching names and numbers then kabui it gets stuck. I tried deleting the entire lists cells and all and starting over but same thing. Anyone ever run into this before? Wayman Don't forget! When using Lookup(), the vector (second argument) must be sorted in ascending order. If it's not, you'll get what you ca...

Problem creating credit card account
When creating a Chase or Amazon.com credit cards I am having this happen to me when it creates the card and I can't figure out how to fix it so there is just one card showing. I attached a jpeg of what I am seeing. It's like it created multiple instances of the card and also show balances of my other cards. I am not sure if it is because these cards are associated with Chase or what. When I had it setup the online service for the card then that is when it creates the other instances. I hope someone can makes sence of this. Thanks! ...

Problems with CRichEditCtrl problems
Hi, I'd like to have some help regarding something I'm having an hard time figuring out. I'm develloping a chat programm where the Output window is a rich edit ctrl (I used CRichEditCtrl in one cases and CreateWindowEx(.."RichEdit"..) in the other one. I use StreamIn to stream in the chat information (since I want to use the rft format). I user \Par to perfrom line skipping, however, for some reason, on the computer of one of my user who's using Windows98, it doesn't not work and instand of seeing the text the way it should be (with the line skipping) the...

OL 2003 registry or installn problem
Outlook 2003 (part of office 2003 suite) opens and forgets password for email accounts. Tried to open tools-email account and I got operation failed due to a registry or installation problem. also can' view Outlook store location, can't open property of folder w/o getting error message tried restart in control panel-mail, can't see any account for any of the profiles. tried restoring form office settings and failed tried repair and no better result what else can I do other than total uninstall / re-install and still keep the app't, tasks, reminders for the m...