Migration to SQL Server 2k5

Hi all,

I have script written in SQL Server 2000.

When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".

But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.

1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'

"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"

2.Moreover none of the tables had been created in "Database->Tables->SystemTable".

In both XP and 2003 Server, While installing, i have choosen same options.

Please anybody could help me to resolve this issue.

Thanks and Regards,

Nilavu

Can you give detailed steps on what exactly you're doing, it's unclear to me what you mean by "migrate". Are you running setup/upgrade, which is failing?|||

Hi,

First i have to thank you for the valuable efforts.

Problem is not with upgrade/Setup of SQL Server 2005, With scripts. Whether the scripts written in SQL Server 2000 will run in SQL Server 2005 without doing any modification .

Here is the scenario.

Actually i have scripts that are written for SQL Server 2000.

Now my clients are using SQL Server 2005.

So i have to test whether my scripts could run in SQL Server 2005 without any modification.

When i tried to run the scripts in SQL Server 2005 installed in Windows XP OS, it is giving error.

At the same time there are no tables created in "Database->Tables->SystemTable".

But Same script is executed properly in SQL Server 2005 installed in Windows 2003 Server.

In this case tables like "sysobjects" etc are created in "Database->Tables->SystemTable".

Still you are not able to understand, Please send me mail.

Please suggest me whether i have to do any changes in script level or in settings of SQL Server 2005.

Moreover Being installed SQL Server 2005, Why tables like "Sysobjects" have been created in 2003 Server OS while not in XP.

Kindly clarify my doubts.

Thanks and Regards,

Nilavu

|||I don't think this has anything to do with sql server on a given OS. If you look at the error message closely, it says the alter table drop column failed because a check constraint exists. You have to drop the check constraint first before you can drop the column.|||

Hi,

Yes Greg, you are right, i'm getting the same error message.

But the problem is, the same scripts works fine in ,

1.Windows XP, SQL Server 2000

2.Windows 2003 Server, SQL Server 2005

But i'm getting error in Windows XP, SQL Server 2005.

Thats what i could not find the solution.

I would like to mention one more different behaviour in SQL Server 2005,

In Windows 2003 Server, SQL Server 2005, "Tables->SystemTables" system tables like "sysobjects" are created.

But in Windows XP, SQL Server 2005, There are no such tables in "Tables->SystemTables".

Could you explain me why it is behaving in two different ways?

Thanks and Regards,

Nilavu.

|||

Look at the error message closely, it says a check constraint exists. You cannot drop a column if a check constraint exists on that column. Please check to see if this check constraint exists in both databases, as well as whether your script is explicitly dropping this constraint. If your script is explicitly dropping this constriant, maybe the script is failing beforehand. If your script is not dropping this constraint, then your databases are not exactly in sync, which is why it is failing on one db and not the other.

|||

Hi,

Thanks Greg.

Before dropping the column, code which is trying to find check constraint fails.

The "info" column in the "sysobjects" table contains only zero values for all objects in case of SQL Server 2005.

But in case of SQL Server 2000, there are certain values have entered.

Could you tell me, Where are the "info" column's values are mapped? Which table and column i have to refer instead of "sysobjects->info".

Here is my code,

IF ((SELECT COUNT(sysobjectsconstraint.id) FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem') = 1)

BEGIN

SET @.sqlcommand = (

SELECT 'ALTER TABLE ' +

QUOTENAME(USER_NAME(sysobjectstable.uid)) + '.' +

QUOTENAME(sysobjectstable.name) +

' DROP CONSTRAINT ' +

QUOTENAME(sysobjectsconstraint.name)

FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem'

)

The "If" condition fails in case of SQLServer 2005, whereas in case of SQLServer 2000, i'm getting value of 1.

Thanks and Regards,

Nilavu.

|||Moving to TSQL forum, someone more familiar with metadata and system tables can offer a better answer.|||The issue is that you are using columns reserved for internal use in your query. So any assumptions made as to what values it can contain will be wrong in SQL Server 2005. It might even break in SQL Server 2000 with a future service pack for example. You can determine CHECK constraints on a table by using INFORMATION_SCHEMA.CHECK_CONSTRAINTS view along with INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view. Or just the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view with OBJECTPROPERTYEX metadata function to filter check constraints. This will work the same in SQL Server 2000/2005. Or you could use sp_helpconstraint system SP for example. It is best to use the documented objects so that your code works consistently and you don't rely on undocumented interfaces that can be modified anytime by us.|||

Hi,

Thank you for your timely help Umachandar.

Its working fine now.

Regards,

Nilavu.