My MSSQl Server Problem

Hi,

There shouldn't be ( ) in the alter table statement, also there should not be any width specifier on bigint:

ALTER table gradmin ALTER COLUMN user_id BIGINT Not Null

/peter

what about the length it goes along with the BIGiNT like BIGINT goes with the length of 4. Then can we write the command as:

ALTER table gradmin ALTER COLUMN user_id BIGINT(4) Not Null

Is this the way to go around

No you shouldn't give any length on bigint. Bigint uses 8 bytes for storage, int uses 4 bytes.

If you wan't only 4 digits, you may use numeric

ALTER table gradmin ALTER COLUMN user_id NUMERIC(4,0) Not Null

well it means i should not give any length for any data type i use, because i am preparing a front end which gets the values from the user to enter and alter the table database. Only things make workable will be the column name and datatype with null or not null attribute

For the datatypes numeric and decimal you could supply length, as well as for the different char/binary datatypes. Float could take a value for the number of bits used to store the value.

Read about it here: http://msdn.microsoft.com/en-us/library/ms187752.aspx

might be as simple as you are missing "," between

ALTER table #list_name# ALTER COLUMN  (#ListGetAt(form.tcolumnName, i)#, #ListGetAt(form.fieldtype, i)#,
(#ListGetAt(tlength, i)#), #ListGetAt(form.tIS_NULLABLE, i)#)

no sorry...misread the code

Ok i tried removing the length attribute and moved with it

i get following error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The object 'PK_gradmin' is dependent on column 'user_id'.
 
The error occurred in C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1
Called from C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

38 :
39 :       <cfquery....>
40 :     <cfloop from="1" to="#listLen(form.tcolumnName)#" index="i">
41 :       ALTER table #list_name# ALTER COLUMN #ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)#
42 :     #ListGetAt(form.tIS_NULLABLE, i)#

SQLSTATE         HY000
SQL          ALTER table gradmin ALTER COLUMN user_id BIGINT Not Null ALTER table gradmin ALTER COLUMN user_name VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_pass VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_email VARCHAR Null ALTER table gradmin ALTER COLUMN rolemodal TINYINT Null ALTER table gradmin ALTER COLUMN super VARCHAR Null
VENDORERRORCODE         5074
DATASOURCE         t1

Now your sql-statement is ok.
The error comes from the column 'user_id' having a constraint 'PK_gradadmin' (the primary key). Having a constraint prevents you from doing an alter. What you can do is drop your constraint alter column and then recreate the constraint.  

and what would be the best way to do it Pleae tell me

Well for my each table this error is poping out:

 Error! [Macromedia][SQLServer JDBC Driver][SQLServer]The object 'PK_grfaqs' is dependent on column 'faq_id'. Error Executing Database Query.

either i change the datatype of other colums not of primary or foreign keys but other colums, still i get the above error for every query

i run

Do you always know the structure of the tables you want to change? If thats's the case it's just a question of dropping the constraint, alter the column and then adding the constraint again.

But if this is a general tool, where you don't know the structure, then it's a more complex question.

With all due respect, the construction of valid DDL is far, far more complex than you may want to know.  You've already seen a couple of syntax variations across datatypes and constraints that interfere.  That is just the beginning of the long and arduos road ahead.  Don't even get me started on the security implications of generating DDL from a web-facing app.

I wonder, what you are up to and if there isn't a better approach.

Hi,

Agree with dqmq. DDL from a web-page is difficult. Wouldn't it be easier to simply run these scripts from Query Analyser or SQL Server Management Studio?

Cheers
  David

I agree, but i have a build a simple application for sql server where i can do small tasks like adding new columns, drop a colum and edit existing colums. By all means i have made it very simple to restrict user to not to delete ptimary key, cannot change column name, just can change datatype etc.

but every time i execute the mssql query for editing the already listed table for datatype change of a specific column, it generates the above and it do for each and every table i go for.,

I under stand the complexity of making an web interface for manging database from front end is entirely a cumbersome task, but i have to build it small and just for a little updation/deletion and still that does not workin

>I agree, but i have to build a simple application...

The point is, as "small" as this application may seem, it is by no means "simple".  

The error occurs because you are trying to alter a column that is constrained by 'PK_grfaqs'.  We assume that is a primary key constraint because of the way it is named, but it could be something else.

What is the full definition of the faqs (just guessing) table and what is the alter command you are attempting to run against it.

 

As u read above  commnents, the alter command is:

Ok i tried removing the length attribute and moved with it

i get following error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The object 'PK_gradmin' is dependent on column 'user_id'.
 
The error occurred in C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1
Called from C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1

38 :
39 :       <cfquery....>
40 :     <cfloop from="1" to="#listLen(form.tcolumnName)#" index="i">
41 :       ALTER table #list_name# ALTER COLUMN #ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)#
42 :     #ListGetAt(form.tIS_NULLABLE, i)#

SQLSTATE         HY000
SQL          ALTER table gradmin ALTER COLUMN user_id BIGINT Not Null ALTER table gradmin ALTER COLUMN user_name VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_pass VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_email VARCHAR Null ALTER table gradmin ALTER COLUMN rolemodal TINYINT Null ALTER table gradmin ALTER COLUMN super VARCHAR Null
VENDORERRORCODE         5074
DATASOURCE         t1

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1
Called from C:\Inetpub\wwwroot\index.cfm: line 40
Called from C:\Inetpub\wwwroot\index.cfm: line 28
Called from C:\Inetpub\wwwroot\index.cfm: line 1
 
38 :
39 :       <cfquery....>
40 :     <cfloop from="1" to="#listLen(form.tcolumnName)#" index="i">
41 :       ALTER table #list_name# ALTER COLUMN #ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)#
42 :     #ListGetAt(form.tIS_NULLABLE, i)#
 
SQLSTATE         HY000
SQL          ALTER table gradmin ALTER COLUMN user_id BIGINT Not Null ALTER table gradmin ALTER COLUMN user_name VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_pass VARCHAR Not Null ALTER table gradmin ALTER COLUMN user_email VARCHAR Null ALTER table gradmin ALTER COLUMN rolemodal TINYINT Null ALTER table gradmin ALTER COLUMN super VARCHAR Null
VENDORERRORCODE         5074
DATASOURCE         t1

Select allOpen in new window

You are not allowed to alter column gradmin.user_id without first dropping the object PK_gradmin. It is probably a primary key constraint.

 

Another few issues:

------------------------------------------------------------------
ALTER table gradmin
ALTER COLUMN user_name VARCHAR Not Null
------------------------------------------------------------------

The USER_NAME ( [ id ] ) is a function in SQL so really should be surrounded by square brackets -- [user_name] -- like that.

Then if you have a column that is pre-existing and has nulls in it, then you would have an error from nulls. In addition, if you are changing VARCHAR to some other  numeric data type (bigint, money, etc.) and have text characters in it -- it will also error.

This is not a small undertaking. You have to validate what you are doing, before trying to make these changes.

what should i try do: i think there is no way

wht  u say guys

As so often happens on this forum, after the project starts in one direction, the question gets focused on a technical glitch.  Sometimes, the best solution is to change directions.   But we don't know the BIG picture.  We don't know what you are trying to accomplish.  We only know you want a form that you can alter every column in a table.  I can't even imagine a reason for developing an application like that.    You need to tell us.

Well I explain a bit..

The relationship is is such a manner that when user account is deleted, his/her listings also get deleted, using ca cascade on them.

the above my comment is incorrent, sorry wrong post

Disregard the above comment

OK well i explain a bit...

I have 10 records listed and sql server do not let me to run a single command to run and update the query.

for every record i have to run as a separate alter statement.

and running 10 alter statements and then finding a primary key to drop and create troubling me.

if u see above code u will i am getting 10 columns to alter simultaneously.

So this is the cause.

"I am looking for a proper solution here, i also do not want to end the question with no solid answer, but did not seems to be working out"

You want to update data in the table?

That is completely different than the commands you are running -- you are changing the structure of the tables, not changing data.

The four standard DML (Data Manipulation Language) commands are SELECT, INSERT, UPDATE and DELETE.

To update more than one field is something like:
---------------------------------------------
UPDATE MyTable
SET Field1 = 0,
       Field2 = 'my text',
WHERE MyPK = 'SomeKey'
---------------------------------------------

No Mate, I am changing the datatype not the data in the table, if u see my above posts, i have been running the alter command to change structure and then there it is displaying and error of primary key:

I list here for ur refernce

<
cfloop 
from
=
"1"
 to
=
"#listLen(form.tcolumnName)#"
 index
=
"i"
>


41
 
:
       ALTER table 
#list_name# ALTER COLUMN #ListGetAt(form.tcolumnName, i)# #ListGetAt(form.fieldtype, i)#


42
 
:
     
#ListGetAt(form.tIS_NULLABLE, i)#

 
SQLSTATE         HY000
SQL          ALTER table gradmin ALTER COLUMN user_id BIGINT 
Not
 
Null
 
ALTER table gradmin ALTER COLUMN user_name VARCHAR 
Not
 
Null
 
ALTER table gradmin ALTER COLUMN user_pass VARCHAR 
Not
 
Null
 
ALTER table gradmin ALTER COLUMN user_email VARCHAR 
Null
 
ALTER table gradmin ALTER COLUMN rolemodal TINYINT 
Null
 
ALTER table gradmin ALTER COLUMN 
super
 VARCHAR 
Null

VENDORERRORCODE         
5074

DATASOURCE         t1

Select allOpen in new window

But changing the structure is not something to be done very often.  And then, there are good tools designed especially for that.   You don't need to reinvent the wheel.  

Please explain why you want to alter the structure.  Big picture.

Okay, This discussion is gets exited and interesting as all experts are presenting their views. I will all everything here from start to end what i am doing and why i wanna reinvent the wheel

• I know there are many tools
• i want to amend that i should list the table columns and its details
  
• Then i will only edit the DATA TYPES and if NULL or NOT NULL not the column Names
• reinventing due to some reasons that many open source applications are quite complex
• • so wanna make it simple
  • do make the alterations.
  • work in the web interface
  • Error is popping when i change datatypes of any column which is not a primary key
  • an every time error is coming out is due to primary key.
• Is this means that i have to put the drop constraint add constraint with every field
• and check through the <cfif> statement.

So this is the whole story

Regards

>> reinventing due to some reasons that many open
>> source applications are quite complex

There is a reason they are complex. Altering the structure of a table(s) should not be done lightly, especially by inexperienced users.

Fine, But wanna make that simple, well i think not working, so it should be closed

Please display the contents of:
 
'PK_gradmin'

Ok here i do it again:

user_id                          BIGINT                             Not Null                                 Primary key

Column Name:  	Data Type:  	Is Nullable:  	Mode:
			
			[Delete This Column]
			[Delete This Column]
			[Delete This Column]
			[Delete This Column]
			[Delete This Column]
 

Select allOpen in new window

<tr
>
        <td
><input
 name
="tcolumnName" 
id
="tcolumnName" 
value
="user_id" 
readonly
="yes" 
type
="text"
></td
>
        <td
><select
 name
="fieldtype" 
id
="fieldType"
>
            
              <option
 value
="BIGINT"
>BIGINT</option
>
            
              

Select allOpen in new window

                                        </select></td>

        <td
><input
 name
="tIS_NULLABLE" 
value
="Not Null" 
type
="text"
></td
>
        <td
>
          
            </td
>

Select allOpen in new window

      </tr
>

Select allOpen in new window

ok can we do thing like make a <cfif statement to find the if all columns are being altered and if it finds the one, it can drop the primary key and then create it again.

if is my approch good enouhg can i know how can i achieve this stuff