Solved

Error Msg 1911

Posted on 2004-09-24
2
975 Views
Last Modified: 2012-08-14
I hope someone can help me with this as my knowledge of SQL Server 2000 is limited. When I run the following script from our office it works fine, but when run on a customers site it returns this error.

Server: Msg 1911, Level 16, State 1, Line 31 - Column name 'DY_RULENO' does not exist in the target table.

This is the script which is run from query analyzer.

begin transaction
--select * from sysobjects where id = object_id(N'[dbo].[dy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
--drop table [dbo].[dy]
CREATE TABLE [dbo].[dy] (
[newtable] [int]
)
ALTER TABLE [dbo].[dy] ADD [dy_checkdt] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_checkdt] DEFAULT ('1900-01-01') FOR [dy_checkdt]
ALTER TABLE [dbo].[dy] ADD [dy_date] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_date] DEFAULT ('1900-01-01') FOR [dy_date]
ALTER TABLE [dbo].[dy] ADD [dy_entryid] [varchar] (200) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_entryid] DEFAULT ('') FOR [dy_entryid]
ALTER TABLE [dbo].[dy] ADD [dy_exdate] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_exdate] DEFAULT ('1900-01-01') FOR [dy_exdate]
ALTER TABLE [dbo].[dy] ADD [dy_key] [varchar] ( 78) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_key] DEFAULT ('') FOR [dy_key]
ALTER TABLE [dbo].[dy] ADD [dy_notes] [text] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_notes] DEFAULT ('') FOR [dy_notes]
ALTER TABLE [dbo].[dy] ADD [dy_owner] [varchar] ( 20) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_owner] DEFAULT ('') FOR [dy_owner]
ALTER TABLE [dbo].[dy] ADD [dy_ruleno] [numeric] ( 7, 0) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_ruleno] DEFAULT (0) FOR [dy_ruleno]
ALTER TABLE [dbo].[dy] ADD [dy_time] [varchar] ( 6) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_time] DEFAULT ('') FOR [dy_time]
ALTER TABLE [dbo].[dy] ADD [dy_type] [varchar] ( 1) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_type] DEFAULT ('') FOR [dy_type]
ALTER TABLE [dbo].[dy] ADD [dy_userid] [varchar] ( 20) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_userid] DEFAULT ('') FOR [dy_userid]
ALTER TABLE [dbo].[dy] ADD [recguid] [char] (36) CONSTRAINT [DF_dy_recguid] DEFAULT (newid()) CONSTRAINT [PK_dy] PRIMARY KEY
ALTER TABLE [dbo].[dy] DROP COLUMN [newtable]
CREATE INDEX [IX_dy_51] ON [dbo].[dy] ([DY_RULENO ])
--DROP INDEX [dy].IX_dy_51
CREATE INDEX [IX_dy_52] ON [dbo].[dy] ([DY_USERID ])
--DROP INDEX [dy].IX_dy_52
rollback transaction

This code should be run from our VFP application but is causing the same error so we have just tried to run it from query analyzer and we get a similar error but can find little information on it. If we remove the 2 Create Index commands it runs perfectly.

Any help on this would be greatly appreciated.

Thanks in advance.

Jon
0
Comment
Question by:coolpj
2 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12145234
the problem is that you need to issue the create index statements in a different sql batch or they will only be able to "see" to previous structure of the tables.

try this

CREATE TABLE [dbo].[dy] (
[newtable] [int]
)
ALTER TABLE [dbo].[dy] ADD [dy_checkdt] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_checkdt] DEFAULT ('1900-01-01') FOR [dy_checkdt]
ALTER TABLE [dbo].[dy] ADD [dy_date] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_date] DEFAULT ('1900-01-01') FOR [dy_date]
ALTER TABLE [dbo].[dy] ADD [dy_entryid] [varchar] (200) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_entryid] DEFAULT ('') FOR [dy_entryid]
ALTER TABLE [dbo].[dy] ADD [dy_exdate] [datetime] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_exdate] DEFAULT ('1900-01-01') FOR [dy_exdate]
ALTER TABLE [dbo].[dy] ADD [dy_key] [varchar] ( 78) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_key] DEFAULT ('') FOR [dy_key]
ALTER TABLE [dbo].[dy] ADD [dy_notes] [text] NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_notes] DEFAULT ('') FOR [dy_notes]
ALTER TABLE [dbo].[dy] ADD [dy_owner] [varchar] ( 20) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_owner] DEFAULT ('') FOR [dy_owner]
ALTER TABLE [dbo].[dy] ADD [dy_ruleno] [numeric] ( 7, 0) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_ruleno] DEFAULT (0) FOR [dy_ruleno]
ALTER TABLE [dbo].[dy] ADD [dy_time] [varchar] ( 6) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_time] DEFAULT ('') FOR [dy_time]
ALTER TABLE [dbo].[dy] ADD [dy_type] [varchar] ( 1) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_type] DEFAULT ('') FOR [dy_type]
ALTER TABLE [dbo].[dy] ADD [dy_userid] [varchar] ( 20) NULL
ALTER TABLE [dbo].[dy] ADD CONSTRAINT [DF_dy_dy_userid] DEFAULT ('') FOR [dy_userid]
ALTER TABLE [dbo].[dy] ADD [recguid] [char] (36) CONSTRAINT [DF_dy_recguid] DEFAULT (newid()) CONSTRAINT [PK_dy] PRIMARY KEY
ALTER TABLE [dbo].[dy] DROP COLUMN [newtable]
GO

CREATE INDEX [IX_dy_51] ON [dbo].[dy] ([DY_RULENO ])
--DROP INDEX [dy].IX_dy_51
CREATE INDEX [IX_dy_52] ON [dbo].[dy] ([DY_USERID ])
--DROP INDEX [dy].IX_dy_52
0
 

Accepted Solution

by:
JonNeale earned 250 total points
ID: 12187786
This looks like a Collation problem to me, check the default collation of the Cleints server against what you have set up. You are trying to create an index on DY_RULENO but the field is dy_ruleno if the collation setting is case sensitive then it will error.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How efficient to move databases to Azure? 5 51
Regarding Disk IO 3 40
SQL- GROUP BY 4 21
SSRS Page Header from Group Data 2 20
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question