Alpha DevCon 2018
Results 1 to 17 of 17

Thread: Question on SQL Server

  1. #1
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Question on SQL Server

    This almost starts with "Once upon a time ..."
    Last edited by Eric Johnson; 05-09-2016 at 07:52 PM. Reason: Moved after chastized by another member

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,844

    Default Re: Question on SQL Server

    Now the question. Wouldn't it be better to ask this in the application server area rather than the desktop area?
    There can be only one.

  3. #3
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Re: Question on SQL Server

    Gosh ... since the question applies to V11 Desktop but the V10 Server, which should it be put in? Wouldn't want to make a second mistake.

  4. #4
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Question on SQL Server

    This almost starts with "Once upon a time ..."

    For several years I've owned a public database dedicated to a breed of dog. It was programmed by another individual (developer) using SQL Server and then maintained by another. I put together the functional description of what the maintainer needed to do and provided the support for the some 1200 users.

    My health is no longer the greatest and I advance in years. I found a person doing much the same thing for two other breeds. She and I came to an understanding that she would absorb the data records for my breed into her database. I would continue to provide support as the administrator. (As an aside, the database now has almost 750,000 records.) However, I will not have access at the server level to the records as I had before. That's fine and I fully understand the need to keep my little fingers out of the pie.

    However, I had used A5V11 to write SQL queries in the "old days" when I needed to get data in an out of the ordinary fashion. She's agreed that if I ship her the text of a query, she'll run it.

    Now the question. I have the A5V10 Application Server. I also have the "database dictionary" for the database. Can I use the A5V10 Server on my machine to generate these queries? Also, where do I put the database dictionary? Finally, can anyone see any easier method to get what I'm trying to get, SQL Queries for a database I can no longer access.

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,844

    Default Re: Question on SQL Server

    Can I use the A5V10 Server on my machine to generate these queries?
    Must be my failing. I didn't see how that applied to the desktop.
    There can be only one.

  6. #6
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,857

    Default Re: Question on SQL Server

    You have the option depending what you want to do.
    You can do your queries in the database or with xbasic.

    Just like triggers and field rules.

    You can even access the sql if it is sitting on a web sever too as long as the server allows it.

    One question I have is why sql on a desktop??
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  7. #7
    Member
    Real Name
    Nils Boje
    Join Date
    Sep 2010
    Location
    Copenhagen - Denmark
    Posts
    26

    Default Re: Question on SQL Server

    Hi Eric,

    What do you mean by the "database dictionary" ? Is it an empty database, or just the table and view definitions?
    If that is what you mean, the trick is to install Sql server express on your server, or a local windows computer, and run the queries against that model database

    Making the actual queries is best done in Sql server management studio, or Navicat, or some other free tool, then you can send the query to the DBA and have her run it

    You could also just ask for a regular login account for that database only, just with select permissions, Sql Server is fully capable of supporting that.

    Nils

  8. #8
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: Question on SQL Server

    can anyone see any easier method to get what I'm trying to get, SQL Queries for a database I can no longer access.
    few observation to clear out first:
    you used version 11 to generate sql queries in the old days, but now have version 10 server. confusing. you don't need alpha to generate sql query. may be that is what you found it easy. but the versions mentioned are not the same. you do need same version server and development to work together if you still want to generate the queries and then ship them to her.
    if you have the copy of the sql then you don't need to access the real server as long as the structure remains the same, generate the query here and send it out.
    if you do not have the copy all they have to do she can dump the database for you to reconstruct on your end then make the queries and ship it out.

    but finally why still involved with the support. you have given the responsibility to someone. I am sure you trust her otherwise you will not have given to her. that is what is expected of anyone. then walk away. in long run you will be happy, and your 1200 or so subscribers.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  9. #9
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Re: Question on SQL Server

    No. I have Ver 11 on my desktop. I used it to query the database using V11 generated queries. That possibility is gone because I no longer can access the database directly using SQL.

    I have the Ver 10 Application Server from my purchase of Ver 10 products.

    I know how the database is formed as I have the file(s) containing the field names, field types, and sizes ... the dictionary.

    Now, how do I generate an SQL Query that the database owner can execute and then send the results to me.

    I will take that results file and import it into the database structure and then I can do what ever needs to be done.

    For instance. Apparently in the conversion, a field value was changed from a "U" to a "C" meaning "Unknown" and "Carrier". Since this is a genetic test, the results are quite important. I need to find all instances of this and tell her how to fix them. (How I'll find them is unimportant to this discussion.)

    While I really like the idea of limited access to the database, I'm new to her but not the data and she's new to both the data and to me. We're taking baby steps for right now.

    Eric

  10. #10
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: Question on SQL Server

    try this if you have some database where you can practice
    UPDATE table_name SET field_name = "U" where field_name = "C";
    and run the query. if this is good ship that to her.
    this will convert all occurrences of "C" to "U".
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  11. #11
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Re: Question on SQL Server

    I realize that I can write them from scratch and most likely would do this for simple ones.

    The more complex cases worry me. The case I gave was merely to offer an example.

  12. #12
    Member
    Real Name
    Nils Boje
    Join Date
    Sep 2010
    Location
    Copenhagen - Denmark
    Posts
    26

    Default Re: Question on SQL Server

    First of all you should not use Alpha application server or Alpha Developer to make Sql Queries, the proper toll for this is Sql Server Management studio, which is free, or Navicat or similar.
    I would start by installing Sql server Express on your local pc, it runs fine there and is a free download from Microsoft
    Next create a new database and the run all the DDL scripts you have to create the database tables etc.
    Then you can easily create the correct query to update the remote database.

    Having said that, I would really suggest to get the DBA for the remote database to grant you remote access, that will take him / her 5 minutes max. And it will make your tgask much easier.
    If they don't like the idea of having port 1433 open on the internet, either change it or use a vpn connection, or something like RdgGuard to block illegal login attempts, or make a firewall rule for your Ip.

    Nils

  13. #13
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Re: Question on SQL Server

    I have the SSMS. In the past (when I owned the subject database), I used A5 and SSMS in concert to run queries and post mass updates to the database. I'm fully comfortable with this but how will I configure the connection string when I'm not allowed to execute the query? The database is now under new ownership and the new owner is unwilling to give me direct access to it. The problem is that while my portion of the database is 35,000+ records, this is only 5% of the total and she's being cautious about access.

  14. #14
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: Question on SQL Server

    The problem is that while my portion of the database is 35,000+ records, this is only 5% of the total and she's being cautious about access.
    the connection string will expose all the records. would you be willing to share your database connection with anyone?, did you ? it is not question that you are dependable trustworthy or not. it is the question of safety and safeguard the information. you mentioned the database has grown to 750000 records, if your portion is just 35000 then she has to report 715000 other record related individuals that their information is accessible by another person without their consent.

    it is not going to happen, if it were me.

    as I mentioned earlier, it is wise to let it go. in a long run both parities will be happy. baby steps or not, these are not good steps.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  15. #15
    Member
    Real Name
    Eric Johnson
    Join Date
    Apr 2000
    Posts
    171

    Default Re: Question on SQL Server

    All I want is to be able to send her a constructed query. For that I need two things, a copy of the table and a connection string of some kind. I realize that she is unlikely to provide the real connection string due to risk of exposure. I merely need to find a place where I can create one.

    Thus, such a site would have the empty table and be accessible by the string. If you are saying that such is not possible, fine. I candidly did not think such was possible but I thought I'd ask. Instead, I'll just write my requirements in English and let her make up the query.

  16. #16
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: Question on SQL Server

    here is something for you to try, if successful then you are good to go.
    ask her if she can give you the current sqlquery. since the data definition may have changed since you gave her the database.
    if she uses management studio then she has to generate script and select all database objects and tables. that will generate table structures but not any data.( i think)
    then you can execute the sqlqry statement in your management studio to reconstruct the database sans data.
    then you can create any query against the empty database then ship the query to her. if data needed you can fake it to show how it looks.
    Last edited by GGandhi; 05-15-2016 at 06:02 AM.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  17. #17
    Member
    Real Name
    Nils Boje
    Join Date
    Sep 2010
    Location
    Copenhagen - Denmark
    Posts
    26

    Default Re: Question on SQL Server

    If you know SSMS then you don't need a connection string or Alpha Anywhere
    Just open the database in SSMS and construct the query according to requirements, and send her the SQL query, that's all there is to it.
    If you just query 1 table table, it will not matter that there may be changes in the table structure, should you reference unknown columns, she (Or her DBA) will just remove the columns.

    Making a complete dump of the table structure will give you an empty database to check, but no data, any DBA can create those scripts in 10 minutes
    In order to check the differences to your own copy of the database, there are several commercial tools that can do that, I use this one, SQLDBDiff 4.1

    Hope you find a solution

Similar Threads

  1. App Server question
    By Chet Sapino in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 11-19-2009, 08:31 AM
  2. Application Server Question
    By srwhite in forum Application Server Version 9 - Web/Browser Applications
    Replies: 2
    Last Post: 10-03-2008, 02:40 PM
  3. Question on V6 Web App Server
    By djones092 in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-20-2004, 11:16 AM
  4. server question
    By urbanski in forum Alpha Five Version 5
    Replies: 4
    Last Post: 08-02-2003, 06:41 PM
  5. Web Application Server Question
    By djones092 in forum Alpha Five Version 5
    Replies: 4
    Last Post: 07-09-2003, 08:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •