1

Closed

SQL 2005 Issues

description

I been having issues running project for SQL 2005.
 
The first issue was because the following was missing from <ProjectFolder>\dbdiff-62258\DbDiffCommon\DataAccess\SqlCommand9.xml. (I do not know if this was left out intentionally, but after I copied this from SqlCommand10.xml and changed version to Sql2005, I was able to connect Server)
 
<SqlCommand>
<Type>Details</Type>
<Version>Sql2005</Version>
<Select>
  SELECT SchemaName=SCHEMA_NAME(o.schema_id),Name=OBJECT_NAME(m.OBJECT_ID),
  ObjectType=0,
  Text = CASE cast(isnull(objectpropertyex(m.object_id,'IsEncrypted'),0) as bit) WHEN 0 then definition ELSE '-- encrypted text' end
  FROM sys.all_sql_modules AS m
  JOIN sys.objects AS o ON o.object_id = m.object_id
  WHERE o.is_ms_shipped = 0
</Select>
</SqlCommand>
 
The second issue I am having and not being able to fix is the error I get when I click on the "Table" node under "Diff" tab. Please see the attachment for error details.
 
Any help in this matter would be a big help.
 
Thank You,
 
Deleep.

file attachments

Closed Jan 21, 2013 at 12:08 PM by Safi
fixed

comments

Safi wrote Aug 18, 2011 at 11:55 AM

Could give me the table script?

there is an error this part of the code because the default constraint of the field is null. Why? So please give me the table script.
Mail: istvan.safar at gmail.com

if (row.SchemaName == f.SchemaName)
                {
                    currentScript.field.AddElement(
                        f.ConstrNameIsGenerated
                            ? String.Format("DEFAULT {0}", row.Definition)
                            : String.Format("CONSTRAINT {0} DEFAULT {1}", f.ConstrName, row.Definition), false, true);
                }

wrote Aug 18, 2011 at 12:42 PM

dnair926 wrote Aug 18, 2011 at 12:42 PM

Thank you for looking into this. For now, I just did a workaround and added check for System.DBNull.Value before casting and return a default value if value is DBNull for all properties in file: dbdiff-62258\DbDiffCommon\DataAccess\DbObjectsDs.cs. The properties return the following default values based on return types:
bool = false
int = -1
string = ""

It seems to be working and I am not getting any errors, do you think there will be an issue with specifying the above default values when value is DBNull?

e.g.
        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
        public string Definition
        {
            get
            {
                //try
                //{
                if (this[this.tableDefaultCnst.DefinitionColumn] == System.DBNull.Value) {
                    return "";
                } else {
                    return ((string)(this[this.tableDefaultCnst.DefinitionColumn]));
                }
                //}
                //catch (global::System.InvalidCastException e)
                //{
                    //throw new global::System.Data.StrongTypingException("The value for column \'Definition\' in table \'DefaultCnst\' is DBNull.", e);
                //}
            }
            set
            {
                this[this.tableDefaultCnst.DefinitionColumn] = value;
            }
        }

Safi wrote Aug 19, 2011 at 11:52 AM

I think this is an logical error, because we have a default constraint without definition (We have constaint name, so we should have definition). Something is wrong with schema select. Could you give me de table definition?

dnair926 wrote Aug 19, 2011 at 12:54 PM

Sorry to waste your time, the problem was in the database. One of the bit columns had default value specified as 0 and in the schema it was coming up as ((0)). When I removed this default value, the code runs fine. Please see the before and after schema of that table below (problem was with [DefaultParty] column).

Before

CREATE TABLE [dbo].[A_My](
[MyKey] [int] NULL,
[ClientID] [int] NULL,
[DefaultParty] [bit] NULL CONSTRAINT [DF_A_My_DefaultParty]  DEFAULT ((0)),
[ActivityCodeListID] [int] NULL,
[TaskCodeListID] [int] NULL,
[TaskCode] [int] NULL,
[ActivityCode] [int] NULL,
[MyText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

After

CREATE TABLE [dbo].[A_My](
[MyKey] [int] NULL,
[ClientID] [int] NULL,
[DefaultParty] [bit] NULL,
[ActivityCodeListID] [int] NULL,
[TaskCodeListID] [int] NULL,
[TaskCode] [int] NULL,
[ActivityCode] [int] NULL,
[MyText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

wrote Jan 21, 2013 at 12:08 PM

wrote Feb 14, 2013 at 7:02 PM

wrote May 16, 2013 at 9:04 AM