creating triggers in transactional replication on the subscriber side

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?

Thanks,

Arslan.

are these triggers defined at the publisher, or were you wanting to create new triggers? Assuming the latter, you can put them in a SQL file and reference @.post_snapshot_script in sp_addpublication.|||

If what you would like to do is to replicate triggers defined on the published table, you can take a look at @.schema_option 0x100 in sp_addarticle. If you are using UI, there is also a "copy user triggers" option in article properties dialog.

Peng

creating replication and not using default database name

We currently have transactional replication from our ERP system to our
datawarehouse. Our ERP system is being upgraded and will have a new database
name. I am going to break and recreate replication to our datawarehouse. I
would like to change the default database name to be the same as our current
(ie: the old version) database name so we don't have to go update all our
ODBC connection strings. Is there any reason why I should not do this? Any
problems this may cause? Also, I was thinking of not even dropping the old
database on the datawarehouse, but just simply replicating the new table to
this old database. Any red flags there? Thanks so much.
I'm confused, I realize your upgrade path requires you to rename your
database name on your ERP system.
How does the ODBC connection string fit in here? Is it pointing at the ERP
system, or the data warehouse? Replication couldn't care less what the name
of the subscriber database name is. So this shouldn't be a problem. You may
have to edit the publication scripts for the new database name.
One trick for you, the SQL Server account or the NT account used in the ODBC
DSN will have a default database. You should be able to change this on the
publisher or subscriber and avoid having to modify the ODBC DSN's.
You should also be able to leave the old database intact with the same name
on the dataware house/subscriber. Replication will fix the tables there the
way it wants them to be - which will likely be ok for you.
Things to watch out for
1) PK's are replicated as unique indexes which are very similar to PKs (only
they allow a single null). When you are creating your publication and get to
the specify articles dialog box, click on the browse button, snapshot tab,
and select include DRI to replicate the PK's as PK's and pray to your God
sysdepends is accurate on the publisher.
2) triggers on the subscriber will be dropped unless they are replicated
from the publisher (in the snapshot tab as well)
3) normally data warehouses require different sets of indexes on the
subscriber; you may have to rebuild these.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> We currently have transactional replication from our ERP system to our
> datawarehouse. Our ERP system is being upgraded and will have a new
database
> name. I am going to break and recreate replication to our datawarehouse.
I
> would like to change the default database name to be the same as our
current
> (ie: the old version) database name so we don't have to go update all our
> ODBC connection strings. Is there any reason why I should not do this?
Any
> problems this may cause? Also, I was thinking of not even dropping the
old
> database on the datawarehouse, but just simply replicating the new table
to
> this old database. Any red flags there? Thanks so much.
|||Sorry for the confusion. I was referring to all of our ASP code that hits
the datawh and has the db name in the ODBC connection. It's just a reason
why I want to keep the old name, it doesn't have anything to do with the
actual replication.
Thanks for all your answers. I don't really get #1 though. I've never done
that when I've started replication before. Is it necessary?
"Hilary Cotter" wrote:

> I'm confused, I realize your upgrade path requires you to rename your
> database name on your ERP system.
> How does the ODBC connection string fit in here? Is it pointing at the ERP
> system, or the data warehouse? Replication couldn't care less what the name
> of the subscriber database name is. So this shouldn't be a problem. You may
> have to edit the publication scripts for the new database name.
> One trick for you, the SQL Server account or the NT account used in the ODBC
> DSN will have a default database. You should be able to change this on the
> publisher or subscriber and avoid having to modify the ODBC DSN's.
> You should also be able to leave the old database intact with the same name
> on the dataware house/subscriber. Replication will fix the tables there the
> way it wants them to be - which will likely be ok for you.
> Things to watch out for
> 1) PK's are replicated as unique indexes which are very similar to PKs (only
> they allow a single null). When you are creating your publication and get to
> the specify articles dialog box, click on the browse button, snapshot tab,
> and select include DRI to replicate the PK's as PK's and pray to your God
> sysdepends is accurate on the publisher.
> 2) triggers on the subscriber will be dropped unless they are replicated
> from the publisher (in the snapshot tab as well)
> 3) normally data warehouses require different sets of indexes on the
> subscriber; you may have to rebuild these.
> --
> 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
> "AW" <AW@.discussions.microsoft.com> wrote in message
> news:066C2F4C-947A-469B-A0B7-6129584C9987@.microsoft.com...
> database
> I
> current
> Any
> old
> to
>
>
|||Probably not. Some apps do want to see PK's. Not many of them though, and if
they do they are probably badly written.
I think you should be ok changing the name of your ERP database, but keeping
the old name of your DataWarehouse database.
Next time you build an ODBC connection, don't specify a database name, or
use default database account to handle the database connection for you.
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
"AW" <AW@.discussions.microsoft.com> wrote in message
news:13877D10-371E-4632-842F-35192B8E4CC5@.microsoft.com...
> Sorry for the confusion. I was referring to all of our ASP code that hits
> the datawh and has the db name in the ODBC connection. It's just a reason
> why I want to keep the old name, it doesn't have anything to do with the
> actual replication.
> Thanks for all your answers. I don't really get #1 though. I've never
done[vbcol=seagreen]
> that when I've started replication before. Is it necessary?
> "Hilary Cotter" wrote:
ERP[vbcol=seagreen]
name[vbcol=seagreen]
may[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
name[vbcol=seagreen]
the[vbcol=seagreen]
(only[vbcol=seagreen]
get to[vbcol=seagreen]
tab,[vbcol=seagreen]
God[vbcol=seagreen]
datawarehouse.[vbcol=seagreen]
our[vbcol=seagreen]
this?[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]

creating jobs programmatically causes deadlocks

I'm using the transactional sproc below to create jobs in an automated
fashion that will run cmdexecs after a configurable pause. This is
potentially HIGH VOLUME! The sproc is run from a VB.NET EXE using the
ADO.NET command.executenonquery method during the processing of text files to
determine what the job should do.
I have two essentially identical servers (4 X 3.2GHz CPU, 3GB RAM). On one
server, dropping 30 trigger files results in ~150 jobs being created in ~30
seconds (I have a thread.sleep(200) between each job creation due to another
issue). On another server dropping just 10 of the trigger files causes
deadlocks consistently. Both servers are running SQL 2000 SP3 on WinServer
2003 Standard edition and
the assembly is written against DNF 1.1.
Is the logic in the sproc faulty? The transaction is necessary since the
job needs all the properties to do it's job.
What server configurations affect deadlock timeouts, etc. Any help
would be appreciated.
Sproc follows:
--creates single use, self deleting job
CREATE PROCEDURE usp_RunCmdJobSoon (
@.JobName as varchar(50),
@.CmdText as varchar(1000),
@.DelaySeconds as int = 30,
@.RunOnIdle as bit = 0,
@.DeleteWhenDone as bit = 0
)
AS
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
declare @.Date as datetime
declare @.NewDate as int
declare @.NewTime as int
DECLARE @.String char(30)
SET @.String = 'sa'
/*
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @.JobName)
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR ('Unable to import job %s since there is already a
multi-server job with this name.', 16, 1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
ELSE
RAISERROR ('Unable to create job %s since it already exists.', 16,
1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
*/
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = @.JobName, @.owner_login_name = @.String, @.description = N'Automated
Job Run', @.category_name = N'ODS Automated', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0,
@.notify_level_eventlog = 3, @.delete_level= @.DeleteWhenDone
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = @.JobName, @.command = @.CmdText, @.database_name = N'', @.server
= N'', @.database_user_name = N'', @.subsystem = N'CmdExec',
@.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 2, @.retry_interval =
5, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1,
@.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
--in case of restart
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on start', @.enabled = 1, @.freq_type = 64
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
--in case of Idle condition
IF (@.RunOnIdle = 1)
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on Idle', @.enabled = 1, @.freq_type = 128
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
-- Delay start
SET @.String = 'Run job soon'
set @.Date = dateadd(ss,@.DelaySeconds,Getdate())
set @.NewDate = cast(convert(varchar(8), @.Date, 112) as int)
set @.NewTime = cast(replace(convert(varchar(8), @.Date, 108), ':', '') as
int)
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= @.String, @.enabled = 1, @.freq_type = 1, @.active_start_date = @.NewDate,
@.active_start_time = @.NewTime
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error %s while creating JobName= %s ', 16, 1,@.@.Error,
@.JobName) WITH LOG
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi,
Thanks for your post and I will take ownership of this thread now and help
you with this issue.
From your descriptions, I understood that your stored procedures goes
smoothly in one machine but it will be easily hangs in another machine.
Have I understood you? Correct me if I was wrong.
Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional. Please be advised that contacting
phone support will be a charged call. To obtain the phone numbers for
specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required.
For now, I would love to collect more information about your issue
1. Any Error Message given by SQL Server 2000?
2. Collect the information from sp_blocker_pss80 as the following KB
insturcted
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Moreover, here are some documents for your reference to find what is
blocked
Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
I already have a blocker output, but it's pretty straight forward. The ONLY
thing happening on the system is the creation of jobs through this sproc.
The sproc is blocking itself from another instance doing the exact same thing
at the same time. That's why I included the sproc in my first post, it's
blocking itself.
How can I get more blocker output to you? Maybe I can email them to you?
blocker output excerpt:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 8 No Waittypes: 2004-09-30 15:39:06.890 0
AAG0J6DW06
8 No Waittypes: 2004-09-30 15:39:21.907 0 AAG0J6DW06
8.2 Start time: 2004-09-30 15:39:36.920 0
SYSPROCESSES AAG0J6DW06 134218488
spid status blocked open_tran waitresource
waittype waittime cmd lastwaittype cpu
physical_io memusage last_batch
login_time net_address net_library dbid ecid kpid
hostname
hostprocess loginame
program_name
nt_domain
nt_username
uid sid
sql_handle
stmt_start stmt_end
-- -- -- --
-----------
-
-- -- -- --
-- -- -- --
-- -- -- -- -- --
------
------
------
------
------
--------
--- -- --
51 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4547 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:32.360
2004-09-30 15:39:31.610 000E7FB55C16 Named Pipes 4 0 1008
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
61 sleeping 64 1 KEY: 4:2089058478:1
(bf013f652931)
0x0003 5281 CONDITIONAL LCK_M_S
0 0 18 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5700
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
64 sleeping 61 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5406 CONDITIONAL LCK_M_S
0 0 17 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 4136
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
65 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4672 CONDITIONAL LCK_M_S
0 0 0 2004-09-30 15:39:32.360
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5616
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
66 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 11 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3308
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
67 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3284
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
68 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 2064
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
69 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 5780
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
70 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 1204
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
ESP 110
SYSPROC FIRST PASS
spid ecid waittype
-- -- --
64 0 0x0003
51 0 0x0003
61 0 0x0003
65 0 0x0003
66 0 0x0003
67 0 0x0003
68 0 0x0003
69 0 0x0003
70 0 0x0003
Blocking via locks at 2004-09-30 15:39:36.920
SPIDs at the head of blocking chains
spid
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode
Status TransID TransUOW
-- -- -- -- -- -- -- --
-- -- --
51 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
51 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:484 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (bf013f652931) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (bf013f652931) S
WAIT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 PAG 1:4701 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:4886 IS
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:4886 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 KEY (d001dca5d2ee) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 KEY (2302a3aeaeb3) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 0 TAB IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 0 TAB IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 0 TAB IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 0 TAB IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 0 TAB IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 0 TAB IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 0 TAB IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 KEY (850364070d60) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 KEY (a2045bef58ed) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 KEY (d001b54b3a2e) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 KEY (3302eb1fa7ce) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 KEY (c0019414db93) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 KEY (a90444d60925) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 KEY (a904a60ed3b4) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 KEY (95032cb6041d) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 KEY (a204cbc41ffd) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 PAG 1:11483 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 KEY (c0018819704b) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (cf0177d4204c) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171280 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171394 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171417 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171440 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171486 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171525 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171532 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 PAG 1:24427 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 PAG 1:44674 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
ESL 0
|||Hi,
Thanks for your prompt updates and information!
Yes, it will be highly appreciated if you could provide me the information
by means of email. Here are some steps to collect the information I want
this time
1. Create a script file named 'queryprocess.sql' with the following query
to run commands in a loop.
WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO
2. Run the script file created in step d from either Isql.exe or the
Osql.exe query tool in a Windows command prompt on the computer that is
running SQL Server. For example,
osql -E -Sserver_name -iqueryprocess.sql -oqueryprocess.out -w2000
3. Send the information to us for reviewing and my email is
v-mingqc@.online.microsoft.com (remove 'online' as it is only for SPAM)
In the meanwhile, from syslockinfo, I found many sp are waiting for an
object 2089058478. Is this the stored procedure itself? Please use the
following command to check the object name: object_name( 2089058478)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Creating full-text indexes on a table in a publication

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

By default, full-text indexes are not replicated. You can enable it when you setup a Publication through Publication Wizard UI in the "Articles" page; set the article property to "true" for "Copy full text indexes".

If you setup your publication through stored procedure, make sure when you call sp_addarticle, @.schema_option includes 0x1000000.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0483a157-e403-4fdb-b943-23c1b487bef0.htm

Regards,

Gary

|||Gary,

I do have that schema option enabled in my publication, but it only seems to replicate full text indexes on first initialization of that publication, not if you add a full text index and catalog after the publication is up and running.

-mike
|||

Hi, Mike,

The behavior you're observing is by design. Schema change after initial sync has its own rule, and adding an index is considered a schema change to the publication, and it will not be replicated per DDL replication rule, however, you can explicitly add script to be run at subscriber to create the index (for example, use sp_addscriptexec).

Excerpt from SQL 2005 Books Online:

Making Schema Changes on Publication Databases:

Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

Thanks,

Zhiqiang Feng

|||

Mike,

The error you are getting from the distribution agent is indeed caused by the "CREATE FULLTEXT CATALOG" statement.

sp_addscriptexec would have been the best approach for what you want to achieve, however as it stated in the error message, "CREATE FULLTEXT CATALAG" cannot run within a transaction. In this case, your script is applied by distribution agent, and there is always a transaction created by the agent for the purpose of rolling back in case of an error is encountered (and this is by design).

So unfortunately, the sp_addscriptexec would not work for you in this case. The only work around I can think of are the ones you already know: apply scripts directly or reinitialize the publication.

Regards,

Gary

|||Odd. I just created a script to create a full-text catalog and create a full-text index on a table and applied it to a merge publication using sp_addscriptexec and it worked fine. Any reason why this should work on a merge publication but not a transactional?

Thanks for the help guys.
|||

Cut the long story short.

For transactional replication, ACID properties are preserved and transactions are taken place in the same order in publisher tables and subscriber tables. Since it's important all changes are executed sucessfully on subscriber, it needs to be wrap in a transaction, so roll back is possilbe in case of unexpected failure.

For merge replication, since conflicts may occur, and it can be detected and resolved (manually or by preset conflict resolver). There is no need to roll back all the changes in the committed batch of commands, only the failed ones are marked for conflict resolution by user. Therefore it's okay not to wrap it within a transaction.

Regards,
Gary

|||I have a similar question. I have a one-way transactional replication from A to B. I have replicated the initial full text index from A to B. But A will be contantly updated with new data, and its full text index will be incrementally updated. I would like to have only the updates to the full text index replicated to B as they occur or on a schedule.

Is there a way to do this? Thanks
|||

I don't think the full text index updates will be replicated. The full text engine for SQL Server (MSFTESQL) is responsible for updating and building the full text catalog on each server. So if you insert couple new records at the publisher, those new records got replicated to the subscriber, then the full text engine on the subscriber will take care of updating the full-text database on subscriber database. Replication shouldn't give you additional work load because of the full-text index on the table.

Regards,
Gary

|||Gary,

Thanks for the reply. We want to avoid running the full text index update on the subscriber because the subscriber needs to respond to the web server. When we run the full text index updating on the subscriber, the server becomes very busy for over several hours and response to the web server becomes very slow.

We got the following suggestion from a different forum:
Use "Change Tracking" and "Update Index in Background" on the Push Subscribers

Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking.

Will this work?

Paul
|||

Paul,

I think this is more of a full-text index question than replication question, but here is my two cents :-)

The full text index should use the most resources (time-wise) on your subscriber when the initial snapshot is applied, at this time, the full-text index engine is doing a full population of the initial dataset. Depending on the size of the initial dataset, it is not uncommon to see a full population takes several hours. But after the initial population, all the subsequent updates to your subscriber should be a reasonable light weight process to your full-text index engine (this is my personal opinion, don't quote me on that). Having say that, it really depend on what kind of data you have and how much data you are replicating to your subscribers.

"Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking."

I think the suggestion would save you time when you apply the snapshot, because the distribution agent (which responsible for replicating data from publisher to subscriber) is not running at the same time as the full-text index engine when it's trying to populate the full-text index catalog as data was pouring in. But after the initial snapshot, all the subsequent index updates are still done by the full-text index engine on the subscriber.

So, back to your question, I "think" it'll work if you are using snapshot replication where you are always apply a new data snapshot to subscriber.

As for transactional replication, it would save you time when the initial snapshot is applied, and after that, the distribution agent will replicate the changes to subscriber and you really just want the full-text index engine to do incremental update on subscriber tables instead of a full population.

Hope that helps,

GaryC

Creating full-text indexes on a table in a publication

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

By default, full-text indexes are not replicated. You can enable it when you setup a Publication through Publication Wizard UI in the "Articles" page; set the article property to "true" for "Copy full text indexes".

If you setup your publication through stored procedure, make sure when you call sp_addarticle, @.schema_option includes 0x1000000.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0483a157-e403-4fdb-b943-23c1b487bef0.htm

Regards,

Gary

|||Gary,

I do have that schema option enabled in my publication, but it only seems to replicate full text indexes on first initialization of that publication, not if you add a full text index and catalog after the publication is up and running.

-mike|||

Hi, Mike,

The behavior you're observing is by design. Schema change after initial sync has its own rule, and adding an index is considered a schema change to the publication, and it will not be replicated per DDL replication rule, however, you can explicitly add script to be run at subscriber to create the index (for example, use sp_addscriptexec).

Excerpt from SQL 2005 Books Online:

Making Schema Changes on Publication Databases:

Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

Thanks,

Zhiqiang Feng

|||

Mike,

The error you are getting from the distribution agent is indeed caused by the "CREATE FULLTEXT CATALOG" statement.

sp_addscriptexec would have been the best approach for what you want to achieve, however as it stated in the error message, "CREATE FULLTEXT CATALAG" cannot run within a transaction. In this case, your script is applied by distribution agent, and there is always a transaction created by the agent for the purpose of rolling back in case of an error is encountered (and this is by design).

So unfortunately, the sp_addscriptexec would not work for you in this case. The only work around I can think of are the ones you already know: apply scripts directly or reinitialize the publication.

Regards,

Gary

|||Odd. I just created a script to create a full-text catalog and create a full-text index on a table and applied it to a merge publication using sp_addscriptexec and it worked fine. Any reason why this should work on a merge publication but not a transactional?

Thanks for the help guys.|||

Cut the long story short.

For transactional replication, ACID properties are preserved and transactions are taken place in the same order in publisher tables and subscriber tables. Since it's important all changes are executed sucessfully on subscriber, it needs to be wrap in a transaction, so roll back is possilbe in case of unexpected failure.

For merge replication, since conflicts may occur, and it can be detected and resolved (manually or by preset conflict resolver). There is no need to roll back all the changes in the committed batch of commands, only the failed ones are marked for conflict resolution by user. Therefore it's okay not to wrap it within a transaction.

Regards,
Gary

|||I have a similar question. I have a one-way transactional replication from A to B. I have replicated the initial full text index from A to B. But A will be contantly updated with new data, and its full text index will be incrementally updated. I would like to have only the updates to the full text index replicated to B as they occur or on a schedule.

Is there a way to do this? Thanks|||

I don't think the full text index updates will be replicated. The full text engine for SQL Server (MSFTESQL) is responsible for updating and building the full text catalog on each server. So if you insert couple new records at the publisher, those new records got replicated to the subscriber, then the full text engine on the subscriber will take care of updating the full-text database on subscriber database. Replication shouldn't give you additional work load because of the full-text index on the table.

Regards,
Gary

|||Gary,

Thanks for the reply. We want to avoid running the full text index update on the subscriber because the subscriber needs to respond to the web server. When we run the full text index updating on the subscriber, the server becomes very busy for over several hours and response to the web server becomes very slow.

We got the following suggestion from a different forum:
Use "Change Tracking" and "Update Index in Background" on the Push Subscribers

Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking.

Will this work?

Paul|||

Paul,

I think this is more of a full-text index question than replication question, but here is my two cents :-)

The full text index should use the most resources (time-wise) on your subscriber when the initial snapshot is applied, at this time, the full-text index engine is doing a full population of the initial dataset. Depending on the size of the initial dataset, it is not uncommon to see a full population takes several hours. But after the initial population, all the subsequent updates to your subscriber should be a reasonable light weight process to your full-text index engine (this is my personal opinion, don't quote me on that). Having say that, it really depend on what kind of data you have and how much data you are replicating to your subscribers.

"Use change tracking, once the initial population is complete it is much faster than incremental populations.
To replicate the index commands use the post snapshot command and include commands to run a full-population and then enable change tracking."

I think the suggestion would save you time when you apply the snapshot, because the distribution agent (which responsible for replicating data from publisher to subscriber) is not running at the same time as the full-text index engine when it's trying to populate the full-text index catalog as data was pouring in. But after the initial snapshot, all the subsequent index updates are still done by the full-text index engine on the subscriber.

So, back to your question, I "think" it'll work if you are using snapshot replication where you are always apply a new data snapshot to subscriber.

As for transactional replication, it would save you time when the initial snapshot is applied, and after that, the distribution agent will replicate the changes to subscriber and you really just want the full-text index engine to do incremental update on subscriber tables instead of a full population.

Hope that helps,

GaryC