Updateable View

Mar 24, 2008

I have a view that combines several tables, and lastly one column that is determined by a subquery.

i.e.
CREATE VIEW [View]
AS
SELECT
[TableA].*,
[TableB].*,
(SELECT TOP 1 [Column] FROM [TableC] ORDER BY [Column2])
AS [SpecialColumn]
FROM [TableA] INNER JOIN [TableB]
ON [TableA].[ColumnID] = [TableB].[ColumnID]

I have made INSTEAD OF UPDATE and INSTEAD OF INSERT triggers for this view that will then allow you to modify [View], and it will handle everything, adding/modifying the data in the three [TableX] tables accordingly.

In SQL Management Studio, the following code works fine, and is what is expected...

UPDATE [View] SET [SpecialColumn] = 1 WHERE [ColumnID] = @DesiredID

This will call my instead of trigger and update my special column accordingly exactly how I want it to.

Now, the problem lies in the application layer running on top of the database and how it accesses the view. Any call from the application to update [SpecialColumn] returns an OleDB error stating that [SpecialColumn] can not be modified. I assume that sql server is identifying the column as some sort of a derived column that normally wouldn't be able to be updated, and OleDB is restricting the column to just read only. Is there some way to override that in my view to say that the column is not read-only? Is this an OleDB restriction that can't be handled in the database/view itself?

I've also tried creating a stored procedure that called the exact same
UPDATE [View]
SET [SpecialColumn] = @param2
WHERE [ColumnID] = @param1
and it also works within management studio, but still gives a similar OleDB error about how the stored procedure attempts to update a derived column that can not be modified.

I have a solution where my stored procedure calls the exact same code as the instead of triggers to update the underlying tables of the view manually, however, I'm not asking for a solution like this to my problem, because this works fine, I simply am not happy having to do band-aid workarounds and am wondering if this is a common problem in front-ends and whether it can be handled on the database layer.

View 1 Replies


ADVERTISEMENT

Updateable View

May 28, 2002

Hello-

I have a view that used to be updateable (except for a few fields, but that is fine). When I use the results of a CASE statement as one of the field values, the entire view is not updateable. The view is pasted below. Does anyone know how to keep my view updateable and, somehow, add this new field? Thank you.

-Eric


SELECT
DATEPART(yy, Orders.NextShipDate) AS year,
DATEPART(mm, Orders.NextShipDate) AS month,
DATEPART(dd, Orders.NextShipDate) AS day,
People.LastName,
People.FirstName,
Customers.CustomerID,
Orders.NextShipDate,
Customers.Custom,
Orders.AllocationTemp,
Orders.ShippingTemp,
Orders.HorseName,
Orders.PriorShipDate,
Customers.Username,
Customers.Password,
Orders.OrderID,
Orders.Deleted,
Orders.Deadbeat
FROM
People
INNER JOIN Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ON People.PeopleID = Customers.PeopleID
WHERE
(CONVERT(DATETIME, CONVERT(CHAR(10), Orders.NextShipDate, 101)) <= DATEADD(d, 7, CURRENT_TIMESTAMP)) AND (Orders.Deleted = 0) AND
(Orders.Deadbeat = 0) AND
(Customers.Deleted = 0) AND
(Customers.Pending = 0) AND
(Orders.OrderID NOT IN
(SELECT Orders.OrderID
FROM Items INNER JOIN Products ON Items.ProductID = Products.ProductID INNER JOIN Orders ON Items.OrderID = Orders.OrderID
WHERE
(Items.Dose = 0) AND
(Products.IsPharmaceutical = 1) AND
(CONVERT(DATETIME, CONVERT(CHAR(10), Items.Created, 101)) > DATEADD(d, - 5, CURRENT_TIMESTAMP)) AND
(Items.Deleted = 0))) AND
EXISTS (SELECT ItemID FROM Items WHERE Items.OrderID = Orders.OrderID AND Deleted = 0)

View 1 Replies View Related

How To Tell If A VIEW Is Updateable

Oct 11, 2007

I could have swore I posted this earlier today, but it must not have went through...anyway here goes again.

Is there any way to tell if a view is updateable (updates the underlying table(s))?

Thanks,

Van

View 3 Replies View Related

MS SQL Server View Linked Into MSAccess Is Not Updateable Recordset

Jul 29, 2004

Hi All,
How can I link a view into MS Access from MS Sql Server, that I can update as a tabble.
Thanks
Laszlo

View 3 Replies View Related

Updateable Vuew

Aug 14, 2005

I am hoping to create an updatable view from two tables joined on twofields. I have two questions;1. How can I enforce a one to one relation in sql server 2005 based on twofields? Do I need to create one explicitly under relations? Do I cerate anindex on both fields? Is there a step by step process I can look at tocreate one-one relations?2. Is a view updateable if there is a where clause on one of the fields inthe join/relation?Thank again.Regards

View 1 Replies View Related

Views Not Updateable

Mar 23, 2007

How do I make a view non updateable? I want to create a view so that Iwill not be able to update, insert or delete the view so that basetable is not affected (except dropping the view itself). Thanks.

View 4 Replies View Related

Browser Updateable Recordsets ?

Mar 26, 2000

I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net

View 2 Replies View Related

Conflicts In Updateable Subscriptions

Sep 11, 2007

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.


Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,
-chiraj.

View 3 Replies View Related

Updateable Transactional Replication

Mar 6, 2007

Will Sql Express handle being a subscriber for an updateable transactional publication from Sql Server 2005?

View 6 Replies View Related

Replication - Merge Vs Updateable Subscription

Feb 14, 2003

Does anyone have advice as choosing between merge replication and updatable subscriptions? I've read the documentaion, they seem to offer the same functionality, and I don't see any clear guidelines as to why to pick one over the other...any suggestions would be appreciated!

Ed

View 1 Replies View Related

Apply Sp4 For Updateable Transactional Replication

Jul 11, 2006

Hello,
We have two updateable subscribers and a publisher using transactional replication. The distributor is located on publisher. Currenetly all 3 sites are SQL 2000 SP3. We plan to upgrade all sites to sp4 with minimum downtime (if possible zero). Is it possible to upgrade publisher/distributor first and subscribers after few days?
Thanks,
np70

View 1 Replies View Related

Transactional Replication With Updateable Subscribers

Nov 28, 2005

Hi,

View 7 Replies View Related

Transactional With Updateable Subscription And Log Shipping

Mar 8, 2007

We have a database set up for transactional replication with an updateable subscription. When we add log shipping to the publication database (sending the logs to a separate server) the publication and subscription entry show up in Management Studio's replication folder on the log ship target server (although the definitions are correct).

Is this configuration legitimate? Can I add log shipping to the subscription database as well?

Thanks... Liston

View 4 Replies View Related

Synchronize Updateable Pull Subscription

Mar 5, 2007

I am using RMO to synchronize an updateable transactional pull subscription from Sql Express SP2 to Sql Server 2005 64-bit Standard Edition. The data propogates from the server to the client, but changed data on the client does not replicate to the server. Any ideas?

Thanks!

View 3 Replies View Related

Including SQL Server Allow Nulls Fields In Updateable Controls

Jun 13, 2006

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record!  If I remove the Allow Nulls field I can then edit it!  What am I missing here?

View 1 Replies View Related

Transactional Replication With Updateable Subscriptions And Identity Management

Oct 28, 2006

Replication Type: Transactional With Updateable Subscriptions
SQL Server Version: SQL Server 2005 Enterprise Edition
Publisher, Subscriber1 and Subscriber2 all on same SQL Server

Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?

Example:

Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3).
Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1).
Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100.
Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.

I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.

Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?




View 3 Replies View Related

SQLServer 2000 Connected To Access 2003 - Not Updateable

Mar 12, 2008

I have used the Access 2003 Upsize Wizard to create SQLServer Tables, all worked fine except I cannot Update the tables from Access.

The idea being to use Access Forms as the front end to an SQLserver back end as the Access database is quite complex and filled with macros and queries, not one of mine.

I can update the table in SQLServer but the connected tables at the Access end are read only. Why?


Kind regards,

Johnv

View 1 Replies View Related

Support For OLEDB Scrollable Updateable Cursor Via Query

Jul 5, 2007

Hi,

is there any update on the subject?

Jet has it for ages.



I want to use Scrollable/Updateable Cursors & OLEDB Rowsets opened via Select query.

Last time I have heard that MSFT is planning to add this in the future.



Is there a beta version that I can try?



Thanks,

Konstantin

View 8 Replies View Related

SQL Server 2008 :: Replication Articles Read-only AND Updateable At Same Time

Apr 21, 2015

We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.

We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.

Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.

We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.

It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.

I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)

So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?

View 9 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

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

Updating My View Changes My View Content

Feb 17, 2006

I have this view in SQL server:

CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt


When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:

SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt

I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help

View 4 Replies View Related

View Works, But The Sql From The View Does Not

Oct 27, 2006

I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)

View 1 Replies View Related

Selecting A View And Selecting FROM A View Is Wildly Different

Feb 21, 2006

A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.

He wants to use the data returned from that view in an OLE DB Source component.

When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).



He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>

In this instance all the data was returned in approx 60 seconds (as expected).





This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.

Can anyone explain why?

Thanks

-Jamie

View 2 Replies View Related

Need Help With A View

Mar 29, 2007

I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year.
tblPayments (ClientID, PmtID, PmtDate, PmtAmt)
How would I set the criteria for this?

View 1 Replies View Related

Need Help Using A View.

Feb 7, 2008

How do I set up a sql statement to use as a view?
I want to use two tables but they are both from different databases.
From the Offices database I want to use a table called Office Code and only the Name column
The other database is called Library and I want to use a table called Requestors and join it to others.
I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database.
Create view GetDataview
select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name],  Titles.title as Title from requestors
join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column?
 I hope this  makes sense

View 6 Replies View Related

Sql View Help

May 18, 2008

how can i   make a view in my sqlexpress 2005  database that get data from oracle database or from RDB (old oracle database)

View 6 Replies View Related

View ???

May 18, 2001

I am trying to write a view to retrieve data from
two different servers..
create view as
select c1 from ser1.database1.dbo.table1
union
select 1 from ser2.database2.dbo.table1

First it is not accepting to write it from EM, hence I wrote it from QA,
the problem when I am trying to execute it is giving following error

"Could not find server 'hercules' in sysservers"

Help me out what needs to be done?

View 2 Replies View Related

View S

Jan 12, 2001

i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.

ur help is appriciated.

View 1 Replies View Related

View

Sep 21, 2000

I have a view which I am supposed to run manually first thing tomorrow morning but I will not be
in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved