Cursor ends when I don't want it too

Question

Post reply

Cursor ends when I don't want it too

Edward Shaw

Ten Centuries

Points: 1020
More actions
April 15, 2008 at 10:01 am

#379810

Hello all
I have the following cursor below (I have to admit I am not that strong with error handling or using cursors yet, but getting there). What I really need this cursor to do is keep running even if the linked server has no access (would like it to go to the next server that is stored in a list).
I put a comment where the code breaks. It has no problem adding the linked server, but when I try to run a sql statement (the "@sql") it retruns the following message and then stops. I would love for it to continue.
Anyone have any ideas?
Error message I am getting:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
declare @SERVERNAME varchar(100)
declare @sqlls varchar(1000)
declare @sql as varchar(1000)
declare @pLinkName varchar(100)
declare @pAction int
declare @database as varchar(50)
set @database = '.master.dbo.syslogins'
set @pLinkName = 'FindUsers'
set @pAction = '1'
CREATE TABLE #TEMP2
(UserNames varchar(100)
, SERVERNAME VARCHAR(100))
CREATE TABLE #TEMP1
(SERVERNAME varchar(100))
DECLARE SERVERNAME_CURSOR CURSOR FOR
SELECT TOP 2 SERVERNAME
FROM DATA_MGMT_DB.DBO.SERVERLIST
WHERE domain = 'iac_nt'
ORDER BY SERVERNAME
OPEN SERVERNAME_CURSOR
FETCH NEXT FROM SERVERNAME_CURSOR into @SERVERNAME
WHILE @@FETCH_STATUS = 0
BEGIN
select @sqlls = 'exec data_mgmt_db.dbo.uspTICLinkServer ''' + @pLinkName + ''',''' + @SERVERNAME + ''', 1 '
exec(@sqlls)
-- CODE BREAKS HERE IF SQL SERVER DOES NOT EXIST OR DON'T HAVE ACCESS
select @sql = 'insert into #temp2 select name, '''+@SERVERNAME+''' from FindUsers.master.dbo.syslogins '
exec (@sql)
FETCH NEXT FROM SERVERNAME_CURSOR into @SERVERNAME
END
CLOSE SERVERNAME_CURSOR
DEALLOCATE SERVERNAME_CURSOR

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Have you got a server called "FindUsers"??? That's where the error is on my machine...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Edward Shaw Ten Centuries Points: 1020 More actions · Answer 2

My apologies. The "FindUsers" server is a linked server that is setup already. I believe if you set up a linked server to a server that you know is down then you should get the same error that I get.

Thanks for looking into this.