IS THAT A BUG IN SQL 2000? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

IS THAT A BUG IN SQL 2000?

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2006-08-29 : 00:34:57

Hi Guys,

I have one two cursors in my Stored procedure and some insert queries
Cusror...name .1
Cursor Name 2
----Operations......
End Cursor 2
End Cursor 1

Insert query one
Insert query Two
Insert Query Three

Go

Now problem is when I execute the stored procedure, then it keep in the Cursor loops, it never came out from there. Yes i did checked Cursor Part (in QA) it works fine, then I checked Insert query part, it works fine seperately.

I was pissed off by that, then What I figured out that Cursor Name 1 is causing Conflicts (TMS), I checked TMS as reservered word, but no definition found, When I changed 'TMS' to other 'BKP' it works!!!

I am not sure is that my fault or SQL Bug?

Thanks in Advance.

Rik

SKR

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:12:16

It's hard to tell without looking on the real code.

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-29 : 10:30:56

It is not SQL bug, it is you ehehe, Post the code here for us to help you!

If it is that easy, everybody will be doing it

rikleo2001
Posting Yak Master

185 Posts

Posted - 2006-08-29 : 16:32:43

CREATE PROCEDURE KHUTEST AS

Declare @CompanyID INT
Set @CompanyID = 1

DECLARE @Count INT, @TaskID INT, @bcpCommand NVARCHAR(2000), @SQLCom NVARCHAR(400)
DECLARE @Header NVARCHAR(1), @RoundID NVARCHAR(25), @RoundNote NVARCHAR(305), @TDYear NVARCHAR(4)
DECLARE @TDMonth NVARCHAR(2), @TDDay NVARCHAR(2), @CDYear NVARCHAR(4), @CDMonth NVARCHAR(2)
DECLARE @CDDay NVARCHAR(2), @CloseDate NVARCHAR(8), @PropIdentifier NVARCHAR(25), @SequenceNum INT
DECLARE @PropertyName NVARCHAR(100), @Consumer NVARCHAR(100), @PhoneNum NVARCHAR(25), @StreetAdd1 NVARCHAR(20)
DECLARE @StreetAdd2 NVARCHAR(50), @StreetAdd3 NVARCHAR(50), @StreetAdd4 NVARCHAR(50), @StreetAdd5 NVARCHAR(50)
DECLARE @PostCode NVARCHAR(15), @ClientID INT, @LastReadDate NVARCHAR(20), @ErrNum INT
DECLARE @PropID INT, @MeterNum NVARCHAR(40), @RegisterNum INT, @MeterSeq INT
DECLARE @RegisterSeq INT, @AssetNum NVARCHAR(40), @MeterInscript NVARCHAR(40), @Tariff NVARCHAR(15)
DECLARE @LowLow NVARCHAR(20), @Low NVARCHAR(20), @LastRead NVARCHAR(20), @Multiplier NVARCHAR(10)
DECLARE @High NVARCHAR(20), @HighHigh NVARCHAR(20), @Average NVARCHAR(20), @MeterType INT
DECLARE @TaskType INT, @ScenarioCode INT, @Note NVARCHAR(255), @RoundAdd NVARCHAR(25)
DECLARE @KeyLoc NVARCHAR(50), @TMSID INT, @ExportType NVARCHAR(30), @ExportPath NVARCHAR(100)
DECLARE @FileScheme NVARCHAR(50), @FileSuffix NVARCHAR(30), @SuffixValue NVARCHAR(30), @FileExtension NVARCHAR(4)
DECLARE @FileName NVARCHAR(50), @JobID INT, @activityID INT, @actDescript NVARCHAR(255)
DECLARE @RecordsInserted INT, @NotesInserted INT, @PropCount INT, @Round INT
DECLARE @TMSName NVARCHAR(50), @ZoneDesc NVARCHAR(50), @Err INT, @RoundGrouping NVARCHAR(10)
DECLARE @LenRound INT, @OrigRound NVARCHAR(25), @RoundArrList NVARCHAR(2000), @timeStamp datetime

Declare @ExportAct nvarchar(50)
Declare @from varchar(100)
Declare @address nvarchar(100)
Declare @subject nvarchar(100)
Declare @message nvarchar(3900)
Declare @attachment varchar(255)

Set @message = ''

SET @ErrNum = 0

--Print 'mtrS_051_NextReadDateCalc'
--Exec mtrS_051_NextReadDateCalc @CompanyID

DECLARE TMS CURSOR FOR SELECT DISTINCT mtr_TMSystems.TMS_ID, mtr_TMSystems.Export_Type, mtr_TMSystems.TMS_Export_Path, mtr_TMSystems.TMS_ExportFileScheme,
mtr_TMSystems.TMS_ExportFileSuffix, mtr_TMSystems.TMS_ExportFileSuffixValue, mtr_TMSystems.TMS_ExportFileExtension, mtr_TMSystems.TMS_Name
FROM mtr_Task_Types INNER JOIN mtr_Tasks ON mtr_Task_Types.Task_Type_ID = mtr_Tasks.Task_Type_ID_FK INNER JOIN mtr_TMSystems ON mtr_Task_Types.Target_TMS_ID_FK = mtr_TMSystems.TMS_ID
WHERE (mtr_TMSystems.Company_ID_FK = @CompanyID) AND (mtr_Tasks.Status_ID_FK = 2)

Set @RoundArrList = ','

OPEN TMS
FETCH NEXT FROM TMS INTO @TMSID, @ExportType, @ExportPath, @FileScheme, @FileSuffix, @SuffixValue, @FileExtension, @TMSName

WHILE @@FETCH_STATUS = 0
BEGIN
-- BEGIN TRAN LogResultTrans
-- Log the Activity Event
SET @actDescript = 'Task Export for ' + @TMSName
EXEC @activityID = mtrS_030_logMitreActivity 1, 4, @actDescript, 0
-- COMMIT TRAN LogResultTrans

-- BEGIN TRAN TMS
DELETE FROM mtr_TMSExportHoldingData

-- Set up cusor for all rounds needing to be included in Export File
DECLARE RoundsToExport CURSOR FOR SELECT DISTINCT 'R', Company_Round + cnt_Client_Tasks.Client_Task_Round_Code, cnt_Client_Rounds.Client_Round_Note, cnt_Client_Rounds.Key_Location, CONVERT(NVARCHAR(4), YEAR(mtr_Job.Target_Date)), CONVERT(NVARCHAR(2), MONTH(mtr_Job.Target_Date)), CONVERT(NVARCHAR(2), DAY(mtr_Job.Target_Date)),
CONVERT(NVARCHAR(4), YEAR(DATEADD(d, cnt_Client_Tasks.Read_Window_Closes, mtr_Job.Target_Date))), CONVERT(NVARCHAR(2), MONTH(DATEADD(d, cnt_Client_Tasks.Read_Window_Closes, mtr_Job.Target_Date))), CONVERT(NVARCHAR(2), DAY(DATEADD(d, cnt_Client_Tasks.Read_Window_Closes, mtr_Job.Target_Date))), Job_ID, Client_Round_ID, Zone_Description, cnt_Client_Tasks.Round_Grouping
FROM mtr_TMSystems INNER JOIN mtr_Task_Types INNER JOIN mtr_Tasks ON mtr_Task_Types.Task_Type_ID = mtr_Tasks.Task_Type_ID_FK INNER JOIN mtr_Meters INNER JOIN mtr_Property ON mtr_Meters.Prop_ID_FK = mtr_Property.Prop_ID INNER JOIN
mtr_Registers ON mtr_Meters.Meter_ID = mtr_Registers.Meter_ID_FK ON mtr_Tasks.Parent_ID_FK = mtr_Registers.Register_ID ON mtr_TMSystems.TMS_ID = mtr_Task_Types.Target_TMS_ID_FK INNER JOIN mtr_Job ON mtr_Tasks.Job_ID_FK = mtr_Job.Job_ID INNER JOIN
cnt_Client_Rounds ON mtr_Job.Client_Round_ID_FK = cnt_Client_Rounds.Client_Round_ID INNER JOIN cnt_Client_Tasks ON mtr_Property.Client_ID_FK = cnt_Client_Tasks.Client_ID_FK AND
mtr_Task_Types.Task_Type_ID = cnt_Client_Tasks.Task_Type_ID_FK AND (mtr_Job.Transfer_Date <= GETDATE()) INNER JOIN mtr_Company_Rounds ON cnt_Client_Rounds.Company_Round_ID_FK = mtr_Company_Rounds.Company_Round_id INNER JOIN
cnt_Zones ON cnt_Client_Rounds.Client_Zone_ID_FK = cnt_Zones.Client_Zone_ID INNER JOIN mtr_Zones ON cnt_Zones.Zone_ID_FK = mtr_Zones.Zone_ID
WHERE (mtr_Task_Types.Task_Target = N'Register') AND (mtr_Tasks.Status_ID_FK = 2) AND (mtr_Property.Status_ID_FK <> 1 AND mtr_Property.Status_ID_FK <> 2 AND mtr_Property.Status_ID_FK <> 6) AND (mtr_Meters.Active = 1) AND Target_TMS_ID_FK = @TMSID

OPEN RoundsToExport
FETCH NEXT FROM RoundsToExport INTO @Header, @RoundID, @RoundNote, @KeyLoc, @TDYear, @TDMonth, @TDDay, @CDYear, @CDMonth, @CDDay, @JobID, @Round, @ZoneDesc, @RoundGrouping

WHILE @@FETCH_STATUS = 0
BEGIN
--Reset Property, Task and Note counts
SET @PropCount = 0
SET @RecordsInserted = 0
SET @NotesInserted = 0

SET @LenRound = LEN(@RoundID)
SET @OrigRound = @RoundID

IF @RoundGrouping = '4'
BEGIN
SET @RoundID = LEFT(@RoundID, 4) + '========' + RIGHT(@RoundID,1)
SET @RoundID = LEFT(@RoundID, @LenRound-1) + RIGHT(@RoundID,1)
END
ELSE IF @RoundGrouping = '3'
BEGIN
SET @RoundID = LEFT(@RoundID, 3) + '=========' + RIGHT(@RoundID,1)
SET @RoundID = LEFT(@RoundID, @LenRound-1) + RIGHT(@RoundID,1)
END
ELSE IF @RoundGrouping = '2'
BEGIN
SET @RoundID = LEFT(@RoundID, 2) + '==========' + RIGHT(@RoundID,1)
SET @RoundID = LEFT(@RoundID, @LenRound-1) + RIGHT(@RoundID,1)
END
ELSE IF @RoundGrouping = '1'
BEGIN
SET @RoundID = LEFT(@RoundID, 1) + '===========' + RIGHT(@RoundID,1)
SET @RoundID = LEFT(@RoundID, @LenRound-1) + RIGHT(@RoundID,1)
END

IF LTRIM(RTRIM(@RoundNote)) IS NOT NULL AND LTRIM(RTRIM(@RoundNote)) <> ''
BEGIN
SET @ZoneDesc = @ZoneDesc + '. ' + @RoundNote
END

IF LTRIM(RTRIM(@KeyLoc)) IS NOT NULL AND LTRIM(RTRIM(@KeyLoc)) <> ''
BEGIN
SET @ZoneDesc = @ZoneDesc + '. Keys: ' + @KeyLoc
END

--Set up correct Close date format
IF LEN(@CDDay) = 2
BEGIN
IF LEN(@CDMonth) = 2
BEGIN
SET @CloseDate = @CDYear + @CDMonth + @CDDay
END
ELSE
BEGIN
SET @CloseDate = @CDYear + '0' + @CDMonth + @CDDay
END
END
ELSE
BEGIN
IF LEN(@CDMonth) = 2
BEGIN
SET @CloseDate = @CDYear + @CDMonth + '0' + @CDDay
END
ELSE
BEGIN
SET @CloseDate = @CDYear + '0' + @CDMonth + '0' + @CDDay
END
END

--Set up correct target date format and insert round record into table
IF LEN(@TDDay) = 2
BEGIN
IF LEN(@TDMonth) = 2
BEGIN
SET @RoundAdd = @RoundID + @TDDay
IF CHARINDEX(@RoundAdd, @RoundArrList) = 0
BEGIN
SET @RoundArrList = @RoundArrList + @RoundAdd + ','
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field5)
VALUES (@TMSID, @Header + @RoundID + @TDDay, @ZoneDesc, CONVERT(NVARCHAR(8), @TDYear + @TDMonth + @TDDay), @CloseDate)
SELECT @Err = @@ERROR
IF @Err <> 0 SET @ErrNum = @Err
END
END
ELSE
BEGIN
SET @RoundAdd = @RoundID + @TDDay
IF CHARINDEX(@RoundAdd, @RoundArrList) = 0
BEGIN
SET @RoundArrList = @RoundArrList + @RoundAdd + ','
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field5)
VALUES (@TMSID, @Header + @RoundID + @TDDay, @ZoneDesc, CONVERT(NVARCHAR(8), @TDYear + '0' + @TDMonth + @TDDay), @CloseDate)
SELECT @Err = @@ERROR
IF @Err <> 0 SET @ErrNum = @Err
END
END
END
ELSE
BEGIN
IF LEN(@TDMonth) = 2
BEGIN
SET @RoundAdd = @RoundID + '0' + @TDDay
IF CHARINDEX(@RoundAdd, @RoundArrList) = 0
BEGIN
SET @RoundArrList = @RoundArrList + @RoundAdd + ','
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field5)
VALUES (@TMSID, @Header + @RoundID + '0' + @TDDay, @ZoneDesc, CONVERT(NVARCHAR(8), @TDYear +@TDMonth + '0' + @TDDay), @CloseDate)
SELECT @Err = @@ERROR
IF @Err <> 0 SET @ErrNum = @Err
END
END
ELSE
BEGIN
SET @RoundAdd = @RoundID + '0' + @TDDay
IF CHARINDEX(@RoundAdd, @RoundArrList) = 0
BEGIN
SET @RoundArrList = @RoundArrList + @RoundAdd + ','
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field5)
VALUES (@TMSID, @Header + @RoundID + '0' + @TDDay, @ZoneDesc, CONVERT(NVARCHAR(8), @TDYear + '0' + @TDMonth + '0' + @TDDay), @CloseDate)
SELECT @Err = @@ERROR
IF @Err <> 0 SET @ErrNum = @Err
END
END
END

SET @PropCount = 0

INSERT INTO tmp_RndsToExp(TMS_ID,Job_ID_FK,Client_Round_ID_FK,Add_Round,Orig_Round)
VALUES(@TMSID,@JobID,@Round,@RoundAdd,@OrigRound)

FETCH NEXT FROM RoundsToExport INTO @Header, @RoundID, @RoundNote, @KeyLoc, @TDYear, @TDMonth, @TDDay, @CDYear, @CDMonth, @CDDay, @JobID, @Round, @ZoneDesc,@RoundGrouping
END

CLOSE RoundsToExport
DEALLOCATE RoundsToExport

FETCH NEXT FROM TMS INTO @TMSID, @ExportType, @ExportPath, @FileScheme, @FileSuffix, @SuffixValue, @FileExtension, @TMSName
END

CLOSE TMS
DEALLOCATE TMS

--Insert Queries

INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14)
SELECT dbo.tmp_RndsToExp.TMS_ID, 'A' + dbo.mtr_Property.Prop_Identifier, dbo.tmp_RndsToExp.Add_Round, dbo.mtr_Property.Sequence_Num,
dbo.mtr_Property.Property_Name, dbo.mtr_Property.Consumer, dbo.mtr_Property.Phone_Num, Case When mtr_Property.Street_Add_1= '' OR mtr_Property.Street_Add_1 Is Null Then mtr_Property.Street_Add_2 Else mtr_Property.Street_Add_1+'/'+mtr_Property.Street_Add_2 End,
dbo.mtr_Property.Street_Add_3, dbo.mtr_Property.Street_Add_4, dbo.mtr_Property.Street_Add_5, dbo.mtr_Property.Postcode, dbo.transfer_Clients.UMD_Index, '0',
CONVERT(char(8), MAX(dbo.mtr_Registers.Last_Read_Date), 112)
FROM dbo.mtr_TMSystems INNER JOIN
dbo.mtr_Task_Types INNER JOIN
dbo.mtr_Tasks ON dbo.mtr_Task_Types.Task_Type_ID = dbo.mtr_Tasks.Task_Type_ID_FK INNER JOIN
dbo.mtr_Meters INNER JOIN
dbo.mtr_Property ON dbo.mtr_Meters.Prop_ID_FK = dbo.mtr_Property.Prop_ID INNER JOIN
dbo.mtr_Registers ON dbo.mtr_Meters.Meter_ID = dbo.mtr_Registers.Meter_ID_FK ON
dbo.mtr_Tasks.Parent_ID_FK = dbo.mtr_Registers.Register_ID ON dbo.mtr_TMSystems.TMS_ID = dbo.mtr_Task_Types.Target_TMS_ID_FK INNER JOIN
dbo.mtr_Job ON dbo.mtr_Tasks.Job_ID_FK = dbo.mtr_Job.Job_ID INNER JOIN
dbo.cnt_Client_Rounds ON dbo.mtr_Job.Client_Round_ID_FK = dbo.cnt_Client_Rounds.Client_Round_ID INNER JOIN
dbo.cnt_Client_Tasks ON dbo.mtr_Property.Client_ID_FK = dbo.cnt_Client_Tasks.Client_ID_FK AND
dbo.mtr_Task_Types.Task_Type_ID = dbo.cnt_Client_Tasks.Task_Type_ID_FK AND dbo.mtr_Job.Transfer_Date <= GETDATE() INNER JOIN
dbo.mtr_Company_Rounds ON dbo.cnt_Client_Rounds.Company_Round_ID_FK = dbo.mtr_Company_Rounds.Company_Round_ID INNER JOIN
dbo.transfer_Clients ON dbo.mtr_Property.Client_ID_FK = dbo.transfer_Clients.Mitre_Index INNER JOIN
dbo.tmp_RndsToExp ON dbo.mtr_TMSystems.TMS_ID = dbo.tmp_RndsToExp.TMS_ID AND
dbo.mtr_Task_Types.Target_TMS_ID_FK = dbo.tmp_RndsToExp.TMS_ID AND dbo.mtr_Job.Job_ID = dbo.tmp_RndsToExp.Job_ID_FK AND
dbo.cnt_Client_Rounds.Client_Round_ID = dbo.tmp_RndsToExp.Client_Round_ID_FK AND
dbo.mtr_Company_Rounds.Company_Round + dbo.cnt_Client_Tasks.Client_Task_Round_Code = dbo.tmp_RndsToExp.Orig_Round
WHERE (dbo.mtr_Task_Types.Task_Target = N'Register') AND (dbo.mtr_Tasks.Status_ID_FK = 2) AND (dbo.mtr_Property.Status_ID_FK <> 1) AND
(dbo.mtr_Property.Status_ID_FK <> 2) AND (dbo.mtr_Property.Status_ID_FK <> 6) AND (dbo.mtr_Meters.Active = 1)
GROUP BY dbo.mtr_Property.Prop_ID, dbo.mtr_Property.Prop_Identifier, dbo.mtr_Property.Sequence_Num, dbo.mtr_Property.Property_Name,
dbo.mtr_Property.Consumer, dbo.mtr_Property.Phone_Num, dbo.mtr_Property.Street_Add_1, dbo.mtr_Property.Street_Add_2,
dbo.mtr_Property.Street_Add_3, dbo.mtr_Property.Street_Add_4, dbo.mtr_Property.Street_Add_5, dbo.mtr_Property.Postcode,
dbo.transfer_Clients.UMD_Index, dbo.tmp_RndsToExp.TMS_ID, dbo.tmp_RndsToExp.Orig_Round, dbo.tmp_RndsToExp.Add_Round

--Insert Task records for property into holding table
--REPLACE ONCE LIVE (UMD TASK TYPES) !!!
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Client_ID_FK, Task_ID_FK)
SELECT DISTINCT dbo.tmp_RndsToExp.TMS_ID, 'B' + dbo.mtr_Property.Prop_Identifier, dbo.tmp_RndsToExp.Add_Round,
dbo.mtr_Meters.Meter_Num + ':' + CONVERT(NVARCHAR(3), dbo.mtr_Registers.Register_Num), dbo.mtr_Meters.Meter_Sequence,
dbo.mtr_Meters.Asset_Num, dbo.mtr_Meters.Inscription, dbo.mtr_Meters.Tariff, CASE LowLow_Read WHEN '' THEN 0 ELSE CONVERT(decimal,
mtr_Registers.LowLow_Read) END, CASE Low_Read WHEN '' THEN 0 ELSE CONVERT(decimal, mtr_Registers.Low_Read) END,
CASE Last_Read WHEN '' THEN SUBSTRING('0000000000', 1, Num_Of_Dials)
ELSE (CASE WHEN (mtr_Registers.Num_Of_Dials - Len(CONVERT(nvarchar(12), CONVERT(decimal, Last_Read)))) > 0 THEN SUBSTRING('0000000000',
1, Num_Of_Dials - len(CONVERT(nvarchar(12), CONVERT(decimal, Last_Read)))) + CONVERT(nvarchar(12), CONVERT(decimal, Last_Read))
ELSE CONVERT(nvarchar(12), CONVERT(decimal, Last_Read)) END) END, CASE WHEN mtr_Meters.Multiplier IS NULL
THEN '1' WHEN mtr_Meters.Multiplier = '' THEN '1' ELSE mtr_Meters.Multiplier END,
CASE mtr_Registers.High_Read WHEN '' THEN SUBSTRING('9999999999', 1, Num_Of_Dials) WHEN '0' THEN SUBSTRING('9999999999', 1,
Num_Of_Dials) ELSE CONVERT(decimal, mtr_Registers.High_Read) END,
CASE mtr_Registers.HighHigh_Read WHEN '' THEN SUBSTRING('9999999999', 1, Num_Of_Dials) WHEN '0' THEN SUBSTRING('9999999999', 1,
Num_Of_Dials) ELSE CONVERT(decimal, mtr_Registers.HighHigh_Read) END, '0', mtr_Meters.Meter_Type_ID_FK,
tmp_TaskTypes.UMD_Task_Type_ID, mtr_Property.Client_ID_FK, mtr_Tasks.Task_ID
FROM dbo.mtr_TMSystems INNER JOIN
dbo.mtr_Task_Types INNER JOIN
dbo.mtr_Tasks ON dbo.mtr_Task_Types.Task_Type_ID = dbo.mtr_Tasks.Task_Type_ID_FK INNER JOIN
dbo.mtr_Meters INNER JOIN
dbo.mtr_Property ON dbo.mtr_Meters.Prop_ID_FK = dbo.mtr_Property.Prop_ID INNER JOIN
dbo.mtr_Registers ON dbo.mtr_Meters.Meter_ID = dbo.mtr_Registers.Meter_ID_FK ON
dbo.mtr_Tasks.Parent_ID_FK = dbo.mtr_Registers.Register_ID ON dbo.mtr_TMSystems.TMS_ID = dbo.mtr_Task_Types.Target_TMS_ID_FK INNER JOIN
dbo.mtr_Job ON dbo.mtr_Tasks.Job_ID_FK = dbo.mtr_Job.Job_ID INNER JOIN
dbo.cnt_Client_Rounds ON dbo.mtr_Job.Client_Round_ID_FK = dbo.cnt_Client_Rounds.Client_Round_ID INNER JOIN
dbo.cnt_Client_Tasks ON dbo.mtr_Property.Client_ID_FK = dbo.cnt_Client_Tasks.Client_ID_FK AND
dbo.mtr_Task_Types.Task_Type_ID = dbo.cnt_Client_Tasks.Task_Type_ID_FK AND dbo.mtr_Job.Transfer_Date <= GETDATE() INNER JOIN
dbo.tmp_TaskTypes ON dbo.mtr_Task_Types.Task_Type_ID = dbo.tmp_TaskTypes.MTR_Task_Type_ID AND
dbo.tmp_TaskTypes.Client_ID_FK = 3 INNER JOIN
dbo.mtr_Company_Rounds ON dbo.cnt_Client_Rounds.Company_Round_ID_FK = dbo.mtr_Company_Rounds.Company_Round_ID INNER JOIN
dbo.tmp_RndsToExp ON dbo.mtr_TMSystems.TMS_ID = dbo.tmp_RndsToExp.TMS_ID AND
dbo.mtr_Task_Types.Target_TMS_ID_FK = dbo.tmp_RndsToExp.TMS_ID AND dbo.mtr_Job.Job_ID = dbo.tmp_RndsToExp.Job_ID_FK AND
dbo.cnt_Client_Rounds.Client_Round_ID = dbo.tmp_RndsToExp.Client_Round_ID_FK AND
dbo.mtr_Company_Rounds.Company_Round + dbo.cnt_Client_Tasks.Client_Task_Round_Code = dbo.tmp_RndsToExp.Orig_Round
WHERE (dbo.mtr_Task_Types.Task_Target = N'Register') AND (dbo.mtr_Tasks.Status_ID_FK = 2) AND (dbo.mtr_Property.Status_ID_FK <> 1) AND
(dbo.mtr_Property.Status_ID_FK <> 2) AND (dbo.mtr_Property.Status_ID_FK <> 6) AND (dbo.mtr_Meters.Active = 1) AND (dbo.mtr_Meters.Active = 1)

--Insert Note records for property into holding table
INSERT INTO mtr_TMSExportHoldingData (TMS_ID_FK, Field1, Field2, Field3, Field4, Field5, Client_ID_FK)
SELECT DISTINCT dbo.tmp_RndsToExp.TMS_ID, 'C' + dbo.mtr_Property.Prop_Identifier, dbo.tmp_RndsToExp.Add_Round,
dbo.mtr_NoteActions.Scenario_Code + dbo.mtr_NoteTypes.Scenario_Code + dbo.mtr_NoteCategory.Scenario_Code, '0',
dbo.mtr_Notes.Note, dbo.mtr_Property.Client_ID_FK
FROM dbo.mtr_TMSystems INNER JOIN
dbo.mtr_Task_Types INNER JOIN
dbo.mtr_Tasks ON dbo.mtr_Task_Types.Task_Type_ID = dbo.mtr_Tasks.Task_Type_ID_FK INNER JOIN
dbo.mtr_Meters INNER JOIN
dbo.mtr_Property ON dbo.mtr_Meters.Prop_ID_FK = dbo.mtr_Property.Prop_ID INNER JOIN
dbo.mtr_Registers ON dbo.mtr_Meters.Meter_ID = dbo.mtr_Registers.Meter_ID_FK ON
dbo.mtr_Tasks.Parent_ID_FK = dbo.mtr_Registers.Register_ID ON dbo.mtr_TMSystems.TMS_ID = dbo.mtr_Task_Types.Target_TMS_ID_FK INNER JOIN
dbo.mtr_Job ON dbo.mtr_Tasks.Job_ID_FK = dbo.mtr_Job.Job_ID INNER JOIN
dbo.cnt_Client_Rounds ON dbo.mtr_Job.Client_Round_ID_FK = dbo.cnt_Client_Rounds.Client_Round_ID INNER JOIN
dbo.cnt_Client_Tasks ON dbo.mtr_Property.Client_ID_FK = dbo.cnt_Client_Tasks.Client_ID_FK AND
dbo.mtr_Task_Types.Task_Type_ID = dbo.cnt_Client_Tasks.Task_Type_ID_FK AND dbo.mtr_Job.Transfer_Date <= GETDATE() INNER JOIN
dbo.mtr_Notes ON dbo.mtr_Property.Prop_ID = dbo.mtr_Notes.Prop_ID_FK INNER JOIN
dbo.mtr_NoteActions ON dbo.mtr_Notes.Note_Action_ID_FK = dbo.mtr_NoteActions.Note_Action_ID INNER JOIN
dbo.mtr_NoteTypes ON dbo.mtr_Notes.Note_Type_ID_FK = dbo.mtr_NoteTypes.Note_Type_ID INNER JOIN
dbo.mtr_NoteCategory ON dbo.mtr_NoteTypes.Category_ID_FK = dbo.mtr_NoteCategory.Category_ID INNER JOIN
dbo.mtr_Company_Rounds ON dbo.cnt_Client_Rounds.Company_Round_ID_FK = dbo.mtr_Company_Rounds.Company_Round_ID INNER JOIN
dbo.tmp_RndsToExp ON dbo.mtr_TMSystems.TMS_ID = dbo.tmp_RndsToExp.TMS_ID AND
dbo.mtr_Task_Types.Target_TMS_ID_FK = dbo.tmp_RndsToExp.TMS_ID AND dbo.mtr_Job.Job_ID = dbo.tmp_RndsToExp.Job_ID_FK AND
dbo.cnt_Client_Rounds.Client_Round_ID = dbo.tmp_RndsToExp.Client_Round_ID_FK
WHERE (dbo.mtr_Task_Types.Task_Target = N'Register') AND (dbo.mtr_Tasks.Status_ID_FK = 2) AND (dbo.mtr_Property.Status_ID_FK <> 1) AND
(dbo.mtr_Property.Status_ID_FK <> 2) AND (dbo.mtr_Property.Status_ID_FK <> 6) AND (dbo.mtr_Meters.Active = 1) AND (dbo.mtr_Notes.Active = 1)
GO

SKR

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 17:19:19

quote:
Originally posted by OBINNA_EKE

It is not SQL bug, it is you ehehe, Post the code here for us to help you!

If it is that easy, everybody will be doing it

hahaa, that was funny..

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-29 : 17:24:36

This is just a stab in the dark, but maybe you get an error because you have named your transaction TMS also?

also, try eliminating cursors all together. Cursors are usually a bad way to go and cause many performance probelms. Ususally using a set based approach, table variables or temp tables instead of cursors will get you much better performance.

-ec

rikleo2001
Posting Yak Master

185 Posts

Posted - 2006-08-31 : 16:17:36

Many Thanks.
Actually I managed to get around that problem by chaning name of the cursor.
I realize that cursors are bad way, and hopefully i will get it converted soon.
But that is still Mystry, why TMS doesn't work along with all other process.

Anyway Thanks

SKR

Marin
Starting Member

4 Posts

Posted - 2006-09-03 : 00:41:51

You should try to eliminate cursors, read the article about it at http://website-hosting-development.blogspot.com

Cheers, MArin

_______________________________________
Check Website Hosting and Development (website-hosting-development.com) site by DIMM Info Systems Inc. (dimm-is.com) for articles about website hosting, development, design and database tutorials and guidelines.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources