Data Added Before Publication Created Does Not Replicate - Why?

Feb 15, 2007

Hello,

I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.

How can I get the old rows that predate the publication to replicate?

Thanks,

BCB

View 1 Replies


ADVERTISEMENT

New Table Added To P-t-p Transactional Publication Does Not Replicate - Why?

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

How To Replicate Databases Created By Application

Feb 24, 2008

Scenario:

Application uses historical data for alarms, responses, etc. The application came with a sync agent but the agent only syncronizes at a minimum every 15 minutes (nice HA feature we paid for). 15 Minues is unacceptable in the event it syncronizes then 5 minutes later the database goes down - we lose 10 minutes of data. I have configured a standby server and have configured snapshot replication since the application also uses simple recovery mode, has tables with no PK, etc - so transactional / merge replication are out of the picture.

I have created publisher / subscriber configurations and all is fine....the problem comes into play with historical data. All data is stored in a database say 'History0208' indicating the current month/year. Each month a new database is created (I am assuming at 12:00 am of the first day of each month) but I will need to replicate the new database over to the subcriber. How can I go about automating this? There is a table called 'History Data' which does indicate the datapath 'History0208' for the current month. Could I call this value from the table into a stored procedure? How would i go about doing this....thanks for any help.

View 3 Replies View Related

Replicate Schema/Replicate Data Only

Sep 7, 1999

Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!

Thanks for ANY information you can give me...
~Jepadria

View 2 Replies View Related

My Db Grew 8.2GB In Two Weeks BUT Only 4MB Of Data Added

Jul 23, 2005

I have a db that grew 8.2GB in one week and don't understand why.There are three tables added to the db daily. I calculated thespaceused by each of the three tables for a period of two weeks. Thetatal amount of data added to the db for the three daily tables overthe past two weeks was about 4MB yet the db grew approximately 8.2GB.WHY?Can someone please tell me what I should look at so that I canunderstand what is going on?

View 4 Replies View Related

What Is The Best Way To Replicate Data?

Oct 18, 2006

Hi everyone, I've recently been thrown into a DBA role (I've never done any DBA work except writing a few SQL queries), so please go easy on me if these are stupid questions. My first task is to find the best way to replicate data between two SQL Server production databases. The data is to come from Production DB #1 to Production DB #2 (for access by a different system). The data has to be super-close -- not necessarily real-time, but within a few minutes. So when data is updated in #1, #2 shouldn't be be lagged by more than 45 minutes (5-10 is ideal). There are hundreds of thousands of records.What would be the best way to do this? Are there options in SQL Server 2005 to do "differential" updates from DB1 to DB2? Or is that how "transactional replication" works?  If we were to implement a "full recovery model", will this impact any sort of replication? Thanks.  

View 2 Replies View Related

What Is The Best Way To Replicate The Data?

Nov 15, 1999

Hi, there
I have a situation here....
I have Production DB and Development DB in the same SQL7.0 box....
I want to update the data and SP from Pro. to Dev at least once a day...
So this is my plan.

1. Back up(Pro.) and restore(Dev.) so I can have the same DB in the same box.
2. Using a Replication (Pulication and Subscription) ro update the data and SP.
3. Because DTS can not update the SP, I use the replication instead of DTS.
I need to update the SP, too..(Front end is ACCESS and backend is SQL7.0 DB)

Is there any other methods or way to make ti happen... Any suggestion can help...

Thanks in advance

Jay

View 2 Replies View Related

The Component Could Not Be Added To The Data Flow Task

Feb 6, 2007

Dear Colleagues,

I'm trying to develop a custom Data Flow Transformization component in SSIS.

I compiled it without errors, installed it in the GAC and in the Pipeline Components-folder however I always get the following message when I'm trying to drag the component onto the designer surface:

The component could not be added to the Data Flow task.
Please verify that this component is properly installed.

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

The data flow object "RisikoKennzahlenKomponenten.MarktwertTransformation,
RisikoKennzahlenKomponenten, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=cfa8722b8086ac2d" is not installed correctly on this computer.
(Microsoft.DataTransformationServices.Design)

Program Location:

at
Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.GetPipelineInfo(String creationName, IServiceProvider serviceProvider)
at
Microsoft.DataTransformationServices.Design.DesignUtils.GetNewPipelineComponentObjectName(IDTSComponentMetaDataCollection90
parentCollection, String clsid, IDTSComponentMetaData90 componentMetadata,
PipelineComponentInfo& pipelineComponentInfo)
at
Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

This happens with EVERY custom component on my computer. The same components work fine on other machines.

Does anyone have an idea?

Regards

Arne Janning

View 1 Replies View Related

NOT Replicate Data Archive

Jun 5, 2002

Hi Folks,

We have transactional replication setup to replicate data from production across to a reporting server.

We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.

Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?

Cheers,

David

View 2 Replies View Related

NOT Replicate Data Archive

Jun 5, 2002

Hi Folks,

We have transactional replication setup to replicate data from production across to a reporting server.

We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.

Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?

Cheers,

David

View 2 Replies View Related

Replicate A View With Data?

May 4, 2007

Hello,
I am trying to add a View to an existing publication and it the subscribers (all devices with SQL CE) don't get the View after replication. I have deleted and recreated the publication, and only the tables will appear on the device, not the view.

Also, I want the data from the view to be dynamic, filtered by using the Host_Name() function/value. Will this work for a View?

thanks
- will

View 3 Replies View Related

Replicate (Duplicate) A Row's Data Into Same Table

Apr 20, 2008

 I'm working on a web app that needs to be able to take a row in the database and duplicate it, creating a new row in the same table with the same data except for the ID field and reference field.So basically: table1.row1 references table2.row1. I need to duplicate the data in table1.row1 (creating table1.row2) with the same reference to table2.row1.Is there any easy way to do this in SQL? I'm just looking for some ideas or a framework to accomplish this. 

View 1 Replies View Related

Replicate Data From DB2 To SQL Server 2000

Feb 5, 2004

We have a distributed Database in DB2 and SQL Server 2000.
As the user updates/Inserts data to DB2 Database , the data need to be dynamically replicated to SQL Server.
Please Let me know the best possible method of doing it.
Thanks,
Ravi.

View 2 Replies View Related

How Do I Replicate Data From MySQL Into SQL-Server

Jul 23, 2005

Hello group,i am relatively new to SQL-Server database, but i have lots ofexperience with DB2 and Oracle Database. One of my tasks is setting upa replication between a Mysql-Database running on Linux and one of ourSQL-Servers.How do i achieve this ?If i understand the documentation correctly you have to program thereplication mechanism for yourself or you have to use some third partytool.Could anyone please outline, how to set up the replication mechanism(pointing me to some web-site should be enough) and also tell me ifthere is any third party tool.Thanks in advance and greetings from ViennaUli

View 1 Replies View Related

How Long It Take To Finish Replicate Data

Nov 1, 2006

I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?

View 1 Replies View Related

Replicate Data Only From SQL Express To SQLserver2005

Apr 20, 2006

We have merge replication, Publisher work on SQLserver2005
standard edition,


Subscriber work on
SQL Express. My problem is following.


Considering that the Subscriber is on SQL Express we can use
only Merge replication.


Is it possible to develop merge replication to replicate
data only from Subscriber to Publisher.

View 1 Replies View Related

Only One Character Added To Database Instread Of Full Data String??

Aug 18, 2007

When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db??  SqlConnection SqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
SqlCommand SqlCommand1 = new SqlCommand("addRoute", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@ReturnValue", SqlDbType.Int);
SqlParameter1.Direction = ParameterDirection.ReturnValue;
SqlCommand1.Parameters.Add("@xmlData", Request.Form["d"]);
SqlConnection1.Open();
SqlCommand1.ExecuteNonQuery();
Response.Write(SqlCommand1.Parameters["@ReturnValue"].Value);
//Response.Write(Request.Form["d"]);  

View 2 Replies View Related

Emulator Error To Replicate Data On SQL Server

Aug 18, 2005

Hi Guys,

View 7 Replies View Related

Replicate Data From DB2/AS400 To SQL Server 2005

Sep 11, 2007

Hi Guys

I am trying to replicate data from DB2/AS400 to SQL Server2005 (ENT edition) currently we use 3rd party tool to replicate data from DB2 to SQL Server2000 (ENT edition) and like to get rid of this 3rd party tool. I am searching for the last 3 weeks but didn€™t get a good starting point. I have linked DB2 to SQL Server2005 and can run queries against DB2/AS400 box. Now I want to set up transactional replication from DB2 to SQL Server 2005. I have read about peer to peer topologies but I don€™t know if that€™s the route I have to take?



So can someone please help me? I really appreciate your help.



Thanks

Tariq

View 1 Replies View Related

How To Pull Data Or Replicate A Table From One Server To Another

Aug 21, 2015

pulling data from a table in Server A and creating the same table on Server B.

From what I've read you can't export/import or use a wizard as the db is of the type dbo.md_*****.

View 3 Replies View Related

Can Not Delet A Publication In SQL Server 2005: The Publication Does Not Exist [SQL Server Error: 20026]

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

Last Time Publication/subscription Replicated Data

Jun 21, 2007

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??



I want to set up monitoring to make sure I am aware if something for some reason does not replicate



OH and it is on SQL 2005 enterprise... an

View 2 Replies View Related

Replicate Data Driven Query Task(DTS) In SSIS

Sep 6, 2007

I am migrating DTS packages to SSIS (recreating all the logic).
I have a Data Driven Query task in DTS with

Source query - select x,y from table1 (from database db1)
Binding - table2 which contains columns which match table1 x,y (fron database db2)
Transformation - maping from source table1 x,y to Binding table2 x,y
Queries - type update update table2 set x=? where y=?

I know that there is no similar task in SSIS,can someone tell me how to replicate this in SSIS

Thanks in Adv

View 5 Replies View Related

Replication :: Replicate All Data From Remote Locations To Central Location

Aug 29, 2015

there are several remote locations where sql is running, my company has asked me to find a way to collect all the data from the remote locations to a central location automatically,for example day to day data should be synced at night time from 2am to 7 am and it should be compressed automatically before data transfers to the central location. NOTE there is no domain only standalone workstations

View 3 Replies View Related

Replication :: Replicate Data From 3 Publishers To A Single / Central Subscriber Transactionally?

Oct 15, 2015

Is it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.

If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?

View 3 Replies View Related

Created SQL Server Database Using SYBASE DLL && Data, But Data Problem

Jan 21, 2008



Hello,

I took our SYBASE DDL and created the tables on an SQL Server Express datbase on a PC and then downloaded our data from UNIIX using the WinSCP utility and BULK Inserted the data into the SQL Server database and everything uploaded fine apparently, but when we go to use the data we are getting some data differences.


Do you know of any data conversion issues that may be coming into play here maybe?

Thanks very much!
BobK

View 1 Replies View Related

Single Direction Transfer Of Data In Merged Publication (subscriber To Publisher)

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

SQL Server 2008 :: Initialize Transactional Publication From Backup Loss Of Data

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

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related

Accessing Data From A Programmatically Created SqlDataSource

Nov 3, 2007

Hi
I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example????
If Not Page.IsPostBack Then
'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
'Create a SqlConnection instanceUsing connString
'Specify the SQL query
Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17"
'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString)
'Get back a DataSetDim myDataSet As New Data.DataSet
'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -??????????????
'Close the connection
connString.Close()
End Using
End IfThanks for any helpRichard
 

View 1 Replies View Related

Sql Challenge : List Created From Column Data.

Apr 15, 2008

Lets say I have a table called MyData.

MyData has two columns, ID (int), and Value (varchar(x) )

Lets say the data in the table looks like this:

ID Value
- -----------
1 A
1 B
1 C
2 A
2 D
3 E
3 F
3 A
3 G

now, is there any way to construct a select statement from this data that will return a dataset that looks like this?

ID ALL_VALUES
- --------------------
1 A,B,C
2 A,D
3 A,E,F,G


In other words, is there a way to combine all the Value field values into one field for each ID?



View 8 Replies View Related

Performance Of Set-Type Data Types Created Using CLR

May 8, 2008

In my applications, I often encounter situations where the domain of a type is some set (e.g. Student, Staff etc. for Membership type etc.). A common solution is to assign integral values to each possible value of the set & convert them to corresponding strings programatically when fetching data, or use a Foreign Key into a relation defining each of the unique value.

MySql has a Set Data type. So, this time around, I decided to do the same with Sql Server, and resorted to CLR to create a custom data type, whose domain could only be a collection of predefined strings or other data types.

But, just before deploying the assembly, the performance bug struck me. So, assuming a simple custom data type, like:





Code Snippet

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure MemberType
Implements INullable

Private Const Student As String = "Student"
Private Const Staff As String = "Staff"

Private memType As Int16
Private m_Null As Boolean

Public Overrides Function ToString() As String
If (Me.m_Null) Then
Throw New SqlTypes.SqlNullValueException("Null Reference Exception")
End If

Select Case memType
Case 1
Return Student
Case 2
Return Staff
Case Else
Throw New Exception("Invalid State for MemberType")
End Select
End Function

Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
' Put your code here
Return (Me.m_Null)
End Get
End Property

Public Shared ReadOnly Property Null() As MemberType
Get
Dim h As MemberType = New MemberType
h.m_Null = True
Return h
End Get
End Property

Public Shared Function Parse(ByVal s As SqlString) As MemberType
If s.IsNull Then
Return Null

ElseIf (StrComp(s.ToString(), Student, CompareMethod.Text) = 0) _
OrElse StrComp(s.ToString(), "1") = 0 Then
Dim u As New MemberType
u.memType = 1
u.m_Null = False

Return (u)

ElseIf (StrComp(s.ToString(), Staff, CompareMethod.Text) = 0) _
OrElse StrComp(s.ToString(), "2") = 0 Then
Dim u As New MemberType
u.memType = 2
u.m_Null = False

Return (u)

Else
Throw New SqlTypes.SqlTypeException("Invalid Value for MemberType")
End If
End Function
End Structure
Would it be a performance issue, if I use such Data types, whose data is fetched or updated frequently???

View 11 Replies View Related

Adding String To Database, But Name Of String Is Added, Not Data

Mar 12, 2008

Hello, I am tring to add a string my database.  Info is added, but it is the name of the string, not the data contained within.  What am I doing wrong?  The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string.  All help is appreciated!
 
 
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
 
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
 
 
 End Sub
End Class

View 3 Replies View Related







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