Get Backup Directory

I am writing a stored procedure, and I want to set a variable to the default Backup Directory for the current instance. (7.0 or SQL2000).

What is the Standard"way of doing this

Do I have to write a C program that calls HRESULT GetBackupDirectory(SQLDMO_LPBSTR pRetVal);

Or should I install DtReg.exe on all my servers?

/* get default backup location -- by Bob Sturnfield */
--DtReg.exe can be found at http://www.tamedos.com/downloads
set nocount on
declare @.string varchar(4000),
@.regloc varchar(100),
@.BackupDirectory varchar(1000),
@.servernm varchar(30)

select @.regloc='MSSQLServer'
select @.servernm=rtrim(convert(varchar(30),SERVERPROPERTY ('servername')))
if CHARINDEX('\', @.servernm)>0
select @.regloc='Microsoft SQL Server\' + substring(@.servernm, CHARINDEX('\', @.servernm)+1, 30)

create table #DtReg( BackupDirectory varchar(4000))

select @.string='xp_cmdshell ''DtReg -ListValue "HKEY_LOCAL_MACHINE\Software\Microsoft\' +
@.regloc + '\MSSQLServer\BackupDirectory"'''

insert into #DtReg exec(@.string)

select top 1 @.BackupDirectory=substring(BackupDirectory,8,1000) from #DtReg
Where BackupDirectory like 'REG_SZ%'

if @.@.rowcount<>1
Select * from #DtReg

drop table #DtReg

print @.BackupDirectoryHave you looked at xp_regread ?

exec xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'BackupDirectory'

Value Data
------------------------
BackupDirectory C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP|||Thank you,

xp_regread -- not in books on-line

http://www.4guysfromrolla.com/webtech/101499-1.shtml

There's a little known xp procedure in the master database called xp_regread. This stored procedure accepts three parameters. The first one being the root key, next is the path to the key, and finally the key value you are looking to return.

http://www.swynk.com/friends/green/xp_reg.asp

xp_regread [@.rootkey=]'rootkey', [@.key=]'key'[, [@.value_name=]'value_name'][, [@.value=]@.value OUTPUT]

xp_regwrite [@.rootkey=]'rootkey', [@.key=]'key', [@.value_name=]'value_name', [@.type=]'type', [@.value=]'value'

http://www.sql-server-performance.com/ac_extended_stored_procedures.asp

These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

This is great, I very much appreciate the response

Bob Sturnfield

/* get default backup location -- by Bob Sturnfield */
set nocount on
declare @.regloc varchar(100),
@.BackupDirectory varchar(1000)

select @.regloc=
'Software\Microsoft\MSSQLServer\MSSQLServer'

if CHARINDEX('\\', @.@.servername)>0
select @.regloc='Software\Microsoft\Microsoft SQL Server\' +
substring(@.@.servername, CHARINDEX('\', @.@.servername)+1, 30)+ '\MSSQLServer'

execute master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key=@.regloc, @.value_name='BackupDirectory',
@.value=@.BackupDirectory OUTPUT

print @.BackupDirectory

Get active directory users from SQL Query Analyser

Get active directory users from SQL Query Analyser
--
I am trying to do a select in SQL Query Analyser to get the list of users in
active directory.
I use the following code:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
But appears the following message error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
What could be?
Tks.Try moving the ORDER BY clause outside of OPENQUERY. There is also something
a little strange in the outside SELECT; maybe a typo?
SELECT [Name],SN -- Why was this here? :[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
ORDER BY Name
--
"Rui Oliveira" wrote:

> Get active directory users from SQL Query Analyser
> --
> I am trying to do a select in SQL Query Analyser to get the list of users
in
> active directory.
> I use the following code:
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> SELECT [Name],SN[Last Name]
> FROM OPENQUERY( ADSI,
> 'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
> DC=domainname,DC=com''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
> name')
> But appears the following message error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> What could be?
> Tks.
>