| Author |
Topic |
|
DianeDavis
Starting Member
6 Posts |
 Posted - 2007-10-24 : 17:43:33
|
| We have a script that drops the views on all the database tables and then creates them as part of version update of the database. For instance:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPONSOR_SYSTEM_TD]') and OBJECTPROPERTY(id, N'IsView') = 1)drop view [dbo].[SPONSOR_SYSTEM_TD]GOCREATE VIEW dbo.SPONSOR_SYSTEM_TD AS SELECT sponsor_installation_type_code,install_directory,sponsor_version,data_directory,system_install_text,record_status_code,mod_date_time,mod_login_id,create_date_time,create_login_id,replicate_to_server_flag,Sponsor_ID FROM SPONSOR_SYSTEMGOThis above code is done for all 120 tables and has worked fine until now. All our clients have been using SQL 2000. Now we are getting clients with SQL 2005. The problem is that for these clients using SQL 2005 on 20 of the tables we get the error: Could not resolve expression for schemabound object or constraint.And the View is not made for these tables. I saw where this could because the server and database collation is differemt but in this case it's the same for both. I can't see any differences so far between the tables that a View was successfully made for and the ones that it wasn't.What else should I be looking for that would give this error?Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
|
|
DianeDavis
Starting Member
6 Posts |
Posted - 2007-10-24 : 19:08:06
|
| Actually I've seen this blog. It talks about how the collations have to be the same. But in my case they are. There is something about the 20 tables out of 120 tables that is different. I don't know if it's something about the indexes or contraints or what. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-24 : 19:40:49
|
| Is it the Create View that is actually failing or the If Exists statement?If Exists(Select * from dbo.sysobjects where name = 'SponsorSystem_TD' and type = 'U')Drop View SponsorSystem_TDIt seems that error is related to the Exists statement, not the create view statement, as the exists statement is the only one with an expression. Unless one of the column names identified is a calculated column...that would also throw an error in some cases. |
|
|
|
DianeDavis
Starting Member
6 Posts |
Posted - 2007-10-24 : 19:47:07
|
| It's the Create View. I've even tried doing "New View" in Views on the database and trying to make it that way and I get the same "Could not resolve expression for schemabound object or constraint." error. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-24 : 19:48:04
|
| Also you might want to query INFORMATION_SCHEMA.Views instead of sysobjects ...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
DianeDavis
Starting Member
6 Posts |
Posted - 2007-10-24 : 20:07:31
|
quote:
Originally posted by dataguru1971
Is it the Create View that is actually failing or the If Exists statement?If Exists(Select * from dbo.sysobjects where name = 'SponsorSystem_TD' and type = 'U')Drop View SponsorSystem_TDIt seems that error is related to the Exists statement, not the create view statement, as the exists statement is the only one with an expression. Unless one of the column names identified is a calculated column...that would also throw an error in some cases.
What is a calculated column? What would it look like?As for expressions when I tried doing New View and doing it that way does that have expressions. It just seemed like you make a SELECT query and then save it as a View. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-24 : 20:20:31
|
| the underlying table may have a calculated column which in the table design would have a columnthat looked like[TotalBill] as Sum(CurrentDue + PastDue)instead of[TotalBill] numeric(12,2) nullfor example.I have never actually gotten that specific error, so I am shooting blanks here a bit. |
|
|
|
DianeDavis
Starting Member
6 Posts |
Posted - 2007-10-24 : 20:35:54
|
quote:
Originally posted by dataguru1971
the underlying table may have a calculated column which in the table design would have a columnthat looked like[TotalBill] as Sum(CurrentDue + PastDue)instead of[TotalBill] numeric(12,2) nullfor example.I have never actually gotten that specific error, so I am shooting blanks here a bit.
Nope. No calculated columns in the 20 tables or any or our tables.Shooting at blanks is a greate description of this problem! Or even shooting in the dark would be better. I've thought about taking off the indexes one by one on a table and seeing if that makes a difference. It just doesn't make sense. Some of the 20 tables are our biggest and most complicated with a lot of columns, dependencies, etc. Then others are very simple like this sponsor_system. It has and will only ever have one record in it. |
|
|
|
DianeDavis
Starting Member
6 Posts |
Posted - 2007-10-25 : 11:11:21
|
| I've actually figured out the problem. I did New View and started creating the view one column at a time. I was able to save the view until I got to a column that had a User Defined Data Type called desc_text that is a varchar(1000). What has happened is we have had to change the size of some of these type of columns to 255. When this was done the column was still defined as a desc_text but the size was 255. SQL 2000 didn't care about this but I guess SQL 2005 does. Once I changed the column to just varchar I was able to add it to the view.Thanks to everyone' help on this. |
|
|
|
|
Create View error