Home » discuss » Public forums » iMIS Community

Taskcentre trigger issue

Posted September 21st, 2009 by Mark Pellicore
|

I'm having issues getting a Taskcentre trigger to work in my iMIS database. The trigger is failing when it has to create an instance of an OLE object (sp_OACreate).  I confirmed that the public DB role has execute permissions on sp_OACreate. This code is autogenerated by Taskcentre v4. It fails on creating this object and therefore doesn't get to my custom code. Any suggestions?

EXEC @hresult = sp_OACreate 'iwsqlea.sqlconnector', @sqlconnector OUT
IF @hresult <> 0
 GOTO Quit

EXEC @hresult = sp_OAMethod @sqlconnector, 'Init', NULL, 36, 708, 6
IF @hresult <> 0
 GOTO Quit

EXEC @hresult = sp_OAMethod @sqlconnector, 'RunTask', NULL
IF @hresult <> 0
 GOTO Quit

END

Quit:

IF @hresult <> 0
BEGIN
 SELECT @errmsg = 'Trigger on "Activity" failed to Queue Task 36(' + CAST(@hresult AS varchar(16)) + ')'
 EXEC master..xp_logevent 70000, @errmsg, ERROR
END

Mark Pellicore

‹ Installing asipublishing15 service on serverPublishing from URL to domain name creates an error ›
login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Some questions ...

On September 22nd, 2009 pncannon says:

What error message(s) are you getting?

What version of SQL do you have, Standard or Express?

Have you enabled OLE Automation?

login or register to post comments

reply to questions

On September 22nd, 2009 Mark Pellicore says:

No error messages display but the error log command is executed at the end of the trigger.

SQL Standard 2005 and we have enabled OLE Automation on this instance.

login or register to post comments

I wonder then, if

On September 22nd, 2009 pncannon says:

I wonder then, if permissions are the problem. Are you using a trusted connection or named SQL user in the trigger?

Have you looked at the application and system logs on the SQL server to see if these tell you anything? I doubt that you'll see anything in the TaskCentre event log because it probably hasn't even got that far, but it's worth checking there too.

That's all I can think of without more information, I suggest you raise an SMR and have support take a look with you.

Sorry I cannot be more help.

login or register to post comments

it must be permisssions

On September 22nd, 2009 Mark Pellicore says:

I agree in that it must be permissions but I cannot pinpoint what permission is needed on sp_OACreate. I tried a handful of roles/users and gave them each execute rights one by one.

I've tried a trusted connection and named SQL user and both return the same result.

Thanks for your advise. I did submit an SMR to ASI.

login or register to post comments

it was permissions

On September 22nd, 2009 Mark Pellicore says:

I had an error in my event log that I started researching (i.e. Googling) and found a solution that fixed my issue:

 The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID{61738644-F196-11D0-9953-00C04FD919C1} (example) to the user <serverName>\<serviceSID>.

This security permission can be modified using the Component Services administrative tool.Copy the GUID following the CLSID above, and Start-->Run-->regedit.  Edit-->Find and paste in the GUID. It'll stop at the application entry - and you will want to note the application name on the right sidepane. Now, open Component Services (typically, from the server - Start-->Administrative Tools-->Component Services), expand Component Services,Computers, My Computer, DCOM Config. Scroll down and find the application. Right-Click-->Properties and selectthe Security tab. You'll have some options here - the first block Launch and Activation Permissions - ensure that the Customize radiobutton is selected, and click Edit. Now, add your service account - giving it launch and activate - and in some requirements - remotelaunch / activate permission.Restart IIS 

The application name was listed as SQLConnector Class.

Thanks to geekswithblogs.net!

login or register to post comments

And the offending app was

On September 22nd, 2009 pncannon says:

And the offending app was IWSQLEA? That's the SQL Trigger Agent and this can arise if the logon account for SQL Server (Service) is not the same a TaskCentre, both of which by default use Local System. I presume that you (your DBA or sysadmin) changed the logon accounts in which case your fix makes sense.

If ususally only seen this occur with SQL Express, which uses Network Service account to log on.

 

login or register to post comments

Yes, IWSQLEA was the

On September 22nd, 2009 Mark Pellicore says:

Yes, IWSQLEA was the culprit. Our SQL Server service was running under a domain acct while Taskcentre server is running under the LocalSystem acct.

Can Taskcentre server use the same domain acct that SQL server uses? Or is it recommended to keep it Local System?

login or register to post comments

Best to keep it as Local

On September 22nd, 2009 pncannon says:

Best to keep it as Local System. If you need different permissions for a particular task, use Task Impersonation.

login or register to post comments

Thanks

On September 23rd, 2009 mstraka says:

I was getting the same error at a client site. In my case it was because of SQL Express using the NETWORK SERVICE.   I added the NETWORK SERVICE to the iwsqlea component service, per your post and I’m not getting that particular error anymore.  (My trigger still isn’t firing, but that's another story. ;)

At any rate, I just wanted to say thanks for the timely thread - I'm sure you saved me tons of headaches down the road. 

login or register to post comments