?
Solved

My MSSQl Server Problem

Posted on 2009-02-17
35
Medium Priority
?
485 Views
Last Modified: 2012-05-06
Hi I am running a alter command in mssql for every single column but i am getting error: as:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '('.
 
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


Well i assume that alter command is running for every column but it is still displaying erro, i cannot figure it out why?
38 : <cfelseif session.dsntype eq 'mssql'>
39 : 	<cfquery datasource="#session.newdsn#" name="change">
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)# (#ListGetAt(tlength, i)#) #ListGetAt(form.tIS_NULLABLE, i)#) 
42 : 	</cfloop>
 
SQLSTATE 	  HY000
SQL 	   ALTER table gradmin ALTER COLUMN (user_id BIGINT (4) Not Null) ALTER table gradmin ALTER COLUMN (user_name VARCHAR (255) Not Null) ALTER table gradmin ALTER COLUMN (user_pass VARCHAR (255) Not Null) ALTER table gradmin ALTER COLUMN (user_email VARCHAR (255) Null) ALTER table gradmin ALTER COLUMN (rolemodal TINYINT (10) Null) ALTER table gradmin ALTER COLUMN (super VARCHAR (10) Null)
VENDORERRORCODE 	  102

Open in new window

0
Comment
  • 17
  • 7
  • 5
  • +3
35 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 23658748
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

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23658872
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23658966
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23659067
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23659219
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
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 23659725
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)#)
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 23659730
no sorry...misread the code
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23660191
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23660464
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.  




0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23660725
and what would be the best way to do it Pleae tell me
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23667097
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23667906
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 23667918
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 23667954
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
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23668275
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 23671802
>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.

 
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23672503
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

Open in new window

0
 
LVL 42

Expert Comment

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

 
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23675838
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.

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23682878
what should i try do: i think there is no way

wht  u say guys
0
 
LVL 42

Expert Comment

by:dqmq
ID: 23683083
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.
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23688099
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.


0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23688104
the above my comment is incorrent, sorry wrong post

Disregard the above comment
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23688132
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"
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23691705
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'
---------------------------------------------
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23691920
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

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 23691984
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.

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23692201
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
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23692712
>> 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.
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23692832
Fine, But wanna make that simple, well i think not working, so it should be closed
0
 
LVL 42

Expert Comment

by:dqmq
ID: 23698521
Please display the contents of:
 
'PK_gradmin'
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23698576
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]
 

Open in new window

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23698578
<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
>
            
              

Open in new window

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

Open in new window

      </tr
>

Open in new window

0
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 23701449
Your error is caused by the constraint 'PK_gradmin'.  The best way to get to the bottom of it is to see the definition of that constraint.  What you show me is something else--I do not know what it is, but it is NOT a constraint definition.  The constraint definition will tell us exactly which columns are constrained and the reason for it.

Having said that, your naming convention implies that 'PK_gradmin' is a primary key constraint for the gradmin table.  So, let's assume that's correct. It still does not tell us which column is the primary key.  However, symptoms suggest that user_id is the primary key or at least part of the primary key.  So, let's assume that is correct, also.

Then, you cannot issue the alter command for the user_id column.  You have repeatedly said that you are not issuing the the alter for the primary key or foreign key, but I continue to see "alter user_id" in your DDL.   So that's just plain confusing.

Finally, I cannot hold back any longer.  I realize you are trying to make a complex task simple and I applaud you for that.  But that implies a lot of "intelligence" in your code to make up the difference.   By extension, your code will necessarily be more complex than the task you are trying to simplify.  So far, you are struggling with a very basic problem in the scope of what you are try to accomplish.  Trust me, the more challenging problems are yet to come.   Bluntly, but simply, I'm afraid you are in over your head.  


   
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 23806353
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

831 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