September 7, 2010 at 1:39 pm
Hello,
I do not do a lot of programming, so i need some here. I am attaching a cursor which basically loops through different db's and runs the sql but the cursor is exiting after the db at which it is failing. lets say there are three dbs test1,test2 and test3 cursor is exiting after it fails on test2. I do not want this to exit if it fails on any one of the dbs. Also i am not able to capture the exact error, like it doesn't really show that on which db the query failed. Please change the code as necessary. Thanks in advance.
September 7, 2010 at 1:53 pm
For anyone else coming along, here's the code:
declare @database nvarchar (100),@SQLString nvarchar (max),@sql nvarchar (max),@statement nvarchar (max),@sp_executesql nvarchar (max)
declare @dbs table (DBname nvarchar (100))
set @SQLString = '
SELECT name
FROM master.sys.databases where name like ''Test%'' and database_id > 4 and is_read_only = 0
and state = 0 and is_in_standby = 0
order by name '
insert into @dbs
exec (@SQLString)
DECLARE databases CURSOR FORWARD_ONLY FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = 'alter PROCEDURE [dbo].[A_Test_Proc]
select * from dbo.table_test' ---- Script should be placed within the quotes here
SELECT @sp_executesql = quotename(@database) + '..sp_executesql'
EXEC @sp_executesql @sql
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases
I don't think I've ever seen such a love for dynamic sql as what I see here!
I've modified the code... it's a bit simpler, and includes the missing "AS" after the create procedure. Note that both code (yours and mine) assumes that this procedure already exists (ALTER PROC).
declare @database nvarchar (100),
@sql nvarchar (max)
DECLARE databases CURSOR FORWARD_ONLY FOR
SELECT name
FROM master.sys.databases
where name like 'Test%'
and database_id > 4
and is_read_only = 0
and state = 0
and is_in_standby = 0
order by name;
OPEN databases;
FETCH NEXT FROM databases INTO @database;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'USE ' + QuoteName(@database) + N';alter PROCEDURE [dbo].[A_Test_Proc] AS
select * from dbo.table_test;';
exec sp_executesql @sql;
FETCH NEXT FROM databases INTO @database;
END;
CLOSE databases;
DEALLOCATE databases;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 13, 2010 at 12:36 pm
WayneS (9/7/2010)
For anyone else coming along, here's the code:
declare @database nvarchar (100),@SQLString nvarchar (max),@sql nvarchar (max),@statement nvarchar (max),@sp_executesql nvarchar (max)declare @dbs table (DBname nvarchar (100))
set @SQLString = '
SELECT name
FROM master.sys.databases where name like ''Test%'' and database_id > 4 and is_read_only = 0
and state = 0 and is_in_standby = 0
order by name '
insert into @dbs
exec (@SQLString)
DECLARE databases CURSOR FORWARD_ONLY FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = 'alter PROCEDURE [dbo].[A_Test_Proc]
select * from dbo.table_test' ---- Script should be placed within the quotes here
SELECT @sp_executesql = quotename(@database) + '..sp_executesql'
EXEC @sp_executesql @sql
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases
I don't think I've ever seen such a love for dynamic sql as what I see here!
I've modified the code... it's a bit simpler, and includes the missing "AS" after the create procedure. Note that both code (yours and mine) assumes that this procedure already exists (ALTER PROC).
declare @database nvarchar (100),@sql nvarchar (max)
DECLARE databases CURSOR FORWARD_ONLY FOR
SELECT name
FROM master.sys.databases
where name like 'Test%'
and database_id > 4
and is_read_only = 0
and state = 0
and is_in_standby = 0
order by name;
OPEN databases;
FETCH NEXT FROM databases INTO @database;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'USE ' + QuoteName(@database) + N';alter PROCEDURE [dbo].[A_Test_Proc] AS
select * from dbo.table_test;';
exec sp_executesql @sql;
FETCH NEXT FROM databases INTO @database;
END;
CLOSE databases;
DEALLOCATE databases;
Thanks Wayne. Couple things:
i) Your code doesn't work
ii) My code works fine, only condition is that if it fails on any one of the db's it is exiting out of the cursor. I would like to just ignore the part where it failed and move to next statement.
Thanks
September 13, 2010 at 2:05 pm
Slight modifications and it works for me
Add the BEGIN TRY, END TRY, BEGIN CATCH and END CATCH to your code. I added to Wayne S code and tested OK for me.
DECLARE databases CURSOR FORWARD_ONLY FOR
SELECT name
FROM master.sys.databases
where database_id > 4
and is_read_only = 0
and state = 0
and is_in_standby = 0
order by name;
OPEN databases;
FETCH NEXT FROM databases INTO @database;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'USE ' + QuoteName(@database) + N';alter PROCEDURE [dbo].[A_Test_Proc] AS
select * from dbo.table_test;';
--PRINT @sql -- used during testing only
BEGIN TRY -- added
exec sp_executesql @sql;
END TRY -- added
BEGIN CATCH --added
PRINT 'Error with ' + @sql -- Used during testing only
END CATCH --added
FETCH NEXT FROM databases INTO @database;
END;
CLOSE databases;
DEALLOCATE databases;
September 13, 2010 at 4:16 pm
Thanks. I have used try and catch and works for me now. I have created another topic on how to hightlight the print errormsg(). Thanks
September 14, 2010 at 7:43 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply