SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run SQL backup proc within C# program


Run SQL backup proc within C# program

Author
Message
edward-837480
edward-837480
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 119
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

--------------
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28480 Visits: 39970
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
edward-837480
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 119
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
shr_khr
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
justinb138
justinb138
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search