This project is read-only.

Upgrade the assembly with a new function problem: Cannot find the object "dbo.T" because it does not exist or you do not have permissions.

Topics: Developer Forum
Sep 16, 2009 at 6:28 AM

Hi everyone,

I created a new function A in the MsSqlSpatial assembly, and tried to upgrade the assembly without using the msscmd to deploy it.

That's because it can consume lots of time for a large database.

What I did is alter the assembly and create the signature of the new function and run it:

=====================================================================================

Drop function ST.A

ALTER ASSEMBLY MsSqlSpatialLibrary  From 'E:\...\bin\Release\MsSqlSpatialLibrary.dll'
with PERMISSION_SET = UNSAFE, UNCHECKED DATA
Go

Create function ST.SnapToRoads_withBearing( @table_name nchar,  @column_name nchar ,  @ewkb varbinary, @a smallint)
RETURNS table( oid bigint, b float)
EXTERNAL NAME MsSqlSpatialLibrary.TableValuedFunctions.A
GO

=====================================================================================

The assembly has been upgraded with the new function A successfully. However when I run A:

Select * from A('Test.ROADS', 'the_geom', ST.PointFromText('POINT(888.7530 -111.8736)', -1), 1)                                                                                          Go

Got the following error:

=====================================================================================

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "A":
System.Data.SqlClient.SqlException: Cannot find the object "dbo.T" because it does not exist or you do not have permissions.
System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
   at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MsSqlSpatial.GeometryTable.GetPrimaryKeyColumn(SqlCommand command, String schemaName, String tableName)
   at TableValuedFunctions.SpatialQuery.Execute(SqlString table_name, SqlString column_name, SqlBytes ewkbMBR, SqlBytes ewkb, SqlString condition, Arguments args, ReaderHandler handler)
   at TableValuedFunctions.SpatialQuery.Execute(SqlString table_name, SqlString column_name, SqlBytes ewkbMBR, SqlBytes ewkb, Arguments args, ReaderHandler handler)

......................................

=====================================================================================

What does that "Cannot find the object "dbo.T" because it does not exist or you do not have permissions." mean?

Is there any better solution to upgrade with an assembly with new functions?

 

Cheers

Sep 16, 2009 at 10:55 AM
Edited Sep 16, 2009 at 11:40 AM

Hi Steven, it means there is a reference to an object in the "dbo" schema with the name "T" which cannot be resolved either because it doesn't exist or the permissions/schema of the requesting user does not allow access to it.
It may be a table, view, sproc, function, type, synonym etc.

Try running

select OBJECT_ID('dbo.T')

as the database owner - if it returns null the object doesnt exist - if it returns a number then the object does exist and you have permissions issues

hth jd