irajani
asked on
MS SQL Server 2005 Standard Edition - Service Broker and SqlDependency (ASP.NET)
Hi,
I have Sql Server 2005 Standard Edition installed on a little development server on my small network. I develop ASP.NET applications on my workstation and want to try out the new data caching features available with SQL Server 2K5 Broker Service and ASP.NET 2.0.
I have created a database on the server with ENABLE_BROKER set. I have granted the permissions to the user as specified here (although I have only created one user for both the subscriber at starter):
http://www.codeproject.com/useritems/SqlDependencyPermissions.asp
For some reason I still cannot my application to work - my application throws the following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
It points to the following line in my application:
System.Data.SqlClient.SqlD
Now before you tell me to enable remote connections in SQL Server Management Studio, I have already done so. In fact I can happily read data from the database with the same login if I do not use data caching (SQLDependency).
The funny thing is that there is absolutely no traffic between my workstation and the database (at least not in sql query analyzer) when I try to use SQLDependency (Data caching). Is there anything else that I need to do?
I've also created an endpoint for servicebroker using windows authentication.
I'm really out of my depth here. I'm a part-time asp.net developer and work with SQL Server 2K and 2K5 from a development perspective. I have no DBA experience.
500 points for a working solution!
Kind Regards,
Imran
I have Sql Server 2005 Standard Edition installed on a little development server on my small network. I develop ASP.NET applications on my workstation and want to try out the new data caching features available with SQL Server 2K5 Broker Service and ASP.NET 2.0.
I have created a database on the server with ENABLE_BROKER set. I have granted the permissions to the user as specified here (although I have only created one user for both the subscriber at starter):
http://www.codeproject.com/useritems/SqlDependencyPermissions.asp
For some reason I still cannot my application to work - my application throws the following error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
It points to the following line in my application:
System.Data.SqlClient.SqlD
Now before you tell me to enable remote connections in SQL Server Management Studio, I have already done so. In fact I can happily read data from the database with the same login if I do not use data caching (SQLDependency).
The funny thing is that there is absolutely no traffic between my workstation and the database (at least not in sql query analyzer) when I try to use SQLDependency (Data caching). Is there anything else that I need to do?
I've also created an endpoint for servicebroker using windows authentication.
I'm really out of my depth here. I'm a part-time asp.net developer and work with SQL Server 2K and 2K5 from a development perspective. I have no DBA experience.
500 points for a working solution!
Kind Regards,
Imran
smell something fishy here!!
ASKER
Hi,
All of the protocols except VIA are enabled on both those items imran_fast.
I did notice that analysis services, reporting services and the sql server browser are all stopped. Do I need to start any of these services?
Also, I don't know if I mentioned this but I have tried turning off Windows Firewall to make sure that my workstation is not blocking the connection... I don't think this is the problem though since there is no traffic in sql query analyzer.
Kind Regards,
Imran
All of the protocols except VIA are enabled on both those items imran_fast.
I did notice that analysis services, reporting services and the sql server browser are all stopped. Do I need to start any of these services?
Also, I don't know if I mentioned this but I have tried turning off Windows Firewall to make sure that my workstation is not blocking the connection... I don't think this is the problem though since there is no traffic in sql query analyzer.
Kind Regards,
Imran
If you use the same your to connect to the server using management studio it is working. The only problem is that when you turn on sql dependecy you are facing this problem.
So from where you are turning sqldependecy on
So from where you are turning sqldependecy on
ASKER
Hi Imran,
SQLDependency is a class in the .NET Framework 2.0. I am calling SQLDependency.Start() from an ASP.NET 2.0 application. The thing is, the Start() method takes a connection string as an argument. If I do not use SQLDependency.Start() with this same login I can successfully query the database through my ASP.NET application. Unfortunately, it is necessary to call start to use SQLDependency - the easiest way to implement data caching in ASP.NET 2.0.
I really don't know where the problem lies... no errors are thrown. SQL Query Analyzer shows no traffic. Could it be that SQL Server is throwing a silent error?
Kind Regards,
Imran
SQLDependency is a class in the .NET Framework 2.0. I am calling SQLDependency.Start() from an ASP.NET 2.0 application. The thing is, the Start() method takes a connection string as an argument. If I do not use SQLDependency.Start() with this same login I can successfully query the database through my ASP.NET application. Unfortunately, it is necessary to call start to use SQLDependency - the easiest way to implement data caching in ASP.NET 2.0.
I really don't know where the problem lies... no errors are thrown. SQL Query Analyzer shows no traffic. Could it be that SQL Server is throwing a silent error?
Kind Regards,
Imran
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start --> program files --> microsoft sql server 2005 -->configuration tools--> sql server configuaration manager -->
1.Enable TCP\IP for sql server network configuration
2.Enable TCP\IP for sql server native client