I have a merge where the subscriber can do changes to its tables ... But as I found out the changes are made only if publisher simulate an row change or changes something effectively
I'm not sure that at every replication the publisher table would be changed so i need something to simulate a change in the publisher table
Can anyone please help me with this ?
I have a table (let's call it MyTable) that consists of four fields:
Id, Source, FirstField, and
SecondField, where Source only takes one of two values:
Source1 and Source2.
The records in this table look as follows:
Id
Source
FirstField
Secondfield
1
Source1
Product 3 name
Product 3 description
[code]...
I need to return, using 3 different T-SQL queries:
1) Products that exist only in Source2 (in red above)
2) Products that exist only in Source1 (in green above)
3) Products that exist both in Source1 and Source2 (in black above)
For 1) so far I've been doing something along the lines of SELECT * FROM MyTable WHERE Source=Source1 AND FirstField NOT IN (SELECT DISTINCT (FirstField) FROM MyTable WHERE Source=Source2)
I have read about INTERSECT and EXCEPT, but I am a little unclear if they could be applied in this case out of the box.
Here's the scenario. I have a table (let's call it MyTable) that consists of four fields: Id, Source, FirstField, and SecondField, where Source only takes one of two values: Source1 and Source2.
The records in this table look as follows:
I need to return, using 3 different T-SQL queries:
1) Products that exist only in Source2 (in red above)
2) Products that exist only in Source1 (in green above)
3) Products that exist both in Source1 and Source2 (in black above)
For 1) so far I've been doing something along the lines of
SELECT * FROM MyTable WHERE Source=Source1 AND FirstField NOT IN (SELECT DISTINCT(FirstField) FROM MyTable WHERE Source=Source2)
Not being a T-SQL expert myself, I'm wondering if this is the right or more efficient way to go. I have read about INTERSECT and EXCEPT, but I am a little unclear if they could be applied in this case out of the box.
Hi all,
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
Any help is greatly appreciated.
tia.
-m
I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:
Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture
Is my only option to change the TRUNCATE to DELETE?
[URL]
I am referring to the group concatenation where we get all the values of a single column (of all the rows) grouped into one cell as a concatenated string. I know I can achieve this using temporary tables and cursors in SQL Server. But I cannot have a cursor in SQL CE. I need to add this as a command to a tableadapter in a Dataset Designer. Please let me know if you can think of a workaround.
This is what I want:
Table 1:
Col1 Col2
1 a
1 b
2 c
2 d
2 e
Resultset should be:
Table 2:
Col1 Col2
1 a, b
2 c, d, e
How can I simulate 1,000 simultaneous users in my database?
View 3 Replies View RelatedI would like to generate an incrementing column value to each row found in a query in SQL Server 2000.
View 13 Replies View RelatedI want to simulate 100 users simultaneously executing a procedure in sql server (best example would be 100 users using one report with different parameters in ssrs at the same time ). can i do that ?
View 3 Replies View RelatedIn my fact table (month granularity), I've got a calculation defined:
Margin%=DIVIDE(SUM(Margin);SUM(SalesAmount))
How should I define a calculation based on "Margin%", so that if I browse the Pivot Table by months it would always show the year level result instead? My date dimension is also with Month granularity and it has a Year column in it.
Hi
I am currently developing a DBA management console for a company who have 45 SQL Servers, 20 of which are in the production environment. As part of the development I have created a DBA dashboard with various alerts and information dials regarding the state of the SQL Infrastructure. I came accross a problem yesterday that I cannot find the answer to.
One of the alerts I have developed is a simple "Heart Beat" indicator that displays that the SQL Server is present and "alive". To do this I have a central database running on SQL 2005 which has linked server connections toall 45 SQL Servers. This central database runs a simple select statement on each server (something that I know works if the server is there). The select is run as dynamic SQL as I need to build up the SQL as part of a cursor to place the server name within the from clause. Here is the select I am using: -
select top 1 dbid from [server].master.dbo.sysdatabases
If the server is not there an error occurrs. I trap this and record that the server may not be present. The cursor then moves on to the next server to check.
This works fine when ran from Query Analyser. I can simulate a failed server by deleting the link server connection. For the server in questions an error is raised when the select is run, I store the error and on the Dashboard screen you can see an indicator saying that the server is currently non-contactable (i.e. failed Heart Beat).
My problem comes when I try and schedule this procedure. If I run it as an SQL Agent Job it behaves differently. Here, as soon as the error is detected it bombs out stating that the step has failed. I don't mind it stating that the step has failed, what I want it to do is to complete the cursor loop on all the servers. You see at the top of the procedure I set all the heartbeats flags to 0 (i.e. not contactable) so I need to to complete the run. I have search the Agent set-up and can find no flag to stop it halting processes when an error occurs.
The servers are configured not to allow RPC and I would rather not have to put a job on every server to tell me they exist as this would need managing going forward.
Any help you can offer would be greatly appreciated - Happy New Year by the way!
Hello ,
How can i define a different TCP/IP port number , other than the default 1433; to replicate the database over a firewall on the source and the destination servers ? My source servers are SQL SERVER 7.0 with SP3 on Windows NT Server 4.0 and the destination servers are SQL 2000 with SP2 on Windows 2000 .
Where do i have to make changes in SQL server and Windows NT ? And further how secured it is to use this way for security ?
Can i get some on this matter please .
Thanks in advance
Sandra .
I have to modify field length in the table, which is part of publication. I tried to use an Enterprise Mgr and received an error message because that table is scheduled for replication.
Is any way around this beside temporary stopping and restarting replication? And actually how to do this to be sure that after restart the data will be again in sync.
We have SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server.
I am trying to make a DDL change to an existing column for a transactionally replicated table. I am attempting to effect the ALTER on the Publisher. Below is the statement I am using:
[Begin Code Snippet]
ALTER TABLE Products ALTER COLUMN LegacyProductID varchar(20)NULL
[Begin Code Snippet]
This is the error message:
Msg 4928, Level 16, State 1, Line 3
Cannot alter column 'LegacyProductID' because it is 'REPLICATED'.
Column properties currently are:
INT
NULL
Not For Replication - False
Primary Key - False
I read this article http://www.replicationanswers.com/AlterSchema2005.asp but still cannot either get the statement to work or identify a reason it isn't working.
Dear all,
I have some questions regard subject above. I want to know the impact of replication process after SA pasword been changed.
Example: server1 (hq), server2 (district) & server3 (district).
If server1, SA password been changed, is it server2 & server3 need to change too?
may i know where is it the replication SA password stored? is it replication script refer ID (SA) from Security -> Logins?
I hope someone can help me.. thanks.
How can I keep schema changes in the subscriber's database when I replicate the snapshot from the publisher?
I just want to move data from a remote server, but it seems that the tables are being dropped which is not good as we use the subscriber as a development box.
Please advise
Hi
Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.
I need to create a publication and Merge subscription with the below structural changes
User table with the fields of Uname varchar(25) and pwd carchar(30).
The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).
Is it possible? If you, please give the details.
Thanks.
I have a publisher, distributor and subscribed in transactional replication topology, each on a different MS Windows 2008 server. SQL server version is 2008. I changed the password of the service account used to the the sql server and sql server agent services on all 3 servers. But now replication stopped working (Distributor to publishers shows errors in replication monitor for all publications).
I restarted the services and also tried restarting the servers. I did "update replication passwords" and also used sp_changereplicationserverpasswords to change replication passwords. But it doesn't seem to work.
From [MSrepl_errors], i get the below errors:
The process could not connect to Subscriber 'XXX'.
Cannot generate SSPI context
SQL Server Network Interfaces: The target principal name is incorrect.
How to change the default snapshot folder (Merge Replication) location in SQL Server 2012 Standard Edition? Actually that field is grayed out.
View 3 Replies View RelatedSQL Server 2008R2: Enabling Change Data Capture on a replicated database or its tables will have any performance impact on existing transactional replication.Is it possible to use both of them con temporarily.
View 5 Replies View RelatedQuestion re Merge rep (pull) and processing order. We have a group of changes associated with an app upgrade, the scripts run fine on the publisher. Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs. is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
I am doing replication through C#.net after creating the publication in server and subscription in client. But i want to know how can we view the transactions that are happening to the both databases through the C#.net code...If anybody knows please help me....
View 1 Replies View Relatedcan a sql server 2000 snapshot replication
solution handle a schema change.
as far as i know i can't drop a table involved in replication
Hi
I am new to Sql server and had just finished the MErge Replication setup on one of the PRoduction server. Today I got the request to change one of the Datatype of one the Published Article. Please help as what are the correct step to make it happen. Is there any production downtime required and if yes , then how much.
Thanks in Advance
Sanjay
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies View RelatedIs there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
Hi....I execute select statement on three tables and i get the following table:
ID
Value
Name
1
10
color
2
20
color
3
30
color
4
40
color
Is it possible to change the name of the owner of a sql database table?Thanks,Zath
View 4 Replies View RelatedHi, guru,
How can I Change all rows into columns from one table? what method can make it easy?
Thank you very much
Tony
We set up a few tables while being logged in as a different user. Now, those tables are not accessible via some ASP pages because they aren't DBO-owned tables. Relationships do exist on these tables, but no significant data. Is there a way to change the owner, or do I need to delete the table and start over?
View 2 Replies View RelatedJust curious - how does one change the ownership of a *table* (not a database) in SQL 7.0?
Thanks
RM
Already know how to change the owner of a database, but that doesn`t seem to change the owner of an individual table.
Any Help???
Please??