May 14, 2010 at 10:57 pm
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
--------------
May 15, 2010 at 5:13 am
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
May 15, 2010 at 8:30 am
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
June 1, 2010 at 6:44 am
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.
July 7, 2010 at 9:22 pm
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