March 11, 2007 at 6:19 am
I have a stored procedure in my SQL Server 2000 database that has a large number of different queries (about 200 or so all up) over which a cursor iterates. This procedure works perfectly in the Query Analyser. However, when I attempt to run this stored procedure in Enterprise Manager in a SQL query pane or through a SQL Server Agent job, it fails to complete.
There are no error messages and the error status is 0. But not all of the queries are executed. It simply seems to stop partway through. By varying the number of queries I can get it to stop in different places. To me this suggests that the query has a timeout limit or a limit on the number of queries or something like that. However, I can't figure out what or where that limit is. I should point out that in the Query Analyzer, the whole query takes less than 2 seconds anyway.
I have also tried splitting my large stored procedure into several smaller procedures to no effect.
Anyone out there got any idea why or how this is happening?
March 11, 2007 at 8:36 am
The only time I saw something like this is when I coded something in QA (with some static values instead of parameters). Then forgetting to change them once going to a sp.
Other than that, assuming everything is the same it will work as an sp (please correct if your experience says otherwise).
March 11, 2007 at 6:33 pm
That would make sense. Unfortunately, it doesn't apply in this case as I wrote it as a static procedure first. Then when it didn't work, I did a copy/paste, removed the create procedure statement at the top and ran the query (no parameters for the sp). And it worked fine at that point.
March 11, 2007 at 8:55 pm
Can you post the code?
March 12, 2007 at 8:41 am
Review the code on the DB to make sure it's all there. You're not passing the contents of the SP thru a variable by any chance? Remember that variables have an 8K limit.
March 12, 2007 at 9:12 pm
The code in the sp is below.
CREATE PROCEDURE MapCandidateData
AS
DECLARE @Cmd varchar (2000)
DECLARE @cris_id int, @office_id int, @title varchar (10), @firstname varchar(255), @lastname varchar(255),
@homephone varchar(255), @workphone varchar(255), @mobile varchar(255), @email varchar(255), @address1 varchar(255),
@address2 varchar(255), @suburb varchar(255), @state varchar(255), @postcode varchar(255), @dob datetime,
@visaexpire datetime, @residency varchar(255), @worktype varchar(255), @edu_level int, @sk_shorthand smallint,
@sk_dictaphone smallint, @sk_meetgreet smallint, @sk_switchsml smallint, @sk_switchlrg smallint, @sk_msword smallint,
@sk_perfect smallint, @sk_lotpro smallint, @sk_excel smallint, @sk_lot123 smallint, @sk_quattro smallint, @sk_access smallint,
@sk_fpro smallint, @sk_works smallint, @sk_act smallint, @sk_psoft smallint, @sk_outlook smallint, @sk_gwise smallint,
@sk_lotorg smallint, @sk_outlookexp smallint, @sk_notes smallint, @sk_proj smallint, @sk_onenote smallint, @sk_ppoint smallint,
@sk_pmaker smallint, @sk_photo smallint, @sk_wppresent smallint, @sk_acro smallint, @sk_fpage smallint, @sk_visio smallint,
@sk_illus smallint, @sk_dream smallint, @sk_pub smallint, @sk_myob smallint, @sk_sage smallint, @sk_pastel smallint,
@sk_sap smallint, @sk_quicken smallint, @sk_gplains smallint, @sk_dyna smallint, @sk_pronto smallint, @sk_jde smallint, @sk_oracle smallint,
@sk_triumph smallint, @sk_accpay smallint, @sk_trlbal smallint, @sk_profloss smallint, @sk_bsheet smallint, @sk_pay smallint,
@sk_accrec smallint, @sk_cash smallint, @sk_inv smallint, @sk_bankrecs smallint, @sk_credit smallint, @sk_genledg smallint,
@sk_purch smallint, @sk_stock smallint, @sk_stats smallint, @sk_finrep smallint, @sk_fore smallint, @sk_manrep smallint,
@sk_cost smallint, @sk_budget smallint, @sk_audit smallint, @sk_ca smallint, @sk_aca smallint, @sk_cpa smallint, @sk_acca smallint,
@sk_cima smallint, @sk_otheracc smallint, @sk_qual smallint, @sk_pqual smallint, @sk_accexp smallint, @otherskills varchar(8000),
@emp1company varchar(255), @emp1position varchar(255), @emp1from datetime, @emp1to datetime, @emp1comments varchar(8000),
@emp2company varchar(255), @emp2position varchar(255), @emp2from datetime, @emp2to datetime, @emp2comments varchar(8000),
@emp3company varchar(255), @emp3position varchar(255), @emp3from datetime, @emp3to datetime, @emp3comments varchar(8000)
DECLARE @title_id int, @residential_id int, @candidate_id int, @worktype_id int, @skill_id int
DECLARE import_cursor CURSOR READ_ONLY FOR
SELECT * FROM mySQLImport
OPEN import_cursor
FETCH NEXT FROM import_cursor
INTO @cris_id, @office_id, @title, @firstname, @lastname, @homephone, @workphone, @mobile, @email, @address1,
@address2, @suburb, @state, @postcode, @dob, @visaexpire, @residency, @worktype, @edu_level, @sk_shorthand,
@sk_dictaphone, @sk_meetgreet, @sk_switchsml, @sk_switchlrg, @sk_msword,
@sk_perfect, @sk_lotpro, @sk_excel, @sk_lot123, @sk_quattro, @sk_access, @sk_fpro,
@sk_works, @sk_act, @sk_psoft, @sk_outlook, @sk_gwise, @sk_lotorg, @sk_outlookexp,
@sk_notes, @sk_proj, @sk_onenote, @sk_ppoint, @sk_pmaker, @sk_photo, @sk_wppresent,
@sk_acro, @sk_fpage, @sk_visio, @sk_illus, @sk_dream, @sk_pub, @sk_myob, @sk_sage,
@sk_pastel, @sk_sap, @sk_quicken, @sk_gplains, @sk_dyna, @sk_pronto, @sk_jde, @sk_oracle,
@sk_triumph, @sk_accpay, @sk_trlbal, @sk_profloss, @sk_bsheet, @sk_pay, @sk_accrec,
@sk_cash, @sk_inv, @sk_bankrecs, @sk_credit, @sk_genledg, @sk_purch, @sk_stock, @sk_stats,
@sk_finrep, @sk_fore, @sk_manrep, @sk_cost, @sk_budget, @sk_audit, @sk_ca, @sk_aca,
@sk_cpa, @sk_acca, @sk_cima, @sk_otheracc, @sk_qual, @sk_pqual, @sk_accexp, @otherskills,
@emp1company, @emp1position, @emp1from, @emp1to, @emp1comments,
@emp2company, @emp2position, @emp2from, @emp2to, @emp2comments,
@emp3company, @emp3position, @emp3from, @emp3to, @emp3comments
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @title_id = TitleID
FROM tblTitle
WHERE TName = @title
IF @@rowcount = 0
SELECT @title_id = null
UPDATE tblPerson
SETPersonFirstName = LEFT(@firstname,50),
PersonSurname = LEFT(@lastname,50),
PersonHomeTelephone = LEFT(@homephone,50),
PersonWorkTelephone = LEFT(@workphone,50),
PersonMobileTelephone = LEFT(@mobile,50),
PersonHomeEmail = LEFT(@email,100),
TitleID = @title_id
WHERE PersonID = @cris_id
SELECT @residential_id = ResidenceID
FROM ResidentalStatus
WHERE ResidenceDesc = @residency
IF @@rowcount = 0
SELECT @residential_id = 0
SELECT @worktype_id = JobTypeID
FROM JobType
WHERE JobTypeName = @worktype
IF @@rowcount = 0
SELECT @worktype_id = 0
SELECT @candidate_id = CandidateID
FROM tblCandidate
WHERE CandidateID = @cris_id
IF @@rowcount = 0
BEGIN
INSERT INTO tblCandidate (CandidateAddress, CandidateAddress2, CandidateCity, CandidateState,
CandidatePostcode, CandidateDateofBirth, CandidateVisaExpireDate, CandidateResidenceID,
CandidateDegreeQualified, CandidateWantedJobType, CandidateCurrentSkills, BranchID, CandidateID)
VALUES (LEFT(@address1,100), LEFT(@address2,100), LEFT(@suburb,50), LEFT(@state,20),
LEFT(@postcode,10), @dob, @visaexpire, @residential_id, @edu_level, @worktype_id, @otherskills,
@office_id, @cris_id)
END
ELSE
BEGIN
UPDATE tblCandidate
SETCandidateAddress = LEFT(@address1,100),
CandidateAddress2 = LEFT(@address2,100),
CandidateCity = LEFT(@suburb,50),
CandidateState = LEFT(@state,20),
CandidatePostcode = LEFT(@postcode,10),
CandidateDateofBirth = @dob,
CandidateVisaExpireDate = @visaexpire,
CandidateResidenceID = @residential_id,
CandidateDegreeQualified = @edu_level,
CandidateWantedJobType = @worktype_id,
CandidateCurrentSkills = @otherskills,
BranchID = @office_id
WHERE CandidateID = @cris_id
END
DELETE FROM CandExperience
WHERE CandidateID = @cris_id
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'SH', @skillvalue = @sk_shorthand
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'DICTA', @skillvalue = @sk_dictaphone
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'M&G', @skillvalue = @sk_meetgreet
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'SML S', @skillvalue = @sk_switchsml
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'BIG S', @skillvalue = @sk_switchlrg
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'MSWord', @skillvalue = @sk_msword
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'Perfect', @skillvalue = @sk_perfect
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'LOTPR', @skillvalue = @sk_lotpro
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'EXCEL', @skillvalue = @sk_excel
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'LOT123', @skillvalue = @sk_lot123
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'QUATT', @skillvalue = @sk_quattro
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ACCESS', @skillvalue = @sk_access
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'FPRO', @skillvalue = @sk_fpro
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'WORKS', @skillvalue = @sk_works
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ACT', @skillvalue = @sk_act
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PS', @skillvalue = @sk_psoft
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'OUT', @skillvalue = @sk_outlook
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'GW', @skillvalue = @sk_gwise
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ORG', @skillvalue = @sk_lotorg
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'OXPRS', @skillvalue = @sk_outlookexp
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'NOTES', @skillvalue = @sk_notes
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PROJ', @skillvalue = @sk_proj
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ONE', @skillvalue = @sk_onenote
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PP', @skillvalue = @sk_ppoint
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'APM', @skillvalue = @sk_pmaker
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PHOTO', @skillvalue = @sk_photo
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'WPP', @skillvalue = @sk_wppresent
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ACRO', @skillvalue = @sk_acro
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'FRONT', @skillvalue = @sk_fpage
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'VISIO', @skillvalue = @sk_visio
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ILLUS', @skillvalue = @sk_illus
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'DREAM', @skillvalue = @sk_dream
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PUB', @skillvalue = @sk_pub
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'MYOB', @skillvalue = @sk_myob
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'SAGEMIC', @skillvalue = @sk_sage
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PASTEL', @skillvalue = @sk_pastel
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'SAP', @skillvalue = @sk_sap
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'QUIK', @skillvalue = @sk_quicken
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'GREAT', @skillvalue = @sk_gplains
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'DYNA', @skillvalue = @sk_dyna
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PRONTO', @skillvalue = @sk_pronto
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'JDE', @skillvalue = @sk_jde
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ORAC', @skillvalue = @sk_oracle
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'TRI', @skillvalue = @sk_triumph
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'AP', @skillvalue = @sk_accpay
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'TRIAL', @skillvalue = @sk_trlbal
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'P&L', @skillvalue = @sk_profloss
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'BSHEET', @skillvalue = @sk_bsheet
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PROLL', @skillvalue = @sk_pay
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'AR', @skillvalue = @sk_accrec
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'CBOOKS', @skillvalue = @sk_cash
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'INV', @skillvalue = @sk_inv
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'RECS', @skillvalue = @sk_bankrecs
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'CC', @skillvalue = @sk_credit
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'GL', @skillvalue = @sk_genledg
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PURCH', @skillvalue = @sk_purch
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'STOCK', @skillvalue = @sk_stock
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'STATS', @skillvalue = @sk_stats
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'FINREP', @skillvalue = @sk_finrep
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'FORE', @skillvalue = @sk_fore
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'MREP', @skillvalue = @sk_manrep
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'COST', @skillvalue = @sk_cost
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'BUDGE', @skillvalue = @sk_budget
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'AUDIT', @skillvalue = @sk_audit
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'CA', @skillvalue = @sk_ca
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ACA', @skillvalue = @sk_aca
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'CPA', @skillvalue = @sk_cpa
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'ACCA', @skillvalue = @sk_acca
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'CIMA', @skillvalue = @sk_cima
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'OTHER', @skillvalue = @sk_otheracc
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'QUAL', @skillvalue = @sk_qual
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'PQUAL', @skillvalue = @sk_pqual
EXEC MapCandidateSkill @cris_id = @cris_id, @skillcode = 'YRSXP', @skillvalue = @sk_accexp
IF @emp1company IS NOT NULL AND @emp1company ''
BEGIN
INSERT INTO tblHistory (CandidateID, HFrom, HTo, HEmployer, HPosition, HComments, JobTypeID)
VALUES (@cris_id, @emp1from, @emp1to, LEFT(@emp1company,50), LEFT(@emp1position,100),
LEFT(replace (@emp1comments, '
', char(10)),250), 0)
END
IF @emp2company IS NOT NULL AND @emp2company ''
BEGIN
INSERT INTO tblHistory (CandidateID, HFrom, HTo, HEmployer, HPosition, HComments, JobTypeID)
VALUES (@cris_id, @emp2from, @emp2to, LEFT(@emp2company,50), LEFT(@emp2position,100),
LEFT(replace (@emp2comments, '
', char(10)),250), 0)
END
IF @emp3company IS NOT NULL AND @emp3company ''
BEGIN
INSERT INTO tblHistory (CandidateID, HFrom, HTo, HEmployer, HPosition, HComments, JobTypeID)
VALUES (@cris_id, @emp3from, @emp3to, LEFT(@emp3company,50), LEFT(@emp3position,100),
LEFT(replace (@emp3comments, '
', char(10)),250), 0)
END
FETCH NEXT FROM import_cursor
INTO @cris_id, @office_id, @title, @firstname, @lastname, @homephone, @workphone, @mobile, @email, @address1,
@address2, @suburb, @state, @postcode, @dob, @visaexpire, @residency, @worktype, @edu_level, @sk_shorthand,
@sk_dictaphone, @sk_meetgreet, @sk_switchsml, @sk_switchlrg, @sk_msword,
@sk_perfect, @sk_lotpro, @sk_excel, @sk_lot123, @sk_quattro, @sk_access, @sk_fpro,
@sk_works, @sk_act, @sk_psoft, @sk_outlook, @sk_gwise, @sk_lotorg, @sk_outlookexp,
@sk_notes, @sk_proj, @sk_onenote, @sk_ppoint, @sk_pmaker, @sk_photo, @sk_wppresent,
@sk_acro, @sk_fpage, @sk_visio, @sk_illus, @sk_dream, @sk_pub, @sk_myob, @sk_sage,
@sk_pastel, @sk_sap, @sk_quicken, @sk_gplains, @sk_dyna, @sk_pronto, @sk_jde, @sk_oracle,
@sk_triumph, @sk_accpay, @sk_trlbal, @sk_profloss, @sk_bsheet, @sk_pay, @sk_accrec,
@sk_cash, @sk_inv, @sk_bankrecs, @sk_credit, @sk_genledg, @sk_purch, @sk_stock, @sk_stats,
@sk_finrep, @sk_fore, @sk_manrep, @sk_cost, @sk_budget, @sk_audit, @sk_ca, @sk_aca,
@sk_cpa, @sk_acca, @sk_cima, @sk_otheracc, @sk_qual, @sk_pqual, @sk_accexp, @otherskills,
@emp1company, @emp1position, @emp1from, @emp1to, @emp1comments,
@emp2company, @emp2position, @emp2from, @emp2to, @emp2comments,
@emp3company, @emp3position, @emp3from, @emp3to, @emp3comments
END
CLOSE import_cursor
DEALLOCATE import_cursor
GO
March 12, 2007 at 9:44 pm
Assuming the code works. Is it possible you have a deadlock somewhere?
Or is it possible that you have a silent failure in the child procedures?
Do you have triggers on any tables that could rollback a transaction?
March 12, 2007 at 11:00 pm
There are definitely no triggers involved and I don't think it could be a question of deadlock or silent failure since the code works perfectly in Query Analyzer and I can guarantee that I was the only one accessing the db at the time.
March 13, 2007 at 5:42 am
I've pretty much run out of options at this point. The best way you have to see the problem is to use the debug option in query analyser and go through the proc step by step. That will show you the problem area(s) and give you an hint to see where to start.
March 13, 2007 at 6:50 am
I'd suggest some logging as well. Create a table to store IDs or PKs or just varchar data, then insert data at various points in the table to determine what is happening.
Be sure you use a date col in the log table and insert GETDATE() so you can track the ordering.
March 13, 2007 at 7:48 am
Or this would actually be an approved use of the identity propriety by our friend Joe Celko... but I wouldn't add the identity without the date. I can't explain why, but that's what I'd do at the moment.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply