Run SQL backup proc within C# program

Question

Run SQL backup proc within C# program

edward-837480

Say Hey Kid

Points: 682
More actions
May 14, 2010 at 10:57 pm

#139074

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
--------------

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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!

edward-837480 Say Hey Kid Points: 682 More actions · Answer 2

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

shr_khr SSC Enthusiast Points: 110 More actions · Answer 3

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]

justinb138 SSC Enthusiast Points: 173 More actions · Answer 4

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.