Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bkvagnini
Starting Member
5 Posts |
 Posted - 2010-08-02 : 16:35:09
|
| I have a question that is puzzling. The short version is this: I have a view that doesn't have anything in it, although the table does. It used to have the same data in it before I deleted the test data in the table. The users added a record back into the table and now the view is empty.The long version: The users had an Access Database that wasn't designed for multiple users, so they asked me to recreate it in SQL Server 2005 with an Access frontend (using linked tables). The initial design was a single table with the users typing in user names in 6 different places. The version that I created has 1 main table wit 7 supporting tables- the relationships are all one to many (basically using the support tables to populate the dropdown menus in the Access application. The wrinkle with this design is that they would have to learn how to do joins in their queries. Otherwise, I have to write every single query and every single report.My solution was to create a view called alldata that basically flattens the data back out to look like a single table again. It worked very well, up to the point that I ran "delete from maintable" to clear the sample data out (to go live with the application). I checked the SQL server and it still had the view in the database.The users continued to test (why I don't know, since it was supposed to be live at this point), but the data doesn't appear in the queries or the reports (that are based solely upon the view), but DOES appear in the table. This occurs whether or not you look in Access or in SQL. I deleted the view and recreated it. No change. I also restarted the SQL Server 2005 virtual machine. No change either. I tried rebuilding the index on the view- no indexes found- I rebuilt the index on the maintable- no issues there, but still no data in the view.Theoretically, the view should retrieve the data when they run a query based upon that view, which would show the sample data. Where do I go from here? |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
konark
Yak Posting Veteran
60 Posts |
Posted - 2010-08-02 : 19:57:36
|
| Refresh the view sp_refreshview <view>Chandragupta Mourya |
 |
|
|
bkvagnini
Starting Member
5 Posts |
Posted - 2010-08-11 : 09:31:47
|
| thanks for the help thus far, but neither option worked...here is the code for the viewCREATE VIEW [dbo].[dub_db_alldata]ASSELECT d.Request_Id, d.Approval_date, d.Urgent, d.Broad_Qual, d.Non_pol, d.Urgent_Date, d.Urgent_Time, d.Request_Date, st.First AS staff_first, st.Last AS staff_last, t.Title, d.First, d.Last, d.Org, d.Room_Num, d.Phone, d.Ext, d.Alt_Phone, d.Email, d.Address, d.City, d.St, d.Zip, f.Format, r.Request_Type, d.Multievent, d.Date_of_Material1, d.Date_of_Material2, d.Date_of_Material3, d.Date_of_Material4, d.Event1, d.Event2, d.Event3, d.Event4, d.Num_Dubs1, d.Num_Dubs2, d.Num_Dubs3, d.Num_Dubs4, d.Dub_Notes, d.Date_of_Dub, dmb.First AS Dub_Made_by_first, dmb.Last AS Dub_Made_by_last, d.Date_Tape_Rec_Req, d.Num_Stock_Used, m.Machine AS From_machine, ma.Machine AS To_machine, d.Notes, d.Tape_Op_Notes, p.Payment, d.Check_Num, d.Card_Zip, d.Cost, d.Date_Pay_Rec, d.Replacement_Rec, d.Req_Sent_To_WFSU, sb.First AS Sent_by_first, sb.Last AS Sent_by_last, d.Date_Rep_Rec, d.Media_Picked_Up, d.Picked_Up_By, d.Date_Picked_Up, d.Needs_Shipping, sh.Ship_Method, d.Ship_Date, shb.First AS Shipped_by_first, shb.Last AS Shipped_by_last, conb.First AS Contacted_by_first, conb.Last AS Contacted_by_last, d.Date_Contacted, d.Contact_Notes, d.Completed, d.Date_Completed, comb.First AS Completed_by_first, comb.Last AS Completed_by_last, d.Recycled, d.Date_Recycled, d.Requestor_Other, d.Tape_Op_Other, d.Ship_Other, d.Office_Other, d.Followup_Other, d.Replacement_OtherFROM dbo.Dub_Request_tbl AS d INNER JOIN dbo.Staff_tbl AS st ON d.Staff = st.Staff_Id LEFT OUTER JOIN dbo.Staff_tbl AS dmb ON d.Dub_Made_By = dmb.Staff_Id LEFT OUTER JOIN dbo.Staff_tbl AS sb ON d.Sent_By = sb.Staff_Id LEFT OUTER JOIN dbo.Staff_tbl AS shb ON d.Shipped_By = shb.Staff_Id LEFT OUTER JOIN dbo.Staff_tbl AS conb ON d.Contacted_By = conb.Staff_Id LEFT OUTER JOIN dbo.Staff_tbl AS comb ON d.Completed_By = comb.Staff_Id INNER JOIN dbo.Format_tbl AS f ON d.Format = f.Format_Id INNER JOIN dbo.Title_tbl AS t ON d.Title = t.Title_Id INNER JOIN dbo.Request_Type_tbl AS r ON d.Request_Type = r.Request_Type_Id LEFT OUTER JOIN dbo.Payment_tbl AS p ON d.Payment_Method = p.Payment_Id LEFT OUTER JOIN dbo.Ship_Method_tbl AS sh ON d.Ship_Method = sh.Ship_Method_Id LEFT OUTER JOIN dbo.Machine_tbl AS m ON d.From_Machine = m.Machine_Id LEFT OUTER JOIN dbo.Machine_tbl AS ma ON d.To_Machine = ma.Machine_Idthe main table is hereCREATE TABLE [dbo].[Dub_Request_tbl]( [Request_Id] [int] IDENTITY(1,1) NOT NULL, [Approval_date] [datetime] NULL, [Urgent] [bit] NULL CONSTRAINT [DF__Dub_Reque__Urgen__7C8480AE] DEFAULT ((0)), [Broad_Qual] [bit] NULL CONSTRAINT [DF__Dub_Reque__Broad__7D78A4E7] DEFAULT ((0)), [Non_pol] [bit] NULL CONSTRAINT [DF__Dub_Reque__Non_p__7E6CC920] DEFAULT ((0)), [Urgent_Date] [datetime] NULL, [Urgent_Time] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Request_Date] [datetime] NULL, [Title] [int] NULL, [First] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Last] [nvarchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Staff] [int] NULL, [Org] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Room_Num] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Phone] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Ext] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Alt_Phone] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Email] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [St] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Format] [int] NULL, [Request_Type] [int] NULL, [Multievent] [bit] NULL CONSTRAINT [DF__Dub_Reque__Multi__7F60ED59] DEFAULT ((0)), [Date_of_Material1] [datetime] NULL, [Date_of_Material2] [datetime] NULL, [Date_of_Material3] [datetime] NULL, [Date_of_Material4] [datetime] NULL, [Event1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Event2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Event3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Event4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Num_Dubs1] [int] NULL, [Num_Dubs2] [int] NULL, [Num_Dubs3] [int] NULL, [Num_Dubs4] [int] NULL, [Dub_Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Date_of_Dub] [datetime] NULL, [Dub_Made_By] [int] NULL, [Date_Tape_Rec_Req] [datetime] NULL, [Num_Stock_Used] [int] NULL, [From_Machine] [int] NULL, [To_Machine] [int] NULL, [Notes] [bit] NULL CONSTRAINT [DF__Dub_Reque__Notes__00551192] DEFAULT ((0)), [Tape_Op_Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Payment_Method] [int] NULL, [Check_Num] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Card_Zip] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Cost] [int] NULL, [Date_Pay_Rec] [datetime] NULL, [Replacement_Rec] [bit] NULL CONSTRAINT [DF__Dub_Reque__Repla__014935CB] DEFAULT ((0)), [Req_Sent_To_WFSU] [bit] NULL CONSTRAINT [DF__Dub_Reque__Req_S__023D5A04] DEFAULT ((0)), [Sent_By] [int] NULL, [Date_Rep_Rec] [datetime] NULL, [Media_Picked_Up] [bit] NULL CONSTRAINT [DF__Dub_Reque__Media__03317E3D] DEFAULT ((0)), [Picked_Up_By] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Date_Picked_Up] [datetime] NULL, [Needs_Shipping] [bit] NULL CONSTRAINT [DF__Dub_Reque__Needs__0425A276] DEFAULT ((0)), [Ship_Method] [int] NULL, [Ship_Date] [datetime] NULL, [Shipped_By] [int] NULL, [Contacted_By] [int] NULL, [Date_Contacted] [datetime] NULL, [Contact_Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Completed] [bit] NULL CONSTRAINT [DF__Dub_Reque__Compl__0519C6AF] DEFAULT ((0)), [Date_Completed] [datetime] NULL, [Completed_By] [int] NULL, [Recycled] [bit] NULL CONSTRAINT [DF__Dub_Reque__Recyc__060DEAE8] DEFAULT ((0)), [Date_Recycled] [datetime] NULL, [Date_Record_Created] [datetime] NULL, [Date_Record_Modified] [datetime] NULL, [SSMA_TimeStamp] [timestamp] NOT NULL, [Other] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [Dub_Request_tbl$PrimaryKey] PRIMARY KEY CLUSTERED ( [Request_Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFsuggestions, anyone?My next step (if I can't get this to work) is to recreate SQL database as a single table (negating the need for the view) and let them re-enter the same information numerous times. I realize that it's not standard (nor proper) database design, but I will need to do something by next week |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-11 : 09:51:01
|
| Dub_Request_tbl.StaffDub_Request_tbl.FormatDub_Request_tbld.Titleall NOT NULL? and have corresponding records in their associated tables? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
bkvagnini
Starting Member
5 Posts |
Posted - 2010-08-11 : 10:12:08
|
| @Kristen- yes...the ONLY thing I did was delete the data out of the Dub_Request_tbl (to clear the test data)- left all of the support tables (Staff, Format, Title etc..) alone- went back and verified just in case, data is in the support tables-Other weird thing is: ran just the query done in the view creation- came up empty; however, nothing changed in the creation of the view- even went back to original file (view creation) and added the last few columns- still nothing |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
bkvagnini
Starting Member
5 Posts |
Posted - 2010-08-11 : 10:29:33
|
| @X002548 - DUDE!!! You ROCK!! It works like a charm...deleted the test data (using Delete From Dub_Request_tbl), verified that data was gone, then re-entered some test data (Dr. Mindbender from Cobra needed some dubs of "GI Joe vs Cobra" - hey, if you're gonna put test data in, might as well be fun test data!) and the data was in the table, then verified that it was still in the view- it was, then checked in Access in the report that they run- it's all there- Thanks so much..... |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-08-11 : 10:33:40
|
quote:
Originally posted by bkvagnini
@X002548 - DUDE!!! You ROCK!! It works like a charm...
Stop it..I'm blushing...quote:
Sally used to play with her hula hoopsNow she tells her problems to therapy groupsGrampa's on the front lawn staring at a rakeWondering if his marriage was a terrible mistakeI'm sitting on the front steps drinking orange crushWondering if it's possible if I could still blushUh huh Oh yeah
-- John Prine[url]http://www.lyricsg.com/173987/lyrics/johnprine/thesinsofmemphisto.html[/url]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
bkvagnini
Starting Member
5 Posts |
Posted - 2010-08-11 : 16:17:51
|
| "I am saying that any INNER JOIN that has NO MATCHING data, or is empty, will return 0 rows"This make sense now...The sample data that the user put in the second time only contained the required fields, not all of the fields, like my original sample data had. When I tried another test record, I only put the required fields in as well....this would explain why even though I didn't change anything in the view, it broke after deleting the sample data...thanks for the tip...Now I understand...will have to buy you a beer the next time you're in Tallahassee |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
SQL Server View has no data