?
Solved

MS SQL Server 2005 Standard Edition - Service Broker and SqlDependency (ASP.NET)

Posted on 2006-05-31
6
Medium Priority
?
345 Views
Last Modified: 2008-03-03
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.SqlDependency.Start(ConfigurationManager.ConnectionStrings[0].ConnectionString);

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

0
Comment
Question by:irajani
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 16806029
Go to your sql server

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
0
 
LVL 6

Expert Comment

by:ksbhat
ID: 16806050
smell something fishy here!!
0
 

Author Comment

by:irajani
ID: 16806235
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:imran_fast
ID: 16806816
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
0
 

Author Comment

by:irajani
ID: 16807332
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
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 16822226
>>If I do not use SQLDependency.Start() with this same login I can successfully query the database through my ASP.NET application.

So what your are saying i sthat you are facing this problem with a particular user.


>>SQL Query Analyzer shows no traffic
Did you mean sql profiler?
try using Batch Starting in the events window. and also restart IIS before running the profiler.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question