Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error Msg 1911

Posted on 2004-09-24
2
Medium Priority
?
1,041 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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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