View Or Function Xxx Is Not Updatable Because The Modification Affects Multiple Base Tables?????
Aug 17, 2007
I have read that I need to add an instead of trigger. I have no idea how to do this. I am basicaly trying to update at the moment but will also need to insert later.
From the code in VWD2008 I only get the error when i include this info here: When i press update and these below are included in the update statement then i get the error.
[S_DATE] = @S_DATE,
[END[IS_CONFIRMED] = @IS_CONFIRMED,
[IS_PAID] = @IS_PAID,
[S_Descript] = @S_DESCRIPT
[COMPANY] = @COMPANY
[MONTH] = @MONTH
[ACCOUNT] = @ACCOUNT
Here is view from SQL 2000.
CREATE VIEW dbo.VIEW_TRAINING
AS
SELECT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name], dbo.ADDRESS.STATE,
dbo.ADDRESS.TEL1 AS phone, dbo.CUST.NAME AS Company, dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS Job_Title,
dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE,
dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account,
dbo.TRAINING_SCHEDULE.SCHEDULE_ID
FROM dbo.TRAINING_SCHEDULE INNER JOIN
dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN
dbo.X_INVOIC RIGHT OUTER JOIN
dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN
dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN
dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODE
WHERE (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND
(dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND
(dbo.TRAINING_SCHEDULE.CUST_CODE = 'steve')
Here is the forms code I am working with: used VWD to generate the forms...I added update...<%@ Page Language="C#" AutoEventWireup="true" CodeFile="training.aspx.cs" Inherits="training" TRACE = TRUE%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:FormView ID="FormView1" runat="server" DataSourceID="dstraining"
DefaultMode="Edit" AllowPaging="True">
<EditItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel1" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
CommandName="Update" Text="Update" />
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<InsertItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate>
<ItemTemplate>
EMAIL:
<asp:Label ID="EMAILLabel" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:Label ID="first_nameLabel" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:Label ID="last_nameLabel" runat="server" Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:Label ID="STATELabel" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:Label ID="phoneLabel" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:Label ID="CompanyLabel" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:Label ID="S_DescriptLabel" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:Label ID="MONTHLabel" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:Label ID="S_DATELabel" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:Label ID="END_DATELabel" runat="server" Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:Label ID="IS_CONFIRMEDLabel" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:Label ID="IS_PAIDLabel" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:Label ID="AccountLabel" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="dstraining" runat="server"
ConnectionString="<%$ ConnectionStrings:EVEREST_FIBEROPTConnectionString %>"
SelectCommand="SELECT , [first name] AS first_name, [last name] AS last_name, [STATE], [phone], [Company], [S_Descript], [Job_Title] AS Job_Title, [MONTH], [S_DATE], [END_DATE], [IS_CONFIRMED], [IS_PAID], [Account], [SCHEDULE_ID] FROM [VIEW_TRAINING]"
UpdateCommand="UPDATE view_training SET email=@email, [FIRST NAME] = @FIRST_NAME, [LAST NAME] = @LAST_NAME, [STATE] = @STATE, [PHONE] = @PHONE, [JOB_TITLE] = @JOB_TITLE, [COMPANY] = @COMPANY WHERE Account = @Account">
</asp:SqlDataSource>
</form>
</body>
</html>
View 1 Replies
ADVERTISEMENT
Mar 4, 2008
View or function 'MutualFund' is not updatable because the modification affects multiple base tables.
I get the above error when i try to delete rows from a view.
i tried the following code with a table. it works perfectly. i want the same behaviour for a view.
can anyone help.
Delete MutualFund
from MutualFund inner join MutualFund_history
on MutualFund.SecurityID= MutualFund_history.SecurityIDwhere MutualFund.MorningstarCategoryID=MutualFund_history.MorningstarCategoryID and
MutualFund.MorningstarAssetClassID= MutualFund_history.MorningstarAssetClassID and
MutualFund_history.insertdt =(Select MAX(DsegLastUpdated) from Timestamp_History(nolock))
"MutualFund" is a view.
if it were a table, the above works well. but how can i simulate the same effect for a view.
any help will be greatly appreciated.
thanks
View 1 Replies
View Related
Jun 7, 2006
Hi
This is for Sql Server 2000.
I get the following error :
View or function 'PS_EMPLOYMENT' is not updatable because the modification affects multiple base tables.
Sql is as follows:
INSERT INTO PS_EMPLOYMENT(EMPLID,EMPL_RCD,PER_ORG,BENEFIT_RCD_NBR,HOME_HOST_CLASS,OWN_5PERCENT_CO,
SECURITY_CLEARANCE,NEE_PROVIDER_ID,POSITION_PHONE,BUSINESS_TITLE,REPORTS_TO,
SUPERVISOR_ID)
VALUES('000004', 0, ' ', 0, 'M', 'N', ' ', ' ', ' ', ' ', ' ', ' ')
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Searching the KB i only found links to SP1 and SP3, the server is already at SP3.
BOL says the following:
SQL Server 6.x :
Updatable views were restricted to modifications that affected only one table
SQL Server 2000:
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
So why can this view not be updated in SS2000 SP3 ?
Here is a copy of the view defintion:
ALTER VIEW PS_EMPLOYMENT
(EMPLID, EMPL_RCD, PER_ORG, BENEFIT_RCD_NBR, HOME_HOST_CLASS, LAST_DATE_WORKED, HIRE_DT, REHIRE_DT,
TERMINATION_DT, SENIORITY_PAY_DT, CMPNY_SENIORITY_DT, SERVICE_DT, PROF_EXPERIENCE_DT,
LAST_VERIFICATN_DT, EXPECTED_RETURN_DT, LAST_INCREASE_DT, OWN_5PERCENT_CO, PROBATION_DT,
SECURITY_CLEARANCE, NEE_PROVIDER_ID, POSITION_PHONE, BUSINESS_TITLE, REPORTS_TO, SUPERVISOR_ID)
AS
SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,
D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,
A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,
A.PROBATION_DT ,A.SECURITY_CLEARANCE ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,
D.SUPERVISOR_ID
FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D
WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )
View 3 Replies
View Related
May 16, 2008
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
View 1 Replies
View Related
Apr 12, 2000
I have created a view ('Data_test') based on Table1 and Table2 joined in the following manner:
Table1.PrimaryKey <-----> Table2.ForeignKey /* same as Primary Key */
When trying to insert data into the tables through the view, I get the following error:
Server: Msg 4405, Level 16, State 2, Line 1
View 'Data_test' is not updatable because the FROM clause names multiple tables.
Any ideas of how to make the view updatable?
View 1 Replies
View Related
Nov 17, 2005
I have listed a view below and a portion of the result set that is returned when I run the code in Query Analyzer. This is part of a timesheet application that logs hours per SCHLSTUID per SECTIONID per week. This returns the SCHLSTUID(user's ID), SECTIONID, Date that the week starts, the first date that time was logged. The user could be in several SECTIONID's for the same week. I need to modify this so that it returns the date that the first time was logged for any of the SECTIONID's per week. I know that this is probably something simple that I'm overlooking but I just can't get it to work correctly.Example: SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120This would need to return 2005-09-18 20:59:21.120------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427This would need to return either 2005-10-02 20:59:37.427------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823This would need to return 2005-10-09 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------CREATE VIEW dbo.vExportStartWeekASSELECT TOP 100 PERCENT schlstuid, sectionid, ATTSTARTDT, MIN(TimesheetDate) AS FirstTimeEnteredOnFROM (SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuemns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuehrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frimns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frihrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Satmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Sathrs AS TimeEntered FROM TimeSheetDailyAttendance) TimesheetDatesWHERE (TimeEntered <> 0)GROUP BY schlstuid, sectionid, ATTSTARTDTORDER BY schlstuid----------------------------------------------------------------------------------------------------------------------------------------------------This is a portion of what is returned:SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120601868445 EN4AR001 2005-09-25 20:59:36.670 2005-09-25 20:59:36.670601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------Thank you for any help that you can give me.Scott
View 3 Replies
View Related
Aug 8, 2013
I have been inserted one row in to table called as Teachers..
vTeachers is the View ctreated on Teachers Table.
When i Inserted one row in to Table Teachers i didn't see that row in vTeachers View.
How can i get that newly inserted values in to my View?
View 4 Replies
View Related
Mar 2, 2007
I have created the following view:
Create view vwOrderItemTotal2
AS
SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
26 Milk
Now I need to filter my data by OrderDate and Zipcode. I created this new view:
Create view vwOrderItemTotal5
AS
SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems]
FROM Orders INNER JOIN OrderItems
ON Orders.OrderID = OrderItems.fkOrderID
GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
1 Milk
5 Milk
6 Milk
6 Milk
3 Milk
1 Milk
4 Milk
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet).
I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
View 4 Replies
View Related
Mar 5, 2006
Is there a way to force the modification of a User Defined Function upon which depend other objects.
This is the message:
Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22
Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.
I have 48 objects related to the function and each time I need modify the function is a big pain.
Any help will be much appreciated.
Best Regards
ggpnetwork
View 3 Replies
View Related
May 2, 2014
I want to combine 2 tables into a view. The first table is a list of 'Products' (P1, P2.....)
Table: Product
IDUserStart Date..
=====================================
P1Fred12/04/2012
P2Jane01/12/2011
P3Mike01/12/2011
P4Stu01/12/2009
P5 Etc
Table two is qualities for each product. A product can have from zero to 8 qualities. Qualities have a description and a value (varchar).
Table: Qualities
ProductDescrip.Value
=====================================
P1ColourRed
P2ColourGreen
P2Hard2.2
P2Weight123
P3ColourBlack
P4ColourWhite
P4Weight8.12
P6Mass12.2
P2abcdef Etc
I would like to create a view that joins these tables. Each row show a product and all the qualities it has, from quality 1 to quality 8. If there is no N'th quality, to show blank/null/zero.
The problem is how to get values from multiple rows (in table Qualities) in a single row in the view.
Wanted View
ProductUserQuality1Value1Quality Value2Quality3Value3Quality4Value4 etc..
=====================================
P1FredColourRed0000000
P2JaneColourGreenHard2.2Weight123abcdef
P3MikeColourBlack000000
P4StuColourWhiteWeight8.120000
etc
View 2 Replies
View Related
May 7, 2015
I have setup Transnational replication with updatable subscription.
There were around 7000 rows update at subscriber from one query which was executed at subscriber side.
But this update has not taken place at publisher yet and so to other subscribers.
Is there any way i can monitor pending log or where is the issue?
I find so many ways to see pending logs or statistics for - "Publisher to Subscriber" but not for "subscriber to publisher"
What is the way to see subscriber to publisher pending and issues?
View 0 Replies
View Related
Dec 5, 2005
I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover
View 3 Replies
View Related
Feb 7, 2008
Hi,
I am rather new to SQL 2005 and I am currently facing a problem when I attempt to sum the income and expenses which requires joining 2 tables. My sql string that is currently having a problem is listed below:
I USED THIS STATEMENT:
SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses
FROM tblincome,tblexoenses
WHERE tblincome.recieve=tblexpenses.paymentto
GROUP BY TblIncome.Recieve
AND I ALSO USED THIS STATEMENT:
SELECT TblIncome.Recieve, SUM(TblIncome.Amount) AS TotalIncome, SUM(TblExpenses.Amount) AS TotalExpenses
FROM TblIncome INNER JOIN
TblExpenses ON TblIncome.Recieve = TblExpenses.paymentTo
GROUP BY TblIncome.Recieve
The problem that occurs is it sums and multiplies the number of records in the second table. For example,
TblIncome:
Recieve Amount
John 5
John 2
David 1
TblExpenses:
PaymentTo Amount
John 3
David 2
Currently, it is showing me as follows:
Recieve TotalIncome TotalExpenses
John 7 6
David 1 2
The problem is in total expenses, it multiplies the number of records in TblIncome.
Also, if TblExpenses has more than 1 record, it multiplies the total income with the number of records.
Does anyone know how to solve this problem? Thanks
--
http://blog.slickw0rm.net
View 10 Replies
View Related
Oct 17, 2006
Hi,
I have created a publication for sql server 2005 Mobile Edition subscriber,
I need to add another Publication/merge replication on the same tables,
Scenario:
-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)
-The same Server A send data "1 way direction" to Server B by merge replication publication.
NB: My Tables are updatble in both publications.
Any idea?
Thanks,
Tarek Ghazali
SQL Server MVP
View 1 Replies
View Related
Jul 31, 2014
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
View 5 Replies
View Related
Sep 28, 2007
Hi,
In the SQL92 Specifications i read the foloowingf statement...
"All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are
permitted for updatable tables, subject to constraining Access
Rules. The operations of insert, update, and delete are not allowed
for read-only tables."
But i am concentrating on the below line from the above written lines,
"Derived tables are either updatable or read-only."
I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...
Code Block
Update (select * from test1) AS de SET id=0
????
View 10 Replies
View Related
Jun 23, 2014
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date
FROM ((project_manager
INNER JOIN files
ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending')
INNER JOIN projects
ON projects.project_id = project_manager.project_id)
GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
ORDER BY projects.status, projects.start_date DESC
result of this query:
View 5 Replies
View Related
Sep 1, 2015
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName = Abraham and
Industry= Film
How to write query?
View 2 Replies
View Related
Dec 3, 2007
Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
View 3 Replies
View Related
Aug 19, 2002
Is there any way to get the base table/column from a view by supplying the view name/column name_in_view? I know about INFORMATION_SCHEMA.VIEW_COLUMN_USAGE, but it doesn't always work. Try it on a regular database, not pubs. Any help is appreciated!
View 1 Replies
View Related
Aug 26, 2014
We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.
View 2 Replies
View Related
Apr 21, 2015
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
View 2 Replies
View Related
Mar 14, 2006
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
View 1 Replies
View Related
Apr 7, 2008
Hi,
Very basic beginner XML question. Is it possible to have an attribute name repeated in a schema ? I received an XML and XSD file for which I'm getting the error "multiple base for derived type on dte_effective is not supported." "dte_effective" is repeated under several elements. Is there a way to resolve this error by adding something to the schema to indicate that the date is specific to each element ? Can't figure this one out. I've googled the error and found nothing to resolve this.
XSD file sample is below...
Thanks, J.
<xs:element name="Element1">
<xs:complexType>
<xs:attribute name="dte_effective" "NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>
<xs:element name="Element2">
<xs:complexType>
<xs:attribute name="dte_effective" type="NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>
View 7 Replies
View Related
Sep 15, 2006
OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim searchString As String
searchString = Me.SearchStringBox.Text
Try
Dim filter As String
filter = "LastName LIKE '" & searchString & "'"
Dim search() As System.Data.DataRow
search = myCDDataSEt.ClientData.Select(filter)
If search.Length > 0 Then
'no code as yet
Else
MessageBox.Show("The client " & searchString & "is not in the database")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
View 2 Replies
View Related
Nov 4, 2015
Just wonder if system base tables always use clustered index? I am using SQL Server 2005 and find sys.sysidxstats base table is using heap, not clustered index. Why?
View 2 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Jul 20, 2005
himy self avicurrently i am developing one application in vb 6 and back end assqlserver 7 which is used on lan.i have some problem. like in my database i have one table salesvoucherwhich has 'voucherno' field. when 2-3 user will work on salesform at atime [since the softwrae will run on lan] then the same voucherno willsave for all users data which is wrong i need to save different vouchernofor each no. is there any way in sqlserver to apply some condition ondatabase or to set some its property[tables] so that at a time only oneusers data will save depend on firstcome first serve base or is there anyway to make changes in programme.plz help me regarding this i need it very badly.since its my first lan based software plz give me some books nameregarding this software for vb6 and sqlserver7
View 2 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Dec 5, 2007
hi
at my work place some of the users migrated to another domain even thought with new domain name they can access SQL Server but user who are part of sysadmin(part of some group) on the box unable to execute a job. it means at operating system level newdomainuser credentials are get resolved that's why users are able to access box but why not able to execute jobs. why sysadmin Privilege not get resolved at SQL Server level very confused............
View 7 Replies
View Related
Jul 21, 2004
Hiya folks,
Having a problem with a view.
I'm using the syntax
Right('000' & Myfield,3) to 'pad' out the results.
ie if MyField contained 45 the answer should be : 045
but i'm getting the results of : 0
Stuck!! Any offers kind people??
View 4 Replies
View Related
Sep 18, 2007
SELECT *
FROM dbo.Objects
WHERE (UserRights & dbo.GetUserRights()) > 0)
The purpose of this view is to implement row level security. The Field, UserRights is a BigInt and holds up to 64 bits corresponding to the user or group that has a right to view this record.
The Function GetUserRights() is a function which returns an Int corresponding to the user rights of the person who is currently logged in. In a bitwise fashion, it then "ands" them together to see if the value is greater than 0. If so, the user has rights to the record.
If I hardcode a value to replace the function, the Select statement is very fast. However, if I use the function it is extremely slow. I believe this is due to the function getting run once for each row of the table, instead of just once.
Is there a way to rewrite this so that it only runs once? I could do this in a stored procedure, but I need it in a view so that I can use replace "Objects" (the table) with "vwObjects" the view.
Thanks,
Greg
View 6 Replies
View Related
