Solved

Error Msg 1911

Posted on 2004-09-24
2
953 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

867 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

16 Experts available now in Live!

Get 1:1 Help Now