November 9, 2005 at 1:59 pm
Hi all,
I am trying to use the results of a proc in VB using record set and command object. With SQL authentication, everything works fine. But with nt authentication, i get the error
Operation is not allowed when the object is closed.
Here is my stored procedure
CREATE procedure dbo.SMRICREATE
@tname sysname, /* if null - stored procedure will be recreated for all of the "_SMDBA_" tables */
@ri_type smallint = 3, /* @ri_type & 1 - create RI...INS, @ri_type & 2 - create RI...DEL */
@ri_all smallint = 1, /* 1 - create RI...DEL for all tables involved (has meaning only if (@ri_type & 2) = 2) */
@do_out smallint = 1 /* 1 - just do output of a source w/o actual creation */
as
--==
SET NOCOUNT ON
declare
@error integer,
@rowcount integer,
@stat integer,
@xact_table smallint
declare @ParamDef nvarchar(300)
declare @Database nvarchar(80)
set @Database = DB_NAME()
declare @collatename nvarchar(500)
Select @collatename = convert(nvarchar(128),databasepropertyex(''+@Database+'','collation'))
if @@error != 0
goto error_exception
declare
@line integer,
@proc_buf nvarchar(2000)
create table #proc_buf
(line integer,
proc_txt nvarchar(2000))
create index #proc_buf_idx on #proc_buf(line)
declare c_smtbls cursor for
select SYSTBLNAME from dbo.SMSYSTABLES
--==
set xact_abort on -- will abort the whole transaction if error is raised ??
set nocount on
if (@ri_type & 3) = 0 -- has nothing to do
return 1
select @xact_table = case when @tname is null then 0 else 1 end
if @xact_table != 1
begin
-- select @ri_all = 0 -- has no sense because anyway we are going to create stored procedures for all of them
if @ri_all != 0 -- little trick for user who doesn't know what he's doing
begin
exec dbo.RAISE_ERROR 13 --'You have no rights to perform batch build'
goto error_exception
end
open c_smtbls
if @@error != 0
goto error_cursor_exception
end
select @line = 0
if (@ri_type & 1) = 1 -- create source for RI data checking on insert/update
-->START
select @proc_buf = -- right now it's 45 characters long
'if object_id( ''tempdb..#SMRI_inserted'' ) is not null drop table #SMRI_inserted
create table #SMRI_inserted
(SEQ integer)
'
-->STOP
if (@ri_type & 2) = 2 -- create source for RI rules checking on delete
-->START
select @proc_buf = @proc_buf + -- right now it's 75 characters long
'if object_id( ''tempdb..#SMRI_closure'' ) is not null drop table #SMRI_closure
create table #SMRI_closure
(SEQ integer, REFTBL sysname, STAT smallint)
'
-->STOP
-->START
select @proc_buf = @proc_buf + -- right now it's 83 characters long
'
--===========================================================================
--'
-->STOP
insert into #proc_buf values (@line, @proc_buf)
if @@error != 0
goto error_exception
select @line = @line + 1
while (1 = 1)
begin
if @xact_table != 1
begin
fetch next from c_smtbls into @tname
if @@error != 0
goto error_exception
if @@fetch_status < 0
break
end
if @xact_table = 1 -- check and delete all foreign key definitions if they are wrong
begin
execute @stat = dbo.SMSYSCHECKFKDATA;2 0
if @@error != 0 or @stat != 0
goto error_exception
end
print @line
if (@ri_type & 1) = 1 -- create source for RI data checking on insert/update
begin
execute @stat = dbo.SMRIBUILDINSERT @tname, @line OUTPUT
if @@error != 0 or @stat != 0
goto error_exception
end
if (@ri_type & 2) = 2 -- create source for RI rules checking on delete
begin
execute @stat = dbo.SMRIBUILDDELETE @tname, @line OUTPUT
if @@error != 0 or @stat != 0
goto error_exception
if @ri_all != 0
begin
if exists (select * from sysobjects where id = object_id(N'[dbo].[reftbls]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[reftbls]
exec ('create table reftbls
(tname sysname COLLATE '+@collatename+')')
insert into reftbls
select distinct PKTABLE_NAME from dbo.SMSYSFKDATA
where FKTABLE_NAME = @tname and PKTABLE_NAME != @tname
select @error = @@error, @rowcount = @@rowcount
if @error != 0
goto error_tmptbl_exception
select @stat = case when @rowcount > 0 then 1 end
while (@rowcount != 0)
begin
insert into reftbls
select distinct src.PKTABLE_NAME
from dbo.SMSYSFKDATA src join reftbls tgt
on src.FKTABLE_NAME = tgt.tname and src.PKTABLE_NAME != @tname
where
not exists(select null from reftbls where tname = src.PKTABLE_NAME)
select @error = @@error, @rowcount = @@rowcount
if @error != 0
goto error_tmptbl_exception
end
if @stat is not null
begin
declare c_tbls cursor for
select tname from reftbls
declare
@reftname sysname
open c_tbls
if @@error != 0
goto error_tmpcursor_exception
while (1 = 1)
begin
fetch next from c_tbls into @reftname
if @@error != 0
goto error_batch_exception
if @@fetch_status < 0
break
execute @stat = dbo.SMRIBUILDDELETE @reftname, @line OUTPUT
if @@error != 0 or @stat != 0
goto error_batch_exception
end
close c_tbls
deallocate c_tbls
end
drop table reftbls
end
end
-->START
select @proc_buf = -- right now it's 81 characters long
'--===========================================================================
--'
-->STOP
insert into #proc_buf values (@line, @proc_buf)
if @@error != 0
goto error_exception
select @line = @line + 1
if @xact_table = 1
break -- done
end
if @xact_table != 1
close c_smtbls
deallocate c_smtbls
if (@ri_type & 1) = 1 -- create source for RI data checking on insert/update
-->START
select @proc_buf = -- right now it's 27 characters long
'
if object_id( ''tempdb..#SMRI_inserted'' ) is not null drop table #SMRI_inserted
'
-->STOP
if (@ri_type & 2) = 2 -- create source for RI rules checking on delete
-->START
select @proc_buf = @proc_buf + -- right now it's 26 characters long
'
if object_id( ''tempdb..#SMRI_closure'' ) is not null drop table #SMRI_closure
'
-->STOP
insert into #proc_buf values (@line, @proc_buf + '
go') -- and plus 4 characters
if @@error != 0
goto error_exception
select @line = @line + 1
---- !!! REMAINDER !!!
-- upon creation of ODS external stored procedure
-- which supposed to compile source which will
-- come from temporary table
-- put call to it right here ...
----
if @do_out != 0
select proc_txt from #proc_buf order by line
drop table #proc_buf
set nocount off
return 0
error_batch_exception:
close c_tbls
error_tmpcursor_exception:
deallocate c_tbls
error_tmptbl_exception:
drop table reftbls
error_exception:
close c_smtbls
error_cursor_exception:
deallocate c_smtbls
drop table #proc_buf
return 1
--==
GO
Here is the piece of code from VB
ADORecSet1.CursorLocation = adUseClient
ADORecSet2.CursorLocation = adUseClient
sqlstr1 = "Select SYSTBLNAME from SMSYSTABLES"
ADORecSet1.Open sqlstr1, ADOConn
If ADORecSet1.RecordCount > 0 Then
For rowcount = 0 To ADORecSet1.RecordCount - 1
Dim CmdObj As New Command
CmdObj.ActiveConnection = ADOConn
CmdObj.CommandText = "SMRICREATE"
CmdObj.CommandType = adCmdStoredProc
Dim ParamObj1 As Object
Dim ParamObj2 As Object
Dim ParamObj3 As Object
Set ParamObj1 = CmdObj.CreateParameter("@tname", adVarWChar, adParamInput, 100, ADORecSet1("SYSTBLNAME"))
CmdObj.Parameters.Append ParamObj1
Set ParamObj2 = CmdObj.CreateParameter("@ri_type", adSmallInt, adParamInput, -1, 1)
CmdObj.Parameters.Append ParamObj2
Set ParamObj3 = CmdObj.CreateParameter("@ri_all", adSmallInt, adParamInput, -1, 0)
CmdObj.Parameters.Append ParamObj3
Set ADORecSet2 = CmdObj.Execute
proctext = ""
Do While (Not ADORecSet2.EOF And Not IsCanceled()) - this is where it fails.
DoEvents
For k = 0 To ADORecSet2.Fields.Count - 1
proctext = proctext & vbCrLf & ADORecSet2(k).value
DoEvents
Next
ADORecSet2.MoveNext
Loop
ExecuteSql "SET QUOTED_IDENTIFIER ON"
Thanks
Haritha
November 9, 2005 at 3:31 pm
I've encountered this issue. You cannot have print statements in a stored procedure that returns a recordset to VB, otherwise the recordset will be returned "closed". Try removing the print statements (or commenting them out).
A.J.
DBA with an attitude
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply