Hi All, We are using SQL2000 servers with transact replication to a warm
stand-by server. We are seeing the following error from the Log reader
agents.
Does anyone have any insight on this error?
The process could not execute 'sp_MSadd_repl_commands27hp'
TIA Scott B.
The problem case for transactional replication in MS SQL 7.0 is as follows:
Server A (intended publisher)
Server B (distributor, intended subscriber)
When configuring replication, the distributor (B) can be configured easily; the publisher (A) that uses B as its distributor will not allow for a publication to be created using either the Enterprise Manager wizards or TSQL commands. The error '627: Cannot use SAVE TRANSACTION within a distributed transaction' occurs when creating the publication.
Any suggestions; I need some help ASAP.
Thanks
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch
only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs
could not update the distribution database subscription table, the subscription status could not be changed.
changed database context to 'master',(MSSQL SERVER error 22538)
I am trying to set up a merge replication between SQL 2005 and SQL Express. When I try to apply the snapshot to the SQL Express database. I get the error "Can not access filepathSnapshot.pre due to error OS 3"
Does anyone know what OS error 3 applies to?
Also, when I try to place the snapshop in the default folder location say D:ReplicationData the create snapshot process will work. If I try to place the snapshot folder not in the default and in a UNC like this \serverIPReplicationData I get an access error. sqlAgent has access to the share, which is fully shared out. Any ideas?
Posted - 02/01/2007 : 22:35:02
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch
only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs
could not update the distribution database subscription table, the subscription status could not be changed.
changed database context to 'master',(MSSQL SERVER error 22538)
Please help
publisher: 2005 Standard SP2 CTP
Hi,
I get the following error when
i configure the lookup transform in the data flow task "Input column has a data type that cannot be matched".
This is the query that i use to set the reference table dataset
select firstname, lastname, address, email from customers_dimension cd , cust_test ct where cd.address<>ct.address.
I basically want to try and find all those records that have the same firstname, lastname, email in the customer dimension table where the records do not match. Both the input fields and the lookup fields have the same data type [varchar(max)].
It is pretty confusing, so much so that i did the lookup against the exact same table and got the same error.
Does anyone have a better idea as to what the problem is?
Thankyou
P.S.-This is the caching statement in the advanced tab
select * from
(SELECT firstName
FROM Customers_Dimension) as refTable
where [refTable].[firstName] = ?
When I using Create Publication Wizard for database 'MY DATABASE', and setup 'MY SERVER' as the Distributor, then I get a message:
SQLServer Agent on 'MY SERVER' current uses a system acount, which cause replication between servers to fail. In the following dialog, specify a domain account for the Service startup account.
I don't understand this message (current in SQL Server Agent properties, tab Service startup account using System account)? Anyone can explain to me?
Is it safe to change 'max text repl size (B)' on a live system while it is running with users and services running?? I'm using SQL Server 7.0 running on Windows 2000.
View 2 Replies View RelatedI am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
Your help is greatly appreciated.
Gary
I'm in trouble again. I want to make merge replication between SS 2000 Enterprise and SS 2005 Express edition. 2000 will be publisher, 2005 will be subscriber. In addition I want to use push subsription at 2000 side, very last thing is it will be download only (I know how to make a merge rep. downloadonly, thanks god!). That's all I want to do.
I made a little search, it seems what I want can't be done automatically, using SMO or scripts is suggested. In one of other forums I saw "distibuter>=publisher>=subsriber" (refering sql server versions). Is it an incontestable truth that I cannot make SS 2000 a publisher where SS 2005 Express is subscriber?
How can I achieve my goal, isn't there anyway to make 2000 publisher for merge replication while a 2005 Express is subscriber?
Thanks
abdul
Hi,
We're running a replicationprocess for months now, and 2 days ago it broke down, for no appearant reason I can detect. The setup is this:
A SQL2K server has a publication defined on a database, consisting of several dozen tables. It is a transactional publication, running continuously. There is 1 subscriber, a SQL2005. Both servers run with Win2003 and all have the latest servicepacks.
Up until 2 days ago there was hardly a problem. Then I received errormessages on being unable to load into a specific table. That table had been changed on that day, namely, 1 column was changed from CHAR(13) to CHAR(12). I do not know if this has any relation with the problem we experience.
Currently the databases are structural the same. I could not get the replicationprocess to work and deleted all, publication on server1 and subscription on server2. I tried to setup a new transactional publication, which is not a problem, however, I cannot create a succesful subscription. I receive the message:
The process could not bulkcopy into table 'Tablename', where tablename is the changed table...
Again, both tables in publisher and subscruiber are the same in all respects. When I remove the offending table from the publication, the same message now with another tablename... Removing this table will result in again this message with another tablename...
I am really flabbergasted. Any idea where to look at?
TIA
We have VLDBs running on a SQL 2005 server. Some users have questioned speed and response times. When I investigated the server (W2K3) using the perfomrance monitor, I noticed an interesting questionabel result. The Repl. Pending Xacts showed 44,000,000 transactions waiting to be delived to the distribution databse. Problem is - we do not have replication tunred on on any of the db's on the box. In my research, I have not noticed anyone with a similiar issue. Does this amount of transaction take up space, or is it simply a counter that I don't have to be worried about?
View 4 Replies View RelatedI'll start off by saying these questions were submitted to Microsoft and no
one can give us an answer without them doing a source code review. Their
response was "these are internal BCP scripts that are used by SQL"....yes, I
know that....that doesn't help to explain why the system created them. At
this point, I don't want my compary to pay support hours for Microsoft
personnel to learn what their software does.
--------------------
We've had merge publication in place for several years.
Last year, we added some tables/articles to publications.
After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2
Standard, when the snapshot was run, the system created some new files that
had never existed before. Also, and more importantly, these files were only
created for the articles added after the initial snapshot was created.
Articles existing in the intial snapshot did not have these type of files
created.
Examples of these files for a SA_MOVE table that was added to an existing
publication:
MSmerge_contents_SA_MOVE.bcp
MSmerge_contents_SA_MOVE_forall.bcp
MSmerge_rowtrack_SA_MOVE90.bcp
sysmergesubsetfilters_SA_MOVE.bcp
sysmergesubsetfilters_SA_MOVE90.bcp
Also some new bcps created were:
MSmerge_contents90_forall.bcp
MSmerge_genhistory90.bcp
MSmerge_rowtrack90.bcp
sysmergesubsetfilters90.bcp
The system wanted to apply these files before it would start synchronizing
data so they had to be delivered to the subscriber. It appears it was the
sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in
size and we do not have filter on(at least not as far as I know...see the
addmergearticle code below).
New databases created this year that had all of the articles defined in the
publication before the initial snapshot was taken did not get these
additional files created, nor did the system want to try to send files to the
subscriber before syncrhonization could begin.
This is an example of how an article had been cretead under SQL Server 2000
when adding a new database, before an initial snapshot is run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000003000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
This is an example of how an article was added under SQL Server 2000 to an
existing publication that already had the initial snapshot run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000000000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0,
@force_invalidate_snapshot = 1
A few questions:
Does anyone have an idea why the system created these files are part of the
2005 upgrade?
What is the real purpose of the files with names such as _forall.bcp ?
Thanks for any help/info on this,
Doug
Hi,
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
Thanks for your help
Graham
I am setting up a combination of transactional and merge replicationon a very large database and do not want to apply the initial snapshotsince this would take to long. I am doing this with scripts that werepreviosly created when the database was not this big. I want to copythe publisher DB over to the Subscriber Server. Can I use theparameter @sync_type to 'none' when running the sp_addsubscription?Does anyone have any experience in doing this?Thanks in Advance for any help on this.Alan
View 2 Replies View RelatedHello...
We have had Merge Replication working for the past few months (SQL 2005 to SQL Mobile) and suddenly today the replication URL that points to the SqlCESA30.DLL does not work. I have tried re-running the SQL Mobile virtual folder wizard a few times....resetting IIS and even a re-boot...and still the URL is not available.
Is there anything that might provide some clue as to why this has unexpectedly failed?
thanks for any help!
- will
Hi,
View 6 Replies View RelatedHello,
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
Thx
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
I am researching the possible use of Peer-to-Peer Transactional Replication with SQL 2005 to improve the availability and scalability of the SQL-farm.
View 5 Replies View RelatedWe are going to have two SQL 2000 Servers with a SQL 2005 distributor server that will handle 1 way transactional replication. What kind of server should I ask IT to spec out that would satisfy an installation such as this?
Our production DB is 30GB and we are replication about 60% of the data to another server.
I have an equation
( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(1000000, 0) ) ) - 1 ) * 100 AS FibreScrapFactor
This works for the example order that I am looking at. However if i take out the where clause (so i have all orders) i get divide by zero error.
If I limit the / 1000000 to 10 which i then have
( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(10, 0) ) ) - 1 ) * 100 AS FibreScrapFactor
This works for all orders, but is incorrect I need to divide by a million. How can I get this to work?
I am trying to compare the ADDRESS FIELDS Between 2 tables in SQL SERVER 2008. However when I run the comparisons below it throws the error below:
Query:
select
inner
JOIN TABLE2 B
ON
COLLOAN=
COLLOAN1
a.ADDRESS<>b.PropertyAdd
Error : Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP437_CI_AS" and "SQL_Latin1_General_CP850_BIN" in the equal to operation.
WHERE
A.Address ,b.PropertyAdd
,a.*
from TABL1 A
My Code is
SELECT Client,State,
CASE WHEN Weight>=11 and Weight<=455 and Height>=110 and Height<=250
THEN isnull((cast(nullif(Weight,0)/power((NullIf(Weight,0)/100),2) as int)),0) else -1 end as BaseBMIScore
FROM Test100
And I got following error:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
I keep getting an error of error in statement near = on this update statement. What is incorrect?
Update abcd
Set CAST(field1 As varchar(4000)) = 'Computers and 2-in-1s'
where CAST(field1 As varchar(4000)) = 'Computeres and and 2-in-1s'
I am having an issue when trying to union all between two databases.
This is the error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
I have updated the collation in both DB to Latin1_General_CI_AS and I am still getting the error when running the query.
The queries run separately ok. why I am still getting the collation error.
Below code gives the error "No value given for one or more required parameters"
strSQL = "insert into FILE_UPLOAD(RefNo, FileName, FileType, FileData, FileDescription, Created, CreatedBy) values "
strSQL = strSQL & "(?, ?, ?, ?, ?, ?, ?)"
Dim cmd As New OleDbCommand
cmd.Connection = objCommon.oConn
cmd.CommandText = strSQL
cmd.Parameters.Add("@RefNo", OleDbType.VarChar).Value = ""
cmd.Parameters.Add("@FileName", OleDbType.VarChar).Value = strFileName
[code]....
In my sp I'm have an Insert statement. In the event that after the insert @@rowcount = 0 I'm throwing an error with RAISEERROR.
This is fine, however, in my CATCH block I'm referencing a local temp table but getting an error that it no longer exists.
My question is, does throwing an error drop any #temp tables? I thought they were in scope for the session?
I have a work database where I implemented a table-valued function. One colleague of mine reported to me that this function gave a Divide by Zero error when executed with some specific values given to its arguments (there are a 15 arguments). Then I started debugging, and I introduced some exit points to the function before its end in order to detect the point where the error appeared, since I don't have access to the database server and I cannot use the debugging tools from remote, due to the network configuration of my office. I can only do attempts on the code to try to find a solution.
Since I didn't manage to get rid of this error, I decided to make a silly and desperate attempt: I put a RETURN statement immediately after the BEGIN of the function body, with the idea that the function should not raie any error if it exit immediately after its beginning, despite the fact that this results in an empty table in return.
The result of my attempt is that the Divide by Zero error is still THERE (!), even if my function looks like
ALTER FUNCTION [dbo][<myFuncName>](...parameters...) RETURNS TABLE (...table definition...) AS BEGIN
RETURN
END
GO
How I can check it.
Why do I get an error when creating XML schema as follows?
CREATE XML SCHEMA COLLECTION PersonalInfoSchema AS
'<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/xmlSchema">
<xsd:element name="personal">
<xsd:complexType>
<xsd:sequence>
[Code] ....
I don't want show the DBMS error to my clients. I want show a simple error instead of SQL error.
View 3 Replies View RelatedHow can I get the value (pr_profit / pr_sales ) * 100, (cur_profit / cur_sales) * 100 for %profit. When I am giving zero devide error is coming . How can I give if pr_profit = 0 then % profit should be zero.
SELECT
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal Else 0 End) as cur_Sales,
SUM(CASE WHEN invdate >={d '2014-01-01' } and invdate <= {d '2014-11-03' } Then linetotal - linecost Else 0 End) as pr_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) as cur_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) -
[code]...
I have the following querry:
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,
[Code] ....
That query is suppose to add to calculation field OutStock and InStock based on the value of n
When executing this query I get the following message :
Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.