ADO/SQLExpress Connection String Question

ADO/SQLExpress Connection String Question

• allroundmusician

   

  Blair it's no longer about a connection string.

  I downloaded sseutil last night. I added the database to the server in everyway imagineable also with the CTP database manager suite. The application still could not connect. It's not so much that ADO.Net can't connect it's that ADO in excel can't connect.

  I haven't found a way to manually add the database to the server so that both can connect. I think I could do it if I went in and manipulated roles etc. But what's troubling me is how to do this in an automated way within the deployment project and it's getting to be a serious problem.

  So the altered question still stands....

  How do I programmatically permanently attatch the database to the server

  Security in SSE is so tight that I can add the database by hand with sseutil and I can't examine with the SSE suite Even when I add it as SA with the password and I log onto the server in the CTP suite as sa.

   

  
  
  

• Werdna

  ReneeC wrote:

  I'm sorry but few things are more irritating to me than Experts Exchange because I'm not a member.

  Aww, c'mon renee! where's your sense of adventure pay the 10 bucks to find out whether or not the answer is there!!!

  It is possible to attach a SQL database via the conneciton string in ADO, but it leaves it on the tree (at least it did in SQL2000/MSDE) and i don't know if user instance is available. Actually, I am not really familiar with what user instance does.

  Here's a trick. . .

  open notepad and save the empty file as c:\test.udl.

  then in explorer, double click it and the DataLinks interface opens up.

  The interface defaults to the odbc provider, so go to the provider tab and select the specific provider you want, in this case SQL. on the connection tab, in section 3, specify the name you want for the database and the file to attach. close the interface, then bring up test.udl in notepad an voila, you have the connection string.

  cheers

  
  
  

• Chrisjune

  I'm sorry but few things are more irritating to me than Experts Exchange because I'm not a member.

  I know the answer to the question already. Yes, ADO can connect. My customer has thousands of lines of code that does just that. That's not the question at all.

  I have a customer application that connects well to an installed system.

  Our problem is one of the of a newly installed application and newly installed server.

  As I learn more, I think I can articulate things better.

  I'm looking for a programmatic way to permanently attach a database.

  Also, with ADO.Net connections, whenever I install a copy of SQLServerExpress these apps spend huge amount of time telling that I can't make a software connection because the default settings do not allow remote connections.

  The problem is, I'm on a single node and SSE is local. Why are my connection attempts seen as remote connection attempts.

  When I started working with this customers solution I had this problem and I remember I really had to track it down but I don't remember how I fixed it. There were lots of references to named pipes etc. Perhaps I simply fixed it by attaching the database.

  At any rate, the ADO.NET can connect to instances of the server but ADO cannot and that's where the problem is.

  So I guess I'm altering my question to how do I programmatically permanently attatch the database to the server

  Thanks

  Renee

  
  
  

• brikshoe

  Check the link below

  http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20189684.html

  cheers

  
  
  

• zkac054

  You may need following code becuase I'll be back after 3 hrs.

   
  using System;
  using System.IO;

  class Test
  {
      public static void Main()
      {
          try
          {
              // Create an instance of StreamReader to read from a file.
              // The using statement also closes the StreamReader.
              string query = "";
              using (StreamReader sr = new StreamReader("TestFile.sql"))
              {
                  String line;
                  // Read and display lines from the file until the end of
                  // the file is reached.
                  while ((line = sr.ReadLine()) != null)
                  {
                      query = query + line;
                  }
                  //Use this query as CommandObj.CommandText and then execute it.
              }
          }
          catch (Exception e)
          {
              // Let the user know what went wrong.
              Console.WriteLine("The file could not be read:");
              Console.WriteLine(e.Message);
          }
      }
  }
     Hope this helps and if does then please mark it as helpful and it solve you problem then mark it as answer so that other could benefit.   cheers. 
  

  
  
  

• Reg Bust

   

  I promise you I'm far from an expert on SQL and databases. My forte is operating systems.

  I have a customer application, a large one which has been developed on a development system of course and in the development environment, it has had an attached database.

  We have written a deployement project which installs this massive application and also SQL express and as distributed the the database is no longer attached.

  To emulate this condition with SQL express, I have detached the database and am using a new connection string, which suggests to me that it requests that SQL express attach the database.

  This works. I am able to connect to the database with a test program with the connection string shown above.

  Although Ado.net is able to do this connection, ADO used by excel VBA is not able to connect and it appears on the surface to be a connection string problem.

   

  Thank you for your assistance!!!!!!!

   

   

   

   

   

  
  
  

• iowacbr600f4guy

  whoa!!!

  I have tried attachdbfilename and actually that what sseutil uses according to the documentation ... BUT their is much more to it... because there are all the secrity descriptors and not even sseutil figures this out.

  From any of these utilities how I get a descriptor file for adding the database

  When I got this customers project I spent hours messing with sql so his app could use the database and I'd never really used SQL before.....

  I'm a systems woman....

  
  
  

• Ken Robertson

  I'm asking for how to create such a script from a database that already exists  and the script needs to one that is compatible with a newly deployed server and deployment project.
  
  

• Pravin03

  Reneec!

  Security is very tight in SSE but there are ways to handle it.

  There are two option that you can use as you say you want programatic solution:

  1) Use script file to create database on client system by executing it.

  2) Use sp_attach_db stored procedure and attach your database.

  You can do any of these while intallation is going on or when your application is run first time.

  hope this helps, cheers

  
  
  

• Rob Plates

  In SSE 2K5 you can do this by right clicking on your existing database

  then choose "script database as" then "create to" and then "file" and save it to the location you are comfortable with by specifying any suitable filename.

  In SS 2K you can do this by right clicking on your existing database

  then choos "All Tasks" and then "Generate SQL Script". A window will popup click "Show All" button on the right side top. Select the objects of your interest. For sure you'll check All tables and most probably All stored procedures/User defined functions as well. If you are using Views check them as well. WHen done with this click Ok and save dialog will appear. thats it.

  If your database is not attached to any server then first attach it then do the above procedure.

  cheers

  
  
  

• Codemachine

  thank you ! Brb ...don't go away!
  
  

• JKohlhepp

  Did u try creating script for whole database and then executing it,if so what happened . Or you don't know how to do it.
  
  

• OmegaMan

  Forgive me, but is it even possible to connection through a SQL provider to a detached database

  As far as the connection strings, I have not yet run across a case where ADO and ADO.NET connection strings were different.

  Maybe if you could describe what you are currently doing, and why it is not working, that would help in determining the solution.

  
  
  

• ert304

  string constr = @"=.\SQLExpress;Initial Catalog=master;Integrated Security=True;";
  string dbname = "TheDatabase";
  string filename = @"[SOME DIRECTORY]\Database.mdf;"
  

  using(SqlConnection con = new SqlConnection(constr) )
  {
  using(SqlCommand cmd = new SqlCommand("sp_attach_single_file_db", con) )
  {
  con.Open();
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = dbname;
  cmd.Parameters.Add("@physname", SqlDbType.NVarChar).Value = filename;
  cmd.ExecuteNonQuery();
  }
  }

  
  
  
• ADO/SQLExpress Connection String Question