Docstoc

set nocount on - TechTarget

Document Sample
set nocount on - TechTarget Powered By Docstoc
					set nocount on
go
-----------------------------------------------------------------------
-- Create a table that will hold the SQL Server names in tempdb:
-----------------------------------------------------------------------
if exists (select 1 from tempdb..sysobjects where type = 'U'
           and name = 'tblInstances')
drop table tempdb..tblInstances
go
create table tempdb..tblInstances (InstanceName varchar(255) not null)
go
-- Insert all the SQL instances names (if more than one instance
-- on the same computer, no need to run this more than on one instance:
insert into tempdb..tblInstances values ('SQL_1')
insert into tempdb..tblInstances values ('Server_1\SQL_2')
insert into tempdb..tblInstances values ('SQL_3')
insert into tempdb..tblInstances values ('SQL_30')
...
go
declare @InstanceName    varchar(400), @cmd varchar(1000)
-----------------------------------------------------------------------
-- For each server - run the delete statement
--
-----------------------------------------------------------------------
-- I will insert the results of the xp_cmdshell procedure to a temp
-- table so the output will be kept for errors analysis
create table #tmp (id int identity,a varchar(4000))

declare curs_srvr cursor for select rtrim(ltrim(InstanceName))
from tempdb..tblInstances
open curs_srvr
fetch curs_srvr into @InstanceName
WHILE @@FETCH_STATUS = 0
begin
      -- Run a command on the Remote server using osql
      -- (no need to add a Linked Server).
      -- Restriction: the Login running this script should be defined
      -- the same way on all the servers.
      -- Note: the -E can be replaced by -U and -P switches,
      --        but it is more secured to use the Windows Authentication
      --        because no password is hard-coded.
      -- The command will run using xp_cmdshell to erase the OS file:
      select @cmd = 'osql -E -S"' + @InstanceName + '" -Q "set nocount
on;exec master..xp_cmdshell ''del c:\temp\michelle.trc''"'
      print @cmd
      insert into #tmp (a) exec master..xp_cmdshell @cmd
    fetch curs_srvr into @InstanceName
end
deallocate curs_srvr
-- Show results. We should verify that there are no errors:
select * from #tmp
drop table #tmp

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:3/22/2013
language:Unknown
pages:1