remove columns created by replication process

One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :

select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'

Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :

...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?

Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

-PatP

remove article from trans replication?

Hi,
Using sql server 2005, we've got transactional replication going between two
servers. if i need to remove a couple of tables from the replication, do i
have to reinitialize the snapshot every time i do that? or is there an
easier way to achieve this?
thanks for any help on this,
Fred
Hi Paul, and thanks for your response,
I've checked out the page your referenced, and because I'm just learning
about replication, it's not clear to me if it applies in our situation, and
also to Sql Server 2005. So, allow me to ask again with more details.
In our next release procedure, we're changing the name of a dozen fields in
a dozen tables. some of them are primary key fields. we're also dropping a
couple of constraints. (I had thought we were dropping tables but just
learned differently.) Can these things be done without reinitializing the
snapshot.
what I've done this time, is to remove all the tables from replication, and
reinit the snapshot. Now the developer can make the schema changes
mentioned above, and then immediately after the release, I'll add the tables
back into the replication and reinit the snapshot again. Not pretty, but it
works. but that's why I'm asking about a way to make changes and have them
propagated thru replication, without breaking the replication.
I hope this is clear, and makes some sense! thanks for your thoughts.
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:enB1dfLsHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Please take a look here: http://www.replicationanswers.com/AddColumn.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
If you're still reading this, might it work to turn off DDL replication for
the publication, make the changes, then turn DDL replication back on?
I'm starting to implement via stored procedures now...
thanks,
Fred
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGPTFsSsHHA.3480@.TK2MSFTNGP02.phx.gbl...
> The way involving the least amount of work for SQL Server would be to drop
> the subscriptions to those tables which you are going to change radically
> (several fields and/or changing the PK fields) then drop the articles.
> Make the changes then add the articles, add the subscription and then run
> the snapshot agent and distribution agent. This effectively is a
> reinitialization of just those articles you are changing. For minor
> changes you'd use sp_repladdcolumn and sp_repldropcolumn.
> HTH,
> Paul Ibison
>

Remove Article from Merge replication

Hi NG
Publisher:
Win 2003 Server
SQL 2000 - SP3a
Subscriber:
Win 2000 - SP4
SQL 2000 - SP3a
After many headaches with dog servers and virtually non-existent bandwidth,
I have finally got my Merge Replication up and running OK.
However, I have inadvertently selected 2 tables for replication which should
not be replicated. The reason for this is that these tables are used as
staging tables when consolidating large amounts of data. They get 200,000
inserts/updates/deletes per day. Normally we only expect to be replicating
1,000 inserts/updates/deletes per day.
Is there a way to remove these 2 articles form the replication process.
If not, I will simply have to create 2 new tables, and ensure that all
references to the existing tables are changed to the new non-replicated
tables. However, I dislike workarounds and orphan items.
I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
have been unable to find any reference to this SP in Books online.
Regards
Des Norton
Des,
unlike transactional replication this is not possible via normal means. Your
best bet is to drop the publication and recreate without the 2 tables. This
can be made more palatable - especially if you have large tables - by doing a
nosync one, provided you can prevent updates to the data during this period.
The other option is to disable the merge triggers on these tables, but again
this is a bit of a hack.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kick the users off the subscribers, drop the subscriptions, drop the
articles using sp_dropmergerarticle, or by right clicking on your
publication and selecting properties, then in the articles tab uncheck the
articles you wish to drop.
Then redeploy your subscribers doing a no-sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Des Norton" <desREMOVEnortonUPPERCASE@.gmail.com> wrote in message
news:uncu3HIAGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi NG
> Publisher:
> Win 2003 Server
> SQL 2000 - SP3a
> Subscriber:
> Win 2000 - SP4
> SQL 2000 - SP3a
>
> After many headaches with dog servers and virtually non-existent
> bandwidth, I have finally got my Merge Replication up and running OK.
> However, I have inadvertently selected 2 tables for replication which
> should not be replicated. The reason for this is that these tables are
> used as staging tables when consolidating large amounts of data. They get
> 200,000 inserts/updates/deletes per day. Normally we only expect to be
> replicating 1,000 inserts/updates/deletes per day.
> Is there a way to remove these 2 articles form the replication process.
> If not, I will simply have to create 2 new tables, and ensure that all
> references to the existing tables are changed to the new non-replicated
> tables. However, I dislike workarounds and orphan items.
> I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
> have been unable to find any reference to this SP in Books online.
>
> Regards
> Des Norton
>

Removal of superflous replication data

Hi,
Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:\MSSQL\REPLDATA\unc\ directories which seem to represent another log of some description.

Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.

Does anyone know what it is used for?
thanks
Pete StoreyThe REPLDATA directory contains snapshot data for the replication. Each time you initialize the subscription, replication agent will write data onto this directory. Yes, you can delete them. But I think your replication setup is incorrect because sql delte those data right after the refresh is complete.|||Hi!

This depends on the type of MSSQL server you use. In MSSQL 7 there's unfortunately no standard functionality which cleans up these replication logings (automatically).
I seem to remember though u can find a Stored Procedure example on the Microsoft Website which does just that...removing older loggings from the Replication loging table.

A crude method would be of course to delete certain publications with the Replication-menu option in Enterprise manager and then go to the subscribing sql server with (for example Query analyzer) go into the database which 'receives' the replicated data and use the sp_deletemergeconflictrow or better yet the sp_mergesubscription_cleanup Stored Procedure to clean up some of the database.

I'm sorry I can't be more specific since it's been quite a while since I had to look into this problem.
Good luck anyway!

Vincent JS|||FYI is SQL Server 2000 SP4.
Cant really be bothered to mess around manually with it all - Im not too impressed overall with replication because it seems to have so many problems with it and things that simply should have been sorted but never were.
Hmm anyone else got any ideas how to delete the data from the replication tables?
Thanks joe for the advice will get rid of that data now
cheers
Pete|||I agree with Joe, check your configuration. Replication might be failing when generating these files - which in that case they would not be deleted.|||Originally posted by rnealejr
I agree with Joe, check your configuration. Replication might be failing when generating these files - which in that case they would not be deleted.

The snapshots appear to be generated correctly and I dont get any errors coming through so not sure what might be wrong with the config.
Any possible pointers?
thanks
Pete

Removal of merge replication.

I've got this SQL2K/WIN2K setup and a database having merge replication
setup. It's been running there for more than couple of years. Now due to
strategic changes we don't need this replication on the database.
When I tried to remove it, it started blocking normal user activity that is
mostly on the same tables which are published. After 15-20 minutes I had to
cancle it. The database is serving 24/7 and I want to remove replication with
minimal disruption to regular opration. Is there any easier way to remove
replication and metadata generated by it.?
I would appreciate any help/pointers.
Have you tried to call sp_removedbreplication? This proc should remove the
replication from the database.
Yi
"adi" <adi@.discussions.microsoft.com> wrote in message
news:7B04463C-FFA2-4BEA-8339-EB3340FEE5A2@.microsoft.com...
> I've got this SQL2K/WIN2K setup and a database having merge replication
> setup. It's been running there for more than couple of years. Now due to
> strategic changes we don't need this replication on the database.
> When I tried to remove it, it started blocking normal user activity that
> is
> mostly on the same tables which are published. After 15-20 minutes I had
> to
> cancle it. The database is serving 24/7 and I want to remove replication
> with
> minimal disruption to regular opration. Is there any easier way to remove
> replication and metadata generated by it.?
> I would appreciate any help/pointers.

RemoteDataAccess

Dear Friends,

To use RDA, do I still have to use Replication, Publishing? if yes which publication need to be used? is it Transaction

If you can guide me as to how I can imliment RDA, would be a great help.

Check the topic: Remote Data Access (RDA)

at http://msdn2.microsoft.com/en-us/library/aa257442(SQL.80).aspx

It also has examples in the Push/Pull etc methods.

|||

Thankx,

But what I need to know is if i need to use replication publishing of the SQL server db?

Is there any way that I can access the SQL server DB directly?

Regards

RemoteDataAccess

Dear Friends,

To use RDA, do I still have to use Replication, Publishing? if yes which publication need to be used? is it Transaction

If you can guide me as to how I can imliment RDA, would be a great help.

Check the topic: Remote Data Access (RDA)

at http://msdn2.microsoft.com/en-us/library/aa257442(SQL.80).aspx

It also has examples in the Push/Pull etc methods.

|||

Thankx,

But what I need to know is if i need to use replication publishing of the SQL server db?

Is there any way that I can access the SQL server DB directly?

Regards

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRThis is a multi-part message in MIME format.
--090909090705070004050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
--090909090705070004050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
ChrisR wrote:
<blockquote cite="mid%230BGYuarFHA.3424@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
</pre>
</blockquote>
</body>
</html>
--090909090705070004050507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that =doesn't imply you can actually login using that standard SQL login.)
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =
login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Mike, but no thats not the case.
"Mike Hodgson" wrote in message news:O$vQwAdrFHA.908@.t=k2msftngp13.phx.gbl...Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that doesn't =imply you can actually login using that standard SQL login.)
--mike =hodgsonblog: http://sqlnerd.blogspot.com ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR

---

--=_NextPart_000_0008_01C5AE09.DF950070--

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRIs it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:

>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged i
n
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I
:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is tha
t
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$v
QwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authenticati
on only? (Even though you can set the 'sa' password, that doesn't imply you
can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:

>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authentication only? (Even though you can set the 'sa' password, that doesn't imply you can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR

Remote Server / Linked Server

I have transactional replication working and it created a remote server
to be used for replication. I am not able to insert/delete/update
between the two servers through the "remote server" connection. I need
to use a linked server, do I need to set up this linked server prior to
configuring any replication so a remote server isn't created during
configuration? Is there something else I can do instead of this.
Thanks
sp_serveroption 'remoteServerName','data access',true
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142538888.208897.59030@.e56g2000cwe.googlegro ups.com...
>I have transactional replication working and it created a remote server
> to be used for replication. I am not able to insert/delete/update
> between the two servers through the "remote server" connection. I need
> to use a linked server, do I need to set up this linked server prior to
> configuring any replication so a remote server isn't created during
> configuration? Is there something else I can do instead of this.
> Thanks
>
|||I changed the data access to true, but now I get the following error
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
The security properties are set to: "Be made using the login's current
security context"
I have sysadmin priviledges so I shouldn't be restricted by that. I
can get it to work and pull data, if I change the security properties
to use the sa login and password, but that isn't secure enough. What
options do I have. Thanks for your time.
|||Enter the sa account and password on the linked server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142872805.003828.93830@.t31g2000cwb.googlegro ups.com...
>I changed the data access to true, but now I get the following error
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> The security properties are set to: "Be made using the login's current
> security context"
> I have sysadmin priviledges so I shouldn't be restricted by that. I
> can get it to work and pull data, if I change the security properties
> to use the sa login and password, but that isn't secure enough. What
> options do I have. Thanks for your time.
>
|||Maybe I didn't state my question correctly. I am wanting to use the
security property of: "Be made using the login's current
security context" so it can limit the users priviledges to the other
server. When I do this I get the following error.
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection
I was able to use the sa login and password, but that allowed everyone
to have unlimited access to the linked server.
How do I get the security property of: "Be made using the login's
current security context" to work without getting the error. Thanks
|||I don't believe you can as you are logging on using Windows Authentication,
and it appears that the remote server is not part of the domain. Perhaps try
pass through authentication. By any chance is the remote server across the
internet?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142879939.773655.304440@.z34g2000cwc.googlegr oups.com...
> Maybe I didn't state my question correctly. I am wanting to use the
> security property of: "Be made using the login's current
> security context" so it can limit the users priviledges to the other
> server. When I do this I get the following error.
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection
>
> I was able to use the sa login and password, but that allowed everyone
> to have unlimited access to the linked server.
>
> How do I get the security property of: "Be made using the login's
> current security context" to work without getting the error. Thanks
>

Remote replication question

I'm not a db person so please excuse any simplicity or lack of clarity in my
question.
My company currently uses a model of remote employees updating MS Access dbs
and then transferring those dbs to the main office where they are merged with
a main Access db. The remote employees are disconnected from the company
domain/network during their workday.
The plan is to migrate the Access dbs into SQL 2000 (or 2005?) and then set
up some sort of replication topology that will allow the remote employees to
replicate their daily updates/entries to the SQL Server at the main office.
My questions are:
1) What are the possibilities for the db on the remote machines? Can MS
Access be used on the client side and the Access data replicated to the SQL
server (and if so, would data transformation be necessary?)
2) Can MSDE be used on the remote clients? If so what are the possibilities
for a front end to the client MSDE dbs?
3) How can the replication be done over the internet? I believe VPN and ftp
are possibilities and I think SSL would be a possibility if SQL 2005 is used,
can anyone point me to documentation on how to best setup replication between
the remote clients and the SQL server in the main office.
Thanks
Ideally, MSDE would be used for the sql server replication. In this case you
can't have MSDE publishers of transactional replication, so merge would be
used, with the head office as a publisher and the MSDE databases as
subscribers. This can be best done over a VPN for security. Replication over
the internet is also possible (in SQL 2000 you'd use certificates and TCP/IP
while in SQL Server 2005 you can use HTTPS). For the former option, please
check out this article:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Remote Replication

Hi,

Now, I try doing remote replication between 2 pc(eg.pc A and B). Pc A as a server for publisher and distributor, and PC B is a normal pc to store data. I want replicate data from PC B to PC A. I used merge replication for this replication. I don't understanding something like:

1. is I need 2 servers for this replication? One server for publisher and distributor and others one server for subscriber?

2. What should I do if one server also can doing replication? What the step?

karencylIntroducing Replication Options
Options available with the types of replication allow you more replication solutions and greater flexibility and control in your applications. Replication options are:

Filtering published data

Publishing database objects

Publishing schema objects

Updatable subscriptions

Transforming published data

Alternate synchronization partners
Filtering Published Data
Filtering data during replication allows you to publish only the data or partitions of data that are needed at the Subscriber. You can filter data to create partitions that include only the columns and/or only the rows that you specify for replication.

With all types of replication, you can choose to copy and distribute complete tables, or data filtered horizontally or vertically with static filters. Merge replication is especially strong in filtering options, and you can use dynamic filters to customize the filter based on a property of the Subscriber receiving the data.

Filtering data horizontally allows you to publish only the data that is needed, partition data to different sites, avoid conflicts (because Subscribers will be viewing and updating different subsets of data), and manage publications based on user needs or applications.

Additionally, you have the option of employing user-defined functions in your static and dynamic filters and leveraging the power of customized functions.

Merge replication provides the added functionality of join filters and dynamic filters. Join filters enable you to extend filters created on one table to another. For example, if you are publishing customer data based on the state where the customer resides, you may want to extend that filter to the related orders and order details of the customers in a particular state. Dynamic filters allow you to create a merge publication and then filter data from the publishing table.. The filter value can be the user ID or login retrieved based on a Transact-SQL function, such as SUSER_SNAME() or HOSTNAME().

Publishing Database Objects
You can publish database objects including views, indexed views, user-defined functions, stored procedure definitions, and the execution of stored procedures. You can include data and database objects in the same publication or in different publications. Publishing database objects is available with all types of replication (snapshot replication, transactional replication, and merge replication).

Publishing Schema Objects
In addition to database objects, you can also specify if you want schema objects to be published such as declared referential integrity (primary key constraints, reference constraints, unique constraints), clustered indexes, nonclustered indexes, user triggers, extended properties, and collation. You can also change destination table owner names and data formats to optimize for SQL Server 2000 or heterogeneous Subscribers.

Updatable Subscriptions
Data at the Subscriber can be modified if you use merge replication or if you use snapshot replication or transactional replication with an updatable subscription option.

Updatable subscription options available with snapshot replication and transactional replication allow you to make changes to replicated data at the Subscriber and propagate those changes to the Publisher and to other Subscribers. Updatable subscription options include immediate updating, queued updating, and immediate updating with queued updating as a failover.

Immediate updating allows Subscribers to update data only if the Publisher will accept them immediately. If the changes are accepted at the Publisher, they are propagated to other Subscribers. The Subscriber must be continuously and reliably connected to the Publisher to make changes at the Subscriber.

Queued updating allows Subscribers to modify data and store those data modifications in a queue while disconnected from the Publisher for a period of time. When the Subscriber reconnects to the Publisher, the changes are propagated to the Publisher. If the Publisher accepts the changes, normal replication processes occur and the changes are propagated to other Subscribers from the Publisher. You can store data modifications in a SQL Server 2000 queue or use Microsoft Message Queuing.

Immediate updating with the queued updating option allows you to use immediate updating and switch to queued updating if a connection cannot be maintained between the Publisher and Subscribers. After switching to queued updating, reconnecting to the Publisher, and emptying the queue, you can switch back to immediate updating mode.

When using merge replication, data at the Subscriber is automatically updatable.

Transforming Published Data
With snapshot replication or transactional replication, you can leverage the transformation mapping and scripting capabilities of Data Transformation Services (DTS) when building a replication topology. Replication integrated with DTS allows you to customize and distribute data based on the requirements of individual Subscribers. For example, a Subscriber might need to have different table names, column names, or compatible data types.

By transforming published data, you can filter data and simulate dynamic partitions of data so that data from one snapshot or transactional publication can be distributed to Subscribers that require different partitions of data. With static partitions, you need to create and filter separate publications for each Subscriber based on the needs of the Subscriber.

Alternate Synchronization Partners
Subscribers to merge publications can synchronize with servers other than the Publisher at which the subscription originated. Synchronizing with alternate partners allows Subscribers to synchronize data even if the primary Publisher is unavailable. This feature is also useful when mobile Subscribers have access to a faster or more reliable network connection with an alternate Publisher.

See Also

Alternate Synchronization Partners

Filtering Published Data

Merge Replication or Updatable Subscriptions

Publishing Data and Database Objects

1988-2000 Microsoft Corporation. All Rights Reserved.

Remote Distributor question

sql2k sp3
BoxA Publisher.
BoxB Subscriber. To be used for reporting.
I want the processing drain of Replication to be done on
BoxB. So I was thinking of putting the Distributor on BoxB
and using a Push Subscription.
Do any of you gurus see any problems with my idea?
TIA, Chris?
Reporting services chews up a lot of cycles. You might find it better to
keep the distributor on the publisher.
The point you start to move the distributor off the publisher is when you
start to get significant locking on your distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
> sql2k sp3
> BoxA Publisher.
> BoxB Subscriber. To be used for reporting.
> I want the processing drain of Replication to be done on
> BoxB. So I was thinking of putting the Distributor on BoxB
> and using a Push Subscription.
> Do any of you gurus see any problems with my idea?
> TIA, Chris?
|||>Reporting services chews up a lot of cycles.
My bad. I wont be using actual Reporting Services.
You might find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>
Why is this? Why do people do it then?

>--Original Message--
>Reporting services chews up a lot of cycles. You might
find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
BoxB
>
>.
>
|||Well, most people do it to reduce the load on the Publisher. The question is
when should you do it, and there is no real rule of thumb, at least as far
as I am aware.
The deciding factor for me is when you experience locking in your
distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4201c46e8a$e7dd03a0$a601280a@.phx.gbl...[vbcol=seagreen]
> My bad. I wont be using actual Reporting Services.
> You might find it better to
> publisher is when you
> database.
> Why is this? Why do people do it then?
> find it better to
> publisher is when you
> database.
> message
> BoxB

Remote Distributor and Subscriber on same machine - is that OK?

Hi,

Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).

Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.

Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?

Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,

Smitha

This is actually a rather popular configuration (at least the ones that I have seen) inside Microsoft as this allows you to upgrade the Distributor and Subscriber to a new release together and then use the Subscriber as a test bed for the new release while receiving continuous updates from the production system. You do need good network connectivity between the publisher and the distributor\subscriber as pulling snapshot data (or backup\restore for that matter) can easily saturate a 100Mbit Ethernet connection. So to minimize impact on the production system, you may want to get a dedicated connection between the two.

-Raymond

|||

Thanks Raymond. I shouldn't have to worry about the snapshot/backup+restore because I've used this second server as the backup files' location in the past (and still perform copy-only FULL production DB backups to this server). The restore takes a while since it reads from and writes to the same drive but this is OK for an operation that may happen 2-3 times a year.

Thanks again,

Smitha