Delete a subscription but constrains created for auto-identity remains

Hello,
I've a merge replication in SQL Server 2000 with one publication, where all
the articles are tables. Some of these tables have IDENTITYs and I've
choised SQL Server auto identity range.
When I delete a subscription all replication tables and information related
to the replication is deleted in the Subscriber, but the constraints created
for the identity's management (something like
'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still exists
(in the Subscriber).
I'm not sure if I'm doing something wrong or if I've to create a script to
drop all these constraints. Does someone know about it?
Thank you in advance,
Josep.
I have reported this problem to Microsoft. You have to write a script to
remove it.
"Josep" <jmartinez@.autec.es> wrote in message
news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I've a merge replication in SQL Server 2000 with one publication, where
> all the articles are tables. Some of these tables have IDENTITYs and I've
> choised SQL Server auto identity range.
> When I delete a subscription all replication tables and information
> related to the replication is deleted in the Subscriber, but the
> constraints created for the identity's management (something like
> 'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still
> exists (in the Subscriber).
> I'm not sure if I'm doing something wrong or if I've to create a script to
> drop all these constraints. Does someone know about it?
>
> Thank you in advance,
> Josep.
>
|||Thank you Hilary for your help !!
I have wrote the script. I post it here in case someone is interested in it:
/*
This script drops all CONSTRAINTs created by Merge Replication to manage
automaticaly the range of IDENTITYs,
due to SQL Server don't drop them when you delete the subscription in a
Subscriber.
*/
-- It should be executed on the replicated database
DECLARE @.table NVARCHAR(2000), @.constraint NVARCHAR(2000), @.sql
NVARCHAR(2000)
-- We get all the CONSTRAINTS used by automatic IDENTITY range
SELECT
table_name, constraint_name
INTO #IdentityConstraints
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'repl_identity_range_sub_%'
-- We DROP these CONSTRAINTs
WHILE (SELECT COUNT(*) FROM #IdentityConstraints)>0
BEGIN
SELECT TOP 1 @.table=table_name, @.constraint=constraint_name FROM
#IdentityConstraints
SET @.sql = N'ALTER TABLE ' + @.table + ' DROP CONSTRAINT ' + @.constraint
EXEC sp_executesql @.sql
PRINT 'Dropped CONSTRAINT "' + @.constraint + '" in table "' + @.table + '"'
-- Once dropped the constraint selected, we delete it in the temporary
table
DELETE FROM #IdentityConstraints WHERE constraint_name=@.constraint
END
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:u16RYadgHHA.4952@.TK2MSFTNGP02.phx.gbl...
>I have reported this problem to Microsoft. You have to write a script to
>remove it.
> "Josep" <jmartinez@.autec.es> wrote in message
> news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
>

DEFINITIVE ANSWER PLEASE -- can you UPDATE ntext datatype field??

Hi, I've read conflicting articles on updating an ntext field in a
column.

My ntext field will exceed 8,000 characters (typically twice that size
-- but just a text string).

One article (I think from MicroSoft) said you could NOT use ntext in
an UPDATE statement, but I've seen examples from other people using
it...but don't know if it's related to the size/characters issue.

Is this true or not?

Thanks very much...KathyKathyB (KathyBurke40@.attbi.com) writes:
> One article (I think from MicroSoft) said you could NOT use ntext in
> an UPDATE statement, but I've seen examples from other people using
> it...but don't know if it's related to the size/characters issue.

Yes, you can update an ntext column directly in an UPDATE statement.
I dont think there is a limitation, but it may be unpractiable if
you have a string which is million characters long.

There is also UPDATETEXT which permits you change parts of an ntext
column, but this function is certainly more complex to use, so as long
as you can do it with plain UPDATE stay with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, that's what I wanted to hear!

I don't need to save a million, just up to 50,000!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Default Public Permissions and Guest

There are numerous articles about removing permissions from public, dropping
extended stored procedures and removing the guest account from the msdb
database. Has anyone found any documentation from Microsoft on what their
position is on making any of the afore mentioned changes. I'm reluctant to
make any changes to the default security model even though external audits
indicate that these changes should be made. Any advise would be appreciated
.Hi,
removing guest account from msdb and disallowing public with strong password
mechanism , encryption of data,ntfs file system , windows authentication
could help to prevent from sql injection issue...
please refer following articles :
http://www.microsoft.com/technet/pr...07.msp
x
http://www.microsoft.com/technet/pr...07.msp
x
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Rick" wrote:
[vbcol=seagreen]
> There are numerous articles about removing permissions from public, droppi
ng
> extended stored procedures and removing the guest account from the msdb
> database. Has anyone found any documentation from Microsoft on what their
> position is on making any of the afore mentioned changes. I'm reluctant t
o
> make any changes to the default security model even though external audits
> indicate that these changes should be made. Any advise would be appreciated.[/vbc
ol]|||Hi,
have you refer as i suggest !?
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> removing guest account from msdb and disallowing public with strong passwo
rd
> mechanism , encryption of data,ntfs file system , windows authentication
> could help to prevent from sql injection issue...
> please refer following articles :
> http://www.microsoft.com/technet/pr...07.m
spx
> http://www.microsoft.com/technet/pr...07.m
spx
> Regards
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "Rick" wrote:
>|||I'm familiar with the article that was referenced, however it doesn't answer
my specific question which is; Can default permissions be revoked from publi
c
and still have Microsoft support the installation. This means revoking
execute from all system stored procedures and select form all catalogs, view
s
and tables from public.
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> have you refer as i suggest !?
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "Andy Davis" wrote:
>|||Hi,
You can remove guest/public access but its not recomended , please refer
following thread FYI why it is not recomended
http://www.sql-server-performance.c...p?TOPIC_ID=3596
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Rick" wrote:
[vbcol=seagreen]
> I'm familiar with the article that was referenced, however it doesn't answ
er
> my specific question which is; Can default permissions be revoked from pub
lic
> and still have Microsoft support the installation. This means revoking
> execute from all system stored procedures and select form all catalogs, vi
ews
> and tables from public.
> "Andy Davis" wrote:
>