Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Question Unanswered: Newbie: Modifying table = wrong data in view?

    Hi there,

    Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.

    Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?

    Thanks.

    Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Scott, where are you? This is a prime example of what you were talking about at the bar, remember?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Hey RD STEP BACK! nothing to see here.....
    Novian,
    give us some DDL on your view and your table

    DDL(object definitions) = a list of all of the cols in the view and in the underlying table


    Perform the following steps:
    • Execute sp_helptext against the view to copy it's code.
    • Execute sp_depends against your view to obtain the name of the tables that the view depends on
    • right click your table in the object browser in the query analyzer and select "script object to new window as create"
    • copy the table script and the view code and reply here and paste them in here and we'll hook you up.


    if you dont know how to run any of these commands, type them in the query analyzer and then select them and press SHIFT+F1 on your keyboard. Books Online will open to the helpfile for the appropriate procedure

  4. #4
    Join Date
    Apr 2004
    Posts
    5

    Background info

    Hey guys,

    Thanks for your help. Here's the info:

    Results of sp_texthelp:

    Text
    CREATE VIEW dbo.GetUsers
    AS
    SELECT dbo.getUsersAndTutors.*,dbo.Students.StudentsID AS StudentID
    FROM dbo.getUsersAndTutors LEFT OUTER JOIN
    dbo.Students ON dbo.getUsersAndTutors.UID = dbo.Students.UID

    Results of sp_depends:

    dbo.getUsersAndTutors

    Unfortunately, I can't seem to make the "script object to new window as create" work. I can't right click since I'm on a Mac. Any suggestions?

    Thanks.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    For starters, get rid of SELECT *

    On a MAC?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2004
    Posts
    5
    Hi Brett,

    Yeah, I'm using a db management software for the Mac. As such, "right-click" doesn't exist.

    What do you mean by "get rid of Select *" ?

    I know I can type this into the query analyzer and add the name of a table in order to see the table but I'm not sure how this fits in with what Ruprect has asked me to do.

    Thanks.

  7. #7
    Join Date
    Apr 2004
    Posts
    5

    Still trying to understand

    Okay, doing a little reading and I "think" I know what's going on...

    As far as I understand, you can use shortcuts to insert data into tables. If you make a change to the table, you could run into problems if your values don't match up with the new table.

    With that being said, I'm wondering if my 'view' which depends on the table that I changed is accepting data into the wrong field because the original table was created using a shortcut?

    I guess the question still remains...how do I see the script that describes how my table was created? More specifically, how do I do this with a Mac-based MS SQL manager?

    One last question: Is there a query that will allow me to see this script?

  8. #8
    Join Date
    Apr 2004
    Posts
    5

    Talking

    Hey Ruprect,

    Figured out that command. Here's what I got...

    CREATE TABLE [dbo].[Users] (
    [UID] int IDENTITY(1,1) NOT NULL,
    [Firstname] nvarchar(255) NOT NULL,
    [Lastname] nvarchar(255) NOT NULL,
    [Nickname] nvarchar(50) NOT NULL,
    [Password] nvarchar(50) NOT NULL,
    [Gender] int NULL DEFAULT (0),
    [EmailAddr] nvarchar(255) NOT NULL,
    [City] nvarchar(255) NULL,
    [ProvID] int NULL DEFAULT (0),
    [CurrentSecurityLevel] int NULL DEFAULT (0),
    [DesiredSecurityLevel] int NULL DEFAULT (0),
    [ATID] int NULL DEFAULT (0),
    [AFID] int NULL DEFAULT (0),
    [SystemStatus] int NULL DEFAULT (0),
    [isOnline] bit NOT NULL DEFAULT (0),
    [isAvailISM] bit NOT NULL DEFAULT (0),
    [UserInfo] ntext NULL,
    [DOB] datetime NULL,
    [RegistrationDate] datetime NOT NULL,
    [LastLoginDate] datetime NULL,
    [stat_TimesLoggedin] int NOT NULL DEFAULT (0),
    [stat_ISMRecvBytes] int NULL DEFAULT (0),
    [stat_ISMSentBytes] int NULL DEFAULT (0),
    [stat_Apptsmade] int NULL DEFAULT (0),
    [stat_MsgPosted] int NULL DEFAULT (0),
    [TutorRate] nvarchar(18) NULL,
    CONSTRAINT [aaaaaUsers_PK] PRIMARY KEY([UID])
    )
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK05]
    FOREIGN KEY([CurrentSecurityLevel])
    REFERENCES [dbo].[Type_SecurityLevel]([SecurityLevel])
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK03]
    FOREIGN KEY([ProvID])
    REFERENCES [dbo].[Type_Province]([ProvID])
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK01]
    FOREIGN KEY([ATID])
    REFERENCES [dbo].[Type_Avatar]([ATID])
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK04]
    FOREIGN KEY([DesiredSecurityLevel])
    REFERENCES [dbo].[Type_SecurityLevel]([SecurityLevel])
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK00]
    FOREIGN KEY([AFID])
    REFERENCES [dbo].[Type_Affiliation]([AFID])
    GO
    ALTER TABLE [dbo].[Users]
    ADD CONSTRAINT [Users_FK02]
    FOREIGN KEY([Gender])
    REFERENCES [dbo].[Type_Gender]([GID])
    GO


    Thanks, again.

Posting Permissions

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