|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Cursor Update problemI am using a cursor to take information from a temp table and either insert
or update another table. I am using a while loop to fetch all the vaules from the cursor and then I close and deallocate the cursor. Everything runs fine the first time but when I run the procedure again it updates the first record with the information from the last record process from the time before. If it helps I am running it as a job just like it will run when completed. Ideas on why I am getting data from the previous run? Can you poste the code instead just a brief description of the problem?
Please provide DDL and sample data. http://www.aspfaq.com/etiquette.asp?id=5006 AMB Show quote "Shannon Thompson" wrote: > I am using a cursor to take information from a temp table and either insert > or update another table. I am using a while loop to fetch all the vaules > from the cursor and then I close and deallocate the cursor. Everything runs > fine the first time but when I run the procedure again it updates the first > record with the information from the last record process from the time > before. If it helps I am running it as a job just like it will run when > completed. Ideas on why I am getting data from the previous run? Here is my code but I cannot give you the code for stored procedures call
from this code here because they are Encrypted and part of the software program I am integrating with. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[xxx] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE procedure xxxx AS /* ** Declare & initialize Local Variables *** */ DECLARE @iReturnCode int, @iRet int, @vchFirstName nvarchar(255) , @vchLastName nvarchar(255) , @vchAdSource nvarchar(255) , @vchOnyxCode nvarchar(255) , @vchAddress1 nvarchar(255) , @vchCity nvarchar(255) , @chStateCode nvarchar(50) , @vchPostCode nvarchar(40) , @chCountryCode nvarchar(50) , @vchPhoneType nvarchar(50) , @vchPhoneNumber nvarchar(40) , @vchBestTime nvarchar(255), @vchEmail nvarchar(255), @iHt_Feet nvarchar(50), @iHt_Inches nvarchar(50), @iWeight nvarchar(50), @dtDOB nvarchar(50), @vchInsurance nvarchar(255), @vchOtherIns nvarchar(255), @vchInsuranceType nvarchar(255), @vchSem nvarchar(255), @vchSemSrc nvarchar(255), @dtTimeStamp nvarchar(255), @iIndividualId int , @iIncidentId int, @onyx_cursor cursor, @chInsUpd nchar(1), @iPhoneTypeId int, @getDate datetime, @dtPreviousUpdateDate datetime, @iHeight int, @dtUpdate datetime, @bmi float set @iReturnCode = 0 set @iRet = 0 set @getDate = getDate() set @bmi = 0 set @onyx_cursor = cursor --local Scroll Keyset Optimistic FOR Select vchFirstName, vchLastName, vchAdSource, vchOnyxCode, vchAddress1, vchCity, chStateCode, vchPostCode, chCountryCode, vchPhoneType, vchPhoneNumber, vchBestTime, vchEmail, iHt_Feet, iHt_Inches, iWeight, dtDOB, vchInsurance, vchOtherIns, vchInsuranceType, vchSem, vchSemSrc, dtTimeStamp from CallCenter_Temp OPEN @onyx_cursor FETCH NEXT from @onyx_cursor into @vchFirstName, @vchLastName, @vchAdSource, @vchOnyxCode, @vchAddress1, @vchCity, @chStateCode, @vchPostCode, @chCountryCode, @vchPhoneType, @vchPhoneNumber, @vchBestTime, @vchEmail, @iHt_Feet, @iHt_Inches, @iWeight, @dtDOB, @vchInsurance, @vchOtherIns, @vchInsuranceType, @vchSem, @vchSemSrc, @dtTimeStamp -- loop while there are still records in table WHILE @@FETCH_STATUS = 0 BEGIN set @iHeight= convert(int,convert(float,ROUND(@iHt_Inches,0)) + (convert(float,ROUND(@iHt_Feet,0))*12)) IF @iHeight <> 0 AND @iWeight <> 0 BEGIN declare @meters float, @totalinches float, @kilos float, @metersq float set @totalinches = convert(float,@iHeight) set @meters = @totalinches/39.36 set @kilos = convert(float,@iWeight)/2.2 set @metersq = @meters * @meters set @bmi = Round(@kilos/@metersq,0) END set @vchFirstName = UPPER(@vchFirstName) set @vchLastName = UPPER(@vchLastName) set @vchAddress1 = UPPER(@vchAddress1) set @vchCity = UPPER(@vchCity) set @chStateCode = UPPER(@chStateCode) set @chCountryCode = UPPER(@chCountryCode) -- determine phone type SELECT @iPhoneTypeId = CASE LOWER(RTRIM(@vchPhoneType)) WHEN 'home' THEN 119 WHEN 'cell' THEN 103 WHEN 'work' THEN 102 ELSE 119 END -- if no Onyx Id is listed then search for the individual IF @iIndividualId is null BEGIN -- check to see if Person is in Onyx exec @iRet = wbocpscOnyxTalley @vchFirstName, @vchLastName, @vchAddress1, @vchCity, @chStateCode, @vchPostCode, NULL if (@iRet <> 0) begin --update set @chInsUpd = 'U' set @iIndividualId = @iRet end else begin --insert set @chInsUpd = 'I' end end -- iIndividualId is given so this is an update ELSE BEGIN set @chInsUpd = 'U' END print @chInsUpd + ' ' + @vchLastName if @chInsUpd = 'I' begin exec @iReturnCode = wbospsiIndividual 1, @iIndividualId, 'ENG', 'PatientLC', null, @vchFirstName, null, @vchLastName, null, @vchAddress1, null, null, @vchCity, @chStateCode, @chCountryCode, @vchPostCode, @vchPhoneNumber, @vchEmail, '', null, null, null, 0, '', '', '', null, null, @iPhoneTypeId, 119, null, null, 1, 1, 0, null, null, @iHeight, @iWeight, @bmi, null, null, null, @dtDOB, null, 'CCLeads', @getDate, 0, 1, 1 if @iReturnCode <> 0 begin print 'insert failed for ' + @vchFirstName + ' ' + @vchLastName print @iReturnCode end else begin print 'inserted ' + @vchFirstName + ' ' + @vchLastName print @iReturnCode end end ELSE begin select @dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE iIndividualId = @iIndividualId exec @iReturnCode = ospsgCheckRecordLock @dtUpdate OUTPUT, @dtPreviousUpdateDate, 1 -- if error returned then must change dtUpdateDate to current if (@iReturnCode <> 0) begin UPDATE Individual SET dtUpdateDate = @getDate,chUpdateBy='sa' WHERE iIndividualId = @iIndividualId exec @iReturnCode = wbospsuIndividual 1, @iIndividualId, 'ENG', 'PatientLC', null, @vchFirstName, null, @vchLastName, null, @vchAddress1, null, null, @vchCity, @chStateCode, @chCountryCode, @vchPostCode, @vchPhoneNumber, @vchEmail, '', null, null, null, 0, '', '', '', null, null, @iPhoneTypeId, null, null, null, 1, 1, 0, null, null, null, @iHeight, @iWeight, @bmi, null, null, @dtDOB, null, 'CCLeads', @getDate, 0, 1 if @iReturnCode <> 0 begin print 'update failed for ' + @vchFirstName + ' ' + @vchLastName print @iReturnCode end else begin print 'updated ' + @vchFirstName + ' ' + @vchLastName print @iReturnCode end end end --fetch next record FETCH NEXT from @onyx_cursor into @vchFirstName, @vchLastName, @vchAdSource, @vchOnyxCode, @vchAddress1, @vchCity, @chStateCode, @vchPostCode, @chCountryCode, @vchPhoneType, @vchPhoneNumber, @vchBestTime, @vchEmail, @iHt_Feet, @iHt_Inches, @iWeight, @dtDOB, @vchInsurance, @vchOtherIns, @vchInsuranceType, @vchSem, @vchSemSrc, @dtTimeStamp END CLOSE @onyx_cursor DEALLOCATE @onyx_cursor return @iReturnCode GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Show quote "Alejandro Mesa" wrote: > Can you poste the code instead just a brief description of the problem? > > Please provide DDL and sample data. > http://www.aspfaq.com/etiquette.asp?id=5006 > > > AMB > > "Shannon Thompson" wrote: > > > I am using a cursor to take information from a temp table and either insert > > or update another table. I am using a while loop to fetch all the vaules > > from the cursor and then I close and deallocate the cursor. Everything runs > > fine the first time but when I run the procedure again it updates the first > > record with the information from the last record process from the time > > before. If it helps I am running it as a job just like it will run when > > completed. Ideas on why I am getting data from the previous run? Shannon,
The cursor is based on a permanent table. How are you feeding this table before calling the sp? AMB Show quote "Shannon Thompson" wrote: > Here is my code but I cannot give you the code for stored procedures call > from this code here because they are Encrypted and part of the software > program I am integrating with. > > if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') > and OBJECTPROPERTY(id, N'IsProcedure') = 1) > drop procedure [dbo].[xxx] > GO > > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS OFF > GO > > > CREATE procedure xxxx > > AS > /* > ** Declare & initialize Local Variables > *** > */ > > > DECLARE @iReturnCode int, > @iRet int, > @vchFirstName nvarchar(255) , > @vchLastName nvarchar(255) , > @vchAdSource nvarchar(255) , > @vchOnyxCode nvarchar(255) , > @vchAddress1 nvarchar(255) , > @vchCity nvarchar(255) , > @chStateCode nvarchar(50) , > @vchPostCode nvarchar(40) , > @chCountryCode nvarchar(50) , > @vchPhoneType nvarchar(50) , > @vchPhoneNumber nvarchar(40) , > @vchBestTime nvarchar(255), > @vchEmail nvarchar(255), > @iHt_Feet nvarchar(50), > @iHt_Inches nvarchar(50), > @iWeight nvarchar(50), > @dtDOB nvarchar(50), > @vchInsurance nvarchar(255), > @vchOtherIns nvarchar(255), > @vchInsuranceType nvarchar(255), > @vchSem nvarchar(255), > @vchSemSrc nvarchar(255), > @dtTimeStamp nvarchar(255), > @iIndividualId int , > @iIncidentId int, > @onyx_cursor cursor, > @chInsUpd nchar(1), > @iPhoneTypeId int, > @getDate datetime, > @dtPreviousUpdateDate datetime, > @iHeight int, > @dtUpdate datetime, > @bmi float > > set @iReturnCode = 0 > set @iRet = 0 > set @getDate = getDate() > set @bmi = 0 > > set @onyx_cursor = cursor > --local Scroll Keyset Optimistic > FOR Select > vchFirstName, > vchLastName, > vchAdSource, > vchOnyxCode, > vchAddress1, > vchCity, > chStateCode, > vchPostCode, > chCountryCode, > vchPhoneType, > vchPhoneNumber, > vchBestTime, > vchEmail, > iHt_Feet, > iHt_Inches, > iWeight, > dtDOB, > vchInsurance, > vchOtherIns, > vchInsuranceType, > vchSem, > vchSemSrc, > dtTimeStamp > from CallCenter_Temp > > OPEN @onyx_cursor > FETCH NEXT from @onyx_cursor into > @vchFirstName, > @vchLastName, > @vchAdSource, > @vchOnyxCode, > @vchAddress1, > @vchCity, > @chStateCode, > @vchPostCode, > @chCountryCode, > @vchPhoneType, > @vchPhoneNumber, > @vchBestTime, > @vchEmail, > @iHt_Feet, > @iHt_Inches, > @iWeight, > @dtDOB, > @vchInsurance, > @vchOtherIns, > @vchInsuranceType, > @vchSem, > @vchSemSrc, > @dtTimeStamp > > -- loop while there are still records in table > WHILE @@FETCH_STATUS = 0 > BEGIN > set @iHeight= convert(int,convert(float,ROUND(@iHt_Inches,0)) + > (convert(float,ROUND(@iHt_Feet,0))*12)) > IF @iHeight <> 0 AND @iWeight <> 0 > BEGIN > declare @meters float, > @totalinches float, > @kilos float, > @metersq float > > set @totalinches = convert(float,@iHeight) > set @meters = @totalinches/39.36 > set @kilos = convert(float,@iWeight)/2.2 > set @metersq = @meters * @meters > set @bmi = Round(@kilos/@metersq,0) > END > > set @vchFirstName = UPPER(@vchFirstName) > set @vchLastName = UPPER(@vchLastName) > set @vchAddress1 = UPPER(@vchAddress1) > set @vchCity = UPPER(@vchCity) > set @chStateCode = UPPER(@chStateCode) > set @chCountryCode = UPPER(@chCountryCode) > > -- determine phone type > SELECT @iPhoneTypeId = > CASE LOWER(RTRIM(@vchPhoneType)) > WHEN 'home' THEN 119 > WHEN 'cell' THEN 103 > WHEN 'work' THEN 102 > ELSE 119 > END > > -- if no Onyx Id is listed then search for the individual > > IF @iIndividualId is null > BEGIN > -- check to see if Person is in Onyx > exec @iRet = wbocpscOnyxTalley > @vchFirstName, > @vchLastName, > @vchAddress1, > @vchCity, > @chStateCode, > @vchPostCode, > NULL > > if (@iRet <> 0) > begin > --update > set @chInsUpd = 'U' > set @iIndividualId = @iRet > end > else > begin > --insert > set @chInsUpd = 'I' > end > end > -- iIndividualId is given so this is an update > ELSE > BEGIN > set @chInsUpd = 'U' > END > print @chInsUpd + ' ' + @vchLastName > if @chInsUpd = 'I' > begin > exec @iReturnCode = wbospsiIndividual > 1, > @iIndividualId, > 'ENG', > 'PatientLC', > null, > @vchFirstName, > null, > @vchLastName, > null, > @vchAddress1, > null, > null, > @vchCity, > @chStateCode, > @chCountryCode, > @vchPostCode, > @vchPhoneNumber, > @vchEmail, > '', > null, > null, > null, > 0, > '', > '', > '', > null, > null, > @iPhoneTypeId, > 119, > null, > null, > 1, > 1, > 0, > null, > null, > @iHeight, > @iWeight, > @bmi, > null, > null, > null, > @dtDOB, > null, > 'CCLeads', > @getDate, > 0, > 1, > 1 > > if @iReturnCode <> 0 > begin > print 'insert failed for ' + @vchFirstName + ' ' + @vchLastName > print @iReturnCode > > end > else > begin > print 'inserted ' + @vchFirstName + ' ' + @vchLastName > print @iReturnCode > end > end > ELSE > begin > select @dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE > iIndividualId = @iIndividualId > > exec @iReturnCode = ospsgCheckRecordLock > @dtUpdate OUTPUT, > @dtPreviousUpdateDate, > 1 > > -- if error returned then must change dtUpdateDate to current > if (@iReturnCode <> 0) > begin > UPDATE Individual SET dtUpdateDate = @getDate,chUpdateBy='sa' WHERE > iIndividualId = @iIndividualId > exec @iReturnCode = wbospsuIndividual > 1, > @iIndividualId, > 'ENG', > 'PatientLC', > null, > @vchFirstName, > null, > @vchLastName, > null, > @vchAddress1, > null, > null, > @vchCity, > @chStateCode, > @chCountryCode, > @vchPostCode, > @vchPhoneNumber, > @vchEmail, > '', > null, > null, > null, > 0, > '', > '', > '', > null, > null, > @iPhoneTypeId, > null, > null, > null, This table (CallCenter_temp) is populated by another stored procedure that
gets a file list from a directory, puts that into a true temp table (gets created and deleted within procedure) and batch inserts each text file: BEGIN /* ** Declare & initialize Local Variables */ DECLARE @iReturnCode int, @MyFile varchar(200), @SQL varchar(2000), @Path varchar(400), @onyx_cursor cursor, @vchFileName varchar(255), @vchXPCMD nvarchar(255) select @iReturnCode = 0 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name = 'CCFiles') BEGIN DROP TABLE CCFiles END CREATE TABLE [dbo].[CCFiles] ( [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] SET @Path = '\\wms1\c$\CallCenterWebSite\data\' EXECUTE cpListFiles @Path,'CCFiles','%.txt',NULL,0 IF @iReturnCode = 0 BEGIN SET @onyx_cursor = cursor FOR SELECT vchFileName FROM CCFiles OPEN @onyx_cursor FETCH NEXT from @onyx_cursor into @vchFileName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @Path + @vchFileName + '"' + ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|" ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)' --SELECT @SQL EXECUTE (@SQL) IF @iReturnCode = 0 BEGIN set @vchFileName = @Path + @vchFileName set @vchXPCMD = '@Del ' + RTrim(@vchFileName) --execute master..xp_cmdshell @vchXPCMD END FETCH NEXT from @onyx_cursor into @vchFileName END END DROP TABLE CCFiles return @iReturnCode END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Show quote "Alejandro Mesa" wrote: > Shannon, > > The cursor is based on a permanent table. How are you feeding this table > before calling the sp? > > > AMB > > "Shannon Thompson" wrote: > > > Here is my code but I cannot give you the code for stored procedures call > > from this code here because they are Encrypted and part of the software > > program I am integrating with. > > > > if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') > > and OBJECTPROPERTY(id, N'IsProcedure') = 1) > > drop procedure [dbo].[xxx] > > GO > > > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS OFF > > GO > > > > > > CREATE procedure xxxx > > > > AS > > /* > > ** Declare & initialize Local Variables > > *** > > */ > > > > > > DECLARE @iReturnCode int, > > @iRet int, > > @vchFirstName nvarchar(255) , > > @vchLastName nvarchar(255) , > > @vchAdSource nvarchar(255) , > > @vchOnyxCode nvarchar(255) , > > @vchAddress1 nvarchar(255) , > > @vchCity nvarchar(255) , > > @chStateCode nvarchar(50) , > > @vchPostCode nvarchar(40) , > > @chCountryCode nvarchar(50) , > > @vchPhoneType nvarchar(50) , > > @vchPhoneNumber nvarchar(40) , > > @vchBestTime nvarchar(255), > > @vchEmail nvarchar(255), > > @iHt_Feet nvarchar(50), > > @iHt_Inches nvarchar(50), > > @iWeight nvarchar(50), > > @dtDOB nvarchar(50), > > @vchInsurance nvarchar(255), > > @vchOtherIns nvarchar(255), > > @vchInsuranceType nvarchar(255), > > @vchSem nvarchar(255), > > @vchSemSrc nvarchar(255), > > @dtTimeStamp nvarchar(255), > > @iIndividualId int , > > @iIncidentId int, > > @onyx_cursor cursor, > > @chInsUpd nchar(1), > > @iPhoneTypeId int, > > @getDate datetime, > > @dtPreviousUpdateDate datetime, > > @iHeight int, > > @dtUpdate datetime, > > @bmi float > > > > set @iReturnCode = 0 > > set @iRet = 0 > > set @getDate = getDate() > > set @bmi = 0 > > > > set @onyx_cursor = cursor > > --local Scroll Keyset Optimistic > > FOR Select > > vchFirstName, > > vchLastName, > > vchAdSource, > > vchOnyxCode, > > vchAddress1, > > vchCity, > > chStateCode, > > vchPostCode, > > chCountryCode, > > vchPhoneType, > > vchPhoneNumber, > > vchBestTime, > > vchEmail, > > iHt_Feet, > > iHt_Inches, > > iWeight, > > dtDOB, > > vchInsurance, > > vchOtherIns, > > vchInsuranceType, > > vchSem, > > vchSemSrc, > > dtTimeStamp > > from CallCenter_Temp > > > > OPEN @onyx_cursor > > FETCH NEXT from @onyx_cursor into > > @vchFirstName, > > @vchLastName, > > @vchAdSource, > > @vchOnyxCode, > > @vchAddress1, > > @vchCity, > > @chStateCode, > > @vchPostCode, > > @chCountryCode, > > @vchPhoneType, > > @vchPhoneNumber, > > @vchBestTime, > > @vchEmail, > > @iHt_Feet, > > @iHt_Inches, > > @iWeight, > > @dtDOB, > > @vchInsurance, > > @vchOtherIns, > > @vchInsuranceType, > > @vchSem, > > @vchSemSrc, > > @dtTimeStamp > > > > -- loop while there are still records in table > > WHILE @@FETCH_STATUS = 0 > > BEGIN > > set @iHeight= convert(int,convert(float,ROUND(@iHt_Inches,0)) + > > (convert(float,ROUND(@iHt_Feet,0))*12)) > > IF @iHeight <> 0 AND @iWeight <> 0 > > BEGIN > > declare @meters float, > > @totalinches float, > > @kilos float, > > @metersq float > > > > set @totalinches = convert(float,@iHeight) > > set @meters = @totalinches/39.36 > > set @kilos = convert(float,@iWeight)/2.2 > > set @metersq = @meters * @meters > > set @bmi = Round(@kilos/@metersq,0) > > END > > > > set @vchFirstName = UPPER(@vchFirstName) > > set @vchLastName = UPPER(@vchLastName) > > set @vchAddress1 = UPPER(@vchAddress1) > > set @vchCity = UPPER(@vchCity) > > set @chStateCode = UPPER(@chStateCode) > > set @chCountryCode = UPPER(@chCountryCode) > > > > -- determine phone type > > SELECT @iPhoneTypeId = > > CASE LOWER(RTRIM(@vchPhoneType)) > > WHEN 'home' THEN 119 > > WHEN 'cell' THEN 103 > > WHEN 'work' THEN 102 > > ELSE 119 > > END > > > > -- if no Onyx Id is listed then search for the individual > > > > IF @iIndividualId is null > > BEGIN > > -- check to see if Person is in Onyx > > exec @iRet = wbocpscOnyxTalley > > @vchFirstName, > > @vchLastName, > > @vchAddress1, > > @vchCity, > > @chStateCode, > > @vchPostCode, > > NULL > > > > if (@iRet <> 0) > > begin > > --update > > set @chInsUpd = 'U' > > set @iIndividualId = @iRet > > end > > else > > begin > > --insert > > set @chInsUpd = 'I' > > end > > end > > -- iIndividualId is given so this is an update > > ELSE > > BEGIN > > set @chInsUpd = 'U' > > END > > print @chInsUpd + ' ' + @vchLastName > > if @chInsUpd = 'I' > > begin > > exec @iReturnCode = wbospsiIndividual > > 1, > > @iIndividualId, > > 'ENG', > > 'PatientLC', > > null, > > @vchFirstName, > > null, > > @vchLastName, > > null, > > @vchAddress1, > > null, > > null, > > @vchCity, > > @chStateCode, > > @chCountryCode, > > @vchPostCode, > > @vchPhoneNumber, > > @vchEmail, > > '', > > null, > > null, > > null, > > 0, > > '', > > '', > > '', > > null, > > null, > > @iPhoneTypeId, > > 119, > > null, > > null, > > 1, > > 1, > > 0, > > null, > > null, > > @iHeight, > > @iWeight, > > @bmi, > > null, > > null, > > null, > > @dtDOB, > > null, > > 'CCLeads', > > @getDate, > > 0, > > 1, > > 1 > > > > if @iReturnCode <> 0 > > begin > > print 'insert failed for ' + @vchFirstName + ' ' + @vchLastName > > print @iReturnCode > > > > end > > else > > begin > > print 'inserted ' + @vchFirstName + ' ' + @vchLastName > > print @iReturnCode > > end > > end > > ELSE > > begin > > select @dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE > > iIndividualId = @iIndividualId > > > > exec @iReturnCode = ospsgCheckRecordLock > > @dtUpdate OUTPUT, > > @dtPreviousUpdateDate, > > 1 > > > > -- if error returned then must change dtUpdateDate to current > > if (@iReturnCode <> 0) > > begin > > UPDATE Individual SET dtUpdateDate = @getDate,chUpdateBy='sa' WHERE > > iIndividualId = @iIndividualId > > exec @iReturnCode = wbospsuIndividual > > 1, > > @iIndividualId, > > 'ENG', > > 'PatientLC', > > null, > > @vchFirstName, > > null, > > @vchLastName, > > null, > > @vchAddress1, > > null, > > null, > > @vchCity, > > @chStateCode, > > @chCountryCode, > > @vchPostCode, > > @vchPhoneNumber, > > @vchEmail, > > '', > > null, > > null, > > null, Shannon,
In the previous post you are not closing and deallocating the cursor and this cursor. AMB Show quote "Shannon Thompson" wrote: > This table (CallCenter_temp) is populated by another stored procedure that > gets a file list from a directory, puts that into a true temp table (gets > created and deleted within procedure) and batch inserts each text file: > > BEGIN > > /* > ** Declare & initialize Local Variables > */ > DECLARE > @iReturnCode int, > @MyFile varchar(200), > @SQL varchar(2000), > @Path varchar(400), > @onyx_cursor cursor, > @vchFileName varchar(255), > @vchXPCMD nvarchar(255) > > select > @iReturnCode = 0 > > IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name = > 'CCFiles') > BEGIN > DROP TABLE CCFiles > END > > CREATE TABLE [dbo].[CCFiles] ( > [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > > SET @Path = '\\wms1\c$\CallCenterWebSite\data\' > > EXECUTE cpListFiles @Path,'CCFiles','%.txt',NULL,0 > > IF @iReturnCode = 0 > BEGIN > SET @onyx_cursor = cursor > > FOR SELECT > vchFileName > FROM CCFiles > > OPEN @onyx_cursor > FETCH NEXT from @onyx_cursor into @vchFileName > > WHILE @@FETCH_STATUS = 0 > BEGIN > SET @SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @Path + > @vchFileName + '"' + > ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|" > ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)' > > --SELECT @SQL > EXECUTE (@SQL) > > IF @iReturnCode = 0 > BEGIN > set @vchFileName = @Path + @vchFileName > set @vchXPCMD = '@Del ' + RTrim(@vchFileName) > --execute master..xp_cmdshell @vchXPCMD > END > > FETCH NEXT from @onyx_cursor into @vchFileName > END > END > > DROP TABLE CCFiles > > return @iReturnCode > END > > > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > > > > > "Alejandro Mesa" wrote: > > > Shannon, > > > > The cursor is based on a permanent table. How are you feeding this table > > before calling the sp? > > > > > > AMB > > > > "Shannon Thompson" wrote: > > > > > Here is my code but I cannot give you the code for stored procedures call > > > from this code here because they are Encrypted and part of the software > > > program I am integrating with. > > > > > > if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') > > > and OBJECTPROPERTY(id, N'IsProcedure') = 1) > > > drop procedure [dbo].[xxx] > > > GO > > > > > > SET QUOTED_IDENTIFIER OFF > > > GO > > > SET ANSI_NULLS OFF > > > GO > > > > > > > > > CREATE procedure xxxx > > > > > > AS > > > /* > > > ** Declare & initialize Local Variables > > > *** > > > */ > > > > > > > > > DECLARE @iReturnCode int, > > > @iRet int, > > > @vchFirstName nvarchar(255) , > > > @vchLastName nvarchar(255) , > > > @vchAdSource nvarchar(255) , > > > @vchOnyxCode nvarchar(255) , > > > @vchAddress1 nvarchar(255) , > > > @vchCity nvarchar(255) , > > > @chStateCode nvarchar(50) , > > > @vchPostCode nvarchar(40) , > > > @chCountryCode nvarchar(50) , > > > @vchPhoneType nvarchar(50) , > > > @vchPhoneNumber nvarchar(40) , > > > @vchBestTime nvarchar(255), > > > @vchEmail nvarchar(255), > > > @iHt_Feet nvarchar(50), > > > @iHt_Inches nvarchar(50), > > > @iWeight nvarchar(50), > > > @dtDOB nvarchar(50), > > > @vchInsurance nvarchar(255), > > > @vchOtherIns nvarchar(255), > > > @vchInsuranceType nvarchar(255), > > > @vchSem nvarchar(255), > > > @vchSemSrc nvarchar(255), > > > @dtTimeStamp nvarchar(255), > > > @iIndividualId int , > > > @iIncidentId int, > > > @onyx_cursor cursor, > > > @chInsUpd nchar(1), > > > @iPhoneTypeId int, > > > @getDate datetime, > > > @dtPreviousUpdateDate datetime, > > > @iHeight int, > > > @dtUpdate datetime, > > > @bmi float > > > > > > set @iReturnCode = 0 > > > set @iRet = 0 > > > set @getDate = getDate() > > > set @bmi = 0 > > > > > > set @onyx_cursor = cursor > > > --local Scroll Keyset Optimistic > > > FOR Select > > > vchFirstName, > > > vchLastName, > > > vchAdSource, > > > vchOnyxCode, > > > vchAddress1, > > > vchCity, > > > chStateCode, > > > vchPostCode, > > > chCountryCode, > > > vchPhoneType, > > > vchPhoneNumber, > > > vchBestTime, > > > vchEmail, > > > iHt_Feet, > > > iHt_Inches, > > > iWeight, > > > dtDOB, > > > vchInsurance, > > > vchOtherIns, > > > vchInsuranceType, > > > vchSem, > > > vchSemSrc, > > > dtTimeStamp > > > from CallCenter_Temp > > > > > > OPEN @onyx_cursor > > > FETCH NEXT from @onyx_cursor into > > > @vchFirstName, > > > @vchLastName, > > > @vchAdSource, > > > @vchOnyxCode, > > > @vchAddress1, > > > @vchCity, > > > @chStateCode, > > > @vchPostCode, > > > @chCountryCode, > > > @vchPhoneType, > > > @vchPhoneNumber, > > > @vchBestTime, > > > @vchEmail, > > > @iHt_Feet, > > > @iHt_Inches, > > > @iWeight, > > > @dtDOB, > > > @vchInsurance, > > > @vchOtherIns, > > > @vchInsuranceType, > > > @vchSem, > > > @vchSemSrc, > > > @dtTimeStamp > > > > > > -- loop while there are still records in table > > > WHILE @@FETCH_STATUS = 0 > > > BEGIN > > > set @iHeight= convert(int,convert(float,ROUND(@iHt_Inches,0)) + > > > (convert(float,ROUND(@iHt_Feet,0))*12)) > > > IF @iHeight <> 0 AND @iWeight <> 0 > > > BEGIN > > > declare @meters float, > > > @totalinches float, > > > @kilos float, > > > @metersq float > > > > > > set @totalinches = convert(float,@iHeight) > > > set @meters = @totalinches/39.36 > > > set @kilos = convert(float,@iWeight)/2.2 > > > set @metersq = @meters * @meters > > > set @bmi = Round(@kilos/@metersq,0) > > > END > > > > > > set @vchFirstName = UPPER(@vchFirstName) > > > set @vchLastName = UPPER(@vchLastName) > > > set @vchAddress1 = UPPER(@vchAddress1) > > > set @vchCity = UPPER(@vchCity) > > > set @chStateCode = UPPER(@chStateCode) > > > set @chCountryCode = UPPER(@chCountryCode) > > > > > > -- determine phone type > > > SELECT @iPhoneTypeId = > > > CASE LOWER(RTRIM(@vchPhoneType)) > > > WHEN 'home' THEN 119 > > > WHEN 'cell' THEN 103 > > > WHEN 'work' THEN 102 > > > ELSE 119 > > > END > > > > > > -- if no Onyx Id is listed then search for the individual > > > > > > IF @iIndividualId is null > > > BEGIN > > > -- check to see if Person is in Onyx > > > exec @iRet = wbocpscOnyxTalley > > > @vchFirstName, > > > @vchLastName, > > > @vchAddress1, > > > @vchCity, > > > @chStateCode, > > > @vchPostCode, > > > NULL > > > > > > if (@iRet <> 0) > > > begin > > > --update > > > set @chInsUpd = 'U' > > > set @iIndividualId = @iRet > > > end > > > else > > > begin > > > --insert > > > set @chInsUpd = 'I' > > > end > > > end > > > -- iIndividualId is given so this is an update > > > ELSE > > > BEGIN > > > set @chInsUpd = 'U' > > > END > > > print @chInsUpd + ' ' + @vchLastName > > > if @chInsUpd = 'I' > > > begin > > > exec @iReturnCode = wbospsiIndividual > > > 1, > > > @iIndividualId, > > > 'ENG', > > > 'PatientLC', > > > null, > > > @vchFirstName, > > > null, > > > @vchLastName, > > > null, > > > @vchAddress1, > > > null, > > > null, > > > @vchCity, > > > @chStateCode, > > > @chCountryCode, > > > @vchPostCode, > > > @vchPhoneNumber, > I cannot give you the code for stored procedures call Personally I'd want to decrypt those procs to see if it's feasible to> from this code here because they are Encrypted and part of the software > program I am integrating with. rewrite your code without a cursor. http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5 http://www.securiteam.com/tools/6J00S003GU.html -- David Portas SQL Server MVP -- The reason I use a cursor is that having limited SQL Stored Procedure
experience (mainly VBScript SQL experience) I know of no other way to go record by record to call these stored procedures. I have de-crypted these procedures but by using these stored procedures instead of recreating them I can safety insert data into the database without breaking any middle tier rules of the software and corrupt any of the data. The store procedures are made to put one person at a time into the database (when a users clicks save on the web page) not bulk so this is why I am using the cursor. Show quote "David Portas" wrote: > > I cannot give you the code for stored procedures call > > from this code here because they are Encrypted and part of the software > > program I am integrating with. > > Personally I'd want to decrypt those procs to see if it's feasible to > rewrite your code without a cursor. > > http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5 > http://www.securiteam.com/tools/6J00S003GU.html > > -- > David Portas > SQL Server MVP > -- > > Why did you destroy Standard SQL behavior? Why are there more NULLs in
one table than should be in an entire Fortune 500 accounting package? Do you really have a lot of data elements that are in Chinese and 255 characters long? Why do you have data type prefixes on variable names, which is a violation of both good programming and ISO-11179? Why do you have numeric data elements in strings? What kind of total garbage are trying to get with things like "weight VARCHAR(50)", "@phonetype VARCHAR(50)", etc. And you don't seem to be aware of floating point rounding errors (does your machine have a floating point processor, or do you want to slow things down with a software floating point package?) You keep height in inches or cm then convert it for display. You do not do this in the database. The syntax for CAST is CAST (<exp> AS <datatype>) -- do not use the proprietary CONVERT(). You are NOT writing SQL at all, but some kind of 3GL, using SQL for it. But even worse, you did absolutely no design or research on the data. Other products have a MERGE or UPSERT statement to do this. The usual pattern in older products is: BEGIN -- insert the new rows INSERT INTO Foobar SELECT * FROM WorkingData AS W WHERE W.keycol NOT IN (SELECT keycol FROM Foobar); -- update the rows are already there UPDATE Foobar SET <column> = (SELECT col FROM WorkingData AS W WHERE W.keycol = Foobar.keycol) WHERE keycol IN (SELECT keycol FROM WorkingData); END; Since you have no idea about the situation or the database schema your
posting is just wasted useless space. Please do not bother my thread again unless you want to know more about the database and can actually just give me a reason to my error. I did not ask for a comments on the coding though constructive criticism, not code bashing, is appreciated - again I stated I am a Computer Programmer using SQL code in ASP pages mostly not much done with SQL Server, I would have done this in VBScript (which I have already done before) but store procedures are more efficient and reliable. Oh and the comment about doing design and research on the data, you have no idea how much design and research in this database I have done. With a database that all the stored procedures are encrypted (which you cannot decrypt unless you want to break the software agreement which I play by the rules maybe you do not), no manuals or references on the database (because it is part of a software program and while they want you to add functionalilty to the product for your own uses they are not forth coming with how to do things) and myself being no where near a DBA I think I have a pretty good understanding of this database which you clearly do not because you did not ask! Thanks but no Thanks for your post...this is why I originally did not post the code because people like you want to just code bash instead of helping! Show quote "--CELKO--" wrote: > Why did you destroy Standard SQL behavior? Why are there more NULLs in > one table than should be in an entire Fortune 500 accounting package? > Do you really have a lot of data elements that are in Chinese and 255 > characters long? Why do you have data type prefixes on variable names, > which is a violation of both good programming and ISO-11179? Why do you > have numeric data elements in strings? What kind of total garbage are > trying to get with things like "weight VARCHAR(50)", "@phonetype > VARCHAR(50)", etc. And you don't seem to be aware of floating > point rounding errors (does your machine have a floating point > processor, or do you want to slow things down with a software floating > point package?) > You keep height in inches or cm then convert it for display. You do > not do this in the database. The syntax for CAST is CAST (<exp> AS > <datatype>) -- do not use the proprietary CONVERT(). > You are NOT writing SQL at all, but some kind of 3GL, using SQL for it. > But even worse, you did absolutely no design or research on the data. > > Other products have a MERGE or UPSERT statement to do this. The usual > pattern in older products is: > > BEGIN > -- insert the new rows > INSERT INTO Foobar > SELECT * > FROM WorkingData AS W > WHERE W.keycol > NOT IN (SELECT keycol FROM Foobar); > -- update the rows are already there > UPDATE Foobar > SET <column> > = (SELECT col FROM WorkingData AS W > WHERE W.keycol = Foobar.keycol) > WHERE keycol IN (SELECT keycol FROM WorkingData); > > END; > >  |
|||||||||||||||||||||||
Other interesting topics