SqlDependency.Start An attempt to attach an auto-named database for file failed

Question

iv'e got copy of NORTHWND.mdf along with NORTHWND.LOG in my App_Data folder

MY CONNECTION STRING :

   <add name="northwind_connection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|NORTHWND.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

when i attempt to open and close the connection everything works out fine.

  string connStr = WebConfigurationManager.ConnectionStrings["northwind_connection"].ToString();            
  SqlConnection conn = new SqlConnection(connStr);            
  SqlCommand command = new SqlCommand("Select * From Products");
  command.Connection = conn;                
  conn.Open();
  SqlDataReader reader = command.ExecuteReader();            
  GridView1.DataSource = reader;
  GridView1.DataBind();
  conn.Close();

now beside this code i want to add SqlCacheDependency to the page when i place the code : Shown in msdn

     SqlDependency.Start(connStr);

I GET THE FOLLOWING ERROR :

An attempt to attach an auto-named database for file C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\10.0\NORTHWND.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

any ideas why this happens , what do i need to configure for the SqlCacheDependency to work.

thanks in advance eran.

in addition i would like to add that if i change my connection string to a specific one

  <add name="northwind_connection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\NORTHWND.MDF; Integrated Security=True" providerName="System.Data.SqlClient" />

everything works as it should but that seems wrong since i don't expect users to change the connection string to their path , that's why i would like to put it in App_Data or at list give a relative path to .\SQLEXPRESS which also doesn't work :

  <add name="myConnection"  connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=NORTHWND;Integrated Security=True;" providerName="System.Data.SqlClient"/>

please shed some light on this issue there must be some configuration that makes this possible . thanks in advance. eran.

score 1 · Answer 1 · edited Sep 25 '11 at 06:20

1

I don't think you can use SqlCacheDependency with an auto-attach (SQLEXPRESS) type connection string.

You need to attach the database in Management studio, and change your connection string to look like:

server=(local);database=Northwind;Integrated Security=SSPI;

Then you need to execute ALTER DATABASE NORTHWIND SET ENABLE_BROKER

If you need to provide this kind of setup for users then you can write a SQL Script that will do it for them.

edited Sep 25 '11 at 06:20

KV Prajapati

93,659
19
148
186

answered Sep 24 '11 at 19:04

Peter Bromberg

1,498
8
11

so what your saying is i cant put it in my App_Data folder ? – eran otzap Sep 24 '11 at 19:30
and that i have to attach it to my Server locally and change my connection string to : – eran otzap Sep 24 '11 at 19:41
Msg 5011, Level 14, State 5, Line 1 User does not have permission to alter database 'NORTHWND', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. – eran otzap Sep 24 '11 at 19:44

SqlDependency.Start An attempt to attach an auto-named database for file failed

1 Answers1