Hi,
I have a problem making the service broker to work, problem details, SQL Server 2005 Dev, the database used is Northwind, Cashing enabled in database and in Customers Table, in my pages, web.config, contain the cash section,
Now the service broker work fine in case if the Sql data source or object data source using SQL statment, but, i create a stored procedure in Northwind, here its,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Select_Customers]
AS
BEGIN
SET NOCOUNT ON;
Select CustomerID, CompanyName, ContactName from dbo.Customers
END
And then in my page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>
<%@ OutputCache Duration="10" VaryByParam="*" SqlDependency="CommandNotification" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<%=DateTime.Now.ToString() %>
Northwind With Stored Procedure!<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
<asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="Select_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</div>
</form>
<p>
<a href="Default.aspx">Default.aspx</a></p>
</body>
</html>
When i run the SQL Profiler, in case of none Stored procedure it work great, quiet communication, But if its stored procedure, each refresh, the DateTime lable change, and Profilers execute the proecdure.
So the conclusion is the Service Broker don't work for Stored Procedure, i test it in SQL Data Source, Object Data Source, And strong typed data set, and finally the simplest way is the SQL Data Source,
This is really urgant, as if there are no solution for the problem i remove the cashing and go on, as i have about 60 stored procedure, i have no select statment in my app, so its not sense to change 60 procedure and use select instead!
Please help in this!
Thanks in advance