Replication With Multiple Server With The Same Server Name

Jul 27, 2007

hey all,

I have been testing SQL merge replication out for the last few weeks and it looks as if it will work for my company. However, I have a couple of question.

the setup:
1) we have multiple servers in the field all using the same server name with different IPs and different workgroups
2) Publisher = SQL 2005 SP2
3) Subscribers = SQLexpress SP2
4) Merge pull scriptions for all subscribers
5) Suser_Sname() used as filter

My questions:

1) Can all the subscribing servers access the same subscription at the publisher with out screwing up the generation table?
2) Is there any way to have a subscribing server point to a particular subscription at the publisher?

I've tried setting up an alies on a subscriber but the sp_addmergepullsubscription_agent will no longer accept subscription parameters.

Thanks in advances for any and all help.

View 12 Replies


ADVERTISEMENT

Using Multiple Versions Of SQL Server In A Replication Topology

Nov 24, 2006

Hi there,

I have one publicacion on ms sql 2005 Enterprise w.sp1/MS Windows 2003 Enterprise w.sp2 and one subscriber on MS sql 2005 Enterprise w.sp1/MS Windows 2003 w.sp2, but I have also 3 subscriber on MS SQL 2000 w.sp3/MS Windows 2000 advanced server w.sp4. They replicate the same database with a publication compatible with 80RTM, All works fine with data replication, but I can't replicate DDL to MS sql 2005 suscriber, I don't care that don't replicate DDL, but I want manually alter triggers at MS sql 2005 suscriber, but I get this error at the subscriber on when I run the ALTER TRIGGER statement :

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertrigger, Line 67

The DDL statement cannot be performed at the Subscriber or Republisher.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181

The DDL operation failed inside merge DDL replication manipulation.

Msg 3609, Level 16, State 2, Procedure TU_Centros_Distribucion_Articulos, Line 58

The transaction ended in the trigger. The batch has been aborted.



Any help will be appreciated,



PD. I miss sql2000 replication, where I have full control over the database logic

View 14 Replies View Related

Replication :: Multiple Instances On Same Instance Of Server 2012 SE

Jul 13, 2015

I am currently having publisher(database A), subscriber (database B) and distributor on the same instance for a test environment that is using a uni directional transactional replication. Now I need to setup another unidirectional transactional replication in the same test instance but for a different database. Publisher database is D and subcsirber database is E.

View 4 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot In Multiple Files

Sep 15, 2015

We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:

MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp

With other tables, I'll get just one LARGE snapshot file, named:

MyOtherTable_4.bcp

In the latter case, the file can be very large (most recent is 38GB).

In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'

My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.

View 0 Replies View Related

A Call To SQL Server Reconciler Failed. SQL Server 2005, SQL Server Mobile Merge Replication

Oct 2, 2005

Hi,

View 36 Replies View Related

DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication

Aug 22, 2007

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

View 4 Replies View Related

Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports

Mar 30, 2007

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.



Thanks.



I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.



--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'



--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'



--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'



--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

View 11 Replies View Related

XML Data To SQL Server With Multiple Orders An Multiple Order Details??

Aug 5, 2004

Hi all!

I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:

I simplified my example to this:
-----------------------------
--CREATE PROCEDURE sp_InsertOrders AS

DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT

--DROP TABLE #Orders
CREATE TABLE #Orders
(
OrderId SMALLINT IDENTITY(1,1),
FkCustomerID SMALLINT NOT NULL,
OrderDate DATETIME NOT NULL
)

--DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
(
OrderDetailsId SMALLINT IDENTITY(1,1),
FkOrderID SMALLINT NOT NULL,
ProductID SMALLINT NOT NULL,
UnitPrice SMALLINT NOT NULL
)

Set @xmlDoc = '
<Orders>
<Order CustomerID="1" OrderDate="2004-04-01">
<OrderDetails ProductID="6" UnitPrice="19"/>
<OrderDetails ProductID="3" UnitPrice="11"/>
<OrderDetails ProductID="9" UnitPrice="7"/>
</Order>
<Order CustomerID="2" OrderDate="2004-04-12">
<OrderDetails ProductID="2" UnitPrice="24"/>
<OrderDetails ProductID="4" UnitPrice="13"/>
</Order>
</Orders>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

INSERT INTO #Orders (FkCustomerID, OrderDate)
SELECT CustomerID, OrderDate
FROM OpenXML(@docHandle, 'Orders/Order', 3)
WITH (
CustomerID INTEGER,
OrderDate DATETIME
)

SET @OrderID = @@IDENTITY;

--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
SELECT @OrderID AS OrderID, ProductID, UnitPrice
FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
WITH (
ProductID INTEGER,
UnitPrice INTEGER
)
-----------------------------

All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?

Cheers,
Christian

View 2 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

SQL Server 2008 :: How To Update Multiple Column With Multiple Condition

Feb 25, 2015

I need to update multiple columns in a table with multiple condition.

For example, this is my Query

update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year

If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column

I can't write an update query for each condition separately because its a huge select

View 7 Replies View Related

Setting Up Replication On SQL Server 2000 Using Distributor Running On SQL Server 2005

Jul 6, 2006

Hi

I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.



Has anybody done this before. If yes what will I need to check. Can you please let me know :-



1) SQL Server 2000 which SP should be installed to support this enviroment.

2) SQL Server 2005 which SP should be installed to support this environment.

3) Any thing that I need to look out for.



Thanks for any inputs on this.



Regards

View 3 Replies View Related

Move Instance Off A Server With Hardware Problems SQL Server 2000 Sp4 ENT With Replication

Apr 2, 2008

We have a SQL Server 2000 ent edition sp4 with 3 small databases and one 4 GB database which is REPLICATED
The Server has been bouncing like a yoyo today because of a disk controller problem and I am preparing to move the Instance to a whole new server this afternoon. The new Server will have the same IP and name as the original server.

Any suggestions for the easiest or most seamless way to make this happen? preferably using Backup and Restore since they plan on taking the old server down completely and then building a new server with the same name and IP ... any suggestions would be appreciated.

View 1 Replies View Related

Mirror Monitor Error: Server Replication Requires The Actual Server Name To Make A Connection

Oct 17, 2007

I'm getting the following error when launching the database mirroring monitor and attempt to connect to my database.


TITLE: Connect to Server
------------------------------

Cannot connect to dborat01.hs.pitt.edu.

------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'xxxxxx'. (Replication.Utilities)

------------------------------
BUTTONS:

OK
------------------------------

View 3 Replies View Related

Sql Server Merge Replication Vs. Sql Server Transactional With Updatable Subscribtion

Jun 12, 2006

Hi,

Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.

Any compared document between merge replication and Transactional with updatable subscribtion ?

Thanks,

Tarek Ghazali

SQL Server MVP

View 4 Replies View Related

Replication :: Server 1 And Server 2 In Mirroring Relationship With Automatic Failover

Sep 24, 2015

I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover. 

Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.

What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover?  Failback and reinintialize

View 3 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Multiple Customers (with Multiple Sql Instances) On 1 Reporting Server

Dec 3, 2007



Hi,

I think this is a question for the specialists among us.

Can I use one general reporting server (installed on instance MAINREPORTING)
for multiple customers who all have their own sql instance (CUST1, CUST2, CUST3, ..)
I would use UserAuthenciation on the reportserver url to display the specific reports customers can use.


Is this possible, and what do i have to take care off concerning installation and/or configuration (especially on
the reporting side) ?

Greetings
Vinnie

View 3 Replies View Related

Replication Between SQL Server Database And Linked Informix Server

Apr 19, 2004

I need help setting up replication between my SQL Server (data warehouse) database and a linked informix database.

Does anyone know the best to accomplish this. I can provide any additional information you require to help you, help me ;-)

View 4 Replies View Related

Transactional Replication From SQL Server 2005 To SQL Server 2000

Nov 8, 2006

I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts:
"Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near
'max'."


Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.

Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.

Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?

View 3 Replies View Related

Replicate To Other Server If Replication With Main Server Failed ...

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

Need Help Regarding Replication Between SQL Server 2005 And SQL Server Mobie Edition!

Mar 20, 2006

Dear support,

I am working on my graduation project that has a wide section of PC Pocket application to merge data between the SQL Mobile Edition and the SQL Server 2005. So, i had done most of the steps mentioned in the Books Online for the SQL Mobile Edition, till i had reached the step of creating a subscription for the Mobile Database, it is known that at the end of this step there would be a code generated in order to use it in developing the application and after clicking finish the wizard will start Synchronization. Here this step fails with a message informing me that the snapshot agent is not started yet or the publisher didnt generate the snapshot yet !!

So looking forward to getting your advice soonly!

Thank you for your attention,

Kindly Regards;

View 3 Replies View Related

SQL Server Admin 2014 :: Multiple Database-collation Running On One Server?

Mar 30, 2015

We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.

Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS

Our new server (2014) has the standard collation Latin1_General_CI_AS

Both collations have CI and AS

From experience I know different databases can reside next to eachother on the same Instance.

The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....

The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).

using different databases using a different collation running on the same instance?

View 5 Replies View Related

Integration Services :: Run Transaction Across Multiple Instance Of Server Without Linked Server

May 16, 2015

i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.

View 4 Replies View Related

Import And Export Wizard: Transferring Multiple Tables From SQL Server 2005 To SQL Server 2000

Jun 15, 2007

Hi!



I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.



I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)



Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.



Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?



This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.



So now I'm trying to understand why the Wizard created this more-complex package.



Any help will be appreciated, including references to non-Microsoft books/websites/etc.



Thanks in advance.



Dan

View 17 Replies View Related

SQL Server Admin 2014 :: Multiple SSRS Instances On Same Server

Jun 23, 2015

I am playing around in a test environment with SQL Server 2014. I have a question about the default location of the report server databases when you have multiple report server instances installed on one server.

I did a very simple install of SQL Server 2014 with the database and Reporting Services in Native Mode (install only) features selected. Accepting the default locations, I ended up with the following locations as you would expect:

C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVER
C:Program FilesMicrosoft SQL ServerMSRS12.MSSQLSERVER

Running the Reporting Services Configuration Manager, I created the Report Server database. After creating the Report Server database, the related files will be located below in the SQL folder as I would expect.

C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA

Next I installed another instance, which I called Test, of SQL Server 2014 like I did above. I now have the following folder structure the Test instance as I expect.

C:Program FilesMicrosoft SQL ServerMSSQL12.TEST
C:Program FilesMicrosoft SQL ServerMSRS12.TEST

Running the Reporting Services Configuration Manager, I select the Test instance and create the Report Server Test instance database.

My first thought would be that the Test instance Report Server database would be in the following location:

C:Program FilesMicrosoft SQL ServerMSSQL12.TESTMSSQLDATA

Instead this database is located with the default instance Report Server database:

C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA

View 2 Replies View Related

Replication From SQL Server 2000 To SQL Server 2005

Apr 11, 2007

I am trying to replicate data from SQL Server 2000 to SQL Server 2005.previously we did it from 2000 to 2000 using script.



but when i am tring to use that script with 2000 to 2005 its not working i have made the necessary changes in the script.Is there major changes to replicate from 2000 to 2005

View 1 Replies View Related

Multiple Server Query Without Linked Server

Feb 12, 2008



Hi,
I have two servers S1,S2.

I am currently connect to S1 but I want to Query a table from S2 without creating a linked server.
Is it possible.Can someone please post some information on how this can be accomplished.
ETL, Lookup ,Stored procedure anything is ok. I am using SQL Server 2005.This is a very urgent requirement.

Please help!
Thanks,

View 6 Replies View Related

SQL Server Mobile Merge Replication Walkthrough, Cant Find The .NET Framework Data Provider For SQL Server Mobile Edition.

Sep 9, 2005

Hi,

View 3 Replies View Related

Moving A SQL 7.0 Replication Server To Another Sql 7.0 Server

Jun 13, 2001

I am looking for a way to move replication from an old server to a new server. The story goes like this. We have a old machine running tran replication. THis machine is crashing because of a hd going bad. I need to move this Stuff over to another server with out having to re-setup and reinitalize all the replication stuff. Basicaly i am trying to make a copy of the old sql server on the new one. ANy help would be appricated.

Thanks jason

View 2 Replies View Related

Does SQL Server 2005 Express Support Web/Internet Replication To Other SQL Server Express Clients?

Jan 22, 2007

HI

Q1: Does Sql Server 2005 Express support Web/Internet to other SQL Server 2005 Express Clients or does it have to Synch across the internet to a fully installed setup SQL Server 2005 with IIS?

Q2: Does SQL Server 2005 Express support Direct Replication between other SQL Server 2005 Express clients?

Regards

View 5 Replies View Related

Replication To Multiple Servers

Jul 12, 2000

suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you

View 2 Replies View Related

SQL Replication For Multiple Sites

Oct 5, 2007

I am trrying to figure out what the best replication would be to use and or setup...

Her eis the current goal and structure..

We are just moving over to a new custom POS system that will be using SQL databases....We have have three locations and we want each location to be independent in case of network connectively failures to our primary location.

Basically, all three locations will be running SQL server 2005 and the POS app...
We want replication to occur overnight, so that each location will have the other locations transactions from the previous days, etc...

Essencially I want all three locations to "syncronize" their data every night....basically two-way replicaiton between all three sites...

Master Site will have say databaseA that the local POS system will use
Sencond Site will have say databaseA that the local POS system will use as well

Third Site will have say databaseA that the local POS system will use well...

Any thoughts or Ideas will be helpful

Thx
Martin

View 1 Replies View Related

Multiple SQLServer Agents For Replication

Feb 24, 2003

Can I have multiple SQL Server Agents running concurrently?

I would like to have 10 instances of database running on my local SQL Server - each of these are Merge Replication to a unique database located offsite.

Do they all run through one SQL Server Agent?

If they do would this be a slow process as, I think, the SQL Server Agent queues the replications pending.

Thanks,

Bianca

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved