How do I call a stored procedure with unconventional parameters?

2018-06-20 09:28:37

I'm attempting to integrate Red Gate's SQLBackup Pro software into my in-house backup software, written in C#. The natural way to do this is via their Extended Stored Procedure. The problems is that it's called in a format I've never seen before:

master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]"'

This works just fine when run via SSMS. Where I run into trouble is trying to call it from C# (using .NET 4 and Dapper Dot Net).

My first attempt doesn't work because it interprets the entire cmd string as the name of the stored procedure and throws the error "Cannot find stored procedure ''":

var cmd = "master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]"'";
connection.Execute(cmd, commandType: CommandType.StoredProcedure, commandTimeout: 0);

My second attempt returns immediately and appears (to C#) to succeed, but no backup is actually taken (this also sucks for parameterization):

var cmd = "master..sqlbackup";
var p = new DynamicParameters();
p.Add("", "'-SQL "BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]"'");
connection.Execute(cmd, p, commandType: CommandType.StoredProcedure, commandTimeout: 0);

My third attempt also appears to succeed, but no backup is actually taken:

var cmd = "master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]"'";
connection.Execute(cmd, commandTimeout: 0);

What am I missing?

UPDATE 1:

I overlooked the Red Gate documentation that says the stored proc won't actually raise a SQL error, it just returns errors in an output table. Slick. This might explain why I was getting silent failures in the second and third tests above: some underlying problem and they're not collecting the output to show why.

Here's where I am now:

var cmd = "master..sqlbackup";
var p = new DynamicParameters();
p.Add("", "'-SQL "BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]"'");
p.Add("@exitcode", DbType.Int32, direction: ParameterDirection.Output);
p.Add("@sqlerrorcode", DbType.Int32, direction: ParameterDirection.Output);
connection.Execute(cmd, p, commandType: CommandType.StoredProcedure, commandTimeout: 0);

When I run this and check those output parameters, I get Exit Code 870:

No command passed to SQL Backup.

The command is empty.

So it's not seeing the empty-named paramater.

Update 2:

Capturing the above in a trace shows that the empty parameter string ends up replaced with @Parameter1= which explains why the stored procedure doesn't see it.

Your first attempt looks almost right. What I notice is that you failed to escape the backslashes. For this kind of thing, it's often easier to use the @ prefix to disable escaping for the string. Also, you want to prepend with exec and make it CommandType.Text :

EDIT: fixed my own escaping bugs here

var cmd = @"exec 'master..sqlbackup -SQL ""BACKUP DATABASE pubs TO DISK = [C:Backupspubs.sqb]""'";
connection.Execute(cmd, commandType: CommandType.Text, commandTimeout: 0);

It's gross and not at all what I wanted, but this is what I've got working now:

var cmd = String.Format(@"
DECLARE @exitcode int; 
DECLARE @sqlerrorcode int;
EXEC master..sqlbackup '-SQL "BACKUP DATABASE [{0}] TO DISK = [{1}])"', @exitcode OUTPUT, @sqlerrorcode OUTPUT;
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR('SQLBackup failed with exitcode %d and sqlerrorcode %d ', 16, 1, @exitcode, @sqlerrorcode)
END
", myDbName, myBkpPath);

connection.Execute(cmd, commandTimeout: 0);

This executes the backup and actually returns failure status, which exposed an underlying issue that caused the failure part of the silent failures.

Before it runs, myDbName is checked against a list of known databases to ensure it exists and myBkpPath is generated by my code, so I'm not worried about injections. It's just...well, look at that. Hideous.

Have you tried creating a typical stored procedure that calls the extended stored procedure, and calling that from code? It looks like you have only a few parameters to deal with here.

链接地址: http://www.djcxy.com/p/57358.html

上一篇: 多文件扩展名/ mimetype意图

下一篇: 如何使用非常规参数调用存储过程？