Windows Azure存储过程未执行(Windows Azure stored procedure not executing)

Windows Azure存储过程未执行(Windows Azure stored procedure not executing)

我的问题如下：我有一个使用少量存储过程的站点，所有这些在我的本地开发环境中工作正常。

但是，其中一个存储过程要么未被触发，要么在发布到Windows Azure后失败。

我与数据库的连接很好，因为网站的其他部分正常运行，我使用fiddler来检查post事件是否正在传递正确的数据 - 它是什么。

我已经进入Azure数据库并查看了存储过程，它看起来都很好，我甚至用Azure工具测试它 - 我可以传入数据并且它执行没有问题。

所以我很难知道我还能做些什么才能找到导致这个问题的原因。

我做的最后一件事是仔细检查我的本地副本，然后将其重新发布到Azure。

存储过程：

BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO [dbo].[Events] ([Type] ,[StartDate]) VALUES (@Type, @StartDate) END

My problem is as follows: I have a site that uses a handful of stored procedures and all of these work fine in my local development environment.

However one of these stored procedures is either not being fired or is failed once it has been published to Windows Azure.

My connection to the database is good as other parts of the site function correctly, I have used fiddler to check that the post event is passing through the correct data - which it is.

I have gone onto the Azure database and looked at the stored procedure and it all looks good, I have even tested it with the Azure tools - I can pass in data and it executes without an issue.

So I an stumped as to what else I can do to find what cause of this problem.

Last thing I did was double check my local copy and then republish this to Azure.

Stored procedure:

BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO [dbo].[Events] ([Type] ,[StartDate]) VALUES (@Type, @StartDate) END

最满意答案

你有多确定你的存储过程没有运行？ 有三种可能的结果：你的代码没有调用proc，proc运行但抛出了你没有捕获的错误，或者proc运行成功。

运行代码后尝试运行以下语句以验证proc未运行。 它取自MSDN 。 它不会向您显示对proc的调用，但它将显示在其中调用的语句。 确保在测试之前和之后运行此语句。 如果你没有看到你的proc正在运行的语句，它可能没有被调用。 我会在proc的顶部添加一个SELECT 0作为令牌，以便在INSERT语句失败的情况下在下面的输出中查找（在这种情况下它可能不会显示在下面）。

SELECT query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;

Cause of this problem found - still to find fix.

The issue was not with the Store Procedure, as this was not being called due to a mismatch of cultures between input, site and finaly the DB.

In all my tests I was passing in UK date format (the proper way to use dates dd/mm/yyyy), this meant that my model was valid, however when deployed to azure (on EU server) the date format that it expects is US (not a proper way to use dates mm/dd/yyyy!)

So the following code was doing its job and redirecting me back to the current page.

if (ModelState.IsValid) { database.SaveNewEvent(model); } return Redirect.....

Once I commented out this line of code I got the real error and can now look at changing the culture of my site and the database to use a real date format.

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Thanks to everyone how helped me out with this one :)