How can I get a reference of MsSqlSpatialLibrary.dll with all the referenced assemblies such as GeoAPI merged

Topics: Developer Forum, User Forum
Mar 26, 2009 at 12:07 AM
Edited Mar 26, 2009 at 12:09 AM
I downloaded both the last release version MsSqlSpatial-0.1.1 and the latest source code mssqlspatial-42843, installed the IlMerge and copied the Ilmerge.CSharp.targets to the MSBuild folder. Then created a new database called TestDB, and run the command "msscmd -deploy -server=myServer -db=TestDB" to deploy it using the last release version(MsSqlSpatialLibrary.dll v0.1.2589.24988) and imported all the table from a shape file successfully.

Then I added the reference of MsSqlSpatialLibrary(MsSqlSpatialLibrary.dll v0.1.2589.24988 SAFE) into my own sql server project using VS2005. And open the assembly MsSqlSpatialLibrary in object browser, there's no GeoAPI, and some other assemblies which I thought should be merged into MsSqlSpatialLibrary.dll.

I tried to rebuild the assembly MsSqlSpatialLibrary.dll, so I opened the source solution file MsSqlSpatial.sln using VS2005. There were GeoAPI and NetTopology Suite in object browser. So I rebuilt the solution in Release mode, and got the MsSqlSpatialLibrary.dll(v0.1.3372.21652) in bin/Release. Because this assembly is not the output
from a sql server project, so it is not in the "Add Reference->SQL Server->Component List" from my sql server proejct.

To make the new build of MsSqlSpatialLibrary.dll available, I ran the undeployed command, and change the path, to deploy it under "...\src\MsSqlSpatialConsole\bin\Release\":

Input:
msscmd -deploy -server="myServer" -db="TestDB"
Output:

MsSqlSpatial Command Line Tool (Build 0.1.3372.21656)
Copyright ? Ricardo Stuven 2006-2007
http://www.codeplex.com/MsSqlSpatial

Deploying to database 'TestDB' at STEVEN\SQLEXPRESS:

Creating scripts...
Establishing connection...
Dropping assembly dependencies...
Running script 1 of 112...
Running script 2 of 112...
Running script 3 of 112...
..............
0000000000000000000000000000000000000000000000000000000
                WITH PERMISSION_SET = SAFE'
IF (@@ERROR <> 0)
                ROLLBACK TRANSACTION

END
 ---> System.Data.SqlClient.SqlException: CREATE ASSEMBLY for assembly 'MsSqlSpa
tialLibrary' failed because assembly 'MsSqlSpatialLibrary' failed verification.
Check if the referenced assemblies are up-to-date and trusted (for external_acce
ss or unsafe) to execute in the database. ....
Then I tried to create the assembly from the SQL Server Management Studio Express:
Query Input:
USE [TestDB]
GO

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MsSqlSpatialLibrary')
   DROP ASSEMBLY MsSqlSpatialLibrary
GO

ALTER DATABASE [TestDB] SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY MsSqlSpatialLibrary FROM 'E:\...\MsSqlSpatial-42843
\src\MsSqlSpatialLibrary\bin\Release\MsSqlSpatialLibrary.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO

Output:
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'MsSqlSpatialLibrary' failed because assembly 'MsSqlSpatialLibrary' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database.

Is this caused by the .SNK file? How can I make the referenced assemblies in MsSqlSpatialLibrary are up-to-date and trusted?

Cheers
Developer
Mar 26, 2009 at 11:24 AM
Hi Steven, the verification issue is almost certainly related to the snk file. Try opening up the dll in reflector you should definately see GeoAPI and NTS (GisSharpBlog.NetTopologySuite) namespaces. BTW be careful if you are trying to update the dependencies they have changed since the ones in the dependencies folder were built and ILMerge can no longer merge them (the 1.7.x branch uses the latest dependencies but installation/uninstallation is considerably more difficult) hth jd
Developer
Mar 30, 2009 at 3:23 PM
Edited Mar 30, 2009 at 6:44 PM
Hi Steven, It appears there was an error which should be rectified now. Please download a fresh copy of the trunk and rebuild.. cheers jd

UPDATE: may require unsafe deployment
Apr 1, 2009 at 12:41 AM
Thanks for the update, John. But it still got the same problem on build mssqlspatial-48758.
Is there anything related to the rebuild configuration on Visual Studio 2005?
Cheers

Input:
==================================================================================
E:\...\mssqlspatial-48758\src\MsSqlSpatialConsole\bin\Release>msscmd -deploy -server="STEVEN\SQLEXPRESS" -db="MsSqlSpatial"
==================================================================================

Output:
==================================================================================
MsSqlSpatial Command Line Tool (Build 0.1.3378.20486)
Copyright ? Ricardo Stuven 2006-2007
http://www.codeplex.com/MsSqlSpatial

Creating scripts...
Establishing connection...
Dropping assembly dependencies...
Running script 1 of 112...
Running script 2 of 112...
Running script 3 of 112...

Unhandled Exception:  System.ApplicationException: Exception occured executing script:
IF (@@TRANCOUNT > 0)
  AND NOT EXISTS (SELECT name FROM sys.assemblies WHERE name=N'MsSqlSpatialLibrary')
BEGIN
EXEC sp_executesql N'
  CREATE ASSEMBLY [MsSqlSpatialLibrary] AUTHORIZATION [public]
  FROM N''E:\MSc\Software\mssqlspatial-48758\src\MsSqlSpatialConsole\bin\Release\MsSqlSpatialLibrary.dll''
  WITH PERMISSION_SET = SAFE'
IF (@@ERROR <> 0)
  ROLLBACK TRANSACTION
END
 ---> System.Data.SqlClient.SqlException: CREATE ASSEMBLY for assembly 'MsSqlSpatialLibrary' failed because assembly 'MsSqlSpatialLibrary' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database.
==================================================================================
Developer
Apr 1, 2009 at 9:17 AM
Edited Apr 1, 2009 at 9:18 AM
You are setting permission set to safe (or rather leaving it to the default setting). You need to set it to unsafe

E:\...\mssqlspatial-48758\src\MsSqlSpatialConsole\bin\Release>msscmd -deploy -server="STEVEN\SQLEXPRESS" -db="MsSqlSpatial" -deploy_permission=UNSAFE
(at your own risk)

I no longer have vs2005 installed and oinly build with 2008 but I dont think there should be any impact there.. hth jd