When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005
End of file reached , terminator missing or field data incomplete in SQL 2005
Consult the BOL for more information on the bcp utility and its supported options.
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.
Replication-Distribution: Task 'CAServer_Page_VAServer_InPage' failed. 3700[Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed - User: Reason: Not associated with a trusted SQL Server connection.
I checked under the SQL Security Manager, View, SA Privilege and my sqlexecutivent login is set up as:
CAServersqlexecutivent sa
Thanks in advance for any tips you can give me with this. Both servers are set up in a workgroup. We are not using Domains.
Also, should my table already exist on the Subscribing Server?
I've been using replication for a long while now but have never come across this error. It's a basic transactional replication from ServerA to ServerB, where ServerA is also the distributor. Everything had been running fine on it until yesterday, when this error started popping up and no further transactions could be delivered.
After some quick googling I was able to determine that the distribution agent account needed write access to C:Program FilesMicrosoft SQL Server100Com. According to the MSFT article it's because the distribution agent is running under a non-default profile. I didn't change this. However, what I did change around the time that these errors started occurring was the server's Replication Max Test Size setting. It would be far too coincidental for this to not be the cause, but what I don't understand is *why* that would have changed it.
how do I change this? It is definitely not preferable to create temp files in this directory in our environment.
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
I am running a transactional replication on two servers. The distribution agent is reporting a timeout, eventhough the distribution database and the replicated database is on the same server.
Folks, We have just finished an application that requires every client PC to have a system DSN that reflects the new datasource. Is there an easier way to distribute this system DSN other than logging on to each pc and creating the datasource?? Thanks!
I'm new to working with replication & I'm just starting to test some implementations. We're still using the beta, v7.00.517. and... basically nothing works, I can't even open any of the replication management features in Enterprise Manager. As soon as I select any item from the replication menu I get:
Error 208: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'msdb.dbo.MSdistributiondbs'
It's either:
A: a bug, B: something screwed up in my system databases, or C: I'm missing something really obvious.
I've got replication set up as a publisher subscriber, to basically sync a primary server with a backup server. My distribution log keeps filling up, I've got a perf alert for now to truncate it at 75% full for now, but why does it fill up? it's size is about 1.5 gig My tran log for my database will also not remove about 500mb of data as well, is there any way to see what is going on?
I have accidentally registered an existing database as a distribution database, which made it a system database. the data itself is safe and sound, but I want to undo the whole thing.
I cannot create a Distribution Server on SQL Server 2005 SP1. When I right click on Replication and click "Configure Distribution" and hour glass appears for a minute, and then nothing happens.
I get the same result if I try to create a publication.
Can someone please tell me how I can take a set of sales orders and generate a table or set of data that show the percentage of sales per product which are in different sales ranges
Hi I have a table with 3 columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
for ex start date end date volume month finalVolume 1/1/2000 12/1/2000 100 jan 100 12/10/2000 30/11/2000 120 oct ? nov ?
I Have not been able to solve this problem from quiete a while now.
I am using sql server 2005.
I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
I have to perform a query on this table so that I can group the volumes for different months and different years.
I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.
Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.
Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?
Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)
I'm trying to estimate possibility of using of MS SQL Server CE in my application. Currently my application is monolithic Delphi Win32 executable. It targeted to inexperienced user audience - accountants, lawyers, insurance agents etc. It does not require any deployment, just unpack ZIP archive and it will work. I want to clarify for myself some features of CE:
1. It is possible to deploy CE with application by simple copy operation? 2. It is required to install any frameworks like .NET CLR in order to CE function? I suppose target PC is the clean Windows XP. 3. It is possible to work with CE from native Win32 application?
4. I'm concerned about performance. Target database have few tables with 10..20 thousand records, It deffinitely will contain about 10 thousands of images. Image size may vary from 50K to 10M but most likely it will be 100..300K. In some time it is required to write 2..3 images per second and this is critical.
I tried to make test with MS SQL Server Express Edition and received following results: 1. Add 10000 images as BLOBs. About 42 images (200K each) per second but MSSQL EE eat about 1.2G memory on my PC. 2. Sequentally read 1000 BLOBs from a table. About 20 images per second. 3. Random read 1000 BLOBs from a table. 37 images per second. Funny random read supposed to be slower but it isn't! 4. Random read/write in transaction 1000 transactions. Each transaction contain random read of two images then write of these two images. ~7 TPS.
Target PC is PIV 2.4GHz with standard 7200 rpm IDE drives and 2Gb of memory.
Hey everyone, sorry for the lame questions, but: 1) I have a transactional replication service set up. However, whenever we select a new table or column within a table to replication to the subscribers, the changes never seem to get picked up unless we make an actual change within some of the other data. Any idea why this is? Even after I've reinitialized snapshots, the new changes don't want to proprogate to teh subscribers until data is actually changed. 2) where do I need to go to manually stop and restart a a distribution agent to force these changes to be proprogated without having to change any data within the tables? Thanks
I seem to be having trouble with the distribution agent not doing what I want it to do. I have set up a few publications to run all the time but when I want to schedule a publication to run once a day this doesn't happen it defaults to run all the time. Can the distrubition agent run some publications continusly and some publications scheduled?
Please, i need some help with the replication i had configured. I have two publications to the same subscriber, one with data filters and other not. The one without data filters i have test manualy. But now after i have created the other one, the log reader says that '145 transactions with 347 commands' were delivered, but the distribution agents says that 'no replicated transactions are avaiable'. I know that have many things to be replicated, but doesn´t work. The Sql server agent is working on the distributor. There is something that can be causing this error ?????
i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
I had replication set up and working but had to unbind the Pub/Sub and re add. Now on the ditribution task in Executive I am getting the following error
The last distributor job id and the last subscriber job id do not match. No jobs were available with a job id > 2147.
This is SQL Server 6.5 by the way.
Does anyone know what has caused this and what the solution is?
I've just installed a replication between 2 NT4 servers with SQL 6.5. A task with a 'distribution' type, owner = repl_subscriber, has started on the subscriber side but crashes with an error code S0001 : "There is already an object named 'Customer' in the database". 'Customer' is one of the tables that I want to replicate. Why do I get this error? And how to correct it? Thanks.
At managing index SQL Server 6.5, there is distribution button. I have a database on production server, one of the table has 20 indexes. When I press the Distribution button, it reveals that most of the indexes have very poor selection of index, from 30% to 99%. There are 6 of them are very good. Based on this statistics, do you think I should remove these indexes? As the book says, if the statistics is higher than 5%, the optimizer will not use index but do a table scan. Removing those index should not affect the performance, is that right? Your suggestion is very appreciated.
However, they cannot be accessed via the SQL Server Enterprise Manager interface like tables in other databases, at least I can't get to them. Is this a problem or a feature? I can query them to get their contents, but I'm wondering if there might be something wrong with my installation of SQL Server.
Any knowledge shared would be greatly appreciated.
I can't use the distribution database because it doesn't exist, and I can't create the distribution database because it already exists.
A failed replication set up had left a fat distribution database that caused the server CPU to peg 100% when I tried to set up a new Transactional replication (halfway through the wizard).
I removed all replication objects from SMSS, but the only thing that fixed it was deleting the distribution database with an ALTER to OFFLINE and a DROP, and restarting SQL.
Now, I'm unable to set up replication.
When I try, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."
In Distributor Properties, the Publisher is shown with a distribution database of 'distribution'. If I untick that, SQL asks if I want to disable the Publisher. I say yes & get the error "Database 'distribution' does not exist.'
Ok, so let's create it. In the General tab of that window I create a new Distribution database and call it 'distribution'. Then I get the error 'Could not add the distribution database 'distribution'. This distribution database already exists.
exec sp_helpdistributor shows distribution database as 'distribution' SMSS doesn't show the distribution database at all.
We have a production SQL2K DB with publication. I found replication stopped, distribution database was marked suspect and log file is gowning. I have tried using sp_resetstatus and restart server but recovery said some IO errors reading distribution's datafile, so I guessed distribution database was totally dead. I have handful documents telling me how to remove replication and I am going to take that ride. Before I do anything real, is there any warning from you Prof people? is there any trap I may encounter into? is that OK to rebuild replication after I remove the dead replication? highly appreciate for any help and comments. thanks in advance! -DW-
