Cursors

[nealgraves]

I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.

The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.

The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message:
Server: Msg 3910, Level 16, State 1, Line 1
Transaction context in use by another session.
[OLE/DB provider returned message: Unspecified error]

There seems to be a connection issue. Can someone help me work around this?

Thank you,

Neal


FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbowner varchar(50),
dbid smallint,
createdate datetime,
status varchar(75) )
declare @servername varchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>> INSERT INTO ##dbtemp
>>> exec (@servername + '.master..sp_helpdb&#39
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update


SECOND PROCEDURE (see >>>> to note problem area):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbowner varchar(50),
dbid smallint,
createdate datetime,
status varchar(75) )
declare @servername varchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>> INSERT INTO ##dbtemp
>>>> exec (@servername + '.master..sp_helpdb&#39
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp

[nealgraves]

I made an error. I posted the same procedure twice. I am very sorry. Here are the two procedures.

Neal


FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbowner varchar(50),
dbid smallint,
createdate datetime,
status varchar(75) )
declare @servername varchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>> INSERT INTO ##dbtemp
>>> exec (@servername + '.master..sp_helpdb&#39
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update

truncate table dbmaintplans
CREATE TABLE ##dbtemp ( plan_name VarChar(100),
plan_id char (50) )
declare @servername varchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>> INSERT INTO ##dbtemp
>>>> Exec('select plan_name, plan_id from ' + @servername + '.msdb.dbo.sysdbmaintplans&#39
insert into dbmaintplans (plan_id, plan_name)
select plan_id, plan_name from ##dbtemp
update dbmaintplans set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbmaintplans.sqlservers_id is null
update dbmaintplans set whenupdate = getdate()
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp