Watson, Lex
2006-05-24T10:01:00Z
I'm having an issue with SQL Triggers.
I'd like the storing of tasks from Outlook into SQL server (table "Tasks") to trigger a Data Transformation Service (DTS) that processes the data.
I'm not good with cursors, so felt a trigger would be best.
If I set one up on the table that GeniusConnect syncs to, I get this error:
Could not save to the database (SyncFolder)

The trigger is
CREATE TRIGGER [updater] ON [dbo].[Tasks]
AFTER INSERT, UPDATE
AS EXEC master.dbo.xp_cmdshell 'DTSRun.exe /S "(local)" /N "Task Trial" /G "{549AC175-B906-44E9-A733-7402C8C381BA}" /W "0" /E'

If I substitute a command like xp_sendmail, it works ok (albeit I get an email for every update or insert - I had hoped that the use of 'AFTER' would prevent this, but I suspect that each record insert or update by GC is considered a complete action by SQL server.

Would I be better to just run the DTS every 10 minutes, checking for new entries?

thanks,
lex
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2006-05-24T12:20:00Z
Hi,

are you getting a syntax error? If it works with xp_sendmail, it has to work also with xp_cmdshell.


Can you try this syntax:
EXEC master.dbo.xp_cmdshell 'dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password'

Try it first in Query Analyzer, if it runs move it to the trigger.

Please note:
If the DTS execution is time consuming, it maybe better to run it on a scheduler!, otherwise the Insert/Save action can fail with
timeouts etc..
Watson, Lex
2006-05-24T14:37:00Z
Hi,
It runs ok in Query Analyser both with and without User Name and Password. The time elapse is 3 seconds.
If I change the Trigger to act after DELETE, then the update and inserting are ok. If changed to INSERT, then the store works if there is only updates required.
Error message is still "Could not save to the database (SyncFolder)"
If there are 7 items to update, 1 to add and the trigger is set as AFTER INSERT, then the failure occurs before any records are added. Is it possible that the trigger is fired before all changes are made, and hence the dts is running whilst geniusconnect is still trying to access the table?
Administrator
2006-05-24T14:59:00Z
It depens on what the DTS is doing, if it locks the table, it is possible GeniusConnect will not be able to insert/update records.
You can try to use the instead of triggers, but the GeniusConnect will wait until the DTS excution has finished.

You can also try a different scenario using a signal table.

Example:
1.create a table
TasksSignal
Columns ID <OrgTablePrimKey>, Done (default FALSE), <TranID> auto.increment prim.key

2.In your trigger replace your DTS command with something like:
INSERT INTO TasksSignal
SELECT <OrgTablePrimKey> FROM inserted


3. Change your DTS package to scan the TasksSignal table instead of the Real table.
After processing of a record set Done=TRUE

advantage of this scenario:
-Update/inserts are faster since the DTS is not running in the trigger
-You don't have to scan a live table but only the TasksSignal table where Done=FALSE


Please see MS documentation:

Specifying When a Trigger Fires
You can specify one of two options to control when a trigger fires:

AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE) and after any constraints are processed. You can request AFTER triggers by specifying either the AFTER or FOR keywords. Because the FOR keyword has the same effect as AFTER, triggers with the FOR keyword are also classified as AFTER triggers.


INSTEAD OF triggers fire in place of the triggering action and before constraints are processed.
Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT). A table can have several AFTER triggers for each triggering action.

Examples
A. Use the INSTEAD OF trigger to replace the standard triggering action
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...

B. Use the AFTER trigger to augment the standard triggering action
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
C. Use the FOR trigger to augment the standard triggering action
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...
Watson, Lex
2006-05-24T16:38:00Z
Sounds plausible. Will look at a trigger table solution.

However, I think that my company will want your add-on to run on the Exchange server, rather than a local (specified user) install, as I've been informed that the user is on a thin-client connection to one of 5 servers.

GeniusConnect will thus have to run on a schedule on the central Exchange server, as otherwise, on auto-update, the trigger will occur on every task assigned to the user, rather than when he's ready to update.

I cannot believe your prompt attention - do you ever take a break?
regards,
lex
Administrator
2006-05-24T16:43:00Z
The GeniusConnect Auto.save option works also with the Dedicated install solution.


Watson, Lex
2006-05-25T12:15:00Z
Hmm,
Tried using another way to trigger the process; I set up a table in the DB to be fed from the Notes folder. A trigger set to be after update of this table then ran a DTS against the task table. The same problem occured; the trigger appears to interrupt the operation of the storage of the Task data, even though the table being updated is not involved in the DTS.
Detail of GeniusConnectProfile shows inserts performed ok, but the item to update produced the red cross icon against it and the "Could not save to database".

I'd like to trial the Auto.save option, but that's greyed out in the trial version - I assume that's only available in the licenced Business version?

regards,
lex
Administrator
2006-05-25T12:54:00Z
Yes, only in Business version and higher.

try also to use SET NOCOUNT ON in your trigger.

Do you see only "Could not save to database" or any other error message?
Try also to perform the same update/insert statements in SQL Query browser.

You can set SQLTrace=1 to trace all SQL Statements to a file.
HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\SQLTrace (DWORD)

The file has .trc extension and is located in you User temp folder, Example:
C:\Documents and Settings\<User>\Local Settings\Temp

Watson, Lex
2006-05-25T14:01:00Z
I think I might just leave the triggers out and work on a twin automatic update system of tasks and notes. The user can then add a note with content 'done' and I'll set the DTS task to check the note table every 10 minutes for the arrival of such in the prior 10 minutes.

A pricing query for the paper I'll be writing for my boss to recommend purchase;
From the pricing, am I right in thinking that the business version for one user, all modules, is 185 euros?
If so, is that annually or a one-off? If the latter, is there a further annual support/upgrade charge?
Sorry for the Qs and thanks again - I'm impressed with your response times in a pre-sales situation; if only all software people were like this (MS, I'm looking at *you*)!

lex
Administrator
2006-05-25T14:11:00Z
Hi,

Yes business version-all modules for 1 user is 185 euros.
Please note you need MultiUser version for Dedicated install (Multiple account sync from one install) or MS Exchange Public Folders.

This is one time fee, during the order process you can order Gold-Support.
(Gold-Support:template creation based on your database for 1 folder type).

All updates in third edition (current edition) are free.
When we release a new edition (once in 1,5-2 year), existing customers
can upgrade with 50% discount.

Thank you.
Watson, Lex
2006-05-25T17:03:00Z
I know I should start a fresh topic, but feel free to delete/move these posts later.
You say:
Yes business version-all modules for 1 user is 185 euros.
Please note you need MultiUser version for Dedicated install (Multiple account sync from one install) or MS Exchange Public Folders.

Just to be certain - we currently only need this add-on for one user and his folders, but because he connects to our network on a thin client, the install will have to be on the Exchange server (which is linked to the thin-client servers).

Are you saying that we will need the MultiUser version (at 825 euros), as the users are Administrator and the NamedUser? In which case, we would be better off with a 12 month subscription at 75e.

I suspect that at some point, there will be a desire to increase the number of users involved, so a subscription would allow for growth.[:)]


Administrator
2006-05-25T17:15:00Z
If you need only one user on his (private/account) folder then 1 user license is OK (even if it is MS Exchange private folder).

IF you need more user licenses in the future, you can upgrade to MultiUser.
Similar Topics