[RESOLVED] default backup directory of the sql server selected

[sureshvijayan]

hello,

I have an application listing sql servers in the network... When i select a server, i need to get the default directory for backup .... how will we do ?
My application lists sqlserver 2005 and sql 2000 servers .....

thankzzzzzzz

[sureshvijayan]

hello,

I have an application listing sql servers in the network... When i select a server, i need to get the default directory for backup .... how will we do ?
My application lists sqlserver 2005 and sql 2000 servers .....

thankzzzzzzz

dear friends,

i got the answer!

[2005]
DECLARE @BACKUP_DIR VARCHAR(200)
EXECUTE master..xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER',
'BackupDirectory',
@BACKUP_DIR OUTPUT
PRINT @BACKUP_DIR

[2000]
DECLARE @BACKUP_DIR VARCHAR(200)
EXECUTE master..xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'BackupDirectory',
@BACKUP_DIR OUTPUT
PRINT @BACKUP_DIR

NOW DEAR FRIENDS I HAVE ANOTHER PROBLEM... I NEED TO EXECUTE THIS STATEMENT FROM DOTNET ....

PLEASE HELP ME !


thankzzzzzzzzzzz

[jmcilhinney]

To execute a non-query SQL statement against SQL Server you create an SqlCommand object and call its ExecuteNonQuery method.

[jmcilhinney]

Actually, looking closer it seems all that's doing is reading a registry value. To read the registry in VB use My.Computer.Registry.GetValue.

[sureshvijayan]

To execute a non-query SQL statement against SQL Server you create an SqlCommand object and call its ExecuteNonQuery method.

Ok, i have done ... but my query is returning a directory path ... and execute nonquery returns only a integer value ... i have used something like this :
**************************************************************************************************** ****************

queryString = "DECLARE @BACKUP_DIR VARCHAR(200) EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer','BackupDirectory', @BACKUP_DIR OUTPUT return";
Object returnValue;
SqlCommand sCom = sCon.CreateCommand();
sCom = sCon.CreateCommand();
sCom.CommandText = queryString;
sCom.CommandType = CommandType.StoredProcedure;
returnValue = sCom.ExecuteScalar();
MessageBox.Show(returnValue.ToString());

**************************************************************************************************** ****************

But i am getting error !!!!

[sureshvijayan]

Actually, looking closer it seems all that's doing is reading a registry value. To read the registry in VB use My.Computer.Registry.GetValue.

I may need to read different sql servers registry values ... so i used to execute this query, so i can return that machines registry values ....

[sureshvijayan]

Ok, i have done ... but my query is returning a directory path ... and execute nonquery returns only a integer value ... i have used something like this :
**************************************************************************************************** ****************

queryString = "DECLARE @BACKUP_DIR VARCHAR(200) EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer','BackupDirectory', @BACKUP_DIR OUTPUT return";
Object returnValue;
SqlCommand sCom = sCon.CreateCommand();
sCom = sCon.CreateCommand();
sCom.CommandText = queryString;
sCom.CommandType = CommandType.StoredProcedure;
returnValue = sCom.ExecuteScalar();
MessageBox.Show(returnValue.ToString());

**************************************************************************************************** ****************

But i am getting error !!!!

I GOT IT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

queryString = "Declare @Path as varchar(100);Set @Path = NULL; EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.1\\MSSQLSERVER', 'BackupDirectory', @Path OUTPUT ;Select @Path ";
Object returnValue;
SqlCommand sCom = sCon.CreateCommand();
sCom = sCon.CreateCommand();
sCom.CommandText = queryString;
sCom.CommandType = CommandType.Text;//StoredProcedure;
returnValue = sCom.ExecuteScalar();
MessageBox.Show(returnValue.ToString());


THANKZZZZZZZZZZZZZ