October 24, 2007 at 5:03 pm
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]
GO
CREATE 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_SYSTEM
GO
This 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
October 24, 2007 at 5:32 pm
In doing a Google search, I saw some reports of this happening on several forums and blogs when the database and server collations don't match up. Could that be the case?
K. Brian Kelley
@kbriankelley
October 24, 2007 at 5:56 pm
K. Brian Kelley (10/24/2007)
In doing a Google search, I saw some reports of this happening on several forums and blogs when the database and server collations don't match up. Could that be the case?
As I said in my original post I this isn't the case. First thing I did was Google the error message and I got to the same blog that talks about that the server and database collations mus be the same. They are both SQL_Latin1_General_CP1_CI_AS.
Besides if they were different then I would be getting the error on all 120 CREATE VIEWs instead of just 20.
October 25, 2007 at 7:55 am
Check to see if anyone has set collation on the columns different than the collation for the server. That would explain why only some of them are having the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2007 at 8:48 am
Grant Fritchey (10/25/2007)
Check to see if anyone has set collation on the columns different than the collation for the server. That would explain why only some of them are having the issue.
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply