Cursor not looping through list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursor not looping through list

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-28 : 10:19:39

Hello all,

I have written a cursor that is inside of a stored procedure and makes use of linked servers. Each element is straight forward but I'm having a heck of a time getting them all to work together. Below is my current version. The cursor works great, but it pulls the information from only one server over and over. Obviously it is some kind of logic problem. @@Fetch_Status always equals 0 for some reason. It never advances to the next server. What needs to change here?

Here is my stored procedure code:


--The Stored Procedure
alter procedure countdb2
	--@servername sysname
as
	DROP TABLE tempDatabases
create table tempDatabases  --creates a temporary table that will house the database information
	(ServerID varchar(4000),
	DatabaseID varchar(4000),
	DatabaseName varchar(4000),
	BusinessUnit varchar(4000),
	CreationDate varchar(4000),
	DatabaseOwner varchar(4000),
	Application varchar(4000),
	Description varchar(4000),
	Status varchar(4000),
	CompatibilityLevel varchar(4000),
	SupportLevelID varchar(4000),
	InactiveFlag varchar(4000),
	OfflineDate varchar(4000),
	DatabaseVersion varchar(4000),
	DB_Size varchar(4000))
	
 DECLARE @servername varchar(400)
 DECLARE PopulateTempTable CURSOR FOR
 SELECT servername from usslcrioprod.admin.dbo.servers order by serverid
 
 OPEN PopulateTempTable
	 FETCH NEXT FROM PopulateTempTable INTO @servername
	 While @@FETCH_STATUS=0
	 BEGIN

exec ('insert into tempdatabases
(ServerID,DatabaseID,DatabaseName,CreationDate,CompatibilityLevel)

select (select serverid 
		from usslcrioprod.admin.dbo.servers s
		where servername='''+@servername+''') as serverid, dbid, name, crdate, cmptlevel
			from '+@servername+'.master.sys.sysdatabases sd')
	fetch next from PopulateTempTable
	END
Close PopulateTempTable
Deallocate PopulateTempTable

Craig Greenwood

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 10:33:32

[code]
OPEN PopulateTempTable
FETCH NEXT FROM PopulateTempTable INTO @servername
While @@FETCH_STATUS=0
BEGIN

exec ('insert into tempdatabases
(ServerID,DatabaseID,DatabaseName,CreationDate,CompatibilityLevel)

select (select serverid
from usslcrioprod.admin.dbo.servers s
where servername='''+@servername+''') as serverid, dbid, name, crdate, cmptlevel
from '+@servername+'.master.sys.sysdatabases sd')
fetch next from PopulateTempTable INTO @servername
END
Close PopulateTempTable
Deallocate PopulateTempTable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-28 : 11:46:10

Ahhhhhhh. Had that earlier and deleted it pursuing another theory. Thanks, man!

Craig Greenwood

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 12:16:37

[code]

exec ('insert into tempdatabases
(ServerID,DatabaseID,DatabaseName,CreationDate,CompatibilityLevel)

select (select serverid, dbid, name, crdate, cmptlevel
from usslcrioprod.admin.dbo.servers s
inner join usslcrioprod.admin.dbo.servers
ON servername = servername
)

[code]

////

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-28 : 13:01:37

quote:
Add yourself!
http://www.frappr.com/sqlteam

Why?

No, you're never too old to Yak'n'Roll if you're too young to die.

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-28 : 13:41:42

quote:
Originally posted by X002548

[code]

exec ('insert into tempdatabases
(ServerID,DatabaseID,DatabaseName,CreationDate,CompatibilityLevel)

select (select serverid, dbid, name, crdate, cmptlevel
from usslcrioprod.admin.dbo.servers s
inner join usslcrioprod.admin.dbo.servers
ON servername = servername
)

[code]

////

Brett

Brett,

What does this do exactly? Can you explain this code and why it is better or different?

Thanks

Craig Greenwood

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 14:10:11

The attempt here is eliminate the Cursor

Try this



	INSERT INTO tempdatabases 
			(ServerID, DatabaseID, DatabaseName, CreationDate, CompatibilityLevel)
	SELECT   serverid, dbid,	   name,		 crdate,	   cmptlevel
	  FROM usslcrioprod.admin.dbo.servers s
INNER JOIN usslcrioprod.admin.dbo.servers
		ON servername = servername

And you can eliminate the dynamic sql

Or just try the SELECT alone without the insert

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources