Hi,
I am using transaction replication and the identity column is not included
for replication.
After creating the snapshot, i manually copying the data through stored
procedure from publishing database to subscription database. Then,if we run
the distribution agent,i am getting the following error. what will be the
process involved in applying the initial snapshot since i have already
copied the data. Will SQL server do bcp again? How can i avoid it?
Error Message: The process could not bulk copy into table '"hsassbtr"'.
Error Details:
Numeric value out of range
(Source: INFOSYS6 (ODBC); Error number: 22003)
Numeric value out of range
(Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
key in object 'hsassbtr'.
(Source: INFOSYS6 (Data source); Error number: 2627)
Aritcle is added as follows:
--added all the columns
exec sp_articlecolumn @.publication = 'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = NULL,
@.operation = N'add',
@.force_invalidate_snapshot = 1
--dropped identity('DEX_ROW_ID') columns
exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = N'DEX_ROW_ID',
@.operation = N'drop',
@.force_invalidate_snapshot = 1
I have created the subscription as follows:
exec sp_addpullsubscription
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.independent_agent = N'true',
@.subscription_type = N'anonymous',
@.description = N'InfoSys POC Setup Table Publication',
@.update_mode = N'read only',
@.immediate_sync = 1
exec sp_addpullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.distributor = @.distributor,
@.subscriber_security_mode = @.security_mode,
@.distributor_security_mode = @.security_mode,
@.frequency_type = 8, --Weekly
@.frequency_interval = 1, --Sunday
@.frequency_recurrence_factor = 1, --Every Week
@.frequency_subday = 1, --Once a Day
@.active_start_time_of_day = 220000, --10 PM
@.enabled_for_syncmgr = N'false',
@.use_ftp = N'false',
@.publication_type = 0,
@.offloadagent = N'false'
Any help?
Thanks,
Vijay
Can you check and make sure the subscribing table is empty?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Vijay" <vijay@.infosysusa.com> wrote in message
news:OEiWoNSkFHA.2852@.TK2MSFTNGP15.phx.gbl...
Hi,
I am using transaction replication and the identity column is not included
for replication.
After creating the snapshot, i manually copying the data through stored
procedure from publishing database to subscription database. Then,if we run
the distribution agent,i am getting the following error. what will be the
process involved in applying the initial snapshot since i have already
copied the data. Will SQL server do bcp again? How can i avoid it?
Error Message: The process could not bulk copy into table '"hsassbtr"'.
Error Details:
Numeric value out of range
(Source: INFOSYS6 (ODBC); Error number: 22003)
Numeric value out of range
(Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
key in object 'hsassbtr'.
(Source: INFOSYS6 (Data source); Error number: 2627)
Aritcle is added as follows:
--added all the columns
exec sp_articlecolumn @.publication = 'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = NULL,
@.operation = N'add',
@.force_invalidate_snapshot = 1
--dropped identity('DEX_ROW_ID') columns
exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = N'DEX_ROW_ID',
@.operation = N'drop',
@.force_invalidate_snapshot = 1
I have created the subscription as follows:
exec sp_addpullsubscription
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.independent_agent = N'true',
@.subscription_type = N'anonymous',
@.description = N'InfoSys POC Setup Table Publication',
@.update_mode = N'read only',
@.immediate_sync = 1
exec sp_addpullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.distributor = @.distributor,
@.subscriber_security_mode = @.security_mode,
@.distributor_security_mode = @.security_mode,
@.frequency_type = 8, --Weekly
@.frequency_interval = 1, --Sunday
@.frequency_recurrence_factor = 1, --Every Week
@.frequency_subday = 1, --Once a Day
@.active_start_time_of_day = 220000, --10 PM
@.enabled_for_syncmgr = N'false',
@.use_ftp = N'false',
@.publication_type = 0,
@.offloadagent = N'false'
Any help?
Thanks,
Vijay
|||Vyas,
Subscribing table has data because i have manually copied the data through
stored procedure from Publising database.
Thanks,
Vijay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OFnpAcSkFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Can you check and make sure the subscribing table is empty?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Vijay" <vijay@.infosysusa.com> wrote in message
> news:OEiWoNSkFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am using transaction replication and the identity column is not
included
> for replication.
> After creating the snapshot, i manually copying the data through stored
> procedure from publishing database to subscription database. Then,if we
run
> the distribution agent,i am getting the following error. what will be the
> process involved in applying the initial snapshot since i have already
> copied the data. Will SQL server do bcp again? How can i avoid it?
>
> Error Message: The process could not bulk copy into table '"hsassbtr"'.
> Error Details:
> Numeric value out of range
> (Source: INFOSYS6 (ODBC); Error number: 22003)
> ----
--
> --
> Numeric value out of range
> (Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
> ----
--
> --
> Unexpected EOF encountered in BCP data-file
> (Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
> ----
--
> --
> Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
> key in object 'hsassbtr'.
> (Source: INFOSYS6 (Data source); Error number: 2627)
> ----
--
> --
> Aritcle is added as follows:
> --added all the columns
> exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
> @.article = @.table_name,
> @.column = NULL,
> @.operation = N'add',
> @.force_invalidate_snapshot = 1
> --dropped identity('DEX_ROW_ID') columns
> exec sp_articlecolumn @.publication =
> 'InfoSys_Point_of_Care_Master_Tables',
> @.article = @.table_name,
> @.column = N'DEX_ROW_ID',
> @.operation = N'drop',
> @.force_invalidate_snapshot = 1
>
> I have created the subscription as follows:
> exec sp_addpullsubscription
> @.publisher = @.publisher,
> @.publisher_db = @.publisher_db,
> @.publication = N'InfoSys_Point_of_Care_Master_Tables',
> @.independent_agent = N'true',
> @.subscription_type = N'anonymous',
> @.description = N'InfoSys POC Setup Table Publication',
> @.update_mode = N'read only',
> @.immediate_sync = 1
> exec sp_addpullsubscription_agent
> @.publisher = @.publisher,
> @.publisher_db = @.publisher_db,
> @.publication = N'InfoSys_Point_of_Care_Master_Tables',
> @.distributor = @.distributor,
> @.subscriber_security_mode = @.security_mode,
> @.distributor_security_mode = @.security_mode,
> @.frequency_type = 8, --Weekly
> @.frequency_interval = 1, --Sunday
> @.frequency_recurrence_factor = 1, --Every Week
> @.frequency_subday = 1, --Once a Day
> @.active_start_time_of_day = 220000, --10 PM
> @.enabled_for_syncmgr = N'false',
> @.use_ftp = N'false',
> @.publication_type = 0,
> @.offloadagent = N'false'
> Any help?
>
> Thanks,
> Vijay
>
>
|||Why did you copy data manually, when you are configuring replication with
automatic sync?
You should either subscrine with nosync option or delete the data from
subscriber before the snapshot is applied (if the snapshot is not configure
to truncate/delete table).
If there are any special requirements for this subscriber, pelase post the
complete info, and someone will be able to assist.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Vijay" <vijay@.infosysusa.com> wrote in message
news:uHJ%23ZgSkFHA.708@.TK2MSFTNGP09.phx.gbl...
> Vyas,
> Subscribing table has data because i have manually copied the data through
> stored procedure from Publising database.
> Thanks,
> Vijay
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:OFnpAcSkFHA.3256@.TK2MSFTNGP12.phx.gbl...
> included
> run
> --
> --
> --
> --
> 'InfoSys_Point_of_Care_Master_Tables',
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
possible to get real time transaction log replication?
hi,
i know there are a variety of 3rd party products that can accomplish this
but am curious if anyone knows of a way to accomplish this using sql? thanks.
You can tweak transactional replication to get minimum latency (very
roughly, a few secs), use log shipping (min 1 min) use distributed
transactions (immediate), or have a look at database mirroring (<1 second
ASAIK) in SQL 2005.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Under load you will be looking at 20s to 1 minute minimum for transactional.
With no load you can be looking at 2-4 secs.
Log shipping latency is greater than 1 min. typically pushing 2, but a more
practical limit is 5 minutes.
Database mirroring which ships in SQL 2005 has two modes high performance
(asynchronous) and high availability (synchronous). In high availability
mode logged operations are written on both sides (split write) which means
increased latency. A transaction is written to the source, and then written
to the destination, and then the app gets the commit. Latency can be
considerably increased depending on your throughput and other factors.
In high performance if your source goes down you can lose transactions.
Performance is better, latency can be larges, but the transactional latency
of transactions hitting your database is less.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O3dmxeVYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> You can tweak transactional replication to get minimum latency (very
> roughly, a few secs), use log shipping (min 1 min) use distributed
> transactions (immediate), or have a look at database mirroring (<1 second
> ASAIK) in SQL 2005.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||thanks for the info. im looking for some sort of a synchronous solution but
im rather new to this. operating in an OLTP environment so have very little
maneuvering room in regard to latency.
what sort of 'real-world' bandwidth / latency do i need to make DTC work?
|||Can't answer this one, but using immediate updating will increase the time
required to commit a transaction on the subscriber. I have seen transactions
take from 43 to 140 ms when using immediate updating.
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
"mb" <mb@.discussions.microsoft.com> wrote in message
news:693B008E-8BE2-4D2C-BC61-737CE180ED88@.microsoft.com...
> thanks for the info. im looking for some sort of a synchronous solution
but
> im rather new to this. operating in an OLTP environment so have very
little
> maneuvering room in regard to latency.
> what sort of 'real-world' bandwidth / latency do i need to make DTC work?
i know there are a variety of 3rd party products that can accomplish this
but am curious if anyone knows of a way to accomplish this using sql? thanks.
You can tweak transactional replication to get minimum latency (very
roughly, a few secs), use log shipping (min 1 min) use distributed
transactions (immediate), or have a look at database mirroring (<1 second
ASAIK) in SQL 2005.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Under load you will be looking at 20s to 1 minute minimum for transactional.
With no load you can be looking at 2-4 secs.
Log shipping latency is greater than 1 min. typically pushing 2, but a more
practical limit is 5 minutes.
Database mirroring which ships in SQL 2005 has two modes high performance
(asynchronous) and high availability (synchronous). In high availability
mode logged operations are written on both sides (split write) which means
increased latency. A transaction is written to the source, and then written
to the destination, and then the app gets the commit. Latency can be
considerably increased depending on your throughput and other factors.
In high performance if your source goes down you can lose transactions.
Performance is better, latency can be larges, but the transactional latency
of transactions hitting your database is less.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O3dmxeVYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> You can tweak transactional replication to get minimum latency (very
> roughly, a few secs), use log shipping (min 1 min) use distributed
> transactions (immediate), or have a look at database mirroring (<1 second
> ASAIK) in SQL 2005.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||thanks for the info. im looking for some sort of a synchronous solution but
im rather new to this. operating in an OLTP environment so have very little
maneuvering room in regard to latency.
what sort of 'real-world' bandwidth / latency do i need to make DTC work?
|||Can't answer this one, but using immediate updating will increase the time
required to commit a transaction on the subscriber. I have seen transactions
take from 43 to 140 ms when using immediate updating.
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
"mb" <mb@.discussions.microsoft.com> wrote in message
news:693B008E-8BE2-4D2C-BC61-737CE180ED88@.microsoft.com...
> thanks for the info. im looking for some sort of a synchronous solution
but
> im rather new to this. operating in an OLTP environment so have very
little
> maneuvering room in regard to latency.
> what sort of 'real-world' bandwidth / latency do i need to make DTC work?
Saturday, February 25, 2012
Possible Bug in sp_repladdcolumn? (SQL 2000, SP4)
Had something interesting happen yesterday.
Was given an alter table script to add two columns to a table we have. As
we use replication and I needed these two columns to replicate, I couldn't
simply use the alter table script but had to translate it into using
sp_repladdcolumn.
No, in the original script, it was: (and forgive typos, going on memory
here)
alter table table_foo
add column [target_object_cache_time] int not null default(0);
Now, if I create the column that way, it adds the column
target_object_cache_time.
However, when I used sp_repladdcolumn 'table_foo',
'[target_object_cache_time]', 'int not null deafult(0)';
it creates the columns with the [] around the name. i.e.
[target_object_cache_time] Moreover, it appeared to create it as
[target_object_cache_time]] on the subscribers.
Needless to say, when I tried to update the stored proc, which uses the new
column, it failed since it was looking for target_object_cache_time not
[target_object_cache_time].
Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate the
columns.
Here's the error message I recieved:
Category:COMMAND
Source: Failed Command
Number:
Message: if exists (select * from syscolumns where
name='[target_object_cache_time]'
and id = object_id('table_foo'))
begin if exists (select * from sysobjects where name='syspublications')
if exists (select * from sysarticles where objid=object_id('table_foo'))
and @.@.microsoftversion >= 0x07320000
exec sp_repldropcolumn
@.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
else alter table [table_foo] drop column [[target_object_cache_t
ime]]] else
alter table [table_foo] drop column [[t
(it got cut off).
As you can see, sometimes it's treating it with an extra set of [] and
sometimes not.
It seems to me that sp_repladdcolumn should treat [] the same way as alt
er
table does. But even if it doesn't, it seems to me that you should be able
to drop the columns cleanly at least.
Thoughts? (I didn't really test too much reproduction since I was trying to
get the root problem fixed on our prod boxes at the time. :-)
If I get a chance, I may try to reproduce this again later if no one else
can.
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.comGo to the Product Feedback Center and file it as a bug. It is using
brackets to ensure that there aren't any issues with spaces in the names.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> Had something interesting happen yesterday.
> Was given an alter table script to add two columns to a table we have. As
> we use replication and I needed these two columns to replicate, I couldn't
> simply use the alter table script but had to translate it into using
> sp_repladdcolumn.
> No, in the original script, it was: (and forgive typos, going on memory
> here)
> alter table table_foo
> add column [target_object_cache_time] int not null default(0);
> Now, if I create the column that way, it adds the column
> target_object_cache_time.
> However, when I used sp_repladdcolumn 'table_foo',
> '[target_object_cache_time]', 'int not null deafult(0)';
> it creates the columns with the [] around the name. i.e.
> [target_object_cache_time] Moreover, it appeared to create it as
> [target_object_cache_time]] on the subscribers.
> Needless to say, when I tried to update the stored proc, which uses the
> new
> column, it failed since it was looking for target_object_cache_time not
> [target_object_cache_time].
> Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> the
> columns.
> Here's the error message I recieved:
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: if exists (select * from syscolumns where
> name='[target_object_cache_time]'
> and id = object_id('table_foo'))
> begin if exists (select * from sysobjects where name='syspublications')
> if exists (select * from sysarticles where objid=object_id('table_foo'))
> and @.@.microsoftversion >= 0x07320000
> exec sp_repldropcolumn
> @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]
'
> else alter table [table_foo] drop column [[target_object_cache
_time]]]
> else
> alter table [table_foo] drop column [[t
> (it got cut off).
> As you can see, sometimes it's treating it with an extra set of [] and
> sometimes not.
> It seems to me that sp_repladdcolumn should treat [] the same way as a
lter
> table does. But even if it doesn't, it seems to me that you should be
> able
> to drop the columns cleanly at least.
> Thoughts? (I didn't really test too much reproduction since I was trying
> to
> get the root problem fixed on our prod boxes at the time. :-)
> If I get a chance, I may try to reproduce this again later if no one else
> can.
>
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
> Go to the Product Feedback Center and file it as a bug.
What's the specific URL?
> It is using
> brackets to ensure that there aren't any issues with spaces in the names.
Right, but besides using them wrongly, it's inconsistent in its use of them.
:-)
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
As[vbcol=seagreen]
couldn't[vbcol=seagreen]
alter[vbcol=seagreen]
trying[vbcol=seagreen]
else[vbcol=seagreen]
>|||Greg D. Moore (Strider) wrote:
>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/produ...ck/default.aspx
*mike hodgson*
http://sqlnerd.blogspot.com
Was given an alter table script to add two columns to a table we have. As
we use replication and I needed these two columns to replicate, I couldn't
simply use the alter table script but had to translate it into using
sp_repladdcolumn.
No, in the original script, it was: (and forgive typos, going on memory
here)
alter table table_foo
add column [target_object_cache_time] int not null default(0);
Now, if I create the column that way, it adds the column
target_object_cache_time.
However, when I used sp_repladdcolumn 'table_foo',
'[target_object_cache_time]', 'int not null deafult(0)';
it creates the columns with the [] around the name. i.e.
[target_object_cache_time] Moreover, it appeared to create it as
[target_object_cache_time]] on the subscribers.
Needless to say, when I tried to update the stored proc, which uses the new
column, it failed since it was looking for target_object_cache_time not
[target_object_cache_time].
Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate the
columns.
Here's the error message I recieved:
Category:COMMAND
Source: Failed Command
Number:
Message: if exists (select * from syscolumns where
name='[target_object_cache_time]'
and id = object_id('table_foo'))
begin if exists (select * from sysobjects where name='syspublications')
if exists (select * from sysarticles where objid=object_id('table_foo'))
and @.@.microsoftversion >= 0x07320000
exec sp_repldropcolumn
@.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
else alter table [table_foo] drop column [[target_object_cache_t
ime]]] else
alter table [table_foo] drop column [[t
(it got cut off).
As you can see, sometimes it's treating it with an extra set of [] and
sometimes not.
It seems to me that sp_repladdcolumn should treat [] the same way as alt
er
table does. But even if it doesn't, it seems to me that you should be able
to drop the columns cleanly at least.
Thoughts? (I didn't really test too much reproduction since I was trying to
get the root problem fixed on our prod boxes at the time. :-)
If I get a chance, I may try to reproduce this again later if no one else
can.
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.comGo to the Product Feedback Center and file it as a bug. It is using
brackets to ensure that there aren't any issues with spaces in the names.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> Had something interesting happen yesterday.
> Was given an alter table script to add two columns to a table we have. As
> we use replication and I needed these two columns to replicate, I couldn't
> simply use the alter table script but had to translate it into using
> sp_repladdcolumn.
> No, in the original script, it was: (and forgive typos, going on memory
> here)
> alter table table_foo
> add column [target_object_cache_time] int not null default(0);
> Now, if I create the column that way, it adds the column
> target_object_cache_time.
> However, when I used sp_repladdcolumn 'table_foo',
> '[target_object_cache_time]', 'int not null deafult(0)';
> it creates the columns with the [] around the name. i.e.
> [target_object_cache_time] Moreover, it appeared to create it as
> [target_object_cache_time]] on the subscribers.
> Needless to say, when I tried to update the stored proc, which uses the
> new
> column, it failed since it was looking for target_object_cache_time not
> [target_object_cache_time].
> Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> the
> columns.
> Here's the error message I recieved:
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: if exists (select * from syscolumns where
> name='[target_object_cache_time]'
> and id = object_id('table_foo'))
> begin if exists (select * from sysobjects where name='syspublications')
> if exists (select * from sysarticles where objid=object_id('table_foo'))
> and @.@.microsoftversion >= 0x07320000
> exec sp_repldropcolumn
> @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]
'
> else alter table [table_foo] drop column [[target_object_cache
_time]]]
> else
> alter table [table_foo] drop column [[t
> (it got cut off).
> As you can see, sometimes it's treating it with an extra set of [] and
> sometimes not.
> It seems to me that sp_repladdcolumn should treat [] the same way as a
lter
> table does. But even if it doesn't, it seems to me that you should be
> able
> to drop the columns cleanly at least.
> Thoughts? (I didn't really test too much reproduction since I was trying
> to
> get the root problem fixed on our prod boxes at the time. :-)
> If I get a chance, I may try to reproduce this again later if no one else
> can.
>
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
> Go to the Product Feedback Center and file it as a bug.
What's the specific URL?
> It is using
> brackets to ensure that there aren't any issues with spaces in the names.
Right, but besides using them wrongly, it's inconsistent in its use of them.
:-)
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
As[vbcol=seagreen]
couldn't[vbcol=seagreen]
alter[vbcol=seagreen]
trying[vbcol=seagreen]
else[vbcol=seagreen]
>|||Greg D. Moore (Strider) wrote:
>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/produ...ck/default.aspx
*mike hodgson*
http://sqlnerd.blogspot.com
Possible Bug in sp_repladdcolumn? (SQL 2000, SP4)
Had something interesting happen yesterday.
Was given an alter table script to add two columns to a table we have. As
we use replication and I needed these two columns to replicate, I couldn't
simply use the alter table script but had to translate it into using
sp_repladdcolumn.
No, in the original script, it was: (and forgive typos, going on memory
here)
alter table table_foo
add column [target_object_cache_time] int not null default(0);
Now, if I create the column that way, it adds the column
target_object_cache_time.
However, when I used sp_repladdcolumn 'table_foo',
'[target_object_cache_time]', 'int not null deafult(0)';
it creates the columns with the [] around the name. i.e.
[target_object_cache_time] Moreover, it appeared to create it as
[target_object_cache_time]] on the subscribers.
Needless to say, when I tried to update the stored proc, which uses the new
column, it failed since it was looking for target_object_cache_time not
[target_object_cache_time].
Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate the
columns.
Here's the error message I recieved:
Category:COMMAND
Source: Failed Command
Number:
Message: if exists (select * from syscolumns where
name='[target_object_cache_time]'
and id = object_id('table_foo'))
begin if exists (select * from sysobjects where name='syspublications')
if exists (select * from sysarticles where objid=object_id('table_foo'))
and @.@.microsoftversion >= 0x07320000
exec sp_repldropcolumn
@.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
else alter table [table_foo] drop column [[target_object_cache_time]]] else
alter table [table_foo] drop column [[t
(it got cut off).
As you can see, sometimes it's treating it with an extra set of [] and
sometimes not.
It seems to me that sp_repladdcolumn should treat [] the same way as alter
table does. But even if it doesn't, it seems to me that you should be able
to drop the columns cleanly at least.
Thoughts? (I didn't really test too much reproduction since I was trying to
get the root problem fixed on our prod boxes at the time. :-)
If I get a chance, I may try to reproduce this again later if no one else
can.
--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.comGo to the Product Feedback Center and file it as a bug. It is using
brackets to ensure that there aren't any issues with spaces in the names.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> Had something interesting happen yesterday.
> Was given an alter table script to add two columns to a table we have. As
> we use replication and I needed these two columns to replicate, I couldn't
> simply use the alter table script but had to translate it into using
> sp_repladdcolumn.
> No, in the original script, it was: (and forgive typos, going on memory
> here)
> alter table table_foo
> add column [target_object_cache_time] int not null default(0);
> Now, if I create the column that way, it adds the column
> target_object_cache_time.
> However, when I used sp_repladdcolumn 'table_foo',
> '[target_object_cache_time]', 'int not null deafult(0)';
> it creates the columns with the [] around the name. i.e.
> [target_object_cache_time] Moreover, it appeared to create it as
> [target_object_cache_time]] on the subscribers.
> Needless to say, when I tried to update the stored proc, which uses the
> new
> column, it failed since it was looking for target_object_cache_time not
> [target_object_cache_time].
> Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> the
> columns.
> Here's the error message I recieved:
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: if exists (select * from syscolumns where
> name='[target_object_cache_time]'
> and id = object_id('table_foo'))
> begin if exists (select * from sysobjects where name='syspublications')
> if exists (select * from sysarticles where objid=object_id('table_foo'))
> and @.@.microsoftversion >= 0x07320000
> exec sp_repldropcolumn
> @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> else alter table [table_foo] drop column [[target_object_cache_time]]]
> else
> alter table [table_foo] drop column [[t
> (it got cut off).
> As you can see, sometimes it's treating it with an extra set of [] and
> sometimes not.
> It seems to me that sp_repladdcolumn should treat [] the same way as alter
> table does. But even if it doesn't, it seems to me that you should be
> able
> to drop the columns cleanly at least.
> Thoughts? (I didn't really test too much reproduction since I was trying
> to
> get the root problem fixed on our prod boxes at the time. :-)
> If I get a chance, I may try to reproduce this again later if no one else
> can.
>
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
> Go to the Product Feedback Center and file it as a bug.
What's the specific URL?
> It is using
> brackets to ensure that there aren't any issues with spaces in the names.
Right, but besides using them wrongly, it's inconsistent in its use of them.
:-)
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> >
> > Had something interesting happen yesterday.
> >
> > Was given an alter table script to add two columns to a table we have.
As
> > we use replication and I needed these two columns to replicate, I
couldn't
> > simply use the alter table script but had to translate it into using
> > sp_repladdcolumn.
> >
> > No, in the original script, it was: (and forgive typos, going on memory
> > here)
> >
> > alter table table_foo
> > add column [target_object_cache_time] int not null default(0);
> >
> > Now, if I create the column that way, it adds the column
> > target_object_cache_time.
> >
> > However, when I used sp_repladdcolumn 'table_foo',
> > '[target_object_cache_time]', 'int not null deafult(0)';
> >
> > it creates the columns with the [] around the name. i.e.
> > [target_object_cache_time] Moreover, it appeared to create it as
> > [target_object_cache_time]] on the subscribers.
> >
> > Needless to say, when I tried to update the stored proc, which uses the
> > new
> > column, it failed since it was looking for target_object_cache_time not
> > [target_object_cache_time].
> >
> > Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> > the
> > columns.
> >
> > Here's the error message I recieved:
> >
> > Category:COMMAND
> > Source: Failed Command
> > Number:
> > Message: if exists (select * from syscolumns where
> > name='[target_object_cache_time]'
> > and id = object_id('table_foo'))
> > begin if exists (select * from sysobjects where name='syspublications')
> > if exists (select * from sysarticles where objid=object_id('table_foo'))
> > and @.@.microsoftversion >= 0x07320000
> >
> > exec sp_repldropcolumn
> > @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> > else alter table [table_foo] drop column [[target_object_cache_time]]]
> > else
> > alter table [table_foo] drop column [[t
> >
> > (it got cut off).
> >
> > As you can see, sometimes it's treating it with an extra set of [] and
> > sometimes not.
> >
> > It seems to me that sp_repladdcolumn should treat [] the same way as
alter
> > table does. But even if it doesn't, it seems to me that you should be
> > able
> > to drop the columns cleanly at least.
> >
> > Thoughts? (I didn't really test too much reproduction since I was
trying
> > to
> > get the root problem fixed on our prod boxes at the time. :-)
> >
> > If I get a chance, I may try to reproduce this again later if no one
else
> > can.
> >
> >
> >
> >
> >
> >
> > --
> > --
> > Greg D. Moore
> > President Green Mountain Software
> > Personal: http://stratton.greenms.com
> > SQL Server Consulting sql at greenms.com
> >
> >
>|||This is a multi-part message in MIME format.
--040005020201060803030401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Greg D. Moore (Strider) wrote:
>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>>Go to the Product Feedback Center and file it as a bug.
>>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/productfeedback/default.aspx
--
*mike hodgson*
http://sqlnerd.blogspot.com
--040005020201060803030401
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">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Greg D. Moore (Strider) wrote:
<blockquote cite="mideCI2gnPZGHA.4752@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">"Michael Hotek" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike@.solidqualitylearning.com"><mike@.solidqualitylearning.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl">news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Go to the Product Feedback Center and file it as a bug.
</pre>
</blockquote>
<pre wrap=""><!-->
What's the specific URL?
</pre>
</blockquote>
<tt><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>">http://lab.msdn.microsoft.com/productfeedback/default.aspx">http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>
<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"><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>
</body>
</html>
--040005020201060803030401--
Was given an alter table script to add two columns to a table we have. As
we use replication and I needed these two columns to replicate, I couldn't
simply use the alter table script but had to translate it into using
sp_repladdcolumn.
No, in the original script, it was: (and forgive typos, going on memory
here)
alter table table_foo
add column [target_object_cache_time] int not null default(0);
Now, if I create the column that way, it adds the column
target_object_cache_time.
However, when I used sp_repladdcolumn 'table_foo',
'[target_object_cache_time]', 'int not null deafult(0)';
it creates the columns with the [] around the name. i.e.
[target_object_cache_time] Moreover, it appeared to create it as
[target_object_cache_time]] on the subscribers.
Needless to say, when I tried to update the stored proc, which uses the new
column, it failed since it was looking for target_object_cache_time not
[target_object_cache_time].
Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate the
columns.
Here's the error message I recieved:
Category:COMMAND
Source: Failed Command
Number:
Message: if exists (select * from syscolumns where
name='[target_object_cache_time]'
and id = object_id('table_foo'))
begin if exists (select * from sysobjects where name='syspublications')
if exists (select * from sysarticles where objid=object_id('table_foo'))
and @.@.microsoftversion >= 0x07320000
exec sp_repldropcolumn
@.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
else alter table [table_foo] drop column [[target_object_cache_time]]] else
alter table [table_foo] drop column [[t
(it got cut off).
As you can see, sometimes it's treating it with an extra set of [] and
sometimes not.
It seems to me that sp_repladdcolumn should treat [] the same way as alter
table does. But even if it doesn't, it seems to me that you should be able
to drop the columns cleanly at least.
Thoughts? (I didn't really test too much reproduction since I was trying to
get the root problem fixed on our prod boxes at the time. :-)
If I get a chance, I may try to reproduce this again later if no one else
can.
--
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.comGo to the Product Feedback Center and file it as a bug. It is using
brackets to ensure that there aren't any issues with spaces in the names.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> Had something interesting happen yesterday.
> Was given an alter table script to add two columns to a table we have. As
> we use replication and I needed these two columns to replicate, I couldn't
> simply use the alter table script but had to translate it into using
> sp_repladdcolumn.
> No, in the original script, it was: (and forgive typos, going on memory
> here)
> alter table table_foo
> add column [target_object_cache_time] int not null default(0);
> Now, if I create the column that way, it adds the column
> target_object_cache_time.
> However, when I used sp_repladdcolumn 'table_foo',
> '[target_object_cache_time]', 'int not null deafult(0)';
> it creates the columns with the [] around the name. i.e.
> [target_object_cache_time] Moreover, it appeared to create it as
> [target_object_cache_time]] on the subscribers.
> Needless to say, when I tried to update the stored proc, which uses the
> new
> column, it failed since it was looking for target_object_cache_time not
> [target_object_cache_time].
> Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> the
> columns.
> Here's the error message I recieved:
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: if exists (select * from syscolumns where
> name='[target_object_cache_time]'
> and id = object_id('table_foo'))
> begin if exists (select * from sysobjects where name='syspublications')
> if exists (select * from sysarticles where objid=object_id('table_foo'))
> and @.@.microsoftversion >= 0x07320000
> exec sp_repldropcolumn
> @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> else alter table [table_foo] drop column [[target_object_cache_time]]]
> else
> alter table [table_foo] drop column [[t
> (it got cut off).
> As you can see, sometimes it's treating it with an extra set of [] and
> sometimes not.
> It seems to me that sp_repladdcolumn should treat [] the same way as alter
> table does. But even if it doesn't, it seems to me that you should be
> able
> to drop the columns cleanly at least.
> Thoughts? (I didn't really test too much reproduction since I was trying
> to
> get the root problem fixed on our prod boxes at the time. :-)
> If I get a chance, I may try to reproduce this again later if no one else
> can.
>
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
> Go to the Product Feedback Center and file it as a bug.
What's the specific URL?
> It is using
> brackets to ensure that there aren't any issues with spaces in the names.
Right, but besides using them wrongly, it's inconsistent in its use of them.
:-)
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:eHQ78PxYGHA.5012@.TK2MSFTNGP04.phx.gbl...
> >
> > Had something interesting happen yesterday.
> >
> > Was given an alter table script to add two columns to a table we have.
As
> > we use replication and I needed these two columns to replicate, I
couldn't
> > simply use the alter table script but had to translate it into using
> > sp_repladdcolumn.
> >
> > No, in the original script, it was: (and forgive typos, going on memory
> > here)
> >
> > alter table table_foo
> > add column [target_object_cache_time] int not null default(0);
> >
> > Now, if I create the column that way, it adds the column
> > target_object_cache_time.
> >
> > However, when I used sp_repladdcolumn 'table_foo',
> > '[target_object_cache_time]', 'int not null deafult(0)';
> >
> > it creates the columns with the [] around the name. i.e.
> > [target_object_cache_time] Moreover, it appeared to create it as
> > [target_object_cache_time]] on the subscribers.
> >
> > Needless to say, when I tried to update the stored proc, which uses the
> > new
> > column, it failed since it was looking for target_object_cache_time not
> > [target_object_cache_time].
> >
> > Ok, not a big deal, I figure I'd issue a sp_repldropcolumn and recreate
> > the
> > columns.
> >
> > Here's the error message I recieved:
> >
> > Category:COMMAND
> > Source: Failed Command
> > Number:
> > Message: if exists (select * from syscolumns where
> > name='[target_object_cache_time]'
> > and id = object_id('table_foo'))
> > begin if exists (select * from sysobjects where name='syspublications')
> > if exists (select * from sysarticles where objid=object_id('table_foo'))
> > and @.@.microsoftversion >= 0x07320000
> >
> > exec sp_repldropcolumn
> > @.source_object=N'[table_foo]',@.column=N'[target_object_cache_time]'
> > else alter table [table_foo] drop column [[target_object_cache_time]]]
> > else
> > alter table [table_foo] drop column [[t
> >
> > (it got cut off).
> >
> > As you can see, sometimes it's treating it with an extra set of [] and
> > sometimes not.
> >
> > It seems to me that sp_repladdcolumn should treat [] the same way as
alter
> > table does. But even if it doesn't, it seems to me that you should be
> > able
> > to drop the columns cleanly at least.
> >
> > Thoughts? (I didn't really test too much reproduction since I was
trying
> > to
> > get the root problem fixed on our prod boxes at the time. :-)
> >
> > If I get a chance, I may try to reproduce this again later if no one
else
> > can.
> >
> >
> >
> >
> >
> >
> > --
> > --
> > Greg D. Moore
> > President Green Mountain Software
> > Personal: http://stratton.greenms.com
> > SQL Server Consulting sql at greenms.com
> >
> >
>|||This is a multi-part message in MIME format.
--040005020201060803030401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Greg D. Moore (Strider) wrote:
>"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl...
>
>>Go to the Product Feedback Center and file it as a bug.
>>
>What's the specific URL?
>
http://lab.msdn.microsoft.com/productfeedback/default.aspx
--
*mike hodgson*
http://sqlnerd.blogspot.com
--040005020201060803030401
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">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Greg D. Moore (Strider) wrote:
<blockquote cite="mideCI2gnPZGHA.4752@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">"Michael Hotek" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mike@.solidqualitylearning.com"><mike@.solidqualitylearning.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl">news:%23D%23lFF8YGHA.500@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Go to the Product Feedback Center and file it as a bug.
</pre>
</blockquote>
<pre wrap=""><!-->
What's the specific URL?
</pre>
</blockquote>
<tt><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>">http://lab.msdn.microsoft.com/productfeedback/default.aspx">http://lab.msdn.microsoft.com/productfeedback/default.aspx</a></tt><br>
<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"><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>
</body>
</html>
--040005020201060803030401--
Monday, February 20, 2012
PORTS USED
Hey all
I am using Transactional replication with updatebale subscriptions
What network ports are used for SQL server 2005 replication
This is for firewall config.
Thanx
The agent will attempt to connect to SQL Server on the port that you have setup in the Network Config. Ususally its 1433 TCP by default.
Martin
|||
Hi all,
I have some doubts about this issue too. We are preparing a landscape to set SQL Server 2005 replication between a internal server and another one in Internet. We need to set the firewall to allow only the ports needed. The only port we need is 1433? The distributor and publisher are in the server in Internet, and the subscriber will stay in our internal network.
Regards
Sergio
PORTS USED
Hey all
I am using Transactional replication with updatebale subscriptions
What network ports are used for SQL server 2005 replication
This is for firewall config.
Thanx
The agent will attempt to connect to SQL Server on the port that you have setup in the Network Config. Ususally its 1433 TCP by default.
Martin
|||Hi all,
I have some doubts about this issue too. We are preparing a landscape to set SQL Server 2005 replication between a internal server and another one in Internet. We need to set the firewall to allow only the ports needed. The only port we need is 1433? The distributor and publisher are in the server in Internet, and the subscriber will stay in our internal network.
Regards
Sergio
PORTS USED
Hey all
I am using Transactional replication with updatebale subscriptions
What network ports are used for SQL server 2005 replication
This is for firewall config.
Thanx
The agent will attempt to connect to SQL Server on the port that you have setup in the Network Config. Ususally its 1433 TCP by default.
Martin
|||Hi all,
I have some doubts about this issue too. We are preparing a landscape to set SQL Server 2005 replication between a internal server and another one in Internet. We need to set the firewall to allow only the ports needed. The only port we need is 1433? The distributor and publisher are in the server in Internet, and the subscriber will stay in our internal network.
Regards
Sergio
Ports for Repication
Hi,
I want to set up a transactional replication between 2 sqlserver2000
databases.
Can anybody say me the ports I need for the replication.
Tanks
Jeanette
for ftp its 21 and 1433
for unc its 137 and 1433
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jeanette" <needmoreinfo@.gmx.de> wrote in message
news:%23xqxOQWdEHA.3796@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to set up a transactional replication between 2 sqlserver2000
> databases.
> Can anybody say me the ports I need for the replication.
> Tanks
> Jeanette
>
|||However,does 1433 hold if you are not using 1433 as the
normal listening port for SQL Server? I somehow doubt it.
|||good point. I guess I should have said
for ftp its 21 and whatever port SQL Server communicates on, the default is
1433
for unc its 137 and whatever port SQL Server communicates on, the default is
1433
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:697c01c47577$67ab63e0$a501280a@.phx.gbl...
> However,does 1433 hold if you are not using 1433 as the
> normal listening port for SQL Server? I somehow doubt it.
>
|||Thanks for your help.
but now I have another problem. What can be the mistake when my Server is
not listening on the port 1433?
Thanks
Jeanette
"Hilary Cotter" <hilaryk@.att.net> schrieb im Newsbeitrag
news:uFLNhZYdEHA.712@.TK2MSFTNGP09.phx.gbl...
> good point. I guess I should have said
> for ftp its 21 and whatever port SQL Server communicates on, the default
is
> 1433
> for unc its 137 and whatever port SQL Server communicates on, the default
is
> 1433
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
> news:697c01c47577$67ab63e0$a501280a@.phx.gbl...
>
|||Open the "SQL Server network utility"
Add TCP/IP to the enabled protocols, and edit it to set the listening port.
Restart SQL Server.
"Jeanette" wrote:
> Thanks for your help.
> but now I have another problem. What can be the mistake when my Server is
> not listening on the port 1433?
> Thanks
> Jeanette
>
> "Hilary Cotter" <hilaryk@.att.net> schrieb im Newsbeitrag
> news:uFLNhZYdEHA.712@.TK2MSFTNGP09.phx.gbl...
> is
> is
>
>
I want to set up a transactional replication between 2 sqlserver2000
databases.
Can anybody say me the ports I need for the replication.
Tanks
Jeanette
for ftp its 21 and 1433
for unc its 137 and 1433
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jeanette" <needmoreinfo@.gmx.de> wrote in message
news:%23xqxOQWdEHA.3796@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to set up a transactional replication between 2 sqlserver2000
> databases.
> Can anybody say me the ports I need for the replication.
> Tanks
> Jeanette
>
|||However,does 1433 hold if you are not using 1433 as the
normal listening port for SQL Server? I somehow doubt it.
|||good point. I guess I should have said
for ftp its 21 and whatever port SQL Server communicates on, the default is
1433
for unc its 137 and whatever port SQL Server communicates on, the default is
1433
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:697c01c47577$67ab63e0$a501280a@.phx.gbl...
> However,does 1433 hold if you are not using 1433 as the
> normal listening port for SQL Server? I somehow doubt it.
>
|||Thanks for your help.
but now I have another problem. What can be the mistake when my Server is
not listening on the port 1433?
Thanks
Jeanette
"Hilary Cotter" <hilaryk@.att.net> schrieb im Newsbeitrag
news:uFLNhZYdEHA.712@.TK2MSFTNGP09.phx.gbl...
> good point. I guess I should have said
> for ftp its 21 and whatever port SQL Server communicates on, the default
is
> 1433
> for unc its 137 and whatever port SQL Server communicates on, the default
is
> 1433
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
> news:697c01c47577$67ab63e0$a501280a@.phx.gbl...
>
|||Open the "SQL Server network utility"
Add TCP/IP to the enabled protocols, and edit it to set the listening port.
Restart SQL Server.
"Jeanette" wrote:
> Thanks for your help.
> but now I have another problem. What can be the mistake when my Server is
> not listening on the port 1433?
> Thanks
> Jeanette
>
> "Hilary Cotter" <hilaryk@.att.net> schrieb im Newsbeitrag
> news:uFLNhZYdEHA.712@.TK2MSFTNGP09.phx.gbl...
> is
> is
>
>
Ports and Replication in SQL Server 2000
Hi,
We are trying to configure sql server 2000 transactional replication across
2 data centres through vpn and firewall. While port 1433 is open for sql
server communication, it looks like the anonymous pull subscription uses a
different port to download the initial snapshot files.
We are not using FTP to download the snapshot files, bu the option to find
the snapshot files in the default folder.
We would like to know which port that the replication agent uses to download
the snapshot files to be opened? Can this port be configured to a fixed
unused port and how? Is FTP the best way for this scenarion?
Thanks for your help and time.
Vivek
If you don't use FTP SQL will download the snapshot from the default folder
which is usually a shared folder (NETBIOS).
I don't know the ports you have to open to make that works... but you
certainly don't want to do that...
I think you should activate the FTP server in your internal network (ie:
using the VPN) and make the subscribers download the snapshots from there.
Jos.
"vivek" <vivek@.discussions.microsoft.com> wrote in message
news:9F8EF204-2EB0-489C-AF3C-4D12B4BCEC59@.microsoft.com...
> Hi,
> We are trying to configure sql server 2000 transactional replication
across
> 2 data centres through vpn and firewall. While port 1433 is open for sql
> server communication, it looks like the anonymous pull subscription uses a
> different port to download the initial snapshot files.
> We are not using FTP to download the snapshot files, bu the option to find
> the snapshot files in the default folder.
> We would like to know which port that the replication agent uses to
download
> the snapshot files to be opened? Can this port be configured to a fixed
> unused port and how? Is FTP the best way for this scenarion?
> Thanks for your help and time.
> Vivek
We are trying to configure sql server 2000 transactional replication across
2 data centres through vpn and firewall. While port 1433 is open for sql
server communication, it looks like the anonymous pull subscription uses a
different port to download the initial snapshot files.
We are not using FTP to download the snapshot files, bu the option to find
the snapshot files in the default folder.
We would like to know which port that the replication agent uses to download
the snapshot files to be opened? Can this port be configured to a fixed
unused port and how? Is FTP the best way for this scenarion?
Thanks for your help and time.
Vivek
If you don't use FTP SQL will download the snapshot from the default folder
which is usually a shared folder (NETBIOS).
I don't know the ports you have to open to make that works... but you
certainly don't want to do that...
I think you should activate the FTP server in your internal network (ie:
using the VPN) and make the subscribers download the snapshots from there.
Jos.
"vivek" <vivek@.discussions.microsoft.com> wrote in message
news:9F8EF204-2EB0-489C-AF3C-4D12B4BCEC59@.microsoft.com...
> Hi,
> We are trying to configure sql server 2000 transactional replication
across
> 2 data centres through vpn and firewall. While port 1433 is open for sql
> server communication, it looks like the anonymous pull subscription uses a
> different port to download the initial snapshot files.
> We are not using FTP to download the snapshot files, bu the option to find
> the snapshot files in the default folder.
> We would like to know which port that the replication agent uses to
download
> the snapshot files to be opened? Can this port be configured to a fixed
> unused port and how? Is FTP the best way for this scenarion?
> Thanks for your help and time.
> Vivek
Subscribe to:
Posts (Atom)