locked
Need help adding table to remote database RRS feed

  • Question

  • User426001450 posted

    I’m using Visual Studio Community 2017. A Website and a link database are working fine on the local server. I created a new table and I want to add it to the remote database. I opened the server explorer, right clicked on the table and I’m looking for ‘Task’ to generate a script, but it is not showing that on the pop up menu. Am I doing something wrong here? What is the way to do it?

    Saturday, March 6, 2021 1:34 AM

Answers

  • User426001450 posted

    Ok guys, good news, finally. I was able to solve the problem. Took me some time but I did it. The asp.net framework includes a command line tool named aspnet_regsql that enable the configuration of the database to support polling cache dependency. This tool works with Msft Server SQL 7.0, the Server 2000 and the 2005. But unfortunately the command does not work with a local instance of the 2005 and that was the problem that I was facing. This is stupid and nonsense. How come Msft made this product available to the public when there are extremely important features like ‘Polling cache dependency’ that you are not going to be able to implement on your local machine if you have the Server SQL 2005 version? A thump down for Msft on this one.   

    Thanks to a good asp.net book writer, Stephen Walther, we are able to do it with a custom command line tool that he coded. The name of this command is ‘enableNotifications’. You have to run this command on your prompt and you will be able to configure your database for cache dependency. I don’t know if you may be able to find this command line code online to run it on your local computer, so search for it.  You have to read the whole article about this issue in order to make it work. Here is the link for the article and the entire book is online as well:

    https://flylib.com/books/en/3.444.1.171/1/

    Thanks to all for your help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 13, 2021 4:41 PM

All replies

  • User-1716253493 posted

    AFAIK, It's available in SQL Management Studio. Right click at the database

    Saturday, March 6, 2021 6:34 AM
  • User426001450 posted

    So, you are saying that I cannot use Visual Studio, the software with what I created everything, to add the table to the remote server? Please explain, because if I'm not wrong, you are talking about using a different software here. Thanks.

    Saturday, March 6, 2021 2:08 PM
  • User475983607 posted

    I’m using Visual Studio Community 2017. A Website and a link database are working fine on the local server. I created a new table and I want to add it to the remote database. I opened the server explorer, right clicked on the table and I’m looking for ‘Task’ to generate a script, but it is not showing that on the pop up menu. Am I doing something wrong here? What is the way to do it?

    The import/export feature task is available in SQL Server Management Studio which is a free download.  As far as I know, this feature is not available in Visual Studio.

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

    Saturday, March 6, 2021 2:39 PM
  • User426001450 posted

    I got you but have to be a way to do it without SQL Server Management Studio because I did it before. I used only Visual Studio but I don’t remember how I did it. I added my database and all my tables to the remote database with Visual Studio but I forgot how to do it, unfortunately.

    The way that I’m doing it at this time only add the table but no the triggers. This is what I’m doing: I open the table definition locally with the server explorer in Visual Studio. The lower partition on the screen shows the T-SQL (table SQL query). This code is unique for this table and even has the trigger. After I copy the code, I open SQL Server Management Studio and get connected to my remote database. I click on New Query and paste the query from the local database. After code is executed the table appears in the remote database but not the trigger.

    I tried again and ran the sql for the table and the one for the triggers separate. Both executed successful, but when I checked the SQL for the new table, the trigger was not there. This is the problem that I’m facing, why is not displaying the triggers?  

    Saturday, March 6, 2021 11:20 PM
  • User475983607 posted

    I cannot reproduce your findings.  I followed the same steps and copied the table design script in Visual Studio's Server Explorer.  The triggers are created when I run the script connected to another database.  

    Sunday, March 7, 2021 12:12 PM
  • User426001450 posted

    Ok, let’s try another way since this is getting too confuse.

    The problem:  My Website/project and database is working fine on my local computer and on the remote server. All tables are sqlcachedependency. I created a new table in my local database and I want to make this table sqlcachedependency as well. After this is done, I want to add this table to the remote database. This is all what I’m trying to do. Hope this is clear.

    What have I done so far? I have to say that I completely forgot how I did it the first time. It was long ago and I’m not a database person. The only thing that I remember is that I followed the following post and I was able to do it. This is the link for the post:  https://stackoverflow.com/questions/3179071/how-do-we-enable-sql-cache-dependency-in-asp-net-2-0#

    The last response to this post reads:

    To enable a table for SQL cache dependency use, you'll need to first run the aspnet_regsql.exe tool from a command-line prompt, with these options: aspnet_regsql -S servername -U login -P password -ed -d databasename -et -t tablename

    I have most of the information need it here to run the command, but I don’t remember how to find the login and password; and if I assigned one to the local database. Have anybody used this command before to make tables SQL cache dependency? Is the command correct?  If so, how could I find the parameters values to enter here? Thanks

    Monday, March 8, 2021 4:34 PM
  • User-1716253493 posted

    As i remember, it's for membership provider long time ago,

    https://docs.microsoft.com/en-us/previous-versions/aspnet/x28wfk74(v=vs.100)

    You can find user/password in connectionstring in web,config or in your codes.

    As i remember, there is connectionstring for membership provider in web.config

    Monday, March 8, 2021 5:44 PM
  • User426001450 posted

    As i remember, it's for membership provider long time ago,

    https://docs.microsoft.com/en-us/previous-versions/aspnet/x28wfk74(v=vs.100)

    Ok, so, what do you mean, I can't use this any longer or what?

    You can find user/password in connectionstring in web,config or in your codes.

    As i remember, there is connectionstring for membership provider in web.config

    Yes, you are right and that was the first thing that came to my mind. However, it looks like I did not assign any username or password to my database when it was created. My connectionstring looks like this:

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

    So, I don't see any password or username here.

    Thanks for your help

    Monday, March 8, 2021 6:20 PM
  • User-1716253493 posted

    So, you don't need provide user and password

    Try use -C with the connection string

    Tuesday, March 9, 2021 12:37 AM
  • User-1716253493 posted

    oned_gk

    As i remember, it's for membership provider long time ago,

    https://docs.microsoft.com/en-us/previous-versions/aspnet/x28wfk74(v=vs.100)

    Ok, so, what do you mean, I can't use this any longer or what?

    I mean, you need reg sql if you are using membership provider.

    Long time ago i have use reg sql to avoid error because there are some membership provider in web.config by default. Actualy, i never use it.

    Tuesday, March 9, 2021 12:58 AM
  • User426001450 posted

    So, you don't need provide user and password

    Yes, that what I thought but the prompt is of a different opinion and it is displaying errors. This is what I got:

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
    
    C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S ServerTower -ed -d LocalDatabase -et -t TestingTable2
    Microsoft (R) ASP.NET SQL Registration Tool version 4.8.3761.0
    Administrative utility to install and uninstall ASP.NET features on a SQL server.
    Copyright (C) Microsoft Corporation. All rights reserved.
    
    Please specify user ID, or use -E for trusted connection.
    
    C:\Windows\Microsoft.NET\Framework\v4.0.30319>

    So, instead I use - E as display but it is not working either. The database is ready for sqlcachedependency as I explained before, I just need to make one table ready for sqlcachedependency as it is explained in this article: https://www.dotnetfunda.com/articles/show/1382/how-to-implement-sql-caching-in-aspnet-poll-based-sql-cache-dependency

    This is nonsense, I don't know what to do. Thanks for your help !!


     

    Tuesday, March 9, 2021 1:10 AM
  • User-1716253493 posted

    I mean like this, -C to provide connection string parameter

    aspnet_regsql.exe
    -C "Data Source=.\SQLEXPRESS;AttachDbFilename=d:\myweb\data\Database.mdf;Integrated Security=True;User Instance=True"

    or -E for trusted connection

    aspnet_regsql.exe -E

    Another parameter please complete yourselft

    Simplest way is using wizards

    Run aspnet_regsql.exe -W

    Tuesday, March 9, 2021 1:03 PM
  • User426001450 posted

    Sorry but I tried everything here and nothing seen to work. This is frustrating. Anything else? Thanks

    Wednesday, March 10, 2021 5:56 AM
  • User426001450 posted

    Do you mind to create a local PraticeTable in your computer and try to make it '‘SQL cache dependency’ just to see what happens?

    The steps are not that difficult to implement but the problem is that nothing is working for me. I entered the right coding for cache dependency in the web.conf file and ran all different commands in the prompt. But after executing the command, the process is not adding triggers to the tables and this is one of the many things that it should be doing. 

    Thanks for your help !

    Wednesday, March 10, 2021 3:23 PM
  • User426001450 posted

    Ok guys, good news, finally. I was able to solve the problem. Took me some time but I did it. The asp.net framework includes a command line tool named aspnet_regsql that enable the configuration of the database to support polling cache dependency. This tool works with Msft Server SQL 7.0, the Server 2000 and the 2005. But unfortunately the command does not work with a local instance of the 2005 and that was the problem that I was facing. This is stupid and nonsense. How come Msft made this product available to the public when there are extremely important features like ‘Polling cache dependency’ that you are not going to be able to implement on your local machine if you have the Server SQL 2005 version? A thump down for Msft on this one.   

    Thanks to a good asp.net book writer, Stephen Walther, we are able to do it with a custom command line tool that he coded. The name of this command is ‘enableNotifications’. You have to run this command on your prompt and you will be able to configure your database for cache dependency. I don’t know if you may be able to find this command line code online to run it on your local computer, so search for it.  You have to read the whole article about this issue in order to make it work. Here is the link for the article and the entire book is online as well:

    https://flylib.com/books/en/3.444.1.171/1/

    Thanks to all for your help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 13, 2021 4:41 PM