Views Not Updateable
Mar 23, 2007How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.
ADVERTISEMENT
Updateable View
May 28, 2002Hello-
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)
How To Tell If A VIEW Is Updateable
Oct 11, 2007I 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
Updateable View
Mar 24, 2008I 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.
Updateable Vuew
Aug 14, 2005I 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 RelatedBrowser Updateable Recordsets ?
Mar 26, 2000I 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
Conflicts In Updateable Subscriptions
Sep 11, 2007I'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.
Updateable Transactional Replication
Mar 6, 2007Will Sql Express handle being a subscriber for an updateable transactional publication from Sql Server 2005?
View 6 Replies View RelatedReplication - Merge Vs Updateable Subscription
Feb 14, 2003Does 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
Apply Sp4 For Updateable Transactional Replication
Jul 11, 2006Hello,
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
Transactional Replication With Updateable Subscribers
Nov 28, 2005Hi,
View 7 Replies View RelatedTransactional With Updateable Subscription And Log Shipping
Mar 8, 2007We 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
Synchronize Updateable Pull Subscription
Mar 5, 2007I 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!
Are Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedIncluding SQL Server Allow Nulls Fields In Updateable Controls
Jun 13, 2006When 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 RelatedMS SQL Server View Linked Into MSAccess Is Not Updateable Recordset
Jul 29, 2004Hi All,
How can I link a view into MS Access from MS Sql Server, that I can update as a tabble.
Thanks
Laszlo
Transactional Replication With Updateable Subscriptions And Identity Management
Oct 28, 2006Replication 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?
SQLServer 2000 Connected To Access 2003 - Not Updateable
Mar 12, 2008I 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
Support For OLEDB Scrollable Updateable Cursor Via Query
Jul 5, 2007Hi,
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
SQL Server 2008 :: Replication Articles Read-only AND Updateable At Same Time
Apr 21, 2015We 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?
Large Views Vs Multiple Small Views
Sep 6, 2007Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedRecompiling Views That Reference Other Views
Jun 28, 2007Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Quicky : Views Of Views Of Views
Feb 22, 2007Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
Views Dependent On Other Views
Mar 14, 2006Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies View RelatedCan Some One Help Me With SQL VIEWS
Apr 15, 2008Hi everyone, I have a problem like this . I have tables Coursegroupcode, which has groupname, codeI have Courses That has Coursename, its code(group code),Term, Course Number Enrollment table which has Foreign keys Term,Course NUmber , SSN I need to get a view like thisI should list all the coursecodes and people enrolled for each course code for selected terms Course Table Primary keys(TERM,COUSE Number)Enrollment Table(Foreign keys) TERM ,COURSE NUMBER, SSNplease help
View 9 Replies View RelatedSql Views
Apr 7, 2005 Hi!
I need to know if i can build an aspx file on top of an sql view? I
can only see my tables when i connect to the database....
Collette.
SQL Views
Oct 18, 2005Can SQL Views insert to the tables they are created from?I have a database that is not well structured. It has a lot of redundency. What I want to do is create a SQL View that brings in all the data I need and have my application use that new SQL View instead of the data tables. Then I want to be able to insert new information to a SQL View that actualy gets inserted into the tables that the SQL Viewer is created from.Can this be done?Does this make any sense?
View 1 Replies View RelatedViews
Mar 26, 2001I have a query which unions the four select statements.....
the select statements are joined with other tables and views.....
When i execute the query i get ODBC timeout error........
But the strange thing is that if i execute the view individually once and again execute the query it works fine.......and later it justs works fine....
Can anyone tell why is it like that.......
Thanks,
Sajai.
SP's And Views
May 23, 2000Is there a performance hit running a sp against a view versus a base table. The view just excludes
several of the records based on some criteria, and all the data I will be retreiving is included in
the view. Or should I just stick my criteria in the SP to exclude the data?
Iif Views
Jun 19, 2000Is there a way to imbed "iif"-like logic in a SQL view? "case when" works in regular queries, but apparently is not supported in views...
View 1 Replies View RelatedViews
Aug 14, 2002I have some rather complex views to work with. Do they slow you down? Would it be better to move a view into a stored procedure? Is there any difference between these three solutions?
<B>1.Using views:</b>
Create view X
As
Select Col1, Col2, Col3 from Table1
Go
Create view Y
As
Select Col1, Col2, Col3 from Table2
Go
Create proc Z
As
Select X.Col2, X.Col3, Y.Col2, Y.Col3
From X inner join Y on X.Col1 = X.Col1
GO
<b>2.Using just a stored proc:</b>
Create proc Z
As
Select X.Col2, X.Col3, Y.Col2, Y.Col3
from
(Select Col1, Col2, Col3 from Table1) X inner join
(Select Col1, Col2, Col3 from Table2) Y on X.Col1 = Y.Col1
<b>.Joining tables</b>
Create proc Z
As
Select Table1.Col2, Table1.Col3, Table2.Col1, Table2.Col3
FromTable1 inner join Table2 on Table1.Col1 = Table2.Col1
go
Thank you!
VIEWS
Aug 1, 2001I am struggling with setting up views in SQL Server 7.0 and 2000. What types of views are available.
I have one database with 112 tables and 2308 fields.
Thanks in Advance
Views
Jun 9, 2004I have created a database connected to a GUI using VB 6.0. When editing the views from the front end, do the tables also get updated
Thanks