1

I had an UPDATE statement which was executing properly. To clean up the code I have created a stored procedure in the Azure SQL database to take care of the update instead. However the stored procedure does not seem to get executed. My code is as follows. Is there any Azure limitation I am not aware of? Am I overlooking anything ? The SP does not return any value.

using (SqlConnection VDCRootConn = new SqlConnection(VDCRootConnectionString))
{
    VDCRootConn.Open();

    SqlCommand VDCRootSqlStatement = new SqlCommand("csp_update_table_sync_ledger", VDCRootConn);

    VDCRootSqlStatement.CommandType = CommandType.StoredProcedure;
    VDCRootSqlStatement.Parameters.Add(new SqlParameter("@sourceDbName", sourceDbName));
    VDCRootSqlStatement.Parameters.Add(new SqlParameter("@sourceDbTablename", sourceDbTablename));
    VDCRootSqlStatement.Parameters.Add(new SqlParameter("@sourceTableMaxSyncedRecordId", sourceTableMaxSyncedRecordId));
    VDCRootSqlStatement.Parameters.Add(new SqlParameter("@clientId", clientId));
    VDCRootSqlStatement.Parameters.Add(new SqlParameter("@sourceTablePrimaryKeyName", sourceTablePrimaryKeyName));

    VDCRootSqlStatement.ExecuteNonQuery();
    VDCRootConn.Close();
}

Update: It seems this is AZURE SQL permission issue. I have double-checked my credentials and permissions. Just to share my thought flow..

1) I can execute the SP from SQL Manager

2) I can validate I have permission to execute as 'MyLoginName' 

SELECT HAS_PERMS_BY_NAME('dbo.csp_update_table_sync_ledger', 'OBJECT', 'EXECUTE');
Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
Milan
  • 3,209
  • 1
  • 35
  • 46
  • if it's not getting executed then why don't you put breakpoints in the code as see if it's hitting the code.. also since you are not returning any data you need to call the `VDCRootSqlStatement.ExecuteNonQuery();` try wrapping that call around a `try{}catch(SqlException sqlex){}` and tell us what the error is.. also can you show what the update query looks like perhaps you are not passing the correct @SqlParameter names to the actual update statement – MethodMan Nov 03 '15 at 18:53
  • ...thanks for the reply. By "not getting executed" I meant on the Azure SQL side, the C# code runs without any errors. Also I have it in TRY/CATCH block and no errors either. I have even printed out the whole statement as a string i,e,: EXEC csp_update_table_symc_ledger param1, para2... and when I manually copy/paste the result into the SQL editor it executes the SP without any issues. It is only from the C# code the SP does not seem to trigger on Azure. :( – Milan Nov 03 '15 at 19:16
  • who owns the domain and or server where you are trying to run the code remotely.. have you contacted them or a sysAdmin..? perhaps it's a rights or permissions or authorization issue.. – MethodMan Nov 03 '15 at 19:18
  • @MethodMan ... I own the SQL instance but you may be right, there could be some Azure based trickery with the RPC... stay tuned... checking! – Milan Nov 03 '15 at 19:22
  • 1
    Change the code in your stored procedure to write the parameters to a table; nothing more; then check whether when you call the stored proc from code anything's inserted - i.e. to ensure it's not a bug in the SP itself. Also, looking at your parameter names I wonder if any are output parameters? See here for an example dealing with those: http://stackoverflow.com/a/10908586/361842 – JohnLBevan Nov 03 '15 at 19:34
  • 1
    @JohnLBevan ... great idea, I just created a simple SP with one hard-coded INSERT statement (no params IN or OUT). The SP did not trigger on Azure SQL when C# is used as above (obviously without the Parameters.Add and with VDCRootSqlStatement.ExecuteNonQuery(); It all seems to point to the AZURE DB itself... RPC permissions... ??? – Milan Nov 03 '15 at 19:45
  • 1
    @JohnLBevan - ultimately your suggestion brought me to solution. My code was correct in the first place, it was a imposed limitation from a different class which was blocking the SP execution after all. Thanks all! – Milan Nov 05 '15 at 17:35

0 Answers0