Replication :: Unable To Replicate A View Or Function
Nov 13, 2015
I have some issue when replicating a view (transactional replication).I have following objects on publisher:
Table: [Metadataschema].[Entity]
View: [dbo].[EntityLogicalView]
the view definition is:
create view [dbo].[EntityLogicalView] as (SELECT * FROM [Entity] WHERE OverwriteTime = 0)
this is what I get when I run "Script View as Create". The same query is used by the replication.Unfortunately this query fails on the subscriber "Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber". I cannot even runt his query manually on the subscriber, it only works when I change it like this:create view [dbo].[EntityLogicalView] as (SELECT * FROM [Metadataschema].[Entity] WHERE OverwriteTime = 0) Is there any way to get this working? Can I change the creation scripts used by the replication or force publisher to include schema names in all objects?
View 5 Replies
ADVERTISEMENT
Mar 1, 2006
Hello,
I am having a problem trying to run websync but can run replication over the lan. During the websync it errors out stating cannot connect to the distributor. The same login and password are used in both scenarios. Am I missing a setting?
John
View 9 Replies
View Related
May 4, 2007
Hello,
I am trying to add a View to an existing publication and it the subscribers (all devices with SQL CE) don't get the View after replication. I have deleted and recreated the publication, and only the tables will appear on the device, not the view.
Also, I want the data from the view to be dynamic, filtered by using the Host_Name() function/value. Will this work for a View?
thanks
- will
View 3 Replies
View Related
Oct 24, 2007
Hi,
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5.
I have published Tables, Views and SPs as articles.
One of the views has more than 300 columns. So when i try to replicate it, I'm encountering the
following error Message.
"Error 20068: The article cannot be created on table because it has more than 255 columns."
When a view can be created with more than 255 cokumns, why the problem arises when we replicate
it?
Can any one help me on this?
Thanks,
SBR.
View 1 Replies
View Related
Oct 18, 2007
prerequisites:
MSSQL 2005 64bit as publisher (transactional replication)
MSSQL 2000 32 bit as subscriber (pull, read-only)
All objects are set to replicate except foreign keys and defaults.
There is a linked server DW on both the publisher and the subscriber that both can access (sa and user logins mapped)
The snapshot generation and loading is fine.
but then I issue this statement at the publisher:
ALTER VIEW [dbo].[v_Test] as
select * from DW.DW_DB.dbo.v_Test
but it fails to replicate at the subscriber with the message:
Category:COMMANDSource: Failed CommandNumber: Message: ALTER VIEW [dbo].[v_Test] asselect * from DW.DW_DB.dbo.v_TestCategoryQLSERVERSource: SubscriberNumber: 7399Message: OLE DB provider 'SQLOLEDB' reported an error.
I tried the command on the Subcriber via Query Analyzer and it works.
What gives?
All suggestions are welcome!
View 2 Replies
View Related
Mar 20, 2008
All,
I have a huge problem to solve.
1) Need to create an Indexed View that joins multiple tables.I know how to create a regular View, but How do I create an Indexed View ?
2)Need be able to replicate the Indexed View [above] across to another server B into a table.
I have to get this done and seriously don't know where to start.
Do HELP me out .Thank You.
View 2 Replies
View Related
Jul 8, 2004
Ok, I'm knee-deep in replication-hell.
I'm trying to set up snapshot replication between two SQL Server 2000 databases over the internet. Both servers run Windows 2000 server edition and SQL server 2000. The idea is to send certain local information, stored in database tables to a database from which the information is published on a website.
I've set up the distribution database (with the original name: distribution) and connected a publisher to it (let's call that one "source_database") using the sp_adddistpublisher stored procedure. No problems there...
Next step, I added a publication, the tables in source_database, and a subscriber, the website database. I've opened up port 1433 on both servers, but still it won't send anything from the source_database to the website database....
It gives no errors, but just doesn't do anything...
Now, I've looked for possible problems and I think these problems might be able to cause problems:
- The initial snapshot is not reaching the subscriber
- I've made a push subscription which may has to become a pull subscription
- The RPC's aren't available, because both servers are highly secured
- The wrong serverpack is installed (this one is a wild guess)
Does anybody have any idea on how I can get my distribution working or solve any of the problems above, cause I'm running out of idea's....
(P.S. I'm a bit of a noobie to replication, I've set it up in a test environment before and it just worked perfectly, but the real thing isn't)
View 4 Replies
View Related
Jan 19, 1999
I am having two NT/SQL Server 6.5 and i tried to replicate the database on SQL Server from One Server to another Server.
When i try to replicate from Server A[Publisher] to Server B[Subscriber] it is giving error on Distribution History Log on Manage Scheduled Task Dialog.
The Error is like this :
08001 [ODBC SQL Server Driver] [dbnmpntw] ConnectionOpen (Createfile()).
and it is trying again and again by using retry option.
Kindly guide me by mentioning the reason for this error.
Email to : rsraja@sfl.soft.net
Regards
R. Suseendran Raja.
View 1 Replies
View Related
Jan 11, 2007
We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.
Thanks in advance for any advice.
View 1 Replies
View Related
Mar 20, 2007
Hello,
I setup the transactional replication to replicate remote database that has 50 tables.
Two of the tables with huge columns.
I splitted the columns by creating several views before running the initial snapshot.
Questions:
1. Can the database replication copy the views.?
2. Where the view will be stored at the subscriber database?
Thank you.
Edwin
View 3 Replies
View Related
Jun 16, 2015
I'm trying to replicate client data from multiple databases into a single table. So database A, B and C replicate client data to a table in database Z. There is a settings table on A, B and C that holds the businessID and I use a function in a computed column to add this to the client table. At the moment I have just database A and Z as a test and what happens is the computed column gets replicated as a computed column with the formula.Â
Can I change this so that I can replicate the computed value (at db A,B,C) of this column?
I don't really want to add a businessID to the table if possible as I'm going to need to add a number of other tables to this replication which will each need to have the businessID, a computed column would be much easier and save updating a load of scripts.
If the above is not possible is there some other solution available so I can identify which database the records came from in the table on database Z?
View 4 Replies
View Related
Jan 19, 2007
hi all,
Could we configure a merge replication such as replicate the master table and its all related tables (relation deep could be 1.)
We dont want to manually find master table relations and configure replication for the related tables.
please help for that configuration
kinds
View 5 Replies
View Related
Oct 22, 2015
Replicate ddl setting is not working if multiple subscribers are used...
View 2 Replies
View Related
May 16, 2007
Hi!
i have a problem with my replication.
I have two SQL 2005 SP2 Server and want to use the transaction replication with updateable subscriber. Now the problem is that i can do any changes on the master server. But if i want to change a record on the subscriber which contains a ntext, text, oder image column - then i geht the error that the field will be NULL on the master.
Is there any solution to fix this problem? I dont wan`t to change the datatype vom ntext to varchar(max) !!
greetings
Holger
View 3 Replies
View Related
Aug 29, 2015
there are several remote locations where sql is running, my company has asked me to find a way to collect all the data from the remote locations to a central location automatically,for example day to day data should be synced at night time from 2am to 7 am and it should be compressed automatically before data transfers to the central location. NOTE there is no domain only standalone workstations
View 3 Replies
View Related
Sep 2, 2015
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
View 2 Replies
View Related
Oct 15, 2015
Is it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.
If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?
View 3 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Sep 7, 1999
Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!
Thanks for ANY information you can give me...
~Jepadria
View 2 Replies
View Related
Aug 16, 2007
Let say I've 4 server with MS SQL 2000 installed and I want all of them having the same data. So I'm using merge replication by assign one of them as publisher/distributor.
The problem is when ( let say ) server that have been assign as publisher/distributor down then all the other server cannot make replication.
My idea was to make it replicate to other available server ( among them ) if replication to main server failed.
Is it possible ?
If possible how to do it ?
Thanks.
View 1 Replies
View Related
Apr 13, 2007
When I try to do a pull using a view I'm getting the following error message:
€œThe query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME.viewname ]€?
but when I do a select col1 from view I get my data. The view has some inner joins included in it, could that be the issue?
NOTE: this is from my handheld device, its not SQL Server related. I posted this earlier in this forum but it was moved to SQL Server.
I get the error on my handheld device and handheld device only
View 4 Replies
View Related
Apr 13, 2007
When I try to do a pull using a view I'm getting the following error message:
€œThe query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME.viewname ]€?
but when I do a 'select col1 from viewname' I get my data. The view has some inner joins included in it, could that be the issue?
NOTE: this is from my handheld device,
View 4 Replies
View Related
Dec 5, 2004
Hi,
Me and my thick skull :)
Couldn't get much help from books on-line and therefore can someone please helpme understand
the YTD function withan example maybe?
View 3 Replies
View Related
Mar 28, 2002
Hi All,
I'm running SQL Server 2002 and trying to create a User Defined Function. However, everytime I try to save the script I get Error 170 Incorrect Syntax near 'FUNCTION'.
This happens if I create the Function from Code or use the Enterprise Manager. I'm logged in with 'sa' privs, so I don't think it's a privilege issue. I'm well confused.
Anyone help?
TIA
William.
View 1 Replies
View Related
Jul 25, 2005
Hi,
While creating an indexed view with the command :
create unique clustered index idx_atrid on account_transactions (policy)
there is a check constraint on the policy column of the tables used in this view.
The following error is encountered
Cannot index the view 'test.dbo.account_transactions'. It contains one or more disallowed constructs.
Can anyone help?
View 1 Replies
View Related
Oct 18, 2005
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
View 2 Replies
View Related
May 1, 2008
Hello,
I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:
(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))
it always return '0'.
Here is a very cut down version of the view, only selects the uid! from a single table
IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL
DROP VIEW GetMessageQueueDetails ;
GO
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW GetMessageQueueDetails
WITH SCHEMABINDING
AS
SELECT Uid
FROM dbo.MyTable
GO
I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :
"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."
Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?
Or am I doing something else wrong?
Can anybody offer any help?
Thanks
View 2 Replies
View Related
Jul 24, 2015
I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
View 4 Replies
View Related
Jan 29, 2007
Msg:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
"open symmetric keys" is not allowed in a function?
if I want to encrypt a string in a function by sql2005's internal functions ,how can I do ?
View 3 Replies
View Related
Feb 12, 2014
I created a view with a simple join query
there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as
"select orderid from joins where orderid=NULL"
Query :
create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid
View 1 Replies
View Related
Sep 9, 2015
We have a maintenance plan in place for updating the statistics on daily basis. Now, i would like to remove the view from maintenance plan. How can i remove that from Update statistics task?
View 8 Replies
View Related
Oct 31, 2007
Hi,
I have couple of Web pages linking to my RDLs using report viewer Web controls. I have no problem running and viewing them in VS2005 debug mode and report manager until I published them to local folder and set the local folder as an application on my IIS7. When running the published version of the Web pages, all my calendar image buttons (which happen to be part of the report parameter fields) turned to red 'X':
http://dyzimw.bay.livefilestore.com/y1pwxE9mLpX4JV7e438WA4DeGwu6YHQOkRoC20r_EhQ5uoXkt15dqs99LmiDSTauSvUCno9HnOwYKR34rPpFYA_lQ/ssrs.jpg
When clicking the View Report button, the animated progress icon not appearing at all. Before the occurance of this problem, I did face the problem whereby the 'NT AUTHORITYNETWORK SERVICE' does not have enough permission to view the report. After referring to some threads, I finally be able to get rid of this error by assigning Browser role to 'NT AUTHORITYNETWORK SERVICE' from the report manager. I wonder could this solution be related to the problem that I'm facing now. Any help would be appreciated.
Thank you.
Regards,
Antonio
View 1 Replies
View Related
Jul 21, 2004
Hiya folks,
Having a problem with a view.
I'm using the syntax
Right('000' & Myfield,3) to 'pad' out the results.
ie if MyField contained 45 the answer should be : 045
but i'm getting the results of : 0
Stuck!! Any offers kind people??
View 4 Replies
View Related
