asked
I need to populate with table
I need to populate table with data using a stored procedure using a cursor. Basically, I have a table with the following columns: ServerName, DBName, State. I am actually needing to monitor database state.
Can you please explain what you're actually trying to do? Are you trying to create a table which holds ServerName, DBName and State? You could use the below to load that. Why do you need a CURSOR? Do you have linked servers? SELECT @@servername AS 'ServerName' , name AS 'DatabaseName' , state_desc AS 'State' FROM sys.databases
CREATE procedure [dbo].[getDatabaseState_usp] as begin IF OBJECT_ID('DatabaseState') IS NOT NULL truncate table DatabaseState; -- drop table SQLInfo ELSE create table DatabaseState ( serverName varchar(50), DBName varchar(50), [State] int ) declare @retval int; declare @colName nvarchar(228); declare @execCol sysname; declare @tablePara varchar(250); declare @sql nvarchar(4000); declare vcList_cursor cursor FAST_FORWARD for select ServerName from LinkedServerInfo where ServerType = 'MSSQL'; open vcList_cursor; fetch from vcList_cursor into @colName; while @@FETCH_STATUS=0 begin begin try exec @retval = sys.sp_testlinkedserver @servername=@colName; if @retval=0 begin set @tablePara = @colName+'.msdb.dbo.DatabaseState'; set @sql = 'insert into DatabaseState( serverName , DBName , State) select * from '+ @tablePara; exec(@sql); end -- end connection end try begin catch print 'error'+@colName; end catch fetch next from vcList_cursor into @colname; end close vcList_cursor; deallocate vcList_cursor;
15 People are following this question.
