Computed column question

I have a SQL table that maintains a field on the status of a report being completed.

I have in the record the date the report is due (DateDue)

I also have a field called DaysLate which I have set to be a calculated field with formula:

DATEDIFF(dd, DateDue, GETDATE())

Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field?

Regards

Cvive

CASE WHEN {Your formula}<0 THEN NULL ELSE {Your formula} END

|||

Many thanks for that - that did the job perfectly.

Clive

Completed(?) and urgent Triggers and Transactions question

I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.

The stored procedures will update tables. These tables use triggers..

My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?

Jag

the trigger will execute when

- new records is being inserted into the table

- records get update

- records get deleted

from the table

|||

In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.

cheers

|||

How would you define too much?

At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.

The stored procedure should be optimised, so it should case too much problems later (?)

EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.

Jag

|||

Hi,

The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.

I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.

I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.

Jag

|||

Just another quick question.

Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.

My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.

Thanks in advance

Jag

completed successfully, but not sending and no error.

hi, i have a message queue system using sql 2005 service broker.

the code and setup is the same on both dev and live database. but

soon after i restored a live backup to dev. the queue stopped

working on dev, live is ok thou. after some trouble shooting, i

found that the server is not sending the message at all, but it says

"Command(s) completed successfully" without any error messages.

setup:

--

create message type TestQueryMessage validation = none

create contract TestQueryContract (TestQueryMessage sent by initiator)

create queue TestSenderQueue

create service TestSenderService on queue TestSenderQueue

create queue TestQueueReceiver

create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)

send message:

-

declare @.conversationhandle uniqueidentifier;

begin dialog @.conversationhandle

from service [TestSenderService]

to service 'TestServiceReceiver'

on contract [TestQueryContract]

with encryption = off;

send on conversation @.conversationhandle

message type [TestQueryMessage] ('blah blah blah');

result:

-

Command(s) completed successfully.

but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.

please advise. thanks a lot.

Look in sys.transmission_queue on the sender's database. The transmission_status column should indicate the problem. Most likely the broker was not enabled back after the restore operation.

HTH,
~ Remus