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

Question

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

Arpan Shinde

SSC Enthusiast

Points: 121
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=' 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 9 posts - 1 through 8 (of 8 total)

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

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

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

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

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

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

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Arpan Shinde SSC Enthusiast Points: 121 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.

Glen SSCertifiable Points: 6727 More actions · Answer 4

What's wrong with sysfiles?

This also will work in SQL 2000 (not in SQL 2005)

DECLARE @data_path NVARCHAR(260)

EXEC master.dbo.sp_MSget_setup_paths @SQL_path OUTPUT, @data_path OUTPUT

PRINT @data_Path

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

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)

Glen SSCertifiable Points: 6727 More actions · Answer 6

Arpan,

the problem with your script is in the fact that Registry location is changing for every version of SQL server. I.E. For SQL 2000; for SQL 2005 and named instance of SQL 2005 as well as for MSDE.

For SQL 2005 it is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2005\Setup\SQLPath

For instanced

HKEY_LOCAL_MACHINE\SOFTWARE\Software\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer\Setup\SQLPath

Arpan Shinde SSC Enthusiast Points: 121 More actions · Answer 7

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 8

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= ' 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

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;-)