Help with a linked server query.
-
I am in the process of writing a script which gives me all database details
on a server. I am trying to get this work on multiple servers when i have a
linked server set up to all my other servers. In this query i have an
insert in a cursor that deosnt work when a linked server is used as it
shows up error as 'insert doesnt work in distributed transaction". Heres
the code i am playing with. Can anyone show me some way of modifying the existing code to work on all servers and collect all the result set in a one table. any help will be greatly appreciated.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MySpaceUsed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_MySpaceUsed]
GO
create proc usp_MySpaceUsed
as
set nocount on
declare @LocalErr int
declare @sql varchar(500)
declare @SrvNm sysname
declare @DbNm sysname
declare @FileNm sysname
declare @Size int
declare @Groupid int
declare @FilePath varchar(200)
declare @FgNm sysname
declare @IndexNm sysname
declare @TableNm sysname
declare @indid int
declare @dpages int
declare @reserved int
declare @used int
if exists (select * from master.dbo.sysobjects where name = 'tblFileLogs')
BEGIN
truncate table master.dbo.[tblFileLogs]
end
else
begin
create table master.dbo.tblFileLogs
(
[Date] smalldatetime default getdate(),
DbNm varchar(200) not null,
FgNm varchar(200) not null,
FileNm varchar(200) not null,
FilePath varchar(200) not null,
SizePages int not null,
UsedPages int not null
constraint PK_tblFileLogs primary key ([Date],DbNm,FgNm,FileNm)
)
end
-- Record free space on drives
create table #Files
(
[filename] sysname,
int,
[filepath] varchar(200),
groupname varchar(50)
)
-- declare c1 cursor for
-- select srvname from sysservers
-- where srvname not in ('repl_distributor')
-- open c1
-- fetch c1 into @SrvNm
-- while @@fetch_status=0
-- begin
--
-- SET @SrvNm = '[' +rtrim(@SrvNm) + ']'
--
-- SELECT @SrvNm
-- Interate over Db-names
declare c2 cursor for
select [name] from master.dbo.sysdatabases
--where has_dbaccess([name])=1
open c2
fetch c2 into @DbNm
while @@fetch_status=0
begin
insert #Files exec (
'use ' + @DbNm + ' '
+ 'select F.[name],F.,F.[filename],G.groupname
from ' + @DbNm + '.dbo.sysfiles F left join ' + @DbNm + '.dbo.sysfilegroups G
on F.groupid=G.groupid'
 
-- Record filesizes for all dbs on this server
declare c3 cursor for
select * from #Files
open c3
fetch c3 into @FileNm,@Size,@FilePath,@FgNm
while @@fetch_status=0
begin
select @sql =
'use '+ RTRIM(@DbNm)
+ ' insert master.dbo.tblFileLogs ([Date],DbNm,FgNm,FileNm,FilePath,SizePages,UsedPages)'
+ ' values ('
+ 'getdate(),'
+ '''' + RTRIM(@DbNm) + ''','
+ '''' + isnull(@FgNm,'LOG') + ''','
+ '''' + RTRIM(@FileNm) + ''','
+ '''' + RTRIM(@FilePath) + ''','
+ cast(@Size as varchar(10)) + ',CAST(FILEPROPERTY(''' + RTRIM(@FileNm) + ''', ''SpaceUsed'') as int)/8)'
exec (@SQL)
fetch c3 into @FileNm,@Size,@FilePath,@FgNm
end
deallocate c3
delete from #Files
fetch c2 into @DbNm
end
deallocate c2
drop table #Files
-- fetch c1 into @SrvNm
-- end
-- deallocate c1
select * from master.dbo.tblFileLogs
go
-- END OF SCRIPT
-
use four part name like server.db.owner.object
open c2
fetch c2 into @DbNm
while @@fetch_status=0
begin
insert #Files exec (
'select F.[name],F.,F.[filename],G.groupname
from ' + @SrvNm + '.' + @DbNm + '.dbo.sysfiles F left join ' + @SrvNm + '.' + @DbNm + '.dbo.sysfilegroups G
on F.groupid=G.groupid' )
-
My whole point of the question is i know how to create a cursor for checking servers but beacuse an insert happens within a ditributed transaction it fails. any solution to this???
-
Modified on top of your post. The cursor is not the problem.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MySpaceUsed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_MySpaceUsed]
GO
create proc usp_MySpaceUsed
as
set nocount on
declare @sql varchar(4000)
declare @SrvNm sysname
create table #Files
(
[srname] sysname,
[dbname] sysname,
[filename] sysname,
int,
[filepath] varchar(200),
groupname varchar(50)
)
declare c1 cursor for select srvname from sysservers where srvname not in ('repl_distributor')
open c1
fetch c1 into @SrvNm
while @@fetch_status=0
begin
set @sql='select '''''+@SrvNm + ''''',''''?'''', F.[name],F.,F.[filename],G.groupname from ' +
@SrvNm + '.?.dbo.sysfiles F left join ' + @SrvNm + '.?.dbo.sysfilegroups G on F.groupid=G.groupid'
set @sql = @SrvNm+'.master.dbo.sp_msforeachdb ''' + @sql + ''''
insert #Files exec (@sql)
fetch c1 into @SrvNm
end
close c1
deallocate c1
select * from #Files
drop table #Files
go
-- END OF SCRIPT
exec usp_myspaceused
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply