Hello,I am new to writing stored procedures and my dilemna is simple, I think. I have a table of server information which is static. I am trying to create a SP that will check (among many other things) the active databases on a server and compare that to the databases in my static table. But my SP will not work. Below is my code:--The Stored Procedurealter procedure countdb3 @servername sysnameas /* declare @sql varchar(4000) */ --result of earlier attemptexecute sp_executesql select (select serverid from usslcrioprod.admin.dbo.servers where servername='+@servername+'), * from @servername.master..sysdatabases;/* set @sql= --this goes with earlier attempt'select (select serverid from usslcrioprod.admin.dbo.servers where servername='+@servername+'), *from '+@servername+'.master.sys.sysdatabases s'exec (@sql) */
I then execute the stored procedure by entering:exec countdb3 'USSLCSQLT11'
I have squirrled around with many variations of ...['+@servername+']' and @servername.master.sys.sysdatabases. All kinds of things. My goal of course is to loop through 120+ servers which will build a massive table and truly be awesome. Later I want to add in things to check job status, backup status and more. All of the things I check in a typical server review. But I have to get this working before I can continue.Help!Craig Greenwood
Wacky stored proc using a variable
Posted - 2009-07-23 : 11:02:40
