Tx Replication Works In 2000 But Not In 2005?
Apr 28, 2006
Hello,
I've got a simple transactional replication set up. I have a separate publisher, distributor, and subscriber with 76 articles (tables only) being pushed from the distributor.
I have this exact setup with the same tables and data working in the SQL 2000 environment. I am testing replication on our SQL 2005 test servers before moving to production, however when the distributor attempts to push out the initial snapshot I keep getting this error.
Error messages:
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Anyone know anythign about it?
Thank you,
Aaron Lowe
View 17 Replies
ADVERTISEMENT
Jun 15, 2006
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
ANY IDEAS?
View 5 Replies
View Related
Mar 15, 2006
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
PLEASE HELP....
=====================================================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
View 3 Replies
View Related
Jan 2, 2008
I hope this is a easy one. We are trying to find a fix for a select statement that works in 2000 but not in 2005 with a simple select statement.
The easiest statement that will duplicate the error is:
TestTable has 3 columns: Primary, strTest, strTest2
SELECT strTest, strTest AS Name
FROM TestTable
ORDER BY strTest2
If you sort by the Primary column you will not receive an error.
How can you select the same column twice and then sort in the SQL statement?
View 3 Replies
View Related
Aug 27, 2007
Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.� So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)� So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
View 2 Replies
View Related
Aug 23, 2007
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @numValues
SET @CurrentValue = (SELECT SCOPE_IDENTITY())
IF @ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@CurrentValue, @ClientID, @UsergroupID)
END
IF @DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@CurrentValue, 1, @DateOfBirth)
END
-------------------My Code Stops here ------------------------------
IF @Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 1)
END
IF @Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
View 5 Replies
View Related
Aug 27, 2007
Hi!
I have installed SQL 2000 and SQL 2005 in my computer. But none of 2005 feature is working. So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000:
€œMicrosoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)€?
So, how can I change it for working with SQL 2005?. It€™s like I€™m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
View 1 Replies
View Related
Mar 28, 2008
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
Weekly sales has approx 100,000,000 rows
Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
Stacy
View 5 Replies
View Related
Oct 10, 2007
Currently I receive the following error when executing script within a DTS package in SQL 2005 (it seems to be working in SQL 2000):
Processed 27008 pages for database 'Marketing', file 'Marketing_Data' on file 5.
Processed 1 pages for database 'Marketing', file 'Marketing_Log' on file 5.
BACKUP DATABASE successfully processed 27009 pages in 15.043 seconds (14.708 MB/sec).
(5 row(s) affected)
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
The code I am using is:
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'Marketing'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:SQL2005 dbsMarketing.dat'
-- the new database name
DECLARE @TestDB2 varchar(200)
SET @TestDB2 = datename(month, dateadd(month, -1, getdate())) + convert(varchar(20), year(getdate())) + 'Inst1'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:SQL2005 dbs' + @TestDB2
DECLARE @RestoreLog varchar (2000)
SET @RestoreLog = 'C:SQL2005 dbs' + @TestDB2
-- ****************************************************************
-- no change below this line
-- ****************************************************************
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreLog + '.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
-- RESTORE FILELISTONLY FROM DISK = 'C: empackup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C: empackup.dat'
-- RESTORE LABELONLY FROM DISK = 'C: empackup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C: empackup.dat'
IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB2)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB2
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB2 + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
END
View 1 Replies
View Related
Aug 3, 2006
If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
Thanks,
Grant
View 1 Replies
View Related
Jun 20, 2007
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
telnet sql5.hostinguk.net 1433 and
sqlcmd -S sql5.hostinguk.net -U username -P password
See below:
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:443 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT
TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT
TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
View 7 Replies
View Related
Jun 27, 2007
Hi Friends
What are the main differences between Sql Server 2000 Replication and Sql server 2005 Replication?
Thanks
Vic
View 2 Replies
View Related
Aug 17, 2005
Where can i find some reference material on how to upgrade to 2005 in a replicated environment please?
does anyone know what order i need to tackle the servers, is it likely to be the same as the Service Pack process?
cheers
View 3 Replies
View Related
May 4, 2006
Ok here is the deal...We have our current dev box running SQL 2000 SP3We have two new desktop boxes that we are using as a test, they are both running SQL 2005 SP1The Dev Box = PUBLISHERDesktop #1 = DISTRIBUTORDesktop #2 = SUBSCRIBERAll three SQL Servers are using the same Domain Account to run SQL Agent and SQL Server processes.When I try to create a publication from the publisher, while running the sp_addpublication step through the UI (or thru SQL Query Analyzer) I get the following error message:Msg 15404, Level 16, State 10, Procedure sp_grant_publication_access, Line 136Could not obtain information about Windows NT group/user 'crumpte', error code 0xffff0002.So as a test I tried using our subscriber box as the publisher and had it point to the same distributor box to act as a distributor - and the publication was created fine.It seems to be an issue using SQL 2k as the Publisher and SQL 2005 as the distributor. From the research that I have done, I found this KB article:http://support.microsoft.com/default.aspx?kbid=830596Which essentially says to install the latest SP for SQL 2k, which would be SP4, in the past I know there were issues with AWE memory allocation.Q1. Has SP4 been hotfixed to resolve this AWE issue?Q2. Has anyone here successfully setup replication from SQL 2k -> SQL 2005 using SQL 2005 as the distributor?Thanks!
View 12 Replies
View Related
Oct 30, 2007
Dear All Is it possible to make replication beetwen:SQL 2005 as publisher & SQL 2000 as subscriber ? :shocked: Best RegardsPiotr
View 2 Replies
View Related
Jan 17, 2006
Hi There
Currently in production we have a publisher, remote distributor, and subscriber all running 2000.
We
want to upgrade the subscriber and possibly the remote distributor to
2005 while leaving the publisher 200 for now, i need a good link or
article about the proper proceudre to accomplish this.
Thanx
View 3 Replies
View Related
Oct 19, 2007
Hi All,
I have replication from SQL 2000 SP3 to SQL 2005 SP2 and now i am expereancing one strange problem, there are situations that last row of some tables(Articles) doesn't come to subscriber.For exmple i insert 50 rows and 49 are coming to subscriber and always it is last row(considered by my Primary key).Before then my replication model was SQL2000 SP3 -> SQL2000 SP3 and i didn't have such problems.
Can someone give me a clue?
View 4 Replies
View Related
Aug 8, 2007
I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.
I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).
I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.
UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard.
UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.
View 3 Replies
View Related
Nov 8, 2007
Hi,
I setup transactional replication between 2005-> 2000 database for only one table. It works fine no problem. I checked replication monitor everything works well.
My Subscription was Push Subscription on Publisher.
This morning I restored main database at publication server, I saw all my publication configuration were gone.
I then went to create a publication and push subscription again..and did it, But when I went to replication monitor.
I checked that Snapshot are being created, but from distributor to Subscriber is saying below message?
"The concurrent snapshot for publication is not available because it has not been fully generated.....lONG CHPPPED off meassage"
Can any body tell me hwo to get over this issue?
Many Thanks
SKR
View 7 Replies
View Related
Apr 19, 2006
Hi everyone,
I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.
Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..
I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.
LSC
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
Jun 27, 2006
We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.
Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.
We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.
For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).
To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.
Examples of the current behaviour are as follows,
On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.
Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.
This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.
Everything still points to the merge replication setup.
So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.
Sorry for the convoluted post. Hope someone can help us.
Cheers
Steve
View 6 Replies
View Related
Jan 8, 2007
I have created the plication and push subscription on SQL 2000 by running sql scripts. I can view it through management console already.
but on SQL 2000 server, i dont' know how to start the snapshot agent and how to synchronize explicitly. In 2005, there is a system sp
sp_startpublication_snapshot @publication = @publication
can do the job. can you tell me how to do the same in the sql 2000?
additionally, I tried to use RMO to implement the replication. but it seems some of the classes only support 2005. doesnt' it means RMO can't be used to program on hybrid replication environment?
plz reply. thanks
View 1 Replies
View Related
Mar 2, 2007
I have a publication on 2000 machine, replicating by pull subscription to a 2005 machine. I added a table to the Articles in the publication. Reinitialized the subscription, and also restarted both the snapshot and log reader agent. I was kind of assuming the publication would automatically create the table on the subscriber, but after a very long wait it is still not there. Am I missing anything?
PS. Distination Object setting : "Action if name is in use" is set to drop and create new, not that that should make any difference since name is not in use anyway.
Thanks
View 4 Replies
View Related
Sep 25, 2006
Hi,
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
Thanks so much,
David
View 3 Replies
View Related
Sep 15, 2006
I want to replicate data from sql server 2000 to a sql server express 2005 box.
I can dts data across..but I want to be able to set up push replication from the Sql Server 2000 box, but the option to push the data to the Sql Express server in the GUI does not come up only Sql Server 2000 boxes are listed. Initially I will be using Snapshot replication but would like to eventually move on to transactional rep if only I can get it to push the data across.
Does anyone know how to acheive this thanks for any help Sammy.
View 3 Replies
View Related
Jun 12, 2015
I configured the transactional replication using push method. I use the option generate snapshot. Until the Snapshot complete, I didn't any tables in the subscriber db.After that also I can see in some tables it has indexes and some tables it doesn't but it has data in that table .
How does the bulk copy works from snapshot?i.e. is it first create the table with columns then insert data later it will create the indexes...How the sequence works in the subscriber.
View 1 Replies
View Related
Aug 3, 2006
Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
View 9 Replies
View Related
Dec 25, 2006
Hello!
We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.
A few questions to experts, familiar with Merge Replication in SQL-2005:
1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.
2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.
3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?
Thanks!
View 3 Replies
View Related
Mar 6, 2006
I have 2 SQL 2000 servers (both have SP4) and are running on Win2003 SP1. We will call them SQL1 and SQL2.
SQL1 is the publisher and distributor for trans replication, SQL2 is the subscriber with immediate updating, and queued updating as failover.
I configured the publisher and subscriber. The snapshot replicates fine to the subscriber and all the agents are working fine. There is only 1 table article configured for replication.
Let's say I am trying to update a single row.
I can make as many updates on this row to the publisher as I want and they all replicate just fine to the subscriber. (Note: an update to publisher row causes a new GUID to be generated in the "msrepl_tran_version" column.) The updated data and the new GUID are successfully replicated to subscriber. I can continue to successfully make as many updates to this row on the publisher just fine.
Now I want to make an update to this row at the subscriber: When I do this (via enterprise manager), I update column 1 with a new value, but a new GUID is NOT created on the subscriber. The column I updated successfully replicates back to the publisher on the first update attempt. This update causes the publisher to create a new GUID for the row, but the new GUID does not replicate back to the subscriber. (at this point the publisher and subscriber do not have matching GUID values).
Further updates to this row on the subscriber cause an error "...rows do not match between publisher and subscriber...", and further updates to the publisher do not cause an error message, but simply does not update back to the subscriber.
Any help would be greatly appreciated!
View 5 Replies
View Related
Aug 3, 2007
We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
Thanks,
Geoff
WA POLICE
View 5 Replies
View Related
Jun 26, 2007
Hello,
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
Thanks in advance,
John
View 3 Replies
View Related
Jun 13, 2006
We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).
We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.
The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???
HELP!!
View 5 Replies
View Related
