Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:59
Hi Erland,

The Cursor doen't take more than 45 rows.

Sanjay Shah

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D02D5EB65A48Yazorman(a)127.0.0.1...
> Michael Coles (admin(a)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(a)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
>


From: Lutz Uhlmann on 19 Jan 2010 09:41
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.
First  |  Prev  | 
Pages: 1 2 3 4
Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host