Publishing your SQL 2008 database to the WinHost SQL 2008 Server

Discussion in 'Databases' started by Michael, Sep 21, 2009.

  1. Note: before you attempt the following, make sure you have created a database in Control Panel using the MS SQL 2008 tool.

    Required applications: Visual Web Developer 2008 or Visual Studio 2008, and SQL Server 2008 Express (see: http://www.microsoft.com/express/product/default.aspx for downloads).

    1. Open Visual Web Developer Express 2008
    2. Open Database Explorer (View -> Database Explorer) (in Visual Studio 2008 you will open "Server Explorer").
    3. The Database Explorer should show up on the right side of the IDE.
    4. Right click Data Connections and select Add Connection.
    5. Enter the Server Name in the first text box (type the name of your local SQL Server).
    6. Type the name of the database in the database name field and click "OK."
    7. In Database Explorer you will see your local database. Right click on it and choose Publish to Provider.
    To generate the script that will create the backup of your database follow the steps listed below.

    1. Click "Next" on the welcome screen of Publishing Wizard.
    2. Insure that your database is selected, check Script all objects in the selected database and click "Next."
    3. Choose the Script to file option and type the name of the script you will be generating. Click Next.
    4. Leave the default Publishing Options and click "Next."
    5. Click Finish.
    To run the script that will publish your database to our SQL 2008 Server you will use SQL Server Management Studio Express or SQL Server 2008. Follow the steps below to publish the database to the Winhost SQL 2008 Server:

    1. Open your database in SQL Server Management Studio 2008.
    2. Click File/Open/File and navigate to the SQL script you just created from your SQL Server.
    3. Type the SQL server name provided for you in Control Panel under Site Info Manager/MS SQL 2008/Manage.
    4. Choose SQL Server Authentication in the "Authentication" drop down box.
    5. Input your database login and password from Control Panel (see #3).
    6. Once the script is opened, navigate to the very top of the page and type:
      USE [database name] (without the brackets)
      GO​
    7. Click the "Execute" button.
     
    Last edited: Oct 14, 2015
  2. Just one comment. I used SQL 2008 Management Studio which has a new icon (at least it was new to me as I was used to the older SQL 2005 Management Studio) next to the Execute tab. This was an all too familiar icon for me since I use Visual Studio all the time so I was trying to make the Execute work by clicking that icon instead of the exclamation point which is on the left of the Execute text. It took me a few days and the kind help here at Winhost.com to make me realize that I was pushing the wrong danged button! Hope this helps someone else.
    Bob
     
    Last edited by a moderator: Oct 14, 2015
  3. Ray

    Ray

    Thanks for that post. We'll keep that in mind when someone experiences a hard time pushing their database to our SQL server.
     
  4. SQL Server 2008 publishing issues.

    I have a database set up using the console and I am trying to publish my local database and have the following issues, my comments in red:

    1. Open Visual Web Developer Express 2008
    2. Open Database Explorer (View -> Database Explorer) (in Visual Studio 2008 you will open "Server Explorer").
    3. The Database Explorer should show up on the right side of the IDE.
    4. Right click Data Connections and select Add Connection.
    5. Enter the Server Name in the first text box (type the name of your local SQL Server).
    6. Type the name of the database in the database name field and click "OK."
    7. In Database Explorer you will see your local database. Right click on it and choose Publish to Provider.
    To generate the script that will create the backup of your database follow the steps listed below.

    1. Click "Next" on the welcome screen of Publishing Wizard.
    2. Insure that your database is selected, check Script all objects in the selected database and click "Next."
    3. Choose the Script to file option and type the name of the script you will be generating. Click Next.
    4. Leave the default Publishing Options and click "Next."
    5. Click Finish.
    To run the script that will publish your database to our SQL 2008 Server you will use SQL Server Management Studio Express or SQL Server 2008. Follow the steps below to publish the database to the Winhost SQL 2008 Server:

    1. Open your database in SQL Server Management Studio 2008.
    2. Click File/Open/File and navigate to the SQL script you just created from your SQL Server.
    3. Type the SQL server name provided for you in Control Panel under Site Info Manager/MS SQL 2008/Manage. Where? I have SQL 2008 Developer Edition, I open the file using File/Open/File and it opens, no problem, so where do I type in the SQL Server name? (I understand the step below requires that I modify the USING statement)
    4. Choose SQL Server Authentication in the "Authentication" drop down box. When? This doesn't come up when opening a file.
    5. Input your database login and password from Control Panel (see #3). Where?
    6. Once the script is opened, navigate to the very top of the page and type:
      USE [database name] (without the brackets)
      GO​
    7. Click the "Execute" button.
    [/QUOTE]

    It appears that I need to connect to Winhost through SQL Server 2008 somehow and am not sure how to do that. Can you help?
     
    Last edited by a moderator: Oct 14, 2015
  5. Ray

    Ray

    Type the SQL server name provided for you in Control Panel under Site Info Manager/MS SQL 2008/Manage. Where? I have SQL 2008 Developer Edition, I open the file using File/Open/File and it opens, no problem, so where do I type in the SQL Server name? (I understand the step below requires that I modify the USING statement)

    ----Once the script is opened, navigate to the very top of the page and type:
    USE [database name] (without the brackets)
    GO


    Choose SQL Server Authentication in the "Authentication" drop down box. When? This doesn't come up when opening a file.
    Input your database login and password from Control Panel (see #3). Where?

    --Typically when you open a SQL script SSMS will automatically try to connect to a database. If it does not, go to File/Connect Object Explorer
     
  6. Reply

    Excuse me I think he had been trying to say this
    [​IMG]
     
  7. Ray

    Ray

    That's correct. This login box should POP up when you open SSMS to run the script against our SQL server.
     
  8. Help

    Using web developer express edition created site using MSDN Walkthrough”Creating a web site with membership and user login”. It worked on local server then uploaded to Winhost after creating a database DB_579_safety. Tried to register a member and got:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

    Looking throught the forum I tried to add a connection string to the web.cofig file
    <remove name="LocalSqlServer" />
    <add name="LocalSqlServer" connectionString="Data Source=s01.Winhost.com;Integrated Security=false;Initial Catalog=DB_579_safety;User ID=DB_579_safety_user;Password=mypassword" providerName="System.Data.SqlClient" />

    Then get
    Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

    Using the Data Explorer in Visual Web developer I added the Winhost database then disconnected the aspnetdb.mdf which is on my drive and tested the connection. Runs on my machine but not when uploaded to Winhost.

    Any ideas would be appreciated..
     
    Last edited by a moderator: Oct 14, 2015
  9. Ray

    Ray

    Did you run 'aspnet_regsql.exe' against our sql server?

    aspnet_regsql.exe -S DBServerName -U DBLogin -P DBPassword -A all -d DBName

    You will need to make sure you run this so that the tables and objects are created on our SQL server.
     
  10. aspnet_reqsql.exe

    What is the path to this file on your server?
    The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database. The Aspnet_regsql.exe file is located in the [drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server.
     
  11. Ray

    Ray

    Aspnet_regsql.exe is not on the server but on the persons computer. The Aspnet_regsql.exe command has the capability to connect to our SQL server and initiate certain commands against the SQL Server.
     
  12. 7. In Database Explorer you will see your local database. Right click on it and choose Publish to Provider.

    In my Visual Studio 2008 Pro in the Server Explorer window I don't have "Publish to Provider" command menu for the right click on the database name. All I have is:
    Refresh
    Delete
    Change View >
    Modify Connection
    Close Connection
    New Query
    Application Debugging
    Allow SQL/CLR Debugging
    Rename
    Properties

    How I can create script?
     
  13. Ray

    Ray

    The Microsoft Database Publishing Wizard usually comes with SQL 2008 Express but if you do not want to install SQL 2008 Express you can download the Microsoft Database Publishing Wizard as its own component and it will automatically configure with your VS 2008.

    Try using these links to help find the Microsoft Database Publishing Wizard as its own separate component.

    http://blogs.msdn.com/webdevtools/a...ing-wizard-is-now-in-visual-studio-orcas.aspx

    http://social.msdn.microsoft.com/forums/en-US/vssetup/thread/26d79950-bf03-4bfa-8fe3-620409ce702c

    http://go.microsoft.com/fwlink/?LinkId=119368

    If you decide to install SQL 2008 Express then you do not need to find and download Microsoft Database Publishing Wizard because it is already in SQL 2008 Express.
     
  14. Thank you, Ray. I have SQL 2008 Developer Edition. It did not add Publish menu to VC2008 but has possibility to generate script. In context menu I select Tasks, then Generate Scripts then Script Wizard opens. I will try this way. Thanks again for quick respond.
     
  15. Publishing your MS SQL 2008 database to Winhost MS SQL 2008 server using Microsoft SQL server 2008 Developer Edition.

    1. Open Microsoft SQL server Management Studio and login to yout SQL server.
    2. Select your database and right click on it.
    3. From context menu select Tasks then Generate Scripts.... Script Wizard opens.
    4. Click Next on starting page.
    5. Make sure correct database is selected on Select Database page, then click Next.
    6. Page Shoose Script Options can be default. Click Next.
    7. Page Choose Object Types - click button Select All, then Next.
    8. Page Choose Schemas - click button Select All, then Next.
    9. Page Choose Stored Procedures - click button Select All, then Next.
    10. Page Choose Tables - click button Select All, then Next.
    11. Page Choose Users - click button Select All, then Next.
    12. Page Output Option - Select Script to File, set filename, select Single File and Unicode text, then click Next.
    13. Check Script Wizard Summary page, then click Finish.
    14. Watch progress, then click Close button.

    Script file should be done.

    15. Right click on your server name and select Disconnect.... Management Studio should be emty - no connections, no opened files.
    16. Select menu File - Open - File with new connection....
    17. In the Open File dialog select your script file then click Open button.
    Connect to Database Engine dialog opens.
    18. Provide settings for Winhost server then click button Connect.
    19. Point to first line in the script text, then change database name from name of your local database to the name of your Winhost database.
    20. Click button Execute.
     
    Last edited by a moderator: Oct 14, 2015
  16. Thanks for the info!
     
  17. Ray

    Ray

    I've actually tested the Script Wizard on SQL 2008 and I frankly like it better then Database Publishing Wizard. It has more options to configure. But because Database Publishing Wizard was simpler to use and it does basically the same function as the Script Wizard, I decided to focus more on the Database Publishing Wizard.
    One thing to point out is on the Script Wizrd you need to setup some values on the Options section. When you get to this phase by default Script Wizard will not script the data on the tables. So if you run through the default settings on the Options it will script the table but when you run the script against our server, the data will be missing. You'll need to scroll down and mark True for 'Script Data' value. Also you have the option to also script the Triggers and Indexes which is not available on Database Publishing Wizard.
     
  18. You right. set Script Date value to true is very important. Unfortunately, I can't edit my previous post.
     
  19. In regards to the previous information being offered.

    [snip]
     
  20. Hi. No need to post your question over and over again. Thanks.
     
  21. Sorry Hank, getting used to the forum... :) It doesn't appear that my reply is being shown on this particular thread. Did I do something wrong?
     
  22. Ray

    Ray

    What thread are you referring to? Our forum does have spam filter enabled so if you are adding links to the thread our spam filter maybe blocking it.
     
  23. These instructions are very clear, but I have a different problem.
    When I right click on my database, no "publish" appears. I also do not have a publishing wizard. When I noticed this, I tried to download one from microsoft, but apparently got an older version that is not able to do scripting. Then I uninstalled my SQL Server Express, and reinstalled it. I used the install on microsoft.com/express/default.aspx. Still no publishing wizard. I ended up repeating the process, and again no luck. Virtual waterboarding.
    There is probablly some tiny flaw somewhere that prevents publishing wizard from loading.

    Is there another way? publshing wizard creates a script that is then sent by management studio. What is the format of the script? MSDN has an article on database scripts. Is this the same format?

    I am also concerned that my databases are in an App_Data folder, and am not sure how that will work in the real world. This is a bit too far down the road, given the current barrier.
     
  24. Ray

    Ray

    Last edited by a moderator: Oct 14, 2015
  25. Just want to say I love this article, and it's counter part in the knowledge base. Worked great for me. The only hiccup I had is that when I seemed to not be connected to my Winhost DB instance at some point; Management Studio kept thinking I was connected to my local instance, even when I used "USE [dbname]; GO;".

    I may have just been confused about which DB I was connected to at the time.

    So I just wound up copying my script to a new file, closing all connections and then exclusively connecting to the Winhost instance, ran the script, worked great.

    You guys rock.
     
    Last edited by a moderator: Oct 14, 2015
  26. Port 1433 headache

    As a new member, it took me two days with lots of searching on the Net to find answers to my issue of unable to connect to the SQL Server on Winhost to upload my Database. Btw, this has nothing to do with Winhost just lack of details information available to help newbies like me. I almost decided to quit Winhost to move to another providers because of this.

    If you happen to be in the same boat like me, try this:
    Try to Telnet s02.Winhost.com 1433 from your Windows command if no error found then you should be good to connect. If not then the port is blocked, in my case my DSL Router Firewall block it. So all I need to do is change my Firewall security from High to Low temporary, and my port works, I was able to follow the instruction provided to upload my SQL Database to the server, after I completed I then change my firewall security back to High again.

    Note: You may also need to do the same for FTP to copy files to Winhost.

    Now if someone could show me a trick to bypass my Corporate's work PC firewall so I can do some file transferring from work vs have to wait until I get home.
     
    Last edited by a moderator: Oct 14, 2015
  27. Ray

    Ray

    Don't forget that you also have port 14330 for your SQL connection. This is the backup SQL port some of our developers use.

    Also try using to site to upload files to a web server.

    http://www.net2ftp.com/
     

Share This Page