May 1, 2008 at 11:01 am
Is there an "on error resume" function in mssql? I have a script that connects to every database. But, when I cannot connect (or run a query against it) the script breaks and ends. I simple want to continue on throughout
Thanks!
May 1, 2008 at 11:18 am
Try this (I did npot test it but it may work in your situation):
begin try
-- script to connect to your db
end try
begin catch
-- nothing
end catch
May 1, 2008 at 11:27 am
Ah, recieving an error "incorrect syntax near 'catch'. I'm guessing this is a 2005 function. 99% of the servers will be for 2000.
May 1, 2008 at 11:28 am
Here is the script. It errors when it cannot connect to the database b/c, say it is in READ ONLY or restoring.... I just need to skip it and go on.
sp_grantlogin 'NAME'
DECLARE @userName sysname,
@dbName sysname
DECLARE dbCursor CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT name
FROM master.dbo.sysdatabases
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'The current database is [' + @dbName + ']:'
EXECUTE ('exec ' + @dbName+ '..sp_grantdbaccess @loginame=''NAME'' ')
FETCH NEXT FROM dbCursor
INTO @dbName
END
CLOSE dbCursor
DEALLOCATE dbCursor
GO
May 1, 2008 at 12:40 pm
Ah sorry, I thought you're asking about 2005. I don't think you can do anything with 2000 for what you need.
May 1, 2008 at 2:48 pm
In your cursor declaration, change the select statement to:
[font="Courier New"]SELECT name
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply