May 8, 2012 at 10:30 am
Is this something that can be done? I have tried creating this by using sp_addsubscription, with @subscriptiontype = 3, which is an oledb provider. This does not seem to work. I am trying to do transactional replication from sql to a flat file.
Thanks.
May 8, 2012 at 11:21 am
What is the error message that you are seeing?
May 8, 2012 at 11:46 am
"The process could not access database 'I:\test_repl.txt' on server 'NYSQLGRP03'."
Here is the code that I am using to create the subscription, I have already set the publication to allow heterogeneous subscribers.
EXEC sys.sp_addsubscriber @subscriber ='server_name', -- sysname
@type = 3, -- tinyint
@description = N'Testing Replication to flat file', -- nvarchar(255)
@encrypted_password = 0,
@publisher = NULL -- sysname
EXEC sys.sp_addsubscription
@publication = 'Repl_Test', -- sysname
@subscriber = 'server_name', -- sysname
@destination_db = 'I:\test_repl.txt', -- sysname
@subscriber_type = 3 -- tinyint
I think I am going to try creating a system DSN for the file and see if that works.
Thanks.
May 8, 2012 at 12:02 pm
I'm going to go out on a limb here and say that you can't replicate to a text file.
May 8, 2012 at 1:46 pm
I highly doubt any of the commonly available OLE DB Providers for Text Files will work for this. Replication support for Jet, which can connect to text files but its possible that the Jet drivers would not do this either, was discontinued in SQL 2005. The recommendation was to move to use SQL Express instead of a Jet-based subscriber. If you really want to replicate to a text file I think you will need to write your own OLE DB Provider to do it.
This begs the questions: Why do you want to replicate to a text file? Does BCP'ing your publisher table to a text file periodically not provide the flexibility you require? What are you trying to do?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 8, 2012 at 2:22 pm
The flat file would be used as the data source by a search platform for user searches on a site. The live data sits on a Sql box. I am doing some POC on this and was wondering if I could replicate it to a flat file. I guess sql express is an option as its free.
The table is too big to bcp periodically, unless I can only bcp the deltas (after the initial load). I guess I ll have to write some custom sps to grab the deltas and bcp it out.
May 8, 2012 at 2:28 pm
I would go with SQL Express. It's free and can be used as a replication subscriber, and will handle locking infinitely better than a writer (replication) and a reader (your website) both accessing a text file on a regular basis.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply