MS SQL Server :: View Or Function 'MutualFund' Is Not Updatable Because The Modification Affects Multiple Base Tables.

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") %>' />
 
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
 
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
 
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
 
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
 
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
 
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
 
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
 
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
 
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
 
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
 
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
 
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
 
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
 
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel1" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
 
<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") %>' />
 
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
 
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
 
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
 
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
 
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
 
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
 
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
 
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
 
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
 
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
 
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
 
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
 
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
 
<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") %>' />
 
first_name:
<asp:Label ID="first_nameLabel" runat="server"
Text='<%# Bind("first_name") %>' />
 
last_name:
<asp:Label ID="last_nameLabel" runat="server" Text='<%# Bind("last_name") %>' />
 
STATE:
<asp:Label ID="STATELabel" runat="server" Text='<%# Bind("STATE") %>' />
 
phone:
<asp:Label ID="phoneLabel" runat="server" Text='<%# Bind("phone") %>' />
 
Company:
<asp:Label ID="CompanyLabel" runat="server" Text='<%# Bind("Company") %>' />
 
S_Descript:
<asp:Label ID="S_DescriptLabel" runat="server"
Text='<%# Bind("S_Descript") %>' />
 
Job_Title:
<asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Bind("Job_Title") %>' />
 
MONTH:
<asp:Label ID="MONTHLabel" runat="server" Text='<%# Bind("MONTH") %>' />
 
S_DATE:
<asp:Label ID="S_DATELabel" runat="server" Text='<%# Bind("S_DATE") %>' />
 
END_DATE:
<asp:Label ID="END_DATELabel" runat="server" Text='<%# Bind("END_DATE") %>' />
 
IS_CONFIRMED:
<asp:Label ID="IS_CONFIRMEDLabel" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
 
IS_PAID:
<asp:Label ID="IS_PAIDLabel" runat="server" Text='<%# Bind("IS_PAID") %>' />
 
Account:
<asp:Label ID="AccountLabel" runat="server" Text='<%# Bind("Account") %>' />
 
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
 
</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 View Related

View Referencing Multiple Tables Not Updatable ?

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

Remote Tables Are Not Updatable. Updatable Keyset-driven Cursors On Remote Tables Require A Transaction With The REPEATABLE_READ

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

View Not Updatable

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

Please Help With This View - Small Modification

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

View Based On Table Modification

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

View With Aggregate Function And Joined Tables

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

Force Modification Of A User Defined Function

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

View From 2 Tables / Multiple Row To One

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

SQL 2012 :: Updatable Subscription - View Pending Log From Subscriber To Publisher

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

Joining Multiple Tables In A View.

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

Using Two Sum Function In Multiple Tables

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

Multiple Updatable Publication On The Same Table!!

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

SQL 2012 :: Create View From Multiple Tables That Have 1 To M Relationship

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

Derived Tables Are UPDATABLE Or NOT ????

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

COUNT Function And INNER JOIN On Multiple Tables

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

Data Access :: How To Create View To Get Records From Multiple Tables

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

Using Updatable Views When Moving Tables From One DB To Another?

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

Derive Base Table/column From View

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

SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index

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

SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level

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

Error (8626) While Inserting Record Into Table With Text Field And Which Is The Base For Indexed View

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

XML - Multiple Base For Derived Type Is Not Suppor

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

Retrieving Multiple Rows From Data Base.

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

DB Engine :: Do System Base Tables Always Use Clustered Index

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

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

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

How Can I Manage Database On Lan Base[network Base]software

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

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables

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

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

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

How AD Affects SQL Server2000/2005

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

RIGHT Function In A View

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

Function In A View

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