Solved

Error Msg 1911

Posted on 2004-09-24
2
940 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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now