Closed Recordset returned?? WHY?
-
Hi, I have an ADO command object that calls a stored procedure passing in two parameters.
when I call this stored procedure from query analyer it dutifully shows me the results set in the grid area.
When I call this stored procedure from ADO it doesn't error out but it reports rs.fields.length=0 and anything I try to do to it returns a "can't when object is closed" error
The stored procedure runs about 7 distinct jobs, one of which inserts values to a table Variable. The last action is a select against this table variable.
Here is the proc in psuedo_code:
set nocount on
begin tran
--step1
do stuff
--step2
do stuff
--Step3
Do stuff inserting into @tblVar
--Step4
Do yet more stuff
SET NOCOUNT OFF
Commit or rollback
select * from @tblVar
GO
That's about it, and as I say in query analyer I get a result set but via ADO I don't
The command object documentation says:
>>If it is a row-returning query, the results are stored in a new Recordset object. If it is not a row-returning query, the provider will return a closed Recordset object<<
Could ADO somehow think it's a non-rowreturning operation?
I'm not sure if the problem is in my T-SQL or my ADO.
Here is the whole proc for those that have the stomach for it:
CREATE proc sprocket_copycourse
@srcCourseID int,
@trgClientID int
as
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
declare @SampleFilesDir varchar(100)
declare @MediaFilesDir varchar(100)
declare @ClientsDir varchar(100)
declare @SampleCourseID int
set @SampleCourseID = 78
declare @trgCourseID int
declare @srcClientDir varchar(50)
declare @trgClientDir varchar(50)
select @srcClientDir = (SELECT dbo.Client.directory FROM dbo.Course INNER JOIN dbo.Client ON dbo.Course.client_fk = dbo.Client.uid WHERE (dbo.Course.uid = @srcCourseID))
select @trgClientDir = (select dbo.Client.directory from dbo.Client where uid = @trgClientID)
begin tran
--step 1
insert into course SELECT @trgClientID, CourseName, TimeLimit, owner, CourseStatus_fk, toppic, toppich, toppicw, toppicalt, bottompic, bottompich, bottompicw, bottompicalt,
courseusername, coursepassword, hidecourseresults, showcoursereport, showcoursesignoff, completedlockout, description, intro, conclusion,
passpercent, showquestionpicture, showanswerpicture, autoplayansweraudio, autonext, moreinfo, askdemoquestions, publicmodule, contact,
recordtimetaken, reportsintrotext, insequence, insequencepass, shortname, author_fk, madelivedate, enrollreqd, fee, enrolluses, enrolldays,
usercategory, version, HideConclusionPageUntilDone, navBackground1, navBackground2, navText, navActionTextOver, isresources, mentor_fk,
shortdescription, sequence
FROM dbo.Course where uid = @srcCourseID
set @trgCourseID = @@identity
--copy resources
insert into courseresources SELECT @trgCourseID, media_fk FROM dbo.courseresources WHERE (course_fk = @srcCourseID)
--step 2
DECLARE @chpID int
declare @trgChapterID int
declare @prmChapterID int
declare @srcMediaID int
declare @trgMediaID int
declare @srcMediaExt varchar(4)
declare @cmd varchar(200)
declare @TableVar table (
srcMediaID int,
trgMediaID int,
srcMediaExt varchar(4) 
DECLARE chapter_cursor CURSOR FOR
SELECT uid FROM chapter
WHERE course_fk = @srcCourseID
ORDER BY chaptersequence
OPEN chapter_cursor
FETCH NEXT FROM chapter_cursor
into @chpID
WHILE @@FETCH_STATUS = 0
BEGIN
--copy a chapter at a time
EXEC sprocket_copychapter @trgCourseID,@chpID, @prmChapterID OUTPUT
set @trgChapterID = @prmChapterID
--this is for copying the sample course, should gain insight so I can copy any course between clients later.
if @srcCourseID = @SampleCourseID
BEGIN
--so if media is associated with with this chapter get info and save it to tblVar so that I can process outside of tran
if exists (select chaptermedia_fk from chapter where uid= @trgChapterID)
BEGIN
select @srcMediaID = (select chaptermedia_fk from chapter where uid= @trgChapterID)
select @srcMediaExt = (select mediaext from mediabase where uid=@srcMediaID)
--make new media row
insert into mediabase SELECT mediasize, medialength, medialocalpath, medianame, mediaremoteurl, owner_fk, mediamimetype_fk, mediaext, mediatypename, displaywidth,
displayheight, shortname, author_fk, status_fk, keywords, usercategory, quizStorage, editable
FROM dbo.mediabase where uid=@srcMediaID
set @trgMediaID = @@identity
--update the new chapter with the new mediaID
update chapter set chaptermedia_fk = @trgMediaID where uid = @trgChapterID
--store values for file copy outside of transaction
insert into @TableVar (srcMediaID,trgMediaID,srcMediaExt) values (@srcMediaID, @trgMediaID,@srcMediaExt)
END
END
--step 3
exec sprocket_copyquestion @chpID, @trgChapterID, @trgCourseID
FETCH NEXT FROM chapter_cursor
INTO @ChpID
END
CLOSE chapter_cursor
DEALLOCATE chapter_cursor
declare @successreport int
if (@@error <> 0)
set @successreport = 0
else
set @successreport = 1
SET NOCOUNT OFF
if @successreport = 0
begin
rollback
end
else
begin
commit
--if it's sample course then copy neccessary files
if @srcCourseID = @SampleCourseID
BEGIN
--update the paths of any img tags in the chapter
declare @srcImgPath varchar(100)
declare @trgImgPath varchar(100)
set @srcImgPath = '/app1/clients/'+@srcClientDir
set @trgImgPath = '/app1/clients/'+@trgClientDir
update chapter set chapterinstructions=dbo.replace_regular_expression(chapterinstructions,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update chapter set quizintroductiontext=dbo.replace_regular_expression(quizintroductiontext,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update chapter set chapterintroductiontext=dbo.replace_regular_expression(chapterintroductiontext,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update question set questiontext =dbo.replace_regular_expression(questiontext,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update question set rightanswerdescription =dbo.replace_regular_expression(rightanswerdescription,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update question set wronganswerdescription =dbo.replace_regular_expression(wronganswerdescription,@srcImgPath,@trgImgPath) where course_fk=@trgCourseID
update course set description =dbo.replace_regular_expression(description,@srcImgPath,@trgImgPath) where uid=@trgCourseID
update course set intro =dbo.replace_regular_expression(intro,@srcImgPath,@trgImgPath) where uid=@trgCourseID
END
end
SET ANSI_WARNINGS ON
SELECT * FROM @TableVar
GO
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Great article here about a problem returning a temp table from a stored procedure using OLEDB. Sure seems to fit my problem, but Im still fighting with it.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
I did a few tests:
CREATE PROCEDURE [dbo].[tblVarTest] AS
declare @TableVar table ( uid int)
insert into @TableVar (uid) values (1)
insert into @TableVar (uid) values (2)
insert into @TableVar (uid) values (3)
insert into @TableVar (uid) values (4)
select * from @tableVar
Generates the same error when called from ADO
CREATE PROCEDURE [dbo].[tblVarTest] AS
SET NOCOUNT ON
declare @TableVar table ( uid int)
insert into @TableVar (uid) values (1)
insert into @TableVar (uid) values (2)
insert into @TableVar (uid) values (3)
insert into @TableVar (uid) values (4)
select * from @tableVar
Does works with no error, however I still can't get my proc to work. I have tried placing the statment at the top, just above the final select, and in both places but I still get the error.
Any ideas welcome...
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Had a brainwave this morning that since this proc calls two other proc's I should ensure that nocount is on in them as well. I had allot of hopes but they were dashed..same error.
I am almost at wits end. Doesn't help to have a conversation with myself in this forum. Guess I'll go post in experts-exchange.com
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Leave the SET NOCOUNT ON active for the entire procedure...
At the end of your procedure you reverse course by SET NOCOUNT OFF....this will give you the typical '55 records updated' message....which is the 1st recordset ADO thinks it is receiving....
Alternatively you could do a getnextrecordset comand....and this should be the recordset containing your SELECT * FROM @Table data.
Totally seperately....I'd advise you to move away from the use of cursors...the poor performance hit is astronomical compared to doing things set-based....which is what SQLServer is designed for.
Unless you are comparing consecutative records together....99.9999% of the time there is no need for cursors and avoiding them will vastly improve the performance of your code.
-
or u can try...
SELECT * FROM @TableVar
SET NOCOUNT OFF
instead of
SET NOCOUNT OFF
...
SELECT * FROM @TableVar
HTH
Very much agree with Andrew on the last to points regarding the cursors and set-base
Sachin
Regards,
Sachin Dedhia -
Well
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
hmmm forum only giving me one line for my reply
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
thats better...
well unfortunetly i think the fetch is neccessary and in this case probable more efficient then any sql statement that if even possible would have too many joins. The fetch set is only 10 - 30 records and this proc is only called when an account is created so it shouldn't be to often compared with other operations in the app. Within the fetch loop I use the fetched value to check some conditions perform a number of inserts, an update and various selects.
So looking back at the code I originaly posted and my now working code and it's hard for me to see all the differences. I think it was a combination of setting ansi warnings off and adding for read only to the cursor declarartion and open. Also not setting nocount off again as suggested.
But now I am having another problem. I am doing a statement like this:
insert into A select replace(fld,@srcVal,@trgValue) from B where...
when I call the proc from Query analyzer it runs fine, however when I call it from ADO OLEDB the fields using the replace() are NULL ??? (using the same account)
My state of confusion returns....
So my mostly working proc now looks like this:
CREATE proc sprocket_copycourse
@srcCourseID int,
@trgClientID int
as
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
declare @SampleCourseID int
set @SampleCourseID = 78
declare @trgCourseID int
declare @srcClientDir varchar(16)
declare @trgClientDir varchar(16)
select @srcClientDir = (SELECT dbo.Client.directory FROM dbo.Course INNER JOIN dbo.Client ON dbo.Course.client_fk = dbo.Client.uid WHERE (dbo.Course.uid = @srcCourseID))
select @trgClientDir = (select dbo.Client.directory from dbo.Client where uid = @trgClientID)
declare @srcImgPath varchar(100)
declare @trgImgPath varchar(100)
set @srcImgPath = '/app1/clients/'+@srcClientDir
set @trgImgPath = '/app1/clients/'+@trgClientDir
begin tran
--step 1
insert into course SELECT @trgClientID, CourseName, TimeLimit, owner, CourseStatus_fk, toppic, toppich, toppicw, toppicalt, bottompic, bottompich, bottompicw, bottompicalt,
courseusername, coursepassword, hidecourseresults, showcoursereport, showcoursesignoff, completedlockout, replace([description],@srcImgPath, @trgImgPath),
replace(intro,@srcImgPath, @trgImgPath),conclusion, passpercent, showquestionpicture, showanswerpicture, autoplayansweraudio, autonext, moreinfo, askdemoquestions, publicmodule, contact,
recordtimetaken, reportsintrotext, insequence, insequencepass, shortname, author_fk, madelivedate, enrollreqd, fee, enrolluses, enrolldays,
usercategory, version, HideConclusionPageUntilDone, navBackground1, navBackground2, navText, navActionTextOver, isresources, mentor_fk,
shortdescription, [sequence]
FROM dbo.Course where uid = @srcCourseID
set @trgCourseID = @@identity
--copy resources
insert into courseresources SELECT @trgCourseID, media_fk FROM dbo.courseresources WHERE (course_fk = @srcCourseID)
--going to need a list of these as well...
--step 2
DECLARE @chpID int
declare @trgChapterID int
declare @prmChapterID int
declare @srcMediaID int
declare @trgMediaID int
declare @srcMediaExt varchar(4)
declare @cmd varchar(200)
declare @tblVar table (srcID int,trgID int, Ext varchar(4))
declare @chapter_cursor cursor
SET @chapter_cursor = CURSOR FOR
SELECT uid FROM chapter
WHERE course_fk = @srcCourseID
ORDER BY chaptersequence
FOR READ ONLY
OPEN @chapter_cursor
FETCH NEXT FROM @chapter_cursor
into @chpID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into chapter SELECT passrequired4complete, chaptername, chapterstatus_fk, chaptersequence, mediamimetype_fk, chaptermedia_fk, @trgCourseID,
replace(chapterinstructions,@srcImgPath,@trgImgPath), chapterconclusiontext, ap_lesson, allowcontinue, showqpic, ap_qaudio, autonext, showapic, ap_aaudio, showadesc, subtractwrong, mediaurl,
randomquestionorder, randomanswerorder, showquizresults, Prerequisitecomplete, Prerequisitepass, passpercent, shortname,
replace(chapterintroductiontext,@srcImgPath,@trgImgPath), replace(quizintroductiontext,@srcImgPath,@trgImgPath), noshowcheckanswer, noshowresults, noshowweight, noshowback, forcecorrect, savemethod_fk,
noshownext, noshownumberlist, quizlockout, noshowjumplist, onlyshowscorewhenmarked, Showpasspercentrequired, showpassfail,
showmarkername, showmarkedstatus, Showscores, Showrightanswer, showquestioncorrectstatus, showquestionscore, showmarkercomment
FROM dbo.chapter where uid =@chpID
set @trgChapterID = @@identity
if @srcCourseID = @SampleCourseID
BEGIN
--so if media is associated with with this chapter get info and save for later
select @srcMediaID = (select chaptermedia_fk from chapter where uid= @trgChapterID)
select @srcMediaExt = (select mediaext from mediabase where uid=@srcMediaID)
--make new media row
if @srcMediaID <> ''
begin
insert into mediabase SELECT mediasize, medialength, medialocalpath, [medianame], mediaremoteurl, owner_fk, mediamimetype_fk, mediaext, mediatypename, displaywidth,
displayheight, shortname, author_fk, status_fk, keywords, usercategory, quizStorage, editable
FROM dbo.mediabase where uid=@srcMediaID
set @trgMediaID = @@identity
--update the new chapter with the new mediaID
update chapter set chaptermedia_fk = @trgMediaID where uid = @trgChapterID
--store values for file copy outside of transaction
insert into @tblVar (srcID,trgID,Ext) values (@srcMediaID, @trgMediaID,@srcMediaExt)
end
END
--step 3
insert into question SELECT [sequence], shortname, questiontype_fk, replace(questiontext,@srcImgPath,@trgImgPath), answers, rightanswer, anyright, replace(rightanswerdescription,@srcImgPath,@trgImgPath), replace(wronganswerdescription,@srcImgPath,@trgImgPath),
rightanswernext, wronganswernext, weight, @trgCourseID, status_fk, qpic, @trgChapterID, qpich, qpicw, qpicalt, apic, apicfilename, apictype, apich, apicw,
apicalt, apic2, apic2h, apic2w, apic2alt, audio_question, audio_right, audio_wrong, audio_question_filename, audio_right_filename,
audio_wrong_filename, optionimage1, optionimage2, optionimage3, optionimage4, optionimagealt1, optionimagealt2, optionimagealt3,
optionimagealt4, questiongroup_fk, answerText, required
FROM dbo.Question where chapter_fk = @chpID
FETCH NEXT FROM @chapter_cursor
INTO @ChpID
END
CLOSE @chapter_cursor
DEALLOCATE @chapter_cursor
declare @successreport int
if (@@error <> 0)
set @successreport = 0
else
set @successreport = 1
if @successreport = 0
begin
rollback
end
else
begin
commit
end
select * from @tblVar
GO
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Go back to basic debugging principles.....Put in some PRINT statements before the INSERT statement to check what you think is being replaced and what you think it is being replaced with.....
-
Yes I did that. The vars contain what they are supposed to. But the proc is working fine from query analyer only not from ADO.
running this in qa performs the replaces as expected:
exec sprocket_copycourse @srcCourseID=78,@trgClientID=160
However from ASP ADO OLEDB
oCommand.activeconnection = cn
oCommand.commandtype = 4
oCommand.commandtext = "sprocket_copycourse"
oCommand.Parameters.Append oCommand.createparameter("@srcCourseID",3,1,4,78)
oCommand.Parameters.Append oCommand.createparameter("@trgClientID",3,1,4,vnewlID)
set rs = oCommand.execute
Does everything correct except that the fields inserted using the replace() are NULL. All the other fields in the inserted record are fine.
When running it from Query analyzer I log into the db with the same account I am using for the connection object.
For the life of me I can't figure out why I should get different results with the same account simply by calling the proc from a different place.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Yes I did that. The vars contain what they are supposed to. But the proc is working fine from query analyer only not from ADO.
running this in qa performs the replaces as expected:
exec sprocket_copycourse @srcCourseID=78,@trgClientID=160
However from ASP ADO OLEDB
oCommand.activeconnection = cn
oCommand.commandtype = 4
oCommand.commandtext = "sprocket_copycourse"
oCommand.Parameters.Append oCommand.createparameter("@srcCourseID",3,1,4,78)
oCommand.Parameters.Append oCommand.createparameter("@trgClientID",3,1,4,vnewlID)
set rs = oCommand.execute
Does everything correct except that the fields inserted using the replace() are NULL. All the other fields in the inserted record are fine.
When running it from Query analyzer I log into the db with the same account I am using for the connection object.
For the life of me I can't figure out why I should get different results with the same account simply by calling the proc from a different place.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
When you get different results in different environments, suspect some setting differs between the environments. Try adding this at the beginning of your stored procedure:
SET CONCAT_NULL_YIELDS_NULL OFF
--Jonathan -
Jonathan! You are the man!
SET CONCAT_NULL_YIELDS_NULL OFF
Ya know after all the other troubles I had with this procedure I was almost all outta fight. It was a fantastic learning event for me. I have stuck to basic SQL statements for so long. It's fun getting into what you can do with T-SQL.
my procedure is humming nicly and the feature is available to my users.
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
If you are doing multiple things in the stored procedure, it may actually be returning multiple recordsets and the results you are really looking for will have to be accessed using the NextRecordset property of the Recordset object.
Set up a loop similiar to the following
Do
Set Rs = Rs.NextRecordset
If Rs Is Nothing Then
Else
If Rs.State = adStateOpen Then
If Rs.EOF = False Then
End If
End If
Loop Until itemsRs Is Nothing Or foundItems = 1
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply