I am having trouble executing a stored procedure supplying string value for a input param that is of type nvarchar(128). The param in question is 'username' . As per the proc definition is nvarchar(128) please look at screenshot#2. Below is my C# code where am setting the parameters
if (StoredProcedureExists(auditStoredProc, localConnString))
{
try
{
using (SqlCommand auditCommand = new SqlCommand("sp_executesql", localConnection, tran))
{
auditCommand.CommandType = CommandType.StoredProcedure;
auditCommand.CommandTimeout = sqlCommandTimeoutFallbackValue;
auditCommand.Parameters.AddWithValue("@stmt", auditStoredProc);
var userNameParam = auditCommand.CreateParameter();
userNameParam.ParameterName = "@username";
userNameParam.SqlDbType = SqlDbType.NVarChar;
userNameParam.Value = "JQS";
auditCommand.Parameters.Add(userNameParam);
//my custom method to show what command will be executed
var commnd = CommandAsSql(auditCommand);
auditCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
}
}
The command execution query built by the code is
use my_db_name_here;
declare @return_value int;
exec [sp_executesql]
@stmt = 'dbo.pr_kr_AuditSetUserName',
@username = 'JQS';
select 'Return Value' = convert(varchar, @return_value);
When it executes, the exception I see is
'Incorrect Syntax near JQS'
Full Exception message: e = {"Incorrect syntax near 'JQS'."}
"System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'JQS'.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)\r\n
1 wrapCloseInAction)\r\n
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n
1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at Endpoint.JQS.Common.Processor.ProcessJob(JobQueueMessage message) in C:\dev\Endpoint.JQS\Endpoint.JQS.Common\Processor.cs:line 92\r\n ClientConnectionId:39dbe3e4-8b0e-4d23-8b13-2bc379313328\r\n Error Number:102,State:1,Class:15"
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
NOTE: Ln#92 in the error message is the line that calls to auditCommand.ExecuteNonQuery();
Stored procedure dbo.pr_kr_AuditSetUserName definition is below
I replaced the "sp_execute_sql" with the stored procedure name itself. That also removes the need to pass the stored proc name in @stmt parameter. The working code is below.
try
{
using (SqlCommand auditCommand = new SqlCommand(
auditStoredProc,
localConnection,
tran))
{
auditCommand.CommandType = CommandType.StoredProcedure;
auditCommand.CommandTimeout = sqlCommandTimeoutFallbackValue;
var userNameParam = auditCommand.CreateParameter();
userNameParam.ParameterName = "@username";
userNameParam.SqlDbType = SqlDbType.NVarChar;
userNameParam.Value = "JobQueueService";
auditCommand.Parameters.Add(userNameParam);
auditCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
Logger.Error("Error executing audit storedprocedure" + e.ToString());
}
User contributions licensed under CC BY-SA 3.0