Run SQL backup proc within C# program

  • Dear experts,

    I create a Backup Proc named _PRM_bk in Stored Procedures of master database. I can execute this procedure under "SQL Query Analyzer" but failed to run under a C# program even I set the login user "sa" within the program.

    Server : Windows 2003 run ASP.NET 2.0 , SQL Server 2000

    Please suggest me how to run under a .NET application...

    Thanks a lot !

    Here is the procedure coding :

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

    --

    -- Sys Proc to create Month-End backup before Month-End process

    -- useful cmd to drop backup device: select * from master.dbo.sysdevices

    --

    -- run Query Analyzer cmd: EXEC master.dbo._PRM_bk 'E:\temp\SkyPRMyymm'

    --

    CREATE PROCEDURE [dbo].[_PRM_bk]

    @Fn varchar(100)

    AS

    BEGIN

    SET NOCOUNT ON

    -- Create a logical backup device for the full SkyPRM backup before month-end.

    -- USE master

    EXEC master.dbo.sp_addumpdevice 'disk','SkyPRM_bk',@Fn

    -- Back up the full SkyPRM database.

    BACKUP DATABASE SkyPRM TO SkyPRM_bk WITH NAME = 'SkyPRM_VERIFY'

    RESTORE HEADERONLY -- Get File number = 1

    FROM SkyPRM_bk

    -- Verify backup file, okay message : The backup set is valid.

    RESTORE VERIFYONLY FROM SkyPRM_bk

    -- Delete device after backup

    EXEC master.dbo.sp_dropdevice 'SkyPRM_bk'

    END

    GO

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

  • edward are you familiar with the SqlCommand.ExecuteNonQuery Method, that is part of the (System.Data.SqlClient) class?

    that is how you'd call a command that has no expected results...insert/update/delete, or in your case, calling a procedure to run.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Dear Lowell,

    Thanks for your comments.

    Actually I use the "SqlCommand.ExecuteNonQuery Method" in my c# applications. Here is the partial codings in program :

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

    //program body

    ....

    string [] UpDateCmd = new string[1];

    UpDateCmd[0] =

    "EXEC master.dbo._PRM_bk 'E:\temp\PRM1005' ";

    Trans_Data(UpDateCmd);

    if (gUFlag) {

    msgLbl.Text = "** Backup Completed **";

    } else

    msgLbl.Text =

    "** Error : Backup Failed **

    "+UpDateCmd[0];

    }

    ......

    // New Proc: Add,Change,Delete functions with Array

    public void Trans_Data(string [] Scmd)

    {

    string conn_str1="Data Source=localhost;Initial Catalog=master;User Id=sa;Password=xxxx;";

    SqlConnection conn = new SqlConnection(conn_str1);

    conn.Open();

    // Start transaction

    SqlTransaction myTrans = conn.BeginTransaction();

    // Assign command in the current transaction

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = conn;

    cmd.Transaction = myTrans;

    // Execute command

    try

    {

    foreach (string SQLcmd in Scmd)

    {

    cmd.CommandText=SQLcmd;

    cmd.ExecuteNonQuery();

    }

    myTrans.Commit();

    gUFlag=true;

    } catch {

    myTrans.Rollback();

    gUFlag=false;

    }

    cmd.Connection.Close();

    conn.Close();

    }

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

    The program result is always failed. But it is okay to run in SQL Query Analyzer. I don't have any idea on this issue.

    Thanks again for your help.

    Regards,

    Edward

  • Check this article: HOW TO: SQL & C# for details on how to connect to SQL Server database from C#.NET database applications as well as Java database applications. It also describes how to pass embedded SQL queries, calling stored procedures, pass parameters etc.

    Shahriar Nour Khondokar[/url]

  • Try something like this:

    string conn_str1="Data Source=localhost;Initial Catalog=master;User Id=sa;Password=xxxx;";

    using (SqlConnection conn = new SqlConnection(conn_str1))

    {

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = conn;

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.CommandText= [proc_name_here];

    conn.Open();

    cmd.ExecuteNonQuery();

    }

    Edit: You'll have to add the parameter in separately, but that shouldn't be too much trouble.

Viewing 5 posts - 1 through 4 (of 4 total)

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