June 2006 - Posts - akila's blog

Setting Project Owner Programmatically in Project Server 2003

When you create a project using Project Data Services (PDS) from a custom application, the project owner for the Project will be set as per the user logon with which the project was created. To change the Project Owner, PDS can be used and there is no need of writing an Extender.

Make a call to the ProjectCodeValues method:

<Request>
   <ProjectCodeValues>
      <ProjectID>100</ProjectID>
      <ProjectName>Project Owner.Published</ProjectName>
      <AutoCheckout>0</AutoCheckout>
   </ProjectCodeValues>
</Request>

AutoCheckOut can be set to 1, if you want to check out the project as well.

All the project outline codes and custom fields that are set in the system are returned along with the value set for the project. The code having the UID 99999 corresponds to the owner field.

Look in the below reply to find the owner column,

<Reply>
   <HRESULT>0</HRESULT>
   <STATUS>0</STATUS>
   <UserName>John Smith</UserName>
   <ProjectCodeValues>
      <ProjectID>100</ProjectID>
      <ProjectName>Project Owner.Published</ProjectName>
      <ProjectCheckedout>0</ProjectCheckedout>
      <ProjectCheckedoutUser>Akila Ananthanarayanan</ProjectCheckedoutUser>
      <CurrencySymbol>$</CurrencySymbol>
      <CurrencyPosition>0</CurrencyPosition>
      <CurrencyDigits>2</CurrencyDigits>
      <Columns>
         <Column>
            <Type>0</Type>
            <UID>99999</UID>
            <Name>Owner</Name>
            <SelectionRequired>1</SelectionRequired>
            <ValueUID>75</ValueUID>
            <Value>Owner</Value>
            <ValueList>
               <ValueItem>
                  <ValueUID>1</ValueUID>
                  <Value>Administrator</Value>
               </ValueItem>
               <ValueItem>
                  <ValueUID>125</ValueUID>
                  <Value>Owner</Value>
               </ValueItem>
               <ValueItem>
                  <ValueUID>145</ValueUID>
                  <Value>Owner1</Value>
               </ValueItem>
            </ValueList>
         </Column>
      </Columns>
   </ProjectCodeValues>
</Reply>

To change the owner for this project, the ProjectCodeValuesUpdate method of PDS can be used. The Value UID for this column 99999 refers to Web Resource Id (WRES_ID) of the resource to be set as Owner. Any person can be set as owner (its not restricted to the ones in the value list), by providing their WRES_ID as Value UID. The valuelist lists all the resources who belong to Administrators Security Group.

Note that the Reply string for ProjectCodeValues contains the ValueUID same as the RES_UID whereas while updating the WRES_ID needs to be set. To get the WRES_ID for a resource, PDS Extender can be used. A PDS Extender request can look like,

<Request>
<GetWebResourceID>
<ResourceName>Akila</ResourceName>
</GetWebResourceID>
</Request>

Use the following stored procedure to get the Web Resource Id (WRES_ID) in SQL:

CREATE PROCEDURE GetWebResourceID
@ResourceName varchar(255)
AS
BEGIN
SELECT wres_id FROM msp_web_resources
WHERE RES_NAME = @ResourceName
for xml auto
END
GO

Since PDS reply is in Xml Format, the data from SQL has to returned in Xml Format. Xml Auto returns the dataset as Xml String. Now get the WRES_ID and make a call to ProjectCodeValuesUpdate method, supplying the new Web Resource Id. Before the method can be executed, the project has to checked out. To checkout the Project, AutoCheckOut can be set to 1 or ProjectCheckout method can be used.

Now the Owner is set and project checked in. When you make another call to ProjectCodeValues method, the ValueUID contains the RES_UID of the resource whom you set as owner and not the WRES_ID (I know its a bit confusing, but that's the way its programmed).

Akila.

Posted Jun 26 2006, 03:26 PM by Unclaimed Blog with no comments

Creating Master & Sub Projects Programmatically in Project Server 2003

Project Server 2003 does not actually provide much capabilities around master and sub projects, but we can still leverage the available capabilities.

To create Master and Sub Projects in Project Server 2003 programmatically, Project Data Services (PDS) cannot be used and hence we have to write PDS Extenders for that. With PDS Extenders you need to know the tables that has to be updated, as Extenders are nothing but a set of stored procedures which will be called in a set of methods and the methods in turn will be executed by the PDS Web Service itself. Well, I'm not going into the details of how to write a PDS Extender or how to register it so that the PDS Web Service can find the methods and execute them.

There are three tables that need to be edited/updated while creating Master & Sub Projects:

MSP_TASKS, MSP_TEXT_FIELDS, MSP_PROJECTS.

Here's the steps that needs to be followed:

1. Create a task in the MSP_TASKS table. The columns that need to be set are:
PROJ_ID -> Valid PROJ_ID in the MSP_PROJECTS table. This will be PROJ_ID of the master project.
TASK_UID -> Unique Identifier for the task. This will be created automatically if not provided.
TASK_ID -> The position identifier of the task within the list of tasks for that project.
TASK_NAME -> Name of the task. In this case, name of the project that needs to be inserted.
EXT_EDIT_REF_DATA -> Must be set to 1 to indicate this task is a new task.

2. Create an entry for that task in the MSP_TEXT_FIELDS table. The columns that need to be set are:
PROJ_ID -> Refers to a valid PROJ_ID in the MSP_PROJECTS table. This will be the PROJ_ID of the master project.
TEXT_CATEGORY -> Set this to 0 to indicate a task.
TEXT_REF_UID -> Refers to a valid UID in the MSP_TASKS table. Give the UID of the task created in Step 1.
TEXT_FIELD_ID -> For inserted projects, this ID will be set as 188743706.
TEXT_VALUE -> The name of the project to be inserted or the name of the task created in Step1.

3. Update the MSP_PROJECTS table for Project to know that data has been edited externally
PROJ_EXT_EDITED -> Must be set to 1 for Project to process.
PROJ_EXT_EDITED_TEXT -> Must be set to 1 for Project to process.

After preforming these editions, when you open the project using Microsoft Project Professional you can see that the project contains inserted project (along with the inserted project's tasks) listed. Also, when you make changes to the inserted project, the changes will be applied to the actual project and vice versa.

Enjoy creating inserted projects!
Ciao, Akila.

Posted Jun 13 2006, 08:45 AM by Unclaimed Blog with no comments

Apostrophe in SQL Search String

Lot of times, we come across strings that contain Apostrophe (') like David o' Sullivan, and we may have to do search operation or insert/update operation with them.

When you try to insert a string that contains apostrophe using the insert statement,
insert in to Names(FirstName) values('David o'Sullivan'), the insert statement fails. This is because, the string is split into two parts with the first part containing 'David o' and the second part Sullivan'. There is no starting apostrophe for the second part of the name and hence the insert failed.
Modify the insert statement as insert in to Names(FirstName) values('David o''Sullivan')
and it works fine.

Same is the case with search queries, when you want to find an entry for David o' Sullivan in the table, the search query should be like this:
select * from Names where FirstName like 'David o'' Sullivan' and you get the exact entry.

The same issue occurs when you try doing a select operation on a DataTable in C#.Net. When you try executing the statement,
DataRow []ResultSet = DataTableName.Select("FirstName = 'David o'Sullivan'"); the operation fails.

Now do the following:

string SearchString = "David o'Sullivan";
SearchString = SearchString.Replace("'","''"); //Replace the single apostrophe with double
DataRow []ResultSet = DataTableName.Select("FirstName = '" + SearchString + "'");

The statement is executed and the result set returned if the name exists.

Posted Jun 12 2006, 12:48 PM by Unclaimed Blog with no comments

SYSTEM ERROR: 0x80004005 when you try to save or publish a project in Microsoft Project Server 2003

When you try to save or publish a project to Microsoft Project Server 2003, you experience the following symptoms:

You receive the following error message in the Microsoft Spooler Error Message dialog box: System Error: 0x80004005
The Microsoft Windows Sharepoint Site is not automatically created for the project.

Cause:

This issue occurs when you try to use a fully qualified domain name (FQDN) to connect to the Microsoft Office Project Server 2003 Web site. For example, it occurs when you use a FQDN instead of a host name, such as: http://projects.projsvr2003.domain.com instead of http://<hostname>

Resolution:

To resolve this issue use one of the following:

Use a host name such as http://<hostname>/projectserver to connect to the ProjectServer website.
Use the WinHTTP Default Proxy Configuration Tool to add the FQDN to the Bypass list.

To add the FQDN to the Bypass list, follow these steps:

Click Start, Click Run, type cmd and then click OK.
Type the following command line and then press Enter:
Proxycfg -d -p * "<local>;servername of WSS;FQDN of WSS;ipaddress of WSS"
At the command prompt, type iisreset and then press Enter. The iisreset command stops and restarts the Microsoft Internet Information Services (IIS) so that the new settings will be applied.

Posted Jun 12 2006, 08:17 AM by Unclaimed Blog with 2 comment(s)

Microsoft Spooler Error: The manager cannot create the resource account (0x8c040017)

The manager cannot create the resource account (0x8c040017) - Microsoft Spooler Error

Many of us would have encountered this error, we just choose to ignore it and go ahead with saving and publishing the project. This error occurs when we try to create a local resource, assign it to tasks and then publish the project.

Cause:

You will get this error if you attempt to create a duplicate user account or a user account that is not specified correctly. This error also occurs when you have denied creating resource accounts from Microsoft Office Project under Server Configuration in PWA. This error can also occur when you try to publish projects to the Project Server database from Project Professional while you are in offline mode(not connected to the computer running Project Server 2003).

Resolution:

1. Double check that you have specified the correct domain\user name combination for the resource.

2. Log on to Project Web Access as an administrator. Click Admin, and then click Server Configuration. Under Select the features that you want to make available to users in Project Web Access , click Allow for Create Accounts from Microsoft Project permission. Save changes and log off from Project Web Access.

3. Restart Project Professional and connect to the computer running Project Server 2003.

Posted Jun 11 2006, 10:59 AM by Unclaimed Blog with no comments

The server instance specified could not be found.Please specify the port and server's address

All that I wanted to do was to move Sharepoint data that involves documents (including versions), risks, issues and other lists data from one sharepoint site to another sharepoint site. The catch here was the sharepoint sites were in different hosts and virtual servers. Note that both the virtual servers had IP Address as All Unassigned. I wrote code using the Sharepoint Object Model and when I ran to check my code, BAM the error pops up. When the source and destination sites are in the same host and virtual server there is no problem but when they are different the issues creeps up.

I tried using stsadm to backup and restore the sites (I was ready to do the manual work) but then to my surprise stsadm also gave me the same error. Back to sqaure one, I tried doing various options given in other blogs. The following are the fixes I tried:

1. Installed SP1 and then force upgraded all the site using the command stsadm -o upgrade -forceupgrade.

2. Followed the steps in Knowledge base article 832816 : http://support.microsoft.com/?kbid=832816. Since the IP Address was All Unassigned, I tried accessing the site using the virtual servername.

3. Gave anonymous access to the site I'm trying to access. Ran the Application Pool using powered user identity. I impersonated in my code with the same identity but of no use. Created a strong name key for my exe and then registered in the GAC of the server which I'm trying to access as it was mentioned that dll's under the GAC have full trust. Set the trust level in the web.config file of WSS to Full instead of Wss_Minimal.

Well none of the fixes worked out and here is what I did finally,

Created a site using the Sharepoint Object model without applying a template and then created a batch file with smigrate backup and restore commands for all the sites that I wanted to migrate and then ran the batch file. This is just a workaround and will post if I get any other fix for this problem.

Posted Jun 09 2006, 08:27 AM by Unclaimed Blog with no comments

Leverage Excel for data management in Sharepoint

Exporting data to Microsoft Excel is well-supported in SharePoint and makes graphing and printing convenient (via the Print with Excel and Chart with Excel options). But it's also possible (and may often be desirable) to export data to Excel just for the sake of manageability. The Excel Export function creates an Excel Web query linking to the original data. In this way, you can create spreadsheets that will accept data, and then push that data to SharePoint.

This can be done by generating an Excel spreadsheet, then linking the spreadsheet to SharePoint (by using Export and Link to Excel from a Datasheet Task Pane). Once this is done, data can be entered into the spreadsheet and pushed from the spreadsheet to Excel with the Synchronize List option.

Posted Jun 07 2006, 08:40 AM by Unclaimed Blog with no comments

Hello All

Hi All,

This is my first blogging experience and am going to share my experiece working with Windows Sharepoint Services 2003. Right now am confined to working with WSS 2.0 but next month I'll start working again with WSS 3.0 as well.

ciao,

Akila.

Posted Jun 06 2006, 02:41 PM by Unclaimed Blog with no comments

SharePoint Blogs

This Blog

Syndication

Recent Posts

Tags

Archives

akila's blog

June 2006 - Posts