Solved

Error Msg 1911

Posted on 2004-09-24
2
1,008 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

624 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