Help on SQL Trigger on Project Server database

[James Coulter]

Hi,

I've written a SQL trigger that attempts to automatically assign a project
to a category based on the value in one of the outline code fields.

The outline code field is mandatory, so the user has to select a value
before the project can be saved.

The trigger is applied to the MSP_CODE_FIELDS table for INSERT. It then
looks up the value in the MSP_OUTLINE_CODES table, then gets the matching
category ID from MSP_WEB_SECURITY_CATEGORIES.

Finally, it inserts a record into MSP_WEB_SECURITY_CATEGORY_OBJECTS to
associate the project with the correct category.

However, after applying the trigger to the table, when I try to save a
project to the database, I get an error message saying:

[Microsoft][ODBC SQL server driver]Connection is busy with another hstmt

Can anyone tell me how I can get this to work?

Thanks,

James

 

[Rod Gill]

There is a warning in one of the htm files (projdb.htm I think) that
suggests adding triggers can corrupt your project data. Editing the web
tables especially can be very dangerous. Much safer to have VBA code edit
the project with events and then publish when saving.

 

[James Coulter]

Rod,

This is a good suggestion, but won't automatically assign a project to a
category. Can you think of an alternative way of accomplishing this without
using a DB trigger?

James

There is a warning in one of the htm files (projdb.htm I think) that
suggests adding triggers can corrupt your project data. Editing the web
tables especially can be very dangerous. Much safer to have VBA code edit
the project with events and then publish when saving.

--

Rod Gill
Project MVP

Hi,

I've written a SQL trigger that attempts to automatically assign a
project to a category based on the value in one of the outline code
fields.

The outline code field is mandatory, so the user has to select a value
before the project can be saved.

The trigger is applied to the MSP_CODE_FIELDS table for INSERT. It then
looks up the value in the MSP_OUTLINE_CODES table, then gets the matching
category ID from MSP_WEB_SECURITY_CATEGORIES.

Finally, it inserts a record into MSP_WEB_SECURITY_CATEGORY_OBJECTS to
associate the project with the correct category.

However, after applying the trigger to the table, when I try to save a
project to the database, I get an error message saying:

[Microsoft][ODBC SQL server driver]Connection is busy with another hstmt

Can anyone tell me how I can get this to work?

Thanks,

James

 

[Ed Morrison]

You probably have a logic error in your trigger. Make sure you are not
referencing the table that the trigger is on. Instead use the "inserted"
table.

Since using custom fields is a common question, I have added sample code to
our public FAQ site http://projectserverexperts.com. The article is titled
"Changing the Security Categories using an Enterprise Custom Flag".

Hope this helps.

 

[James Coulter]

Brilliant, just what I was looking for.

I don't suppose you've got something similar that will add a new resource to
a user group based on RBS value?

James

You probably have a logic error in your trigger. Make sure you are not
referencing the table that the trigger is on. Instead use the "inserted"
table.

Since using custom fields is a common question, I have added sample code
to
our public FAQ site http://projectserverexperts.com. The article is
titled
"Changing the Security Categories using an Enterprise Custom Flag".

Hope this helps.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com

Hi,

I've written a SQL trigger that attempts to automatically assign a
project
to a category based on the value in one of the outline code fields.

The outline code field is mandatory, so the user has to select a value
before the project can be saved.

The trigger is applied to the MSP_CODE_FIELDS table for INSERT. It then
looks up the value in the MSP_OUTLINE_CODES table, then gets the matching
category ID from MSP_WEB_SECURITY_CATEGORIES.

Finally, it inserts a record into MSP_WEB_SECURITY_CATEGORY_OBJECTS to
associate the project with the correct category.

However, after applying the trigger to the table, when I try to save a
project to the database, I get an error message saying:

[Microsoft][ODBC SQL server driver]Connection is busy with another hstmt

Can anyone tell me how I can get this to work?

Thanks,

James

 

[Ed Morrison]

Sorry James. I don't have one for that, but I suppose it would be very
similar.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

Brilliant, just what I was looking for.

I don't suppose you've got something similar that will add a new resource to
a user group based on RBS value?

James

"Ed Morrison" <ed(dot)morrison at msProjectExperts(dot)com> wrote in message

You probably have a logic error in your trigger. Make sure you are not
referencing the table that the trigger is on. Instead use the "inserted"
table.

Since using custom fields is a common question, I have added sample code
to
our public FAQ site http://projectserverexperts.com. The article is
titled
"Changing the Security Categories using an Enterprise Custom Flag".

Hope this helps.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com

Hi,

I've written a SQL trigger that attempts to automatically assign a
project
to a category based on the value in one of the outline code fields.

The outline code field is mandatory, so the user has to select a value
before the project can be saved.

The trigger is applied to the MSP_CODE_FIELDS table for INSERT. It then
looks up the value in the MSP_OUTLINE_CODES table, then gets the matching
category ID from MSP_WEB_SECURITY_CATEGORIES.

Finally, it inserts a record into MSP_WEB_SECURITY_CATEGORY_OBJECTS to
associate the project with the correct category.

However, after applying the trigger to the table, when I try to save a
project to the database, I get an error message saying:

[Microsoft][ODBC SQL server driver]Connection is busy with another hstmt

Can anyone tell me how I can get this to work?

Thanks,

James