Question Of Replication In The Sql Server 2005
May 29, 2007
Dear all
I have a problem that about the Replication of the SQL SERVER 2005. If there are a hundred data in the delivery of replication ,after they transmited fifty data ,somebody
want to read the data in the transmited fifty data that has delivery successfully,but the replication delivery hasn't finished,and could he can read the data successfully?
In the delivery process of the replication,could the table in the subscription will be
lock?And can we oprate the table in the subscription in the delivery process of the replication ?
Thank you.
View 3 Replies
ADVERTISEMENT
Sep 25, 2007
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Thanks in advance!
Scott
View 5 Replies
View Related
Jan 24, 2007
Hi,
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
thnx,
pdns.
View 4 Replies
View Related
Jul 11, 2007
Hello everybody!
I hope that someone could help me.
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
I configure the connection to use the IUSR.
The source code that i use:
repl.InternetUrl = @"http://laptop/SQLMobileIIS/sqlcesa30.dll";
repl.Publisher = @"laptop";
repl.PublisherDatabase = @"database";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"Pubdatabase";
repl.Subscriber = @"SQLMobile";
repl.SubscriberConnectionString = @"Data Source='" + nomeFicheiroBD + "';Password='3409'";
The error that returns is:
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
Does anybody knows what i can do?
HELP I NEED SOMEBODY TO HELP!
Thanks!
View 1 Replies
View Related
Apr 10, 2008
I have written following code in my application
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program
Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition
Error: A request to send data to the computer running IIS has failed. For more information see HRESULT
Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider
Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace DeviceApplication1
{
public partial class Form1 : Form
{
string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1()
{
InitializeComponent();
}
private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");
SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'.
this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists);
}
}
}
}
I have created a merge replication correctlly( I suppose, there were no errros)
Please help
Your help will be appriciated
View 1 Replies
View Related
Mar 16, 2007
Hi all,
I would like to know about "Replication in Sql Server 2005" in a step by step manner! would anybody help this out!
Thanks in anticipation
View 1 Replies
View Related
Aug 28, 2007
Hi,
I have two servers with sql server 2005, one is in the main office and the other in a store...
I would like that the items table from the main office send new data to the server at store, and the sales data from the store to be send to the main office, can anyone help me to do that?!
Thanks
View 3 Replies
View Related
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
May 11, 2007
We are implementing 2005 transaction replication on source database to target staging subscring database but we want to keep all transaction changes from source within staging subscribing tables.
If source column gets updated we want to keep old record and new updated record in staging subscriber. Transaction replication synchronizes but does not keep history on subscriber. Do we update stored proc's
anyone have examples of code or ideas??
View 20 Replies
View Related
Feb 25, 2008
Hi,
I have a problem configuring Bidirectional replication in SQL Server 2005 SP2. I configured Publication and Subscription on two different SQL 2005 instances on different machines (Station1SQL2005 and Station2SQL2005 respectively). Databases are DBTest1 in Station1 and DBTest2 in Station2. I have two tables one in DBTest1 and the other in DBTest2.
Script for the above configuration:
This below configuration does not work if i configure Publication and Subscription on the same machines
---*************************************************************************************************
For Station1:
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest1')
DROP DATABASE dbtest1;
CREATE DATABASE dbtest1
go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest1
go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest1')
DROP TABLE two_way_dbtest1;
GO
CREATE TABLE two_way_dbtest1
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)
/*Allocate a predetermined range of values to the primary key column
so that the values on the different servers are not in the same range.
For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest1
go
ALTER TABLE
two_way_dbtest1
WITH NOCHECK
ADD CONSTRAINT
checkprimcol CHECK NOT FOR REPLICATION
(
pkcol BETWEEN 1 AND 1000
)
go
--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
sp_adddistributor @distributor = 'Station1SQL2005'
go
--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go
--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go
exec sp_adddistpublisher
@publisher = 'Station1SQL2005',
@distribution_db ='distribution',
@security_mode = 0,
@login = 'xxxxxxx',
@password = 'xxxxxxx',
@working_directory ='F:ReplicationReplication Working Directory'
--Enable the identified databases for replication
USE master
go
exec sp_replicationdboption N'dbtest1', N'publish', true
go
--Create the custom stored procedures in the dbtest1 database
USE dbtest1
go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
AS
INSERT INTO two_way_dbtest1
(
pkcol,
intcol,
charcol
)
VALUES
(
@pkcol,
@intcol,
@charcol
)
go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
DECLARE @x int
DECLARE @y int
DECLARE @z char(100)
SELECT
@x=pkcol,
@y=intcol,
@z=charcol
FROM
two_way_dbtest1
WHERE
pkcol = @pkcol
DELETE
two_way_dbtest1
WHERE
pkcol=@pkcol
INSERT INTO two_way_dbtest1
(
pkcol,
intcol,
charcol
)
VALUES
(
CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
)
go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest1
@old_pkcol int
AS
DELETE
two_way_dbtest1
WHERE
pkcol = @old_pkcol
go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest1
go
-- Adding the transactional publication.
EXEC sp_addpublication
@publication = N'two_way_pub_dbtest1',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication for database dbtest1.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
--To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
--to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
@retention = 120
go
EXEC sp_addpublication_snapshot
@publication = N'two_way_pub_dbtest1',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 2,
@frequency_subday_interval = 10,
@active_start_date = 20080225,
@active_end_date = 99991231,
@active_start_time_of_day = 070000,
@active_end_time_of_day = 235959
go
-- Adding the transactional articles.
EXEC sp_addarticle
@publication = N'two_way_pub_dbtest1',
@article = N'two_way_dbtest1',
@source_owner = N'dbo',
@source_object = N'two_way_dbtest1',
@destination_table = N'two_way_dbtest2',
@type = N'logbased',
@creation_script = null,
@description = 'two_way_dbtest1 table data will be replicated to two_way_dbtest2',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_dbtest1',
@del_cmd = N'CALL sp_del_two_way_dbtest1',
@upd_cmd = N'CALL sp_upd_two_way_dbtest1',
@filter = null,
@sync_object = null,
@identityrangemanagementoption = 'manual'
go
/*In this scenario, the dbtest1 database is the central subscriber.
Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest1
go
EXEC sp_addsubscription
@publication = N'two_way_pub_dbtest1',
@article = N'all',
@subscriber = 'Station2SQL2005',
@destination_db = N'dbtest2',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go
EXEC sp_addpushsubscription_agent
@publication = N'two_way_pub_dbtest1',
@subscriber = 'Station2SQL2005',
@subscriber_db = N'dbtest2'
go
For Station2:
--Create database named test1
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest2')
DROP DATABASE dbtest2
go
CREATE DATABASE dbtest2
go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest2
go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest2')
DROP TABLE two_way_dbtest2;
GO
CREATE TABLE two_way_dbtest2
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)
/*Allocate a predetermined range of values to the primary key column
so that the values on the different servers are not in the same range.
For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest2
go
ALTER TABLE
two_way_dbtest2
WITH NOCHECK
ADD CONSTRAINT
checkprimcol CHECK NOT FOR REPLICATION
(
pkcol BETWEEN 1 AND 1000
)
go
--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
EXEC sp_adddistributor
@distributor = 'Station2SQL2005'
go
--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go
--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go
exec sp_adddistpublisher
@publisher = 'Station2SQL2005',
@distribution_db ='distribution',
@security_mode = 0,
@login = 'xxxxxxxxx',
@password = 'xxxxxxx',
@working_directory ='E:ReplicationWorking Directory'
--Enable the identified databases for replication
USE master
go
exec sp_replicationdboption N'dbtest2', N'publish', true
go
--Create the custom stored procedures in the dbtest1 database
USE dbtest2
go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
AS
INSERT INTO two_way_dbtest2
(
pkcol,
intcol,
charcol
)
VALUES
(
@pkcol,
@intcol,
@charcol
)
go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
DECLARE @x int
DECLARE @y int
DECLARE @z char(100)
SELECT
@x=pkcol,
@y=intcol,
@z=charcol
FROM
two_way_dbtest2
WHERE
pkcol = @pkcol
DELETE
two_way_dbtest2
WHERE
pkcol=@pkcol
INSERT INTO two_way_dbtest2
(
pkcol,
intcol,
charcol
)
VALUES
(
CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
)
go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest2
@old_pkcol int
AS
DELETE
two_way_dbtest2
WHERE
pkcol = @old_pkcol
go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest2
go
-- Adding the transactional publication.
EXEC sp_addpublication
@publication = N'two_way_pub_dbtest2',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication for database dbtest2.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
--To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
--to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
@retention = 120
go
EXEC sp_addpublication_snapshot
@publication = N'two_way_pub_dbtest2',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 2,
@frequency_subday_interval = 10,
@active_start_date = 20080225,
@active_end_date = 99991231,
@active_start_time_of_day = 070000,
@active_end_time_of_day = 235959
go
-- Adding the transactional articles.
EXEC sp_addarticle
@publication = N'two_way_pub_dbtest2',
@article = N'two_way_dbtest2',
@source_owner = N'dbo',
@source_object = N'two_way_dbtest2',
@destination_table = N'two_way_dbtest1',
@type = N'logbased',
@creation_script = null,
@description = 'two_way_dbtest2 table data will be replicated to two_way_dbtest1',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_dbtest2',
@del_cmd = N'CALL sp_del_two_way_dbtest2',
@upd_cmd = N'CALL sp_upd_two_way_dbtest2',
@filter = null,
@sync_object = null,
@identityrangemanagementoption = 'manual'
go
/*In this scenario, the dbtest1 database is the central subscriber.
Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest2
go
EXEC sp_addsubscription
@publication = N'two_way_pub_dbtest2',
@article = N'all',
@subscriber = 'Station1SQL2005',
@destination_db = N'dbtest1',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go
EXEC sp_addpushsubscription_agent
@publication = N'two_way_pub_dbtest2',
@subscriber = 'Station1SQL2005',
@subscriber_db = N'dbtest1'
go
---*************************************************************************************************
It would be grateful if somebody gives me a solution.
Thanks in advance.
Ravi.
View 9 Replies
View Related
Feb 24, 2006
Hi,
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greets,
Ivo Klerkx
View 3 Replies
View Related
Oct 4, 2007
Greetings...
Presently, I am doing one way replication in SQL Server 2005. Server-A is local server at local place and Server-B is remote server at different place. There is not a problem in one way replication.
Server-A is Distributor and Server-B is Subscriber in one way replication.
I want to setup the following configuration using bidirectional replication (two way replication) on SQL Server 2005
And I am not able to do it. What should I do for this?
Should I use Merge Replication for bidirectional Replication.
Server-B is live server for users which cannot be stop for a moment. Server-A is local server which is live too.
Now please let me know how to do Bidirectional Replication. So whatever data in Server-B (Which is live) should replicate to Server-A or Vice versa ...
If we add some column into Server-B's table of Database what could be the effect on Server-A...
Please someone help me out into this.
ThanksRicky
View 9 Replies
View Related
Oct 6, 2007
Hello All,
I created all the role and logins as described in oracleadmin.sql file and were able to query Oracle tables.
But when I try to crate publisher on Oracle server from sqL server I get 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+Management+Studio&ProdVer=9.00.3186.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'oracle_dev' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to execute the HREPL.INITPUBLISHER request to Oracle Publisher 'ORACLE_DEV'. Verify that the Oracle package code exists on the Publisher, and that the replication administrative user account has sufficient permissions.
Changed database context to 'master'.
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "One or more errors occurred during processing of command.".
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "ORA-06550: line 1, column 8:
PLS-00201: identifier 'HREPL.INITPUBLISHER' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
".
Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'ORACLE_DEV'. (Microsoft SQL Server, Error: 21651)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=21651&LinkId=20476
--------------------------------------------------------------------------------------------------------------------------------------------------------
I searched the web and could not find any related info, where is this package can I run it manually?
Any help appreciated.
View 6 Replies
View Related
Dec 5, 2007
We are trying to set up the Sql Server CE Server Tools on our Web Server and most of the MSDN support items assume that Sql 2005 is installed on the same PC. However, our Sql 2005 database is stored on a dedicated server off site. When we run the Sql CE ST installation we are tols we cannot sync with Sql 2005 until we install the Replication Components. It seems we cannot download them and they can only be located on the Sql 2005 install CD. Is there another way around this as the Sql SP3a and SP4 dont solve the problem and without having to arrange a courier for the CD we cannot seem to move forward.
View 1 Replies
View Related
Aug 29, 2006
we have the standard licensed copy for sql server 2005
while configure transaction replication not allow to move
message is required licesence for replication again i have to get license for
replication
please advice
View 2 Replies
View Related
Jun 14, 2007
Hi there
im trying to set up a replication from a local server to a remote server.
when starting up and selecting the Distribution / publisher, i get the following errors --->
Details as followed...
TITLE: New Publication Wizard
------------------------------
SQL Server is unable to connect to server 'SERVERX'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
------------------------------
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, 'SERVERY'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
------------------------------
and...
===================================
SQL Server is unable to connect to server 'SERVERX'. (New Publication Wizard)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
===================================
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, 'SERVERY'. (Replication.Utilities)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.PrepareToShow()
View 3 Replies
View Related
Apr 19, 2006
Hi,
I've been trying to get my merge replication to work with a sql ce 2.0 on
sql server 2005, but it keeps generating shapshot scripts my pocket pc can't
execute.
Example of my table.sch file in my snapshot folder:
drop Table [dbo].[Application]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application](
[AppID] [nvarchar](20) NOT NULL,
[AddOnInfo] [nvarchar](50) NULL,
[MaxClients] [int] NULL,
[AppName] [nvarchar](255) NULL,
[NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory]
DEFAULT (10),
[NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture]
DEFAULT (10),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Applicati__rowgu__4C364F0E] DEFAULT (newid())
)
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
I've selected that I was setting up a merge replication that needs to be
compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains
80RTM...
I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
Even if I make a publication for sql mobile 2005 it generates the same
script.... It's almost as if SQL 2005 is ignoring my settings...
Thanks,
Desperate Natasja
View 12 Replies
View Related
Oct 31, 2006
Hello,
I am facing problems trying to replicate SQL Server 2000 table to SQL 2005 Express. The error says:
"login failed for user 'username'. The user is not associated with a trusted SQL server connection"
PS:
The same happens when I try to register the SQL 2005 server in SQL 2000 Enterprise Manager.
Any help?
Thanks
Walid
View 5 Replies
View Related
Jun 15, 2006
Hi,
I am experiencing a problem with deployment of a VB .Net application which carries out merge replication, and would greatly appreciate any assistance. I am currently struggling to find stuff about my problem on the net, which probably means either (a) I'm missing something really simple or (b) I shouldn't be trying to do this in the first place. :)
The story so far:
1. I added the SQLMergXLib.dll COM to my Visual Studio project, allowing me access to SQL Server merge replication functionality from my code, and wrote a procedure for synchronising a given pull subscription. This worked beautifully and with minimal fuss.
2. I tried to deploy the project to a different machine. I received an error to the effect of, "Couldn't create object, it's a COM object and it's not registered correctly." I have encountered this problem a few times before when trying to use Interop, so I called myself a few names, went back to the deployment project, and specified that the object should be registered. Still no joy. I tried this a couple of different ways round but then...
3. ...found an article on MSDN suggesting that I should consider using the managed Microsoft.SqlServer.Replication, Microsoft.SqlServer.Management, Microsoft.SqlServer.RMO interfaces instead. I reasoned that this would eliminate the registry issue altogether as everything would become native to the .Net framework, and this seemed infinitely preferable anyway, so I promptly substituted the SQLMergX DLL for these, rehashing my code to match. Again, this built and ran OK on my development machine.
4. I tried to deploy the project to a different machine. I received the following error: "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. This application has failed to start because the application configuration is incorrect."
Now it's worth pointing out that I think I know what the problem might be here: No SQL Server of any kind is installed on the target machine. However, SQL Server is not required to deploy standard database solutions in .Net, and I am hoping this is also true with the Microsoft.SqlServer... namespaces, as it is a requirement that the subscription database can be located anywhere (not necessarily the target machine), and therefore that SQL Server need not be present on the target machine. However, if this is simply impossible, please let me know so that I can look at other alternatives.
Other than that, I don't mind what method I use to get the synchronisation working as long as it works (I've been at this a good few days now...), so if anyone has done this before and can offer any assistance (any registering/files I need to include etc, I am hoping it is something that simple), it would be appreciated.
Thanks,
Steve.
View 5 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
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
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
Jan 30, 2007
Hi All ...I
have been following the MS on-line tutorials to get replication setup
between SQL Server 2005 and SQL Server Compact Edition. I get to the
point where I run the Configure Web Synchronization and it is running
through the process where it is doing the automated configuration of
the shared directory - assigning users etc.I have attempted
this step with a user account that I expressly created for snapshots
and with the Administrator account and get the following error message:* The operation completed successfully. (Exception from HRESULT: 0x80070000)At
first, I thought this was a rights issue due to the fact that I was
using an account that I manually created. However, by using the
Administrator account (test only until I can figure this out) that
should have removed any rights issues associated with accessing the
shared directory where the snapshot will reside.Any help in getting this figured out will be greatly appreciated.David L. Collison Any day above ground is a good day!
View 9 Replies
View Related
Jul 12, 2007
Hi ppl,
I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.
Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.
Distributor and the Publisher are the same server.
IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS.
So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?
Regards
Nabeel
View 1 Replies
View Related
Sep 21, 2006
Hi,
I'm deploying tran repln between 2 SQL Server 2005 databases and when viewing the 'Distributor to Subscription History' see a error message:
Incorrect syntax near the keyword 'where'. (Source: MSSQLServer, Error number: 156)
On the subscriber, I don't see the snapshot being generated....Please advice.
View 9 Replies
View Related
Sep 28, 2007
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
Thanks
Andrew
View 6 Replies
View Related
Feb 13, 2006
Hey there!
In a nutshell, I want to do a merge replication with a SQL Server and
several Access databases. I haven't been able to find anything in the
documentation or 3rd party books.
Is this possible?
Thanks!
- Erik
View 7 Replies
View Related
Jan 2, 2007
Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.
SQL1MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2MSSQL01 as a subscriber the connection fails to the remote server.
The error message is:
TITLE: Connect to Server
------------------------------
Cannot connect to SQL2MSSQL01.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server SQL2MSSQL01. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.
When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.
TITLE: Connect to Server
------------------------------
Cannot connect to TFMHQARNDE07.
------------------------------
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, ' SQL2MSSQL01'. (Replication.Utilities)
I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.
When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.
Any one have any idea what it can be?
View 1 Replies
View Related
Jul 18, 2007
I have set up merge replication with 1 publisher and 1 subscriber. Distribution is handled by a 3rd server.
I can generate a snapshot at the publisher and apply it to my subscriber. But when I insert some data (approx 30,000 rows) , the Synchronization agent gives the following error when It runs:
The merge process is retrying a failed operation made to article 'xxx' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.
I have increased the OutputVerboseLevel setting and specified a file path in the -OutputMessageFile but the File is not being populated. All the references on books online say to put the file path in the -Output parameter but when I do it says it can only hold an integer value. So I cant see the errors generated by the database engine.
Could anybody please assist with this issue?
Thanks
View 1 Replies
View Related
Apr 17, 2008
Hi All,
Currently we use SQL 2K SP4 and snapshot replication with a Central Publisher with Remote Distributor toplogy.
I am looking to upgrade or migrate our SQL servers to SQL 2005 and was wondering what is the best way to do this for our replicated architecture?
Is the best way to run the SQL 2005 Upgrade on all 3 servers (publisher, distributor, subscriber) and should it automatically upgrade the servers including the replication components? Is there anything i should consider/watch out for when doing the upgrade and it involves replication (namely snapshot replication)?
Thanks in advance.
View 1 Replies
View Related
