03.11.07

Changing a property for all articles on a publication.

Posted in DBA, Microsoft Sql server, TSQL at 1:30 pm by Hedwig Lodrigo

This week I had to change the pre_creation_cmd from ‘delete’ to ‘drop’ on all articles of a publication. I could do this manually or I could find all articles by using sp_helparticle. Lazy as I’m I opt for the latest option.

DECLARE @articles table
(
[article id] int,
[article name] varchar(255),
[base object] varchar(255),
[destination object] varchar(255),
[synchronization object] varchar(255),
[type] int,
status varchar(255),
filter varchar(255),
description varchar(255),
insert_command varchar(255),
update_command varchar(255),
delete_command varchar(255),
[creation script path] varchar(255),
[vertical partition] varchar(255),
pre_creation_cmd varchar(255),
filter_clause varchar(255),
schema_option varchar(255),
dest_owner varchar(255),
source_owner varchar(255),
unqua_source_object varchar(255),
sync_object_owner varchar(255),
unqualified_sync_object varchar(255),
filter_owner varchar(255),
unqua_filter varchar(255),
auto_identity_range varchar(255),
publisher_identity_range varchar(255),
identity_range varchar(255),
threshold varchar(255),
identityrangemanagementoption varchar(255),
fire_triggers_on_snapshot varchar(255)

);
DECLARE @article_name varchar(255);

INSERT INTO @articles
exec sp_helparticle  'Your replication publication name';

DECLARE article_cursor CURSOR FOR
SELECT [article name] as base_object FROM @articles;

OPEN article_cursor;

FETCH NEXT FROM article_cursor
INTO @article_name;

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_changearticle
@publication = 'Your replication publication name',
@article = @article_name,
@property = N'pre_creation_cmd',
@value = 'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription= 1;

FETCH NEXT FROM article_cursor
INTO @article_name;

END

CLOSE article_cursor;
DEALLOCATE article_cursor;

GO

After this you need to reinit all the subscriptions.

Permalink Comments

08.11.06

Rows affected by a querry in VB6?

Posted in TSQL, Visual Basic 6 at 9:11 pm by Hedwig Lodrigo

For some reason you would like to know how many records where affected by your sql statement called from visual basic 6. But you have no clue how to do it.

This is how you do it.

When you do a query in sql server then you can see how many rows where affected by it, because sql server fills up the @@rowcount variable.

Code:

delete from customer where name like 'A%'
print @@rowcount

If you want to return this result to a result set you can do the following:

Code:

delete from customer where name like 'A%'
select @@rowcount as affected

Then in visual basic 6:

dim newrs as ADODB.Recordset
Set newrs = rs.NextRecordset
msgbox newrs.Fields("affected")

Permalink Comments

07.27.06

Transactions

Posted in Microsoft Sql server, TSQL at 10:57 pm by Hedwig Lodrigo

One of the common shortcomings of the novice sql guru is the lack of knowledge about transactions.

What is a transaction?
A transaction consits off several statements which are executed as a single unit. Either all the statements are done or they all fail. You’ll need this functionality in situations which require atomicity. For instance you are coding for a bank and your code has to transfer money from one account to another.

Sample without transactions:

Now the first guy has lost his money and the second guy never received the money.

Sample with transactions:

Now when the server is recovered the first sql statement has never happend so the first guy still has all his money.

Permalink Comments