Comments
12 comments
-
Oleg,
We have a few posts about this, if you see my post here, http://www.red-gate.com/messageboard/vi ... 7311#17311 it will give you some guidance as to what we require to debug this issue.
Thanks,
Jonathan -
Ok, Johnatan.
I'd like to add only:
The this SQL Compare behavoiur is different for Windows authentication and SQL authentication. -
Oleg,
Then this is very likely to be a permissions issue. Are both the users dbo of both the databases in the comparison? Also can you verify that this issue does not occur in SQL Compare v5?
Regards,
Jonathan -
I compare 2 the same databases.
SQL Compare 6
1. Using Windows Auth. for both db
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading constraints,
Unable to cast object of type 'System.DBNull' to type 'System.String'
2. Using SQL Server auth. for both db, user sa (!)
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading object text,
Object reference not set to an instance of an object.
SQL Compare 5
1. Using Windows Auth. for both db
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading constraints,
Specified cast is not valid.
2. Using SQL Server auth. for both db, user sa (!)
All ok, dbs compared successfully. (!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!)
Note. Windows user is not an admin on the sql server machine.
Regards,
Oleg. -
Oleg wrote:I compare 2 the same databases.
SQL Compare 6
1. Using Windows Auth. for both db
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading constraints,
Unable to cast object of type 'System.DBNull' to type 'System.String'
2. Using SQL Server auth. for both db, user sa (!)
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading object text,
Object reference not set to an instance of an object.
SQL Compare 5
1. Using Windows Auth. for both db
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading constraints,
Specified cast is not valid.
2. Using SQL Server auth. for both db, user sa (!)
All ok, dbs compared successfully. (!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!)
Note. Windows user is not an admin on the sql server machine.
Regards,
Oleg.
Hi Oleg,
there is a bug in SQL Server that does not allow non dbowners to explore the full schema. (not even datareader) This is especially a problem with UDTs. One can see this if he executes the SQL Statement on which SQL Compare falls over using both of the users (the admin and nonadmin). The result will be different
, very likely an important schema information, like a UDT owner, is returned as null due to the lack of permissions.
If you could send this info to me (what object, maybe what column is null) I'll try to find an alternative way to retrieve the schema.
Regards,
Andras -
Hi Andras,
I will try to catch this.
Regards,
Oleg. -
Hi Andras,
here are 2 statements
1
-- last for win auth.
SELECT so.name AS TableName,
sys.schemas.name AS TableOwner,
cc.name AS ConstraintName,
cc.object_id AS Constraint_id,
cc.is_not_for_replication AS NotForReplication,
cc.is_not_trusted AS WithNoCheck,
cc.is_disabled AS Disabled,
sc.name AS ColumnName,
cc.parent_column_id AS colid,
cc.definition AS Text
FROM sys.check_constraints cc WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=cc.parent_object_id
LEFT JOIN sys.schemas WITH (NOLOCK) ON sys.schemas.schema_id=so.schema_id
LEFT JOIN sys.columns sc ON sc.column_id=cc.parent_column_id AND sc.object_id=cc.parent_object_id
ORDER BY so.name, cc.name
2
-- last for sql auth user sa
SELECT object_id AS id, definition AS text, 1 AS colid, 1 AS number, CONVERT(bit, 0) AS Encrypted,
CONVERT(bit, OBJECTPROPERTY(object_id, N'IsDefaultCnst')) AS IsDefault,
CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,
NULL AS ctext
FROM sys.sql_modules WITH (NOLOCK) WHERE
(OBJECTPROPERTY(object_id, N'IsProcedure')<>0
OR
OBJECTPROPERTY(object_id, N'IsView')<>0
OR
OBJECTPROPERTY(object_id, N'IsTrigger')<>0
OR
OBJECTPROPERTY(object_id, N'IsInlineFunction')<>0
OR
OBJECTPROPERTY(object_id, N'IsScalarFunction')<>0
OR
OBJECTPROPERTY(object_id, N'IsTableFunction')<>0
OR
OBJECTPROPERTY(object_id, N'IsDefaultCnst')<>0
OR
object_id IN (SELECT object_id FROM sys.triggers WHERE parent_class=0)
)
UNION ALL
SELECT
object_id AS id, definition AS text, parent_column_id AS colid, 0 AS number,
CAST(0 as bit) AS Encrypted,
CAST(1 as bit) AS IsDefault,
CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,
NULL AS ctext
FROM sys.default_constraints WITH (NOLOCK)
UNION ALL SELECT
object_id AS id, definition AS text,
CAST(1 as int) AS colid,
procedure_number AS number,
CAST(0 as bit) AS Encrypted,
CAST(0 as bit) AS IsDefault,
CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,
NULL AS ctext
FROM sys.numbered_procedures WITH (NOLOCK)
ORDER BY id, colid, number
-
Hi Andras,
I'd like to add: SQL compare 5 is working good in my case but SQLCompare 6 is not.
Are useful catched queries?
Regards,
Oleg. -
Oleg wrote:Hi Andras,
I'd like to add: SQL compare 5 is working good in my case but SQLCompare 6 is not.
Are useful catched queries?
Regards,
Oleg.
Oleg,
I'll send you a patched version in a few days.
Regards,
Andras -
Just tried to do a compare on two databases and came up with the error
Aborting
panther\softline.CCR-Reading object text
Object reference not set to an instance of an object
In my SQL logs all I can see is
DBCC TRACEON 3604, server process ID (SPID) 55.
DBCC TRACEOFF 3604, server process ID (SPID) 55.
If you can tell me how to get the actual failed requests I can give you that as well.
Oh and this works fine with 5 and happens with the sa user or windows/auth.
If you would like the database as well please ask. -
I have pm'ed you about this.
Jonathan -
Hi,
after patching its working good. I checked all ways.
Regards
Add comment
Please sign in to leave a comment.
SQL Compare aborting, Registering databases
SQL-SRV1.db87-Reading object text,
...
Object reference not set to an instance of an object.
In other words I cannot compare any 2 databases.
Regards,
Oleg.