473,883 Members | 1,613 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Query Error Help

1 New Member
When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefau lt'.

For some reason it is only returning the first 11 chars of the column name?

Any help would be greatly appreciated...

This query searches a DB and determines which columns are 'Invalid' if the column name is >30 and contains '_T' and then removes them and gives a report:

USE PM7Database


GO
--NOTE: Display to user a list of bad columns in the result set.
Select c.table_name, c.column_name, charindex('_T', c.column_name) as Char_Index, substring(c.col umn_name, charindex('_T', c.column_name), 100) as Bad_Col_Portion , len(substring(c .column_name, charindex('_T', c.column_name), 100)) as Bad_Col_Port_Le ngth
from information_sch ema.columns as c
Inner Join
information_sch ema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c .column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1, 2

--NOTE: Display a count of all columns in the database.
-- Used to help validate that only appropriate fields were removed.
Select 'Column Count of all Columns in the Database: ' + Convert(Varchar (50), Count(*)) as 'Comment....... ............... ............... ............... Comment'
from information_sch ema.columns




-- SCRIPT SETUP!

Declare @Specific_Col varchar(100)
Declare @Specific_Table varchar(100)
Declare @Spec_Col_Eval varchar(100)
Declare @Char_Index_Eva l int
Declare @Spec_Tbl_Eval varchar(100)
Declare @DropStmt varchar(2000)
Declare @RowCount int
Declare @RowIncrement int
set @RowIncrement = 0

-- Table Variable setup to capture some of the process that goes on with this script.
Declare @TableResults Table
(
DropStmt varchar(2000),
DropStmtLength int
)


-- Worker Table Variable used to capture from the database all
Declare @TableInspect Table
(
[Database] varchar(70),
Table_name varchar(70),
Column_name varchar(100),
Char_Index int
)

Insert into @TableInspect
Select c.table_catalog , c.table_name, c.column_name, charindex('_T', c.column_name)
from information_sch ema.columns as c
Inner Join
information_sch ema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c .column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1 desc, 2


-- Set a counter for # of columns to drop.
-- Used for reporting AND required to make sure the last columns are actually dropped.
Select @RowCount = Count(*) from @TableInspect as [Comment........ ............... ............... ..............C omment]
-- Lets the user know how many columns are actually found and need to be dropped.
-- If UDF Fields are present, they will not be dropped but will be added to the counter.
Select 'Total Columns to Drop: ' + Convert(Varchar (100), @RowCount) as [Comment........ ............... ............... ..............C omment]


-- Cursor used to evaluate column names as completely valude to drop and set
-- up a drop statement to remove the columns. Pulls data from the Table Variable
-- that was declared above.
declare ColumnSearcher cursor for
Select Table_Name, Column_Name, Char_Index
from @TableInspect

set nocount on

open ColumnSearcher
-- These 3 variables used by the fetch are required in the evaluations below.
fetch next from ColumnSearcher into @Specific_Table , @Specific_Col, @Char_Index_Eva l
while @@fetch_status = 0


-- Begin Main search for erroneous columns.
Begin

-- This conditional statement ensures that that the Drop statement is prepared to be run.
-- Requirements for Drop Statement to run are:
-- Req: The Currently Fetched table must be a different name than the last fetched table.
-- Reason: Allows less actual hits to the database for columns to be dropped. Each Drop Statement
-- can contain 2000 characters. This allows for roughly 40 columns to be dropped from a single table.
-- That estimate is based on an average Column size using the following equation:
-- 15 (high avg. base column name size) + 34 (constant size of the erroneous column) = 49
-- 2000/49 = 40.8... round down to 40.
-- This is a limitation.
-- Req: The Last Fetched Table and the Currently Fetched table cannot be Null. If so then it is likely
-- the first row in the fetch. This is wehre @Spec_Tbl_Eval gets first assigned. By a null value of
-- the first row.

If @Spec_Tbl_Eval <> @Specific_Table and @Spec_Tbl_Eval IS NOT NULL and @Specific_Table IS NOT NULL
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt

-- Log into the @TableResults table variable the results of what the drop statement actually is dropping.
Insert @TableResults
Values (@DropStmt, len(@dropstmt))

exec(@DropStmt)

-- Force Drop Statement to be NULL every time it is executed. This allows a change to a different table
-- for every drop that is submitted to SQL Server.
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = @Specific_Table
End
else
Set @Spec_Tbl_Eval = @Specific_Table


-- This conditional statement is used to build the complete drop statement.
If @Char_Index_Eva l <> 0 and @Char_Index_Eva l IS NOT NULL
Begin

-- If the @DropStmt variable is null, then a beginning to the drop statement must first be added.
if @DropStmt IS Null
Begin

-- This block sets the beginning of the drop statement and evalutes the @Specific_Col which has been
-- deemed droppable to make sure it is not a UDF field.
if substring(@Spec ific_Col, 1, 4) <> 'udf_'
Begin
Set @DropStmt = 'Alter Table ' + @Specific_Table + ' Drop Column ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End
Else

-- Forces notification regarding UDF fields.
Insert @TableResults
Values ('UDF COLUMN SKIPPED - ' + @Specific_Col, 0)
Set @RowIncrement = @RowIncrement + 1

End
Else
Begin

-- This block adds onto an NOT NULL @DropStmt variable extra columns that need to be dropped from a specific
-- table.
Set @DropStmt = @DropStmt + ', ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End

End

-- Check for last drop statement and make sure it is executed.
If @RowIncrement = @RowCount
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt
exec(@DropStmt)
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = NULL
End

fetch next from ColumnSearcher into @Specific_Table , @Specific_Col, @Char_Index_Eva l
End

close ColumnSearcher
deallocate ColumnSearcher

-- Display the results of the @TableResults Table to the customer. Usefull for troubleshooting missing columns.
Select * from @TableResults

-- Final count of all columns with in the database. Note that UDF Fields have not been dropped so it may
-- appear that you have additional columns that still remain. Make sure to subtract from this number any
-- left over UDF Columns which you can find by looking at the @TableResults.
Select 'Total Columns Remaining: ' + Convert(Varchar (100), Count(*)) as 'Comment....... ............... ............... ............... Comment'
from information_sch ema.columns
May 17 '07 #1
0 4134

Sign in to post your reply or Sign up for a free account.

Similar topics

8
2200
by: Polar | last post by:
I am having troubles finding the parse error in this script. I've been checking for weeks. I am too new to the subject I guess. I am trying to show a readord and them have a form at the bottom of the page for inputting data to Update the record. I get a parse error that points to the last line in the script so I know it is something I am missing earlier in the script but doing a line by line it seems fine to me. Thanks for any...
6
29952
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access newsgroups, the access support centre, I can seem to find no similar situation. I am not using any references, or VBA at all in the first place. I am trying to set up a simple (or so I thought) query to work with the text of two tables. ...
4
2864
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table query. I'm having trouble doing it. Help! Here is my code so far: Sub OldRegionQuery()
3
12615
by: ILCSP | last post by:
Hello, I'm fairly new to the concept of running action pass through queries (insert, update, etc.) from Access 2000. I have a SQL Server 2000 database and I'm using a Access 2K database as my front end. I'm using a blank pass through query which gets the Transact-SQL part inserted from a button in my form. After inserting the Transact-SQL code into the pass through query, I 'open the recordset' to make the query run. However,...
36
3083
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I have set up a Query to show only records that meet a certain criteria...therefore excluding all of the records that do not meet this criteria (just for the record the criteria is any record within my database that falls within two months of its "Due...
5
7374
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the same command inside of mysql_real_query and I keep on getting this error back. "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
9
3067
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
10
6242
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this cross-tab query I am using a simple query with no grouping where I am filtering some data out in the criteria line. I have been out of access for a couple years but I remember in the past I had a solution for this but I can't remember. Any help...
4
7613
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to an excel spreadsheet. There are two tables. One is a list of general contacts, and the other is a list of clubs. The clubs contain members who are within the contacts table. When I add a list of new club members from the
9
8098
by: dizzydangler | last post by:
Hi, all! After receiving a lot of help from ADezzi, Msquared, and NeoPa getting my ms access 2007 db functioning, I've run into another hitch that I haven't been able to solve. The database itself is saved on a MS Sharepoint server, and I would like users to be able to access it and execute the search query in read-only mode. In its current configuration, the query only "works" when the file is opened in "Edit" mode, and I've already had some...
0
9940
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10743
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10415
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5991
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4220
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3232
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.