Script should give default datapath of SQL database files... HELP require

Question

Post reply

Script should give default datapath of SQL database files... HELP require

Arpan Shinde

SSC Enthusiast

Points: 151
More actions
September 25, 2008 at 10:45 am

#123749

Following should give default datapath of SQL database files.
But there some problem in this query,i am unable make out . Can anyone help to sort out this please
It gives blank column
declare @LogFile nvarchar(500), @server varchar(50),@info nvarchar(500),@info2 nvarchar(500)
declare @Datafile nvarchar(500),@ans nvarchar(500)
--truncate table aud23sep
declare servercursor cursor for
select Server
from SQLServerMaster
order by Server
open servercursor
fetch next from servercursor
into @server
while @@fetch_status = 0
begin
select @info-2=' exec ['+@server+'].master.dbo.xp_instance_regread N'+''''+'HKEY_LOCAL_MACHINE'+''''+', N'+''''+'Software\Microsoft\MSSQLServer\MSSQLServer'+''''+
', N'+''''+'DefaultData'+''''+','+@Datafile+ 'OUTPUT'
--'exec ['+@server+'].master.dbo.xp_instance_regread N'+'HKEY_LOCAL_MACHINE'+', N'+'Software\Microsoft\MSSQLServer\MSSQLServer'+
--', N'+'DefaultLog'+','+' @Logfile'+' OUTPUT'
EXEC (@info)
select @info2= 'insert * into aud23sep'+
'SELECT ISNULL('+@Datafile+',N'+''''+@server+''''+')'
exec(@info2)
fetch next from servercursor
into @server
end
close servercursor
deallocate servercursor
select convert(varchar(40),path) ,convert(nchar(20), path) from aud23sep

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 738683

September 25, 2008 at 10:58 am

#877192

What is the problem/error or what are looking for?

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 2

Also, please don't cross post. We scan all forums. The others were deleted.

Arpan Shinde SSC Enthusiast Points: 151 More actions · Answer 3

I am doing scripting which will give default datapath of DATABASE files, in which i am already successfull. By executing following query we can get this:

declare @Datafile nvarchar(500), @LogFile nvarchar(500)

exec [GB-PB-RTS-001].master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultData', @Datafile OUTPUT

exec [GB-PB-RTS-001].master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'DefaultLog', @Logfile OUTPUT

SELECT ISNULL(@Datafile,N''), ISNULL(@Logfile,N'')

But problem is that, I want to make it such a script on executing of which from central server (where all linked server will be added ) should give default data path of all SQL servers.

But the script I had previously posted is not giving it. It gives blank cloumn, Please hel me....

Thanks.

Arpan Shinde SSC Enthusiast Points: 151 More actions · Answer 4

YEs, but in sysfiles current database file path is stored.

I require what we set in sql server properties--database settings tab-- default data path

And require both data and log file path(default)

Arpan Shinde SSC Enthusiast Points: 151 More actions · Answer 5

Yeah thats right, Registries are diffrent for diffrent verions.

But here my problem is different , I had posted the script which is for SQL Server 2000.

I have used cursors and dynamic query concept which is causing problem.

I am poor in scripting, due to which i am not able to find out exact problem.

If i execute my query without cursor and dynamic query i works perfectly.

But as i said i need to execute it from central server for all of my linked server,

i am using this concept.

Thanks 🙂

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

declare @LogFile nvarchar(500), @server varchar(50),@info nvarchar(500),@info2 nvarchar(500)

declare @Datafile nvarchar(500),@ans nvarchar(500)

--truncate table aud23sep

declare servercursor cursor for

select Server

from SQLServerMaster

order by Server

open servercursor

fetch next from servercursor

into @server

while @@fetch_status = 0

begin

select @info-2= ' exec ['+@server+'].master.dbo.xp_instance_regread N'+''''+'HKEY_LOCAL_MACHINE'+''''+', N'+''''+'Software\Microsoft\MSSQLServer\MSSQLServer'+''''+

', N'+''''+'DefaultData'+''''+','+@Datafile+ 'OUTPUT'

--'exec ['+@server+'].master.dbo.xp_instance_regread N'+'HKEY_LOCAL_MACHINE'+', N'+'Software\Microsoft\MSSQLServer\MSSQLServer'+

--', N'+'DefaultLog'+','+' @Logfile'+' OUTPUT'

PRINT @info-2

EXEC (@info)

select @info2= 'insert * into aud23sep'+

'SELECT ISNULL('+@Datafile+',N'+''''+@server+''''+')'

PRINT @info2

exec(@info2)

fetch next from servercursor

into @server

end

close servercursor

-------------------------------------------------------------

When you run above script it will actually print statements before execution

on your result pane

so by seeing that you can figure out where the prblm is exactly

deallocate servercursor

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)