Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run SQL backup proc within C# program Expand / Collapse
Author
Message
Posted Friday, May 14, 2010 10:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:56 PM
Points: 32, Visits: 116
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
Posted Saturday, May 15, 2010 5:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 12,889, Visits: 31,839
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
Posted Saturday, May 15, 2010 8:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:56 PM
Points: 32, Visits: 116
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
Posted Tuesday, June 1, 2010 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 1, 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
Posted Wednesday, July 7, 2010 9:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse