SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 IS THAT A BUG IN SQL 2000?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rikleo2001
Posting Yak Master

New Zealand
181 Posts

Posted - 08/29/2006 :  00:34:57  Show Profile  Send rikleo2001 a Yahoo! Message
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

Sweden
30282 Posts

Posted - 08/29/2006 :  01:12:16  Show Profile  Visit SwePeso's Homepage
It's hard to tell without looking on the real code.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/29/2006 :  10:30:56  Show Profile
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
Go to Top of Page

rikleo2001
Posting Yak Master

New Zealand
181 Posts

Posted - 08/29/2006 :  16:32:43  Show Profile  Send rikleo2001 a Yahoo! Message
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/29/2006 :  17:19:19  Show Profile
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/29/2006 :  17:24:36  Show Profile
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
Go to Top of Page

rikleo2001
Posting Yak Master

New Zealand
181 Posts

Posted - 08/31/2006 :  16:17:36  Show Profile  Send rikleo2001 a Yahoo! Message
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
Go to Top of Page

Marin
Starting Member

Canada
4 Posts

Posted - 09/03/2006 :  00:41:51  Show Profile  Visit Marin's Homepage
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000