How to use the site with remote SQL server RRS feed

  • Question

  • User-1607406849 posted

    Is it possible to use this site with a remote SQL server, other than SQL server express, where it attaches the database.

    I tried replacing the classifiedconnection with the following, but it does not work.

    <add name="classifiedconnection" connectionString="Data Source=myserver;Initial Catalog=classifieds;Persist Security Info=False;User ID=classadmin; Pwd=clsadmin!23" providerName="System.Data.SqlClient"/> 

    I have given classadmin, full access to the classifieds database.

    Any help is greatly appreciated.

    Monday, May 10, 2010 2:45 PM

All replies

  • User-1360095595 posted

    Check this: http://www.connectionstrings.com/sql-server-2008.  It might give you ideas.

    Monday, May 10, 2010 3:48 PM
  • User-122480877 posted
    Stressful old classifieds is it not lol

    1. What version of visual studio are you using? (Visual Web Developer is still an edition of visual studio but what version do you have)
    2. Who is your hosting provider? and check through there help system or SQL Control Panel (not the data base manager) if there is an option for attaching an MDF (.mdf) file. The mdf DataBase is intended for development use, not production or if in doubt contact there support system.
    3. If you right click on your data base in the visual studio solution browser do you have an option like "publish to my provider" 
      1. If that option is not there then this is the software that you need http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en its the database publishing wizard. You will also need to check if your hoster supports the publishing wizard, if they do you will find a special connection string just for this wizard.
      2. In the hosters sql manager you will find an option like "Generate Insert Scripts" that you can run the .sql file found in your App_Data folder. (probably easiest but no good to you if you have modified the database itself [not the contents])
    4. You will find the connection sting in your SQL hosting control panel to put in your site configuration! you will only need to change the section generally within (') like this password='your password' (DO NOT POST YOUR PASSWORD ON THE NET, just use ***** I could attack your DB with it, not that I would but I could [:D])

    It is very nerve racking doing this for your first time how ever next time will be straight forward once you have it sorted.

    One last thing, If you do not have the availabilty of 2 Hosted SQL Servers you may want to look at either scripting the 2 databases together or your hosting provider may have an option to "Install AspNet Schema" that will install everything needed for the default Membership provider. In this case you do not need to worry about publishing the ASPNETDB.mdf only the classifiedsDB.mdf

    Still Stuck? Take your time [:D]

    Answer the above questions and you will soon be on your way. Also search the forums and google for things like "Attatching SQL MDF DB in remote hosting enviroment"

    Hope this dose not give you more of a head ache.

    Tuesday, May 11, 2010 9:42 AM
  • User-1607406849 posted

    Thanks for all your replies. Much appreciated. I will have to start to read on the points that you mentioned. 

    Tuesday, May 11, 2010 11:39 AM
  • User-122480877 posted

    How are you getting on?

    Just a note to correct myself

    3. If you right click on your data base in the visual studio solution browser do you have an option like "publish to my provider"

    This is actually in the visual studio server explorer.

    Also after checking the connection string again, I am pretty sure to get SQL Server to Attach a .mdf DB you have to specify that in your connection string, it works like a command! Data Source=.\SQLEXPRESS;AttachDbFilename=

    With the DataSource here I have shown the local machines default SQLExpress Application how ever your server may be named differently especially being hosted, you will need to find out the server host name and root path perhaps ports and so on, your provider will have this documented otherwise contact the administrator of the server!

    You have specified Initial Catalog in your connection that is used for when the server actually conatins the SQL Data Base Objects where a .mdf is a file for a development DataBase.

    You can then specify the App_Data Driectory for the current application using |DataDirectory| followed by the full name of the database, TestDB.mdf alternatively you can specify the full drive path C:\..............

    <add name="Sample"

           connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TESTDB.mdf;Integrated Security=True;User Instance=True"

           providerName="System.Data.SqlClient" />

    <add name="SpiderMaster.Communities.InternalSqlMemberShipDB"
           connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AspNetDB.mdf;Integrated Security=True;User Instance=True"
           providerName="System.Data.SqlClient" />

    Tuesday, May 18, 2010 3:25 PM