How Do I Extract A Directory Path From Stored Procedure

Question

0.00/5 (No votes)

See more:

I have a stored procedure that returns the path for the backup directory of SQL Server Instances.
All i need to do is to extract the path and store it in local variable in c#.

Stored Procedure is here:

SQL

IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories

CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))

DECLARE @regInstanceKey VARCHAR(500), @s VARCHAR(500);
DECLARE @instance VARCHAR(255)       -- current instance
DECLARE @ver VARCHAR(255)            -- MS SQL version
DECLARE @key VARCHAR(255)            -- Registry key on server side
DECLARE @value_name VARCHAR(255)     -- Registry value name
DECLARE @instance_name VARCHAR(255)  -- Key value name for the instance of SQL Server 2000
DECLARE @version INT                 -- SQL Server version (integer)

DECLARE @ic INT
SET @instance_name = ''

INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                         @key     = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
                         @vn      = 'InstalledInstances',
                         @s       = @regInstanceKey OUTPUT;

SELECT @ic = COUNT(*) FROM #instances
 WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
    SELECT @instance = InstanceName FROM #instances
    WHERE InstanceName = @@servicename
  
    SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
    IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
    ELSE SET @version = CAST(@ver AS INT)

    IF  ( @version = 8) BEGIN
        IF (@instance <> 'MSSQLSERVER' ) SET @key = 
            'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
        ELSE                             SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
        END
    ELSE IF ( @version > 8) BEGIN
        EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                                 @key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
                                 @vn = @instance,
                                 @value = @instance_name out
        SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
    END

    SET @value_name = 'DefaultData'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

    INSERT INTO #directories
    VALUES ('DefaultData', @regInstanceKey)
    SET @value_name = 'DefaultLog'

    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
    INSERT INTO #directories
    VALUES ('DefaultLog', @regInstanceKey)

    SET @value_name = 'BackupDirectory'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    INSERT INTO #directories
    VALUES ('BackupDirectory', @regInstanceKey)
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
END

SELECT * FROM #directories
SELECT * From #instances
DROP TABLE #instances
DROP TABLE #directories

What should be the code to extract the path from the sp??
Thanks

Posted 16-Jun-14 0:10am

Anshumaan Chaturvedi

Updated 16-Jun-14 0:17am

Nirav Prabtani

v2

Add a Solution

Comments

Prasad Avunoori 16-Jun-14 6:22am

Use the output parameter in sqlserver.

Anshumaan Chaturvedi 16-Jun-14 6:23am

could you elaborate please, i,m a freshmen. Don't know much about coding. It's for practice.

Prasad Avunoori 16-Jun-14 7:08am

http://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter

Anshumaan Chaturvedi 16-Jun-14 7:58am

Coudl you find any fault in this.??
using System;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TestForLogic
{
class Program
{
static void Main(string[] args)
{
string[] targetPath;

using (SqlConnection connection = new SqlConnection("Server=BNGTSIDTPC0738;User=sa;Password=login@234;Initial Catalog=master;Connection Timeout=120"))

using(SqlCommand cmd = new SqlCommand("CleanInstall", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter backUpDirectoryPath = new SqlParameter("@backUpdirectoryPath", SqlDbType.VarChar)
{
Direction=ParameterDirection.Output
};
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(backUpDirectoryPath);

connection.Open();

cmd.ExecuteNonQuery(); //it is giving an Exception . String[0] has size 0

connection.Close();

}
}
}
}

Prasad Avunoori 16-Jun-14 8:04am

Have you declared @backUpdirectoryPath output as parameter
in your stored procedure?

Anshumaan Chaturvedi 16-Jun-14 8:10am

USE [master]
GO
/****** Object: StoredProcedure [dbo].[CleanInstall] Script Date: 06/16/2014 15:54:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery9.sql|7|0|C:\Users\c5038671\AppData\Local\Temp\~vs3ACC.sql
ALTER PROCEDURE [dbo].[CleanInstall]
@backUpDirectoryPath VARCHAR (500) OUT
AS
BEGIN
IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories

CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))
DECLARE @regInstanceKey VARCHAR(500)
DECLARE @instance VARCHAR(255) -- current instance
DECLARE @ver VARCHAR(255) -- MS SQL version
DECLARE @key VARCHAR(255) -- Registry key on server side
DECLARE @value_name VARCHAR(255) -- Registry value name
DECLARE @instance_name VARCHAR(255) -- Key value name for the instance of SQL Server 2000
DECLARE @version INT -- SQL Server version (integer)

DECLARE @ic INT
SET @instance_name = ''

INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
@vn = 'InstalledInstances',
@backUpDirectoryPath = @regInstanceKey OUTPUT;

SELECT @ic = COUNT(*) FROM #instances
WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
SELECT @instance = InstanceName FROM #instances
WHERE InstanceName = @@servicename

SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
ELSE SET @version = CAST(@ver AS INT)

IF ( @version = 8) BEGIN
IF (@instance <> 'MSSQLSERVER' ) SET @key =
'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
ELSE SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
END
ELSE IF ( @version > 8) BEGIN
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
@vn = @instance,
@value = @instance_name out
SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
END

SET @value_name = 'DefaultData'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath = @regInstanceKey OUTPUT;
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

INSERT INTO #directories
VALUES ('DefaultData', @regInstanceKey)
SET @value_name = 'DefaultLog'

EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath = @regInstanceKey OUTPUT;

IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
INSERT INTO #directories
VALUES ('DefaultLog', @regInstanceKey)

SET @value_name = 'BackupDirectory'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath =@regInstanceKey OUTPUT;

INSERT INTO #directories
VALUES ('BackupDirectory', @regInstanceKey)
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

END

END
SELECT * FROM #directories
SELECT * FROM #instances
DROP TABLE #instances
DROP TABLE #directories

Anshumaan Chaturvedi 16-Jun-14 8:10am

Yes

Anshumaan Chaturvedi 16-Jun-14 8:16am

Getting InvalidOperationException
String[0]: the Size property has an invalid size of 0.

Eagerly waiting for your reply

Dharmenrda Kumar Singh 16-Jun-14 8:07am

Add
stringdirectoryPath = Convert.String(cmd.Parameters["@backUpdirectoryPath"].Value);
after cmd.ExecuteNonQuery();

Anshumaan Chaturvedi 16-Jun-14 8:11am

My stored Procedure :

USE [master]
GO
/****** Object: StoredProcedure [dbo].[CleanInstall] Script Date: 06/16/2014 15:54:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery9.sql|7|0|C:\Users\c5038671\AppData\Local\Temp\~vs3ACC.sql
ALTER PROCEDURE [dbo].[CleanInstall]
@backUpDirectoryPath VARCHAR (500) OUT
AS
BEGIN
IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories

CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))
DECLARE @regInstanceKey VARCHAR(500)
DECLARE @instance VARCHAR(255) -- current instance
DECLARE @ver VARCHAR(255) -- MS SQL version
DECLARE @key VARCHAR(255) -- Registry key on server side
DECLARE @value_name VARCHAR(255) -- Registry value name
DECLARE @instance_name VARCHAR(255) -- Key value name for the instance of SQL Server 2000
DECLARE @version INT -- SQL Server version (integer)

DECLARE @ic INT
SET @instance_name = ''

INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
@vn = 'InstalledInstances',
@backUpDirectoryPath = @regInstanceKey OUTPUT;

SELECT @ic = COUNT(*) FROM #instances
WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
SELECT @instance = InstanceName FROM #instances
WHERE InstanceName = @@servicename

SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
ELSE SET @version = CAST(@ver AS INT)

IF ( @version = 8) BEGIN
IF (@instance <> 'MSSQLSERVER' ) SET @key =
'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
ELSE SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
END
ELSE IF ( @version > 8) BEGIN
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
@vn = @instance,
@value = @instance_name out
SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
END

SET @value_name = 'DefaultData'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath = @regInstanceKey OUTPUT;
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

INSERT INTO #directories
VALUES ('DefaultData', @regInstanceKey)
SET @value_name = 'DefaultLog'

EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath = @regInstanceKey OUTPUT;

IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
INSERT INTO #directories
VALUES ('DefaultLog', @regInstanceKey)

SET @value_name = 'BackupDirectory'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@backUpDirectoryPath =@regInstanceKey OUTPUT;

INSERT INTO #directories
VALUES ('BackupDirectory', @regInstanceKey)
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

END

END
SELECT * FROM #directories
SELECT * FROM #instances
DROP TABLE #instances
DROP TABLE #directories

Anshumaan Chaturvedi 16-Jun-14 8:18am

I guess, i should use executeReader because there are more than one rows which needs to be read.

Anshumaan Chaturvedi 16-Jun-14 8:12am

Getting InvalidOperationException
String[0]: the Size property has an invalid size of 0.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stephen Hewison · Answer 1 · 2014-06-16T04:26:00

These two lines mean you're selecting record sets back from your SQL script

SQL

SELECT * FROM #directories
SELECT * From #instances

So that fact you using ExecuteNonQuery against your command object is a bit strange.

You can read anything you SELECT from an SQL command by using a DataReader

C#

IDataReader dr = cmd.ExecuteReader();

You then have the dr.Read() command which will return true until it reaches the end of the record set.

You also have dr.NextResult() which returns true until you run out of record sets.

This means you can access all the rows from multiple record sets from any SQL statement executed through ADO.