This project is read-only.

Simple checkout and build instructions

Topics: User Forum
Mar 20, 2009 at 12:38 PM

Hi,

Earlier in the week I downloaded TortoiseSVN with the aim of checking out and building more up-to-date versions of MsSqlSpatial and SharpMap—both the 1.branches rather than the 2.x versions that are being developed. I managed to get the latter downloaded and built without any problems, but I’m unable to get MsSqlSpatial working.

Here’s what I did:

  1. Install TortoiseSVN

  2. SVN checkout of files at “https://MsSqlSpatial.svn.codeplex.com/svn/src” with default options
  3. Downloaded copies of the DLL files for GeoAPI and NetTopologySuite
  4. Opened up the project in VS2008 and pointed the missing references to the above DLLs
  5. Attempt to compile

I get the following error (plus several similar errors):

Error 1 The type or namespace name 'IMathTransform' could not be found (are you missing a using directive or an assembly reference?) G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialLibrary\UserDefinedFunctions\Editors\Transform.cs 36 56 MsSqlSpatialLibrary.VS2008

I can see that I’m missing a reference to SharpMap.CoordinateSystems—but I don’t have a DLL for that anywhere :s All I have is one called SharpMap.dll—so where do I get the missing DLL?

Is there anything else that I’m missing that will cause the build to go wrong?


Thanks,

Dylan

Mar 23, 2009 at 9:09 AM
Hi Dylan, all the required dependenices for MsSqlSpatial are in the directory https://mssqlspatial.svn.codeplex.com/svn/lib .Btw for sharpmap stick to the /trunk, for mssqlspatial stick to the /src (1.7.x works fine too but is more complex to install) hth jd
Mar 23, 2009 at 10:04 AM
Edited Mar 23, 2009 at 10:08 AM
Brilliant, thanks.
Mar 23, 2009 at 10:08 AM
Now that I’ve managed to compile it, how would I go about deploying it as an upgrade rather than a fresh install? I tried the regular “-deploy”, but when running script 3 or 4 it crashed and I got a command line window full of random characters!!
Mar 23, 2009 at 11:19 AM
Hi Dylan, are you sure they were random? I would expect a stack trace / exception message in the command prompt.. if so can you post it  cheers jd
Mar 23, 2009 at 11:25 AM
They looked random to me—it certainly wasn’t like any error message I could recognise anyway! It filled the screen several times over, so I didn’t actually get to see the error message that I’m sure was probably shown... I tried to ctrl+c before the screen got filled, but I couldn’t do it fast enough.

This is the sort of crap that I got:

000020B00076F230100060C160D38160000000809A31500000213040211042897020006000917580
D09088E69FE040A062DE0002A0000000104000013300F006700000043040011000203289B0200060
A06036F090100060B020728990200060C086FF80000066F360100060D027B6700000439110000000

So many screens full that I could no longer scroll back far enough to see any error messages, and so quickly that I couldn’t stop the program in time :(
Mar 23, 2009 at 11:30 AM
Hmmm, that may be the encoded binary of the dll being sent to the db.. I'm not sure though.. any chance you can try and run the installer through the visual studio debugger?
Mar 23, 2009 at 11:55 AM
After figuring out that I had to run Visual Studio as admin (otherwise it fell over when it came to connecting to the db) I ran the debugger and got the following error:


Exception occured executing script:

USE master

IF NOT EXISTS (SELECT name FROM sys.asymmetric_keys WHERE name=N'MsSqlSpatialLibraryKey')
CREATE ASYMMETRIC KEY [MsSqlSpatialLibraryKey]
FROM EXECUTABLE FILE = N'G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\bin\Debug\MsSqlSpatialLibrary.dll'

IF NOT EXISTS (SELECT name FROM sys.syslogins WHERE name=N'MsSqlSpatialLibraryLogin')
BEGIN
    CREATE LOGIN [MsSqlSpatialLibraryLogin] FROM ASYMMETRIC KEY [MsSqlSpatialLibraryKey]
    REVOKE CONNECT SQL FROM [MsSqlSpatialLibraryLogin]
    GRANT EXTERNAL ACCESS ASSEMBLY TO [MsSqlSpatialLibraryLogin]
END


The command line options were set to “-deploy -server=piglet -db=opus3 -deploy_permission=EXTERNAL_ACCESS”. Without the last parameter, Visual Studio showed a yellow arrow next to the throw statement, then hanged and crashed.

Mar 26, 2009 at 1:16 PM
Any luck with deciphering the above error? It means nothing to me :(
Mar 26, 2009 at 2:33 PM
Edited Mar 26, 2009 at 2:33 PM
Hi Dylan, that isnt the error message, it is the script that was executing at the time (or rather the error message part of it is not useful). I can only suggest that you try an undeploy before you try again.. hth jd
Mar 26, 2009 at 2:42 PM
Just tried that. Managed to undeploy the old version, but the new version still failed in the same place. So I tried to redeploy the old version, and although it redeployed correctly I’ve now got a database that won’t work as none of the tables were updated by the redeployment and so don’t contain anything other than oid and the_geom columns. Is there anything I can do to get the extents data back in there so the database works again?
Mar 26, 2009 at 2:48 PM
I also decided to have a look at the debugging in VS again, and I noticed an action to copy the exception to the clipboard, which I didn’t notice before. Is this of any help in figuring out what the problem was:


System.ApplicationException was unhandled
  Message="Exception occured executing script:\n\r\n\r\nUSE master\r\n\r\nIF NOT EXISTS (SELECT name FROM sys.asymmetric_keys WHERE name=N'MsSqlSpatialLibraryKey')\r\nCREATE ASYMMETRIC KEY [MsSqlSpatialLibraryKey]\r\nFROM EXECUTABLE FILE = N'G:\\Documents\\SVN\\MsSqlSpatial\\MsSqlSpatialConsole\\bin\\Debug\\MsSqlSpatialLibrary.dll'\r\n\r\nIF NOT EXISTS (SELECT name FROM sys.syslogins WHERE name=N'MsSqlSpatialLibraryLogin')\r\nBEGIN\r\n\tCREATE LOGIN [MsSqlSpatialLibraryLogin] FROM ASYMMETRIC KEY [MsSqlSpatialLibraryKey]\r\n\tREVOKE CONNECT SQL FROM [MsSqlSpatialLibraryLogin]\r\n\tGRANT EXTERNAL ACCESS ASSEMBLY TO [MsSqlSpatialLibraryLogin]\r\nEND\r\n\r\n"
  Source="msscmd"
  StackTrace:
       at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand command) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 210
       at MsSqlSpatial.Utility.Deployment.DeployAssembly(String assemblyFilename, String connectionString, String database, DeployPermission deployPermission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 80
       at MsSqlSpatial.Utility.Command.Deploy(ConnectionOptions connection, DeployPermission permission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Command.cs:line 18
       at MsSqlSpatial.Program.Deploy(ArgParser arg, ConnectionOptions sqlServerConnection) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 133
       at MsSqlSpatial.Program.Main(String[] args) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 36
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       Message="The certificate, asymmetric key, or private key file does not exist or has invalid format.\r\nCannot find the asymmetric key 'MsSqlSpatialLibraryKey', because it does not exist or you do not have permission.\r\nCannot find the login 'MsSqlSpatialLibraryLogin', because it does not exist or you do not have permission.\r\nCannot find the login 'MsSqlSpatialLibraryLogin', because it does not exist or you do not have permission.\r\nChanged database context to 'master'."
       Source=".Net SqlClient Data Provider"
       ErrorCode=-2146232060
       Class=16
       LineNumber=6
       Number=15208
       Procedure=""
       Server="piglet"
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
            at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
            at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
            at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand command) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 203
       InnerException:


To me it looks like it might be a permissions error, but I have to admit I don’t really understand it.
Mar 26, 2009 at 5:12 PM
Hi Dylan, are you running the console on the DB server? I think you have to to get the Login and Asymetric keys installed hth jd
Mar 26, 2009 at 6:21 PM
Yes. I’m running the console on my dev machine, which is also running my DB server. As regards installing the asymmetric and login keys, I have no idea what that means! Could you tell me what I need to do in order to install them?
Mar 26, 2009 at 6:31 PM
You shouldn't have to do anything - it should be done automatically. You can try opening up the master db, go to the security node >> open up the asymetric keys node and delete the entry for MsSqlSpatialLibraryKey if it exists..
Mar 26, 2009 at 6:35 PM
Had a look there, and there’s nothing listed under asymmetric keys.
Mar 26, 2009 at 6:41 PM
Open the MsSqlSpatial.dll with Reflector and make sure it is properly signed. It should have the PublicKeyToken=f5d26ab2beee04e5
Mar 26, 2009 at 6:55 PM
It appears to be signed correctly.
Mar 29, 2009 at 10:16 PM
Further to the error I was getting last week, I’m now able to get another error:


                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. CLR Verifier error messages if any wil
l follow this message
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000000B][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000021][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000026][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000035][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000003A][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000047][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000004C][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000d8][offset 0x0000001
4][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on t
he stack.
[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000d8][offset 0x0000006
9][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on t
he stack.
[ : MsSqlSpatial.Aggregate`1[T]::Read][mdToken=0x60000da][offset 0x00000007][fou
nd ref 'System.Object'][expected ...
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand comman
d) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:li
ne 203
   --- End of inner exception stack trace ---
   at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand comman
d) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:li
ne 195
   at MsSqlSpatial.Utility.Deployment.DeployAssembly(String assemblyFilename, St
ring connectionString, String database, DeployPermission deployPermission) in G:
\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 75
   at MsSqlSpatial.Utility.Command.Deploy(ConnectionOptions connection, DeployPe
rmission permission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utilit
y\Command.cs:line 18
   at MsSqlSpatial.Program.Deploy(ArgParser arg, ConnectionOptions sqlServerConn
ection) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 134

   at MsSqlSpatial.Program.Main(String[] args) in G:\Documents\SVN\MsSqlSpatial\
MsSqlSpatialConsole\Program.cs:line 36


This error occurs when I’m trying to deploy, and is shown after the blocks of seemingly random characters (which we decided may be the encoded binary) have finished flying past in the cmd window. I’ve checked to see whether the DLL is correctly signed with the public key token, and it appears to be, yet the above error seems to be saying that the DLL isn’t right somehow.

In addition, I realised that I haven’t mentioned that I’m using Vista x64 anywhere in the thread, so thought I should probably mention it now. Could this be affecting it?
Mar 30, 2009 at 10:04 AM
Out of curiosity I decided to see whether it would deploy on another machine—a Windows Server 2003 box that I have access to. It fell over at the exact same point, with the exact same error. So I’m almost positive that I must be doing something wrong in the compilation process, or I’m doing something wrong when it comes to deploying it!

As far as deploying goes, I’m following the same set of instructions that I did for the current release version (0.1.1?) that can be found on http://tinynation.com/wp/2007/01/07/spatial-data-support-for-sql-server-2005/ – has anything changed since then that I should be aware of?

The only thing I can think of with regards the build process is that I’m doing something wrong with ILMerge. I have to admit that I I’ve never used it before, and I simply followed the instructions in the readme.txt file (ie. copy a config file form /lib to somewhere else, and install something). Is there anything I’m missing here?

I’m really tearing my hair out here, and hoping I can get this sorted out :)

Thanks for the help, John—you’ve been very patient so far!
Mar 30, 2009 at 12:10 PM
Edited Mar 30, 2009 at 12:11 PM
I am guessing the problem is with the dependencies. They also need to be signed. Are you using the ones from the lib directory (which have been signed) or some other ones currently?
Mar 30, 2009 at 12:28 PM
Yes, I’m using the ones from the /lib directory from the SVN. I downloaded them all again just now to be on the safe side, recompiled and tried again, but I still got the same errors.

Would you be able to possibly compile it for me and upload it somewhere that I can download it from? (And also check that your compiled version will deploy for you) At least that I’ll be able to tell if it’s something I’m doing wrong in the compilation if your version will deploy for me, or if it won’t deploy then it’s something I’m doing wrong when deploying it! I know it’s a lot to ask, but I’m running out of ideas now...
Mar 30, 2009 at 4:21 PM
Hi Dylan, It seems there was an error which was only manifesting itself with a clean master db. This should be resolved now get latest of the trunk and try again.. hth jd
Mar 30, 2009 at 6:07 PM
Okay, we seem to be getting somewhere... I no longer get the error with the all the byte code filling the screen, but instead I’m getting:


System.ApplicationException was unhandled
  Message="Exception occured executing script:\n\r\nIF (@@TRANCOUNT > 0)\r\n\t\tAND NOT EXISTS (SELECT name FROM sys.assemblies WHERE name=N'MsSqlSpatialLibrary')\r\nBEGIN\r\nEXEC sp_executesql N'\r\n\tCREATE ASSEMBLY [MsSqlSpatialLibrary] AUTHORIZATION [public]\r\n\t\tFROM N''G:\\Documents\\SVN\\MsSqlSpatial\\MsSqlSpatialConsole\\bin\\Debug\\MsSqlSpatialLibrary.dll''\r\n\t\tWITH PERMISSION_SET = SAFE'\r\nIF (@@ERROR <> 0)\r\n\t\tROLLBACK TRANSACTION\r\n\t\r\nEND\r\n"
  Source="msscmd"
  StackTrace:
       at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand command) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 208
       at MsSqlSpatial.Utility.Deployment.DeployAssembly(String assemblyFilename, String connectionString, String database, DeployPermission deployPermission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 78
       at MsSqlSpatial.Utility.Command.Deploy(ConnectionOptions connection, DeployPermission permission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Command.cs:line 18
       at MsSqlSpatial.Program.Deploy(ArgParser arg, ConnectionOptions sqlServerConnection) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 133
       at MsSqlSpatial.Program.Main(String[] args) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 36
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       Message="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. CLR Verifier error messages if any will follow this message\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x0000000C][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x00000027][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x0000002C][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x0000003B][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x00000040][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x0000004E][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000af][offset 0x00000053][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000dc][offset 0x00000015][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000dc][offset 0x00000079][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on the stack.\r\n[ : MsSqlSpatial.Aggregate`1[T]::Read][mdToken=0x60000de][offset 0x00000008][found ref 'System.Object'][expected ..."
       Source=".Net SqlClient Data Provider"
       ErrorCode=-2146232060
       Class=16
       LineNumber=2
       Number=6218
       Procedure=""
       Server="localhost"
       State=2
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
            at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
            at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
            at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand command) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 201
       InnerException:


The above is the error I get when I debug through Visual Studio. It now appears to be getting as far as script 4 of 113, whereas before it only got to script 3. The following is the error I get when attempting to deploy via the command console, it’s slightly different so thought I’d post this too.


C:\MsSqlSpatial>msscmd.exe -deploy -server=localhost -db=opus3.1 -deploy_permission=EXTERNAL_ACCESS
MsSqlSpatial Command Line Tool (Build 0.1.3376.30364)
Copyright © Ricardo Stuven 2006-2007
http://www.codeplex.com/MsSqlSpatial

Deploying to database 'opus3.1' at localhost:

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

Unhandled Exception: System.ApplicationException: Exception occured executing sc
ript:

IF (@@TRANCOUNT > 0)
                AND NOT EXISTS (SELECT name FROM sys.assemblies WHERE name=N'MsS
qlSpatialLibrary')
BEGIN
EXEC sp_executesql N'
        CREATE ASSEMBLY [MsSqlSpatialLibrary] AUTHORIZATION [public]
                FROM N''C:\MsSqlSpatial\MsSqlSpatialLibrary.dll''
                WITH PERMISSION_SET = EXTERNAL_ACCESS'
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. CLR Verifier error messages if any wil
l follow this message
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000000B][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000021][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000026][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000035][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000003A][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x00000047][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.DatabaseHelper::SplitSchemaAndTableName][mdToken=0x60000ab][off
set 0x0000004C][found ref 'System.Object'][expected ref 'System.String'] Unexpec
ted type on the stack.
[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000d8][offset 0x0000001
4][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on t
he stack.
[ : MsSqlSpatial.Aggregate`1[T]::Initialize][mdToken=0x60000d8][offset 0x0000006
9][found ref 'System.Object'][expected ref 'System.String'] Unexpected type on t
he stack.
[ : MsSqlSpatial.Aggregate`1[T]::Read][mdToken=0x60000da][offset 0x00000007][fou
nd ref 'System.Object'][expected ...
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand comman
d) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:li
ne 201
   --- End of inner exception stack trace ---
   at MsSqlSpatial.Utility.Deployment.RunScript(String script, SqlCommand comman
d) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:li
ne 193
   at MsSqlSpatial.Utility.Deployment.DeployAssembly(String assemblyFilename, St
ring connectionString, String database, DeployPermission deployPermission) in G:
\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utility\Deployment.cs:line 73
   at MsSqlSpatial.Utility.Command.Deploy(ConnectionOptions connection, DeployPe
rmission permission) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Utilit
y\Command.cs:line 18
   at MsSqlSpatial.Program.Deploy(ArgParser arg, ConnectionOptions sqlServerConn
ection) in G:\Documents\SVN\MsSqlSpatial\MsSqlSpatialConsole\Program.cs:line 134

   at MsSqlSpatial.Program.Main(String[] args) in G:\Documents\SVN\MsSqlSpatial\
MsSqlSpatialConsole\Program.cs:line 36


It looks like a slightly more complete version of the error I was getting before the latest fix, but at least now there is no mention of asymmetric keys or logins!

Cheers,
Mar 30, 2009 at 7:41 PM
It seems Unsafe deployment is required a static object has probably crept in possibly in NTS.. I will look into it when I get a chance but it is not likely to be very soon.. hth jd
Mar 30, 2009 at 8:34 PM
That appears to have done it. Thanks.
Mar 31, 2009 at 3:59 AM

Hi - Could you help me in resolving following error. I'm getting it when I'm trying to import a shape file through VS deployment. Thanks in advance. - Surya.

Importing...

Unhandled Exception: System.Data.SqlClient.SqlException: A .NET Framework error
occurred during execution of user-defined routine or aggregate "MinX":
System.TypeInitializationException: The type initializer for 'UserDefinedFunctio
ns' threw an exception. ---> System.Security.SecurityException: That assembly do
es not allow partially trusted callers.
System.Security.SecurityException:
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly a
sm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, Secur
ityAction action, Object demand, IPermission permThatFailed)
   at UserDefinedFunctions..cctor()
System.TypeInitializationException:
   at UserDefinedFunctions.MinX(SqlBytes ewkb)
.

Mar 31, 2009 at 9:53 AM
Hi Surya, I am assuming that you have managed to install the assembly with less than UNSAFE permission. The only thing I can suggest at the moment is to run
ALTER ASSEMBLY MsSqlSpatialLibrary WITH PERMISSION_SET = UNSAFE
against the db (at your own risk ;) )
hth jd
Apr 2, 2009 at 6:33 PM
Thank you. It resolved the issue which I was getting.
But, I have one question. If we directly download the MsSqlSpatialLibrary from codeplex and instal it, does it instal 'Unsafe' library? or if we instal 'Unsafe' MsSqlSpatialLibrary, are there any issues with that?
Apr 3, 2009 at 10:42 AM
Edited Apr 3, 2009 at 7:23 PM
Hi umsurya, It depends what -deploy_permission argument you use when you run msscmd. By default it is safe, but you need at least External Access to load shapefiles from within the database
By giving your assembly unsafe permissions you are allowing unfettered access to more sensitive / dangerous actions - which is fine as long as the assembly is known not to do / be able to be hijacked by other assemblies to do any damage to your machine.
Certain things like writing to static fields in the sql-clr require unsafe access but to my knowledge the trunk version does not do this (1.7.x and 2 do) hth jd
Apr 18, 2009 at 5:41 AM

John,

Thanks for the information.

I am using  /src (1.7.x ) for building MsSqlSpatialLibrary. It is getting deployed and Shape files are getting imported properly.
But, it is not inserting any values for following columns
the_geom_Envelope_MinX

the_geom_Envelope_MinY<o:p></o:p>


the_geom_Envelope_MaxX
the_geom_Envelope_MaxY

Could you help in populating these fields.

Thanks in advance.
-Surya

Apr 20, 2009 at 11:38 AM
I am not sure why they haven't been set but you could run an update on the table

UPDATE tableName
SET  the_geom_Envelope_MinX  = ST.MinX(the_geom),
the_geom_Envelope_MaxX = ST.MaxX(the_geom) ...