Row Filter In Transactional Publication With Updateble Publisher Not Working
Oct 5, 2007
I have configured transactional publication where some tables have a simple row filter. And I made a supscription to this publication. When I make snapshot and replication initialization, all rows are updated correctly and passed to subscriber. But when I change some column at a row included by filter at publisher, it is never updated on the subscriber. Other tables and that same table when I remove filter is updated correctly.
What can be problem?
I am using SQL Server 2005 Standard for publisher, and SQL Express 2005 for subscriber, both with SP2.
Any help would be apritiated.
Thanks,
Tomas
View 7 Replies
ADVERTISEMENT
Mar 23, 2006
Hi,
I just want to know, is it possible to do this ?
If yes, how to do it ?
Thanks.
View 5 Replies
View Related
Sep 14, 2006
Can Express edition be a publisher for transactional replication with SQL-DMO? Does scripting out and editing it work? Does stored procedure work for this purpose?
Thanks
abdul
View 3 Replies
View Related
May 28, 2008
Hi,
We have an existing merged replication schema that works well. One of the
tables is named audit. Currently this is a bidirectional transfer. However,
we want a new audit table that will transfer data from the subscriber to the
publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.
View 1 Replies
View Related
Jan 17, 2007
Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
Thanks.
David.
View 7 Replies
View Related
Sep 18, 2006
Hi;
In http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71679 topic, it is stated that SQL-DMO can be used to make msde publisher for transactional replication.
But in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71983 topic, Rishi said that "No Express Edition cant act as Publisher".
I am not clear about the difference btw msde and express.
Why msde can be publisher for transactional replication by SQL-DMO and why express edition can not be?
Thank you so much...
View 2 Replies
View Related
Apr 24, 2007
Hi 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.
View 1 Replies
View Related
Oct 23, 2007
Hi,
I'm creating a new transactional publication and when the process begins to add articles i got the follow error:
Messages
* SQL Server Management Studio could not create article 'Tbl_Menu'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Automatic identity range support is useful only for publications that allow updating subscribers.
Changed database context to 'sodimprumde4'. (Microsoft SQL Server, Error: 21231)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=21231&LinkId=20476
Thanks for any help, If anyone know how to resolve this problem.
View 2 Replies
View Related
Jun 13, 2008
I have a master database at headquarters which will be the publisher. I have 5 databases at client sites that have the same database schemas as the master, which will be the subscribers. Each table in the database contains a Customer_No column, which is set as a default to 1 at client 1, 2 at client 2, 3 at client 3, etc. The master database contains data for all customers.
How do I get Merge Replication to only replicate the data from the publisher based on the customer_no column in each table?
View 1 Replies
View Related
Apr 1, 2015
I am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.
For example:
Publisher database tables that are being replicated:
Table1
Table2
Table3
and I am going to create new tables in publisher database
Table1_new
Table2_new
Table3_new
Move data from
Table1--->Table1_new
Table2--->Table2_new
Table3--->Table3_new
Drop constraints from and then tables (does this require articles to be removed from replication?)
Table1
Table2
Table3
Rename
Table1_new to Table1
Table2_new to Table2
Table3_new to Table3
Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?
View 0 Replies
View Related
Jul 23, 2005
I have taken over a transactional replication setup that is being usedfor fault tolerance (I know, I know...).The scenario I am concerned with is where the publisher goes down due tofailure, so we need to point our application at the subscriber and startupdating the data there. We are not using the immediate updating option.How do I go about re-syncing the publisher with the data that been addedto, or changed on the subscriber, when the publisher comes back online?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Feb 15, 2007
Hello,
We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."
This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.
Can someone point me in the right direction?
Thanks,
BCB
View 1 Replies
View Related
Mar 1, 2007
Hi all,
We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?
Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?
Thanks,
Iain
View 3 Replies
View Related
Jul 21, 2006
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
View 2 Replies
View Related
Apr 15, 2004
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting
In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1
Any help would be greatly appreciated
View 4 Replies
View Related
Jan 16, 2008
Hi,I am trying to set up a Updatable Subscriptions for TransactionalReplication with SQL 2005 (queued updating).I have a stored procedure "SYS_spShellTest" which is replicated to thesubscriber viathe replication option "Execution of the stored procedure":-------------------------------------------------------------ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)WITH EXECUTE AS ownerasBEGINEXEC @result = master..xp_cmdshell @cmd, NO_OUTPUTReturn (@result)END-------------------------------------------------------------1) If I call "exec SYS_spShellTest" on the publisher the call is alsoexecuted on the subscriber.2) If I call "exec SYS_spShellTest" on the subscriber the call is NOTexecuted on the publisher.How can I make 2) work so that it is also executed on the publisherplease?Thanks a lot,Patrick
View 1 Replies
View Related
Jun 26, 2007
Hello,
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
Thanks in advance,
John
View 3 Replies
View Related
Sep 26, 2014
I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
View 0 Replies
View Related
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 2 Replies
View Related
Mar 18, 2015
I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
View 0 Replies
View Related
Jan 12, 2006
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
View 2 Replies
View Related
Apr 13, 2007
Hi All,I have a table with a column DeletedDate which stores a logical deleteof a record.I need to set up transactional replication for reporting purposes thatthis deleted records should not be replicated to the subscriber. Thatis, if i see a value on the DeletedDate, I don't want that record tobe picked up for replication.At the same time, when someone marks the record for deletion (byputting a date on the DeleteDate column), I want that record to bedeleted on the subscriber database. (I can also set up a job to do thedeletes on the subscriber but i'd rather let the replication take careof it).Can this scenario be implemented in Microsoft SQL 2000? I wouldappreciate any ideas / thoughts in this matter.Thanks in advance,Aravin Rajendra.
View 3 Replies
View Related
Jan 22, 2007
Hi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
View 1 Replies
View Related
May 14, 2015
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
View 2 Replies
View Related
Aug 10, 2007
Is this a known issue? When I try to filter my report using Like operator on one of the Report parameters/dataset field it returns nothing. If I use = it works? Any ideas how to use the "Like" operator in reporting?
Thanks,
View 10 Replies
View Related
Jan 12, 2008
Hi,
I've tried using the same method mentioned in the dozens of posts dedicated to this topic but it's not working.
My SQL statement is:
SELECT [University Origins].Origin, COUNT([University Origins].Origin) AS TotalCount, Hospitals.Name
FROM Posts INNER JOIN
Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] CROSS JOIN
[University Origins] INNER JOIN
Incumbents ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin]
WHERE (NOT ([University Origins].Origin = 'No answer')) AND (Hospitals.Name IN (@HospitalFilter))
GROUP BY [University Origins].Origin, Hospitals.Name
The HospitalFilter parameter is set to Multivalue and it's populated with it's own SQL statement giving the Distinct Hospitals.
I have setup a filter on my table with the following expressions:
Expression: =Trim(Fields!Name.Value)
Operator: = IN
Value: =Trim(Parameters!HospitalFilter.Value.ToString )
I have used Trim to remove leading and trailing spaces.
Running the report always returns zero results and I'm puzzled by this. Would appreciate any advice and suggestions
Thanks,
John
View 8 Replies
View Related
May 23, 2006
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx
Wizard results:
Name:
ETL
Type:
SQL Server
Connection name:
ETLConfiguration
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.
Configuration table name:
[dbo].[SSIS Configurations]
Configuration filter:
PT_CUST_ABR
Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]
USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
View 3 Replies
View Related
Nov 9, 2006
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies
View Related
Sep 21, 2007
Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
View 6 Replies
View Related
Apr 15, 2008
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
View 5 Replies
View Related
Jan 26, 2006
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
View 14 Replies
View Related
Dec 1, 2006
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
View 4 Replies
View Related
Aug 25, 2004
What happens when Publisher goes down in Transactional
Replication, no corruption (power , hardware failure)??
Thanks,
John
View 1 Replies
View Related
