Updatable Views
Dec 12, 2007Hello everyone,
How can i create updatable views in sql 2005. Any article or link can be useful.
Thanks.
ADVERTISEMENT
Updatable Views
May 17, 2001I have a fairly complicated data import that needs to be done, but the table structures of the two databases don't match up too neatly. I thought I'd be clever, and create a view in the target database, and import into that view -- only to find out (after reading the manual) that multi-table views are not updatable and cannot be used with BULK INSERT or bcp.
I'm not a database pro, I'm a programmer thrust into this role. Needless to say, I'm a bit of a newbie on these things!
Anyway, the view's code:
<pre>
CREATE VIEW dbo.RJ2_V_Client_Import
AS
SELECT Clients. IDENTITYCOL, Clients.CompanyName,
Clients.Address1, Clients.Address2, Clients.StateID,
Clients.City, Clients.Zip, Clients.CountryID, Clients.PhoneMain,
Clients.PhoneFax, Clients.WebAddress,
Clients.ParentCompany, Clients.DivisionDepartment,
People.NameFirst, People.NameLast,
People.AddressPrimary1, People.AddressPrimary2,
People.CityPrimary, People.StatePrimaryID,
People.ZipPrimary, People.CountryPrimaryID,
People.PhoneWork, People.PhoneWorkExtension,
Clients.EnteredDate, Clients.UserLevelID, Clients.Revenue,
Clients.NumberOfContractors, Clients.SICCode,
Clients.Industry, Clients.ReceptionistName
FROM Clients INNER JOIN
People ON Clients.BillingPersonID = People.PID
</pre>
The reason there is a "People" table is because several (SEVERAL) tables in this database contain records for people, and we felt that this was a better design because of it.
The data we're trying to import contains records for billing contacts, which are the People.* fields referenced. It's extremely important that we get this data in the system.
Since BULK INSERT is out, are there any workarounds? There's got to be some kind of non-manual solution, since I know other people have to do imports that start as one table and end up in two ...
For this particular set, I could do it manually -- only two records are affected. BUT, for future sets, I can't do it manually -- literally thousands of records will be affected, and I just don't have the time (or inclination) to do the entry ... Plus, think of the potential error rate!
Thanks in advance
John
Using Updatable Views When Moving Tables From One DB To Another?
Dec 3, 2007Hello,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 RelatedRemote 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
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 RelatedUpdatable Cursors
Jun 2, 2000I am trying to update the current row of a cursor that I have declared from Query Analyzer and I keep getting an error message saying the cursor is Read only. This doesn't make sense because BOL says that cursor's are updatable by default. Anyone had this problem?
Ex: This is the statement I am using to update the current row in the cursor:
UPDATE TableName SET Field1 = 'D'
WHERE CURRENT OF CursorName
jg
View Not Updatable
Apr 12, 2000I 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?
Updatable Cursors
Jul 20, 2005I am trying to write updatable cursors in a stored procedure? Can I getany help to write updatable cursors?Any help would be appreciated very much.Thanks for your help in advance.Rajah V.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedWhy Isn't Table Updatable????
Jul 20, 2005Hello:I've just converted my Access 2K db to SQL Server 2K and the conversion wentsmoothly. I created users, roles, views and forms and gave the userspermissions to use them. I "THINK" I have done everything, but......youcannto update the form, view, or table. I get "Recordset Not Updatabale"when I modify a field in either.Assuming I set the permissions correctly on the db, table, view, and userscorrectly, is there something else I need to do to make the table and viewupdatable?My front end is Access 2K.Thanks for any advise.....Richard H
View 1 Replies View RelatedDerived Table Not Updatable
Oct 6, 2004I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.
Thanks in advance!
Problem With Updatable Subscriptions
Nov 1, 2006Hi all,
I have a problem with "Updatable Subscriptions" under concurrent transactions on both pub and sub effecting same data. At high level of concurrency it Subscriber starts giving error msg "Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber.".Please tell me any way arround for this.
Replication With Updatable Subscriptions
Jun 13, 2007I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions.
On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER
epl_snapshot, PUBLISHERSERVER
epl_logreader and PUBLISHERSERVER
epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER
epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER
epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
Updatable Subscriber Under Republisher
Dec 1, 2006Hello, There are 2 questions that I interested in:Q1: We are planning to scale out SQL Server 2005 with Transactional Replication and use the following topology:NodeA: PublisherNodeB: Publishing Subscriber of NodeANodeC: Subscriber of NodeBIf it is necessary to allow update on NodeC, is it possible to set NodeC as an updatable subscriber? BOL states that Updatable Subscriptions do not support republishing data. But I'm not sure whether it is applicable to these case. Please advice.Q2:In SQL Server 2005 BOL, in claim that "Standard transactional replication assumes read-only Subscribers and is
hierarchical in structure: typically a single Publisher publishes data
to one or more Subscribers. Standard transactional replication also
supports a republishing hierarchy: updates are delivered from a
Publisher to a set of republishing Subscribers, who in turn deliver
updates to a final set of leaf-node Subscribers. Updating
subscriptions offer the ability for Subscribers to push changes back to
the Publisher, but the arrangement is still hierarchical because
changes follow the hierarchical structure when moving between
Subscribers and Publishers. ..." in the topic "Peer-to-Peer Transactional Replication" Could somebody describe how Subscribers push back changes to Publisher in hierarchical structure? Could Republisher setup in the "hierarchical" structure or topology as a middle-node (Not a leaf node nor root node)?Thanks a lot.Terence
Derived Tables Are UPDATABLE Or NOT ????
Sep 28, 2007Hi,
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
????
Replication Question - Updatable Subscription?
Apr 11, 2006Hi all,
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
Faraz
Cannot Write To Column In Updatable RecordSet
Feb 3, 2004Hi. I receive the SQLException:
[Microsoft][SQLServer 2000 Driver for JDBC]Can not update, the specified column is not writable.
when I attempt to call updateString() on a RecordSet column. I have set the Statement object on which I execute the query to obtain the RecordSet to be scrollable and updatable.
My goal is to replace the ID value contained in a particular column of each row with a longer text string that includes the ID value. If I can't alter the column values via the RecordSet, can I write SQL as part of the query to generate the text string?
Thank you.
Raj
Problem With Queued Updatable Subscriptions
Mar 7, 2008Hi All,
I have a problem replicating data from Subscriber to Publisher.
I configured Queued updatable subscriptions in transactional replication. Publisher is on SQL Server Standard edition and Subscriber is on SQL Server Express edition, both on Windows XP.
When I insert/update data in publisher, the data is replicated to subscriber. If I insert/update data on subscriber the data is not replicated. When I see the replication monitor for errors, it shows an error "Cannot load a DLL xprepl.dll or one of its resources".
Could any one give me a solution or please tell me if I missing something?
Thanks you all,
Ravi.
Updatable Subscription In Transactional Replication
Sep 8, 2005Hi guys,
View 6 Replies View RelatedTransactional Publication With Updatable Subscriptions
Apr 24, 2007Hi to evebody.
I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.
When I do an update in a subscriptor's table, the database engine shows the following error:
21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.
And rollbacks the transaction.
Does anybody knows what to do to solve this problems.
The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2
The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2
I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.
Thank you in advance.
Sebastian.-
PS: Sorry about my english, it's been a long time without using it.
Updatable Subscriptions - No Transaction Is Active
Apr 11, 2007This may be a simple answer, but I was wondering when setting up transactional replication with updatable subscriptions why does this error occur?
Error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK " was unable to begin a distributed transaction.
Changed database context to 'MyDB'.
OLE DB provider "SQLNCLI" for linked server "REPLLINK" returned message "No transaction is active.". (.Net SqlClient Data Provider)
I've looked for MSDTC security documents and checked every box in the security dialog and selected the 'No Authentication Required€™. MSDTC and SQL Agent are running as NETWORKSERVICE user. I also setup permissions on each individual DCOM Objects for SQL Server. And MSDTC service is running. Both Servers are SQL Server 2005 Standard and Windows 2003 R2. The publisher is on the same box as the distributor. No domains or trusts setup on either. This error occurs when changing data on the subscriber.
Thanks,
Patrick
Multiple Updatable Publication On The Same Table!!
Oct 17, 2006Hi,
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
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 RelatedSQL 2012 :: No More Transactional Replication With Updatable Subscriptions
May 24, 2012So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
How To Make Fast Forwad Only Cursor Updatable
Sep 15, 2007In the msdn SQL server document(http://msdn2.microsoft.com/en-us/library/aa172573(SQL.80).aspx), it states:
Transact-SQL cursors can specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
But in the following section 'Implicit Conversion of Fast Forward-only Cursors', it states:
If a fast forward-only cursor is not read-only, it is converted to a dynamic cursor.
Now I am confused: is it possible to make a fast forward-only cursor 'not read-only'(updatable)?
A fast forward-only cursor by definition is read-only. Am I wrong?
Thanks,
Baihao Yuan
View Referencing Multiple Tables Not Updatable ?
Jun 7, 2006Hi
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 )
Unable To Set The Publisher Login For The Updatable Subscription
Jun 15, 2007I keep getting this following error when trying to set-up an updateable subscription on SQL 2005 (latest service packs). Server is set for mixed mode auth.
At the final stage of the new subscription wizard I get:
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
The user is not associated with a trusted SQL Server connection
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Error Number: 18456, Severity: 14, State: 1,Line Number: 1
Can anyone recommend a possible way of debugging this? Is it some issue with RPC security?
SqlCeResultSet Looses Updatable Option If Query Has 2 Params
Feb 26, 2008i have result set from such query :
resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;
queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
Updatable Transactional Replication, Queue Reader Error
Jul 3, 2007I have just started getting the following error from the queue reader but I can't see why.
The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.
Does anybody know what it means / how I can fix the problem?
Thanks
SQL 2012 :: Updatable Subscription - View Pending Log From Subscriber To Publisher
May 7, 2015I 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?
Determine Updatable Transaction Replication Mode From Client Side
Sep 28, 2007
We are currently testing a Replication topology as follows:
SQL Server 2005 Developer as publisher and distributor of a push updatable transaction replication
2 SQL Server 2005 Express clients as subscribers.
The replication is working great. If the connection is lost, the subscribers switch from Immediate to Queued Updating, and seem to shift back to Immediate when they regain the connection.
My question is to determine if there is a way that I can ask SQL Express on the client which mode is it currently running, or ask SQL Express if it has a current live connection to the distributor.
I know that they are usually mutliple ways to accomplish tasks, and I would be open any suggestions, preferrable programming in VB (such as with SMO or RMO) or using stored procedures or functions.
I hope this give some people something to chew on during the weekend and thank you to all that have taking the time to read this.
Transactional Publication With Updatable Subscriptions In SQL Server 2005, SvPk 2
Jul 31, 2007
Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.
I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)
The subscriber is unable to connect to the Distributor using the SQL Server login.
Following is the error message I get:
Creating Subscription(s)...
- Creating subscription for 'SQL3' (Warning)
Messages
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
MSDTC on server 'SQL3' is unavailable.
Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476
Please suggest procedure to end this dilemma. The link has no info available.
View Or Function Xxx Is Not Updatable Because The Modification Affects Multiple Base Tables?????
Aug 17, 2007I 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>
SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index
Aug 26, 2014We 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