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?
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.
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.
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
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?
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.
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...
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.
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.
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?
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?
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?
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.
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.
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
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?
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"]);
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.
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.
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
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
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
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?
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?
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.
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?
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?
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
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???
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
