automatically restarting merge replication after time out

Hello,
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Automatically Fire Trigger Or Procedure in sql server 2000

Hi Guys

I want to automatically fire a trigger or Procedure every day 8:00 P.m. How can we achieve this? is there any way in sql server which checks the time regularly and can fire the trigger or we have to write a batch which runs at the specified time and try to create a situation so that the trigger is automatically fired?
plssssss get back with solution.. this is very urgent....
thanks in advance...

Krishna

In Enterprise Manager, <server name> -> Management -> SQL Server agent -> Jobs

Here you can create your job.

|||

when i execute a procedure it will display error like this

Server: Msg 217, Level 16, State 1, Procedure sp_PPS_send_cdontsmail, Line 12
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Thanks

Krishna

|||Then you need to debug your stored proc and figure out what you're doing wrong. Looks like you have an inadvertent loop that's causing something to continuously call itself.|||

Hi Greg

it working i changed my procedure name then it will be working sucessfully message. but i mail was not sending. it will take more time. i am executing procedure in queryanalyzer it's work fine. i have a created a job it's won't work.

if you don't mind are you online in yahoo please response me

i am in rama_krishna76@.yahoo.com.

Krishna

|||I don't know anything about sending mail, but could it have to do with the login account that's executing the stored proc? What account is used to start the sql server agent service? When you logged into query analyzer, did you log in with the same account, or did you use a different one?

Automatic trigger on a specific time

Hi!

I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:

periode startdate enddate
1 23.08.2006, 15:00 24.08.2006, 17:00
2 24.08 .2006, 17:00 25.08.2006, 08:00
and so on

Can anyone help me how to do this? Thanks!

You can create a maintenance job that runs every few hours or every few days and call a stored proc and put your logic in the stored proc.

|||

Hi ndinakar,

thx for your reply!

I know that jobs are an alternative but there are two problems: firstly it seems, that jobs are not available in SQL Server Express Version and secondly the trigger should actually fire on a specific time that could be every minute. So I would have to fire a job every minute but I think, that might not be a good idea.

|||I havent used Express editions. The other alternative is to create a Windows Service and schedule it to run every 1 minute. You could call a stored proc using OSQL. Activate the OSQL from your windows service. Might sound complicated but I cant think of any other option.|||Sounds like what you really want is a windows service like ndinakar mentioned.|||Thx ndinakar and Motley. I`ll try to use a windows service. That might be the best solution.sql

Automatic Replication Trigger

HI All,
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***

automatic printing from trigger

Hi,
I am new to Sql Server 2000. I am looking for help on how to automatically generate a report to a network printer after a record is added/updated/changed in a table. How should I do this? With a Trigger? With DTS?
Please help.With a Trigger ...|||Thanks for the info. I think I was unclear in my initial question and would like to know what are the exact steps needed to perform this function? I don't know how to create a trigger to do this and would be looking for an example of some sort or instructions for the code.

Any help greatly appreciated.|||What about sp_OA... procedures, write component in any language
and use these SP for communication.

Export with DTS or SQLDMO objects.

Multitask temp tables with :
1.X=OBJECT_ID('temp..#temptbl')
2.Send X to object
3.Get temp name
use tempdb
GO
Y=OBJECT_NAME(X)

Too much work to be done ...
MSSQLSERVER2K has native support for e-mail and pager.

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete or update infomation on a table, the information will insert, delete or update on another table ( we don't have to create any trigger to do that ).
Please tell me the way to do. Thanks !
TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison
|||I think that you want to create a trigger that will allow you to log =
data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main=20
FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main=20
FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =
AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo'=20
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
--=20
Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =
news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =
insert, delete or update infomation on a table, the information will =
insert, delete or update on another table ( we don't have to create any =
trigger to do that ).
> Please tell me the way to do. Thanks !

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete or update infomation on a table, the information will insert, delete or update on another table ( we don't have to create any trigger to do that )
Please tell me the way to do. Thanks !TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison|||I think that you want to create a trigger that will allow you to log =data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo' SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
-- Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =insert, delete or update infomation on a table, the information will =insert, delete or update on another table ( we don't have to create any =trigger to do that ).
> Please tell me the way to do. Thanks !

auto trigger

I don't know how to use an auto trigger. That's mean when we insert, delete
or update infomation on a table, the information will insert, delete or upda
te on another table ( we don't have to create any trigger to do that ).
Please tell me the way to do. Thanks !TuanAnh,
I'm interested in where you have heard of "Auto Triggers" to populate a
table? Indexed views have a similar functionality but are obviously not a
table. In SQL Server if you wanted an audit trail, you would have to use
after triggers and there is no other automatic "auto trigger" way of doing
it that I have ever heard of. Perhaps you are thinking of transactional
replication? This doesn't use triggers (providing the subscribers don't
change the data)?
Regards,
Paul Ibison|||I think that you want to create a trigger that will allow you to log =
data to a table. Is that correct? This example will get you started:
USE tempdb
GO
CREATE TABLE main (SomeColumn int, AnotherColumn varchar(10))
CREATE TABLE mainAudit (SomeColumn int, AnotherColumn varchar(10))
GO
CREATE TRIGGER mainInsert ON main=20
FOR INSERT
AS
INSERT INTO mainAudit (SomeColumn, AnotherColumn)
SELECT SomeColumn, AnotherColumn FROM inserted
GO
CREATE TRIGGER mainUpdate ON main=20
FOR UPDATE
AS
UPDATE mainAudit SET AnotherColumn =3D B.AnotherColumn
FROM mainAudit A JOIN inserted B ON A.SomeColumn =3D B.SomeColumn
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (1, 'test')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn) VALUES (2, 'testing')
SELECT * FROM main
SELECT * FROM mainAudit
GO
INSERT INTO main (SomeColumn, AnotherColumn ) SELECT SomeColumn, =
AnotherColumn + 'new' FROM main
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'foo'=20
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'hello' WHERE SomeColumn =3D 1
SELECT * FROM main
SELECT * FROM mainAudit
GO
UPDATE main SET AnotherColumn =3D 'world' WHERE SomeColumn =3D 2
SELECT * FROM main
SELECT * FROM mainAudit
GO
DROP TABLE main
DROP TABLE mainAudit
--=20
Keith
"TuanAnh" <anonymous@.discussions.microsoft.com> wrote in message =
news:8E4A7419-372A-4EC1-8701-C3EF347D217E@.microsoft.com...
> I don't know how to use an auto trigger. That's mean when we =
insert, delete or update infomation on a table, the information will =
insert, delete or update on another table ( we don't have to create any =
trigger to do that ).
> Please tell me the way to do. Thanks !

auto new line in same table

Hi

I have Table Like this In SQL server

Code Name 1001 Bank 1002 Cash

I need a TRIGGER
FOR INSERT, UPDATE, DELETE

that ADD a new Row's in the same table like this:

Code Name 8001 Bank 9001 Bank 8002 Cash 8002 Cash

In other mean for any row add to table by user that code start with 1xxx the trigger must inset to other row to that table whit code 8xxx & 9xxx.

with the same name.

thanks for your help.You could use an INSTEAD OF TRIGGER to do this. Code below should give you an idea of how to do this.

create trigger InsNewRows on tbl instead of insert
as
begin
insert into tbl (Code, Name)
select i.Code, i.Name from inserted as i
union all
select i.Code + n.Code, i.Name
from inserted as i
cross join (select 8000 union all select 9000) as n(Code)
where i.Code between 1000 and 1999
end|||Probably would want to use

where i.Code >= 1000 and cmpid <= 1999

Auto increment an Primary Key

How do I make a Primary Key in a table automatically increment as a new row of data is entered? do i have to do it with a trigger?? if so what is the code behind it...what I have is a Applicant table, which holds the ApplicantID (<<<needs auto incremented), FirstName, LastName, Address, City, State, Zip, and PhoneNumber. I enter all the other information through visual web developer, and what I need to do when the hit submit on the form with the data i want to automatically set an id right there. any suggestions will help!

If you want an easy way of setting an auto-increment id, use an IDENTITY column. You can use this with smallint, int, etc. You can set the seed (starting value) and the increment value (i.e. by add one to the previous value). You don't have to set a default or increment it yourself.

|||

Where do i go to set the column if already created to an identity column?? this is how i created the table, when in Enterprise manager i went the database folder name, expanded the tables and then right clicked and choose add new table...maybe that is the way to do it, but it gave me a design view...and i just made the column a primary key...how do i make it an identity column?

|||

Right click the table and choose 'Design Table' in SQL Server 2000, 'Modify Table' in 2005. The lower configuration pane is where you set it. Select your column, and you'll notice there's an 'Identity' property. Set this to Yes/True, and then you can set the sub properties (Identity Seed and Identity Increment).