Run SQL backup proc within C# program

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Run SQL backup proc within C# program

Run SQL backup proc within C# program

Rate Topic

Display Mode

Topic Options

Author

Message

edward-837480

Posted Friday, May 14, 2010 10:57 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:49 AM
Points: 32, Visits: 111

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

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

Post #922418

Lowell

Posted Saturday, May 15, 2010 5:13 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 11,789, Visits: 28,063

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #922460

edward-837480

Posted Saturday, May 15, 2010 8:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:49 AM
Points: 32, Visits: 111

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

Post #922494

shr_khr

Posted Tuesday, June 01, 2010 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 11:11 PM
Points: 4, Visits: 54

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

Post #930658

justinb138

Posted Wednesday, July 07, 2010 9:22 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 19, 2010 12:03 AM
Points: 17, Visits: 47

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.

Post #948969

« Prev Topic | Next Topic »

Permissions