Deploying CLR to SQL Server (2005) for accessing Project Server Interface (2007)

[J.A. García Barceló]

Although project server databases are stored on the same SQL Server as our
corporate database, we need to add some business logic for interacting one
with the other. Since Project 2007 SDK states "we strongly advise developers
to use the PSI to access Project Server data in the Draft, Published, and
Archive databases rather than accessing the databases directly." that's what
we are trying to do: Use PSI WebServices from CLR procedures to
access/update project data.

When we try to deploy the CLR, we receive the following error message (exact
error messages may vary, since I have translated them from spanish):

[...]
Running deployment script: PreDeployScript.sql...
Deploying file: ProjectServerCLR.dll, path:
C:\...mypath...\ProjectServerCLR\ProjectServerCLR\obj\Release\ProjectServerCLR.dll...
Error: Assembly 'microsoft.office.project.server.library, version=12.0.0.0,
culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL
catalog.

So, I tried to manually excute the following T-SQL query:

CREATE ASSEMBLY MicrosoftOfficeProjectServerLibrary
FROM 'C:\\Project 2007
SDK\REDIST.TXT\Microsoft.Office.Project.Server.Library.dll'
WITH PERMISSION_S2007 Office System Developer ResourcesET = UNSAFE

And I receive:

Mesg. 10301, Level 16, Status 1, Line 4
Assembly 'Microsoft.Office.Project.Server.Library' references assembly
'microsoft.sharepoint.security, version=12.0.0.0, culture=neutral,
publickeytoken=71e9bce111e9429c.', which is not present in the current
database. SQL Server attempted to locate and automatically load the
referenced assembly from the same location where referring assembly came
from, but that operation has failed (reason: 2(The system cannot find the
file specified.)). Please load the referenced assembly into the current
database and retry your request.

So I did a search for Microsoft.Sharepoint.Security.dll and copied it onto
'C:\2007 Office System Developer Resources\Project 2007 SDK\REDIST.TXT\' and
retried the query. The second time, the missing assembly was System.Web.dll,
the third time System.Drawing.dll... After 13 attempts, the list of required
dlls to register assembly Microsoft.Office.Project.Server.Library in SQL
catalog is:

Microsoft.SharePoint.Security.dll
System.Design.dll
System.DirectoryServices.dll
System.DirectoryServices.Protocols.dll
System.Drawing.Design.dll
System.Drawing.dll
System.EnterpriseServices.dll
System.Runtime.Remoting.dll
System.Runtime.Serialization.Formatters.Soap.dll
System.Web.dll
System.Web.RegularExpressions.dll
System.Windows.Forms.dll
....

and we have not yet been able to register our assembly because we cannot
register Microsoft.Office.Project.Server.Library.dll either (another one is
missing).

Are we doing something wrong? Is this the correct way to create CLR
functions and stored procedures to interact with Project Server Interface?

We have managed to create a WinForm application to do all the logic we need,
but we cannot make the step into converting that logic into a CLR because of
this problem.

Any help would be greatly appreciated. Thanks in advance.

 

[Niels Berglund]

Although project server databases are stored on the same SQL Server as
our corporate database, we need to add some business logic for
interacting one with the other. Since Project 2007 SDK states "we
strongly advise developers to use the PSI to access Project Server data
in the Draft, Published, and Archive databases rather than accessing the
databases directly." that's what we are trying to do: Use PSI
WebServices from CLR procedures to access/update project data.

When we try to deploy the CLR, we receive the following error message
(exact error messages may vary, since I have translated them from spanish): [snip]
Error: Assembly 'microsoft.office.project.server.library,
version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was
not found in the SQL catalog.

So, I tried to manually excute the following T-SQL query:

CREATE ASSEMBLY MicrosoftOfficeProjectServerLibrary
FROM 'C:\\Project 2007
SDK\REDIST.TXT\Microsoft.Office.Project.Server.Library.dll'
WITH PERMISSION_S2007 Office System Developer ResourcesET = UNSAFE
[snip]
Are we doing something wrong? Is this the correct way to create CLR
functions and stored procedures to interact with Project Server Interface?

We have managed to create a WinForm application to do all the logic we
need, but we cannot make the step into converting that logic into a CLR
because of this problem.

Any help would be greatly appreciated. Thanks in advance.

Yes, you are going about it almost the right way. However, copy the the
Microsoft.Office.Project.Server.Library.dll and the
Microsoft.Sharepoint.Security.dll into
Windows\Microsoft.NET\Framework\v2.0.50727 and register the
Microsoft.Office.Project.Server.Library.dll from that location as per
your code above. SQL will automatically load the related dll's.

Niels

 

[J.A. García Barceló]

Thanks a lot for your answer... it seems so obvious now

However, our server is running x64 version OS and has both
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 and
C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727 directories.

Is Microsoft.Office.Project.Server.Library.dll supposed to be 32bit? Should
I copy it onto Framework (32) or Framework64 folder?

Besides that question, http://support.microsoft.com/kb/922672/en-us states
that only:
.. Microsoft.VisualBasic.dll
.. Mscorlib.dll
.. System.Data.dll
.. System.dll
.. System.Xml.dll
.. Microsoft.VisualC.dll
.. CustomMarshalers.dll
.. System.Security.dll
.. System.Web.Services.dll
.. System.Data.SqlXml.dll
.. System.Transactions.dll
.. System.Data.OracleClient.dll
.. System.Configuration.dll

are supported assemblies in the SQL Server 2005 CLR-hosted environment. If
so, how can I interact with PSI from SQL Server itself, without accessing
the tables directly in a supported way?

Thanks in advance.

Although project server databases are stored on the same SQL Server as
our corporate database, we need to add some business logic for
interacting one with the other. Since Project 2007 SDK states "we
strongly advise developers to use the PSI to access Project Server data
in the Draft, Published, and Archive databases rather than accessing the
databases directly." that's what we are trying to do: Use PSI WebServices
from CLR procedures to access/update project data.

When we try to deploy the CLR, we receive the following error message
(exact error messages may vary, since I have translated them from
spanish): [snip]
Error: Assembly 'microsoft.office.project.server.library,
version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was
not found in the SQL catalog.

So, I tried to manually excute the following T-SQL query:

CREATE ASSEMBLY MicrosoftOfficeProjectServerLibrary
FROM 'C:\\Project 2007
SDK\REDIST.TXT\Microsoft.Office.Project.Server.Library.dll'
WITH PERMISSION_S2007 Office System Developer ResourcesET = UNSAFE
[snip]
Are we doing something wrong? Is this the correct way to create CLR
functions and stored procedures to interact with Project Server
Interface?

We have managed to create a WinForm application to do all the logic we
need, but we cannot make the step into converting that logic into a CLR
because of this problem.

Any help would be greatly appreciated. Thanks in advance.

Yes, you are going about it almost the right way. However, copy the the
Microsoft.Office.Project.Server.Library.dll and the
Microsoft.Sharepoint.Security.dll into
Windows\Microsoft.NET\Framework\v2.0.50727 and register the
Microsoft.Office.Project.Server.Library.dll from that location as per your
code above. SQL will automatically load the related dll's.

Niels

 

[J.A. García Barceló]

Although most of the named 13 dlls are inside Microsoft.NET\Framework
folders, there are some of them that are just under C:\WINDOWS (such as
System.Web.dll for instance). I don't feel safe messing DLLs around and
copying them to locations where they should not be. If I copy any of the
required dlls under Microsoft.NET\Framework, any application that uses the
framework could (potentially) make use of them (and that is something we do
not want for a production server).

As far as I know CREATE ASSEMBLY copies the required DLL(s) inside the SQL
Server engine (tables?) so that there is no need to have them on a file to
be accessed anymore. Wouldn't be a safer approach what we where trying from
the begining? I mean keep on copying the required files (one by one) to an
temporary location (that would be removed after the CREATE ASSEMBLY works)?

I really miss some kind of step-by-step official documentation for this.

Thanks in advance.

Although project server databases are stored on the same SQL Server as
our corporate database, we need to add some business logic for
interacting one with the other. Since Project 2007 SDK states "we
strongly advise developers to use the PSI to access Project Server data
in the Draft, Published, and Archive databases rather than accessing the
databases directly." that's what we are trying to do: Use PSI WebServices
from CLR procedures to access/update project data.

When we try to deploy the CLR, we receive the following error message
(exact error messages may vary, since I have translated them from
spanish): [snip]
Error: Assembly 'microsoft.office.project.server.library,
version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was
not found in the SQL catalog.

So, I tried to manually excute the following T-SQL query:

CREATE ASSEMBLY MicrosoftOfficeProjectServerLibrary
FROM 'C:\\Project 2007
SDK\REDIST.TXT\Microsoft.Office.Project.Server.Library.dll'
WITH PERMISSION_S2007 Office System Developer ResourcesET = UNSAFE
[snip]
Are we doing something wrong? Is this the correct way to create CLR
functions and stored procedures to interact with Project Server
Interface?

We have managed to create a WinForm application to do all the logic we
need, but we cannot make the step into converting that logic into a CLR
because of this problem.

Any help would be greatly appreciated. Thanks in advance.

Yes, you are going about it almost the right way. However, copy the the
Microsoft.Office.Project.Server.Library.dll and the
Microsoft.Sharepoint.Security.dll into
Windows\Microsoft.NET\Framework\v2.0.50727 and register the
Microsoft.Office.Project.Server.Library.dll from that location as per your
code above. SQL will automatically load the related dll's.

Niels

 

[Niels Berglund]

Thanks a lot for your answer... it seems so obvious now

However, our server is running x64 version OS and has both
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 and
C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727 directories.

Is Microsoft.Office.Project.Server.Library.dll supposed to be 32bit?
Should I copy it onto Framework (32) or Framework64 folder?

Besides that question, http://support.microsoft.com/kb/922672/en-us
states that only:
. Microsoft.VisualBasic.dll
. Mscorlib.dll
. System.Data.dll
. System.dll
. System.Xml.dll
. Microsoft.VisualC.dll
. CustomMarshalers.dll
. System.Security.dll
. System.Web.Services.dll
. System.Data.SqlXml.dll
. System.Transactions.dll
. System.Data.OracleClient.dll
. System.Configuration.dll

are supported assemblies in the SQL Server 2005 CLR-hosted environment. If
so, how can I interact with PSI from SQL Server itself, without accessing
the tables directly in a supported way?

Those assemblies are the ones that can be loaded from the GAC by SQL
Server, all other assemblies have to be in the database. That's why you
have to do the original steps.

Niels

 

[Niels Berglund]

Although most of the named 13 dlls are inside Microsoft.NET\Framework
folders, there are some of them that are just under C:\WINDOWS (such as
System.Web.dll for instance).

System.Web.dll should be in the Framework folders as well. At least it
is on the various systems I have worked on

I don't feel safe messing DLLs around and
copying them to locations where they should not be. If I copy any of the
required dlls under Microsoft.NET\Framework, any application that uses
the framework could (potentially) make use of them (and that is
something we do not want for a production server).

After you have copied them and run the CREATE ASSEMBLY statement you can
delete them from the location.

As far as I know CREATE ASSEMBLY copies the required DLL(s) inside the
SQL Server engine (tables?) so that there is no need to have them on a
file to be accessed anymore. Wouldn't be a safer approach what we where
trying from the begining? I mean keep on copying the required files (one
by one) to an temporary location (that would be removed after the CREATE
ASSEMBLY works)?

From a "safety" perspective, no - as I said above; after you have run
the CREATE ASSEMBLY statement, you can delete them from the location to
where you copied them. In your scenario - most of the assemblies SQL is
complaining about are asemblies that are located originally in the
Framework folder - we're only talking about copying a couple of
assemblies - the Office and Sharepoint ones.

Niels

 

[J.A. García Barceló]

Ok, thanks a lot for your replies.

I have managed to create the assembly for
Microsoft.Office.Project.Server.Library successfully. Now the problem
appears when trying to create my own assembly (it compiles fine). The error
is:

CREATE ASSEMBLY failed because method "add_ResourcesRowChanging" on type
"ResourcesDataTable" in external_access assembly "ProjectServerCLR" has a
synchronized attribute. Explicit synchronization is not allowed in
external_access assemblies.

There are few references on Google for this error. Any idea? The assembly
must run in external_access because it is connecting to another server which
runs Projec Server 2007 webservices.

 

[Niels Berglund]

Ok, thanks a lot for your replies.

I have managed to create the assembly for
Microsoft.Office.Project.Server.Library successfully. Now the problem
appears when trying to create my own assembly (it compiles fine). The
error is:

CREATE ASSEMBLY failed because method "add_ResourcesRowChanging" on type
"ResourcesDataTable" in external_access assembly "ProjectServerCLR" has
a synchronized attribute. Explicit synchronization is not allowed in
external_access assemblies.

There are few references on Google for this error. Any idea? The
assembly must run in external_access because it is connecting to another
server which runs Projec Server 2007 webservices.

Create it with permission set UNSAFE instead of EXTERNAL ACCESS. UNSAFE
gives you everything that EXTERNAL ACCESS does and more.

Niels