Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Wink Unanswered: Incorrect Syntax Error

    hi. i am relatively new to sql. i am wondering how to resolve my "incorrect syntax error" on an IP Address string i am trying to add.
    the table has IPAddress as data type "text" and length 16 (which won't let me change the length). It seems that sql is looking for a data type with no more than one decimal (money, float, etc). How do i resolve this? thanks in advance. Peter

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to change the datatype of the column. This can be done using SQL Enterprise Mangler or the ALTER TABLE ALTER COLUMN command from SQL Query Analyzer.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    Hi. THanks for the reply. Well, i am new to sql but not that new. i have tried changing the data type a number of times. I tried varchar, text, nvarchar, and a few others. I did this in the designer view of the SQL EM. I still get the same response... "incorrect syntax error". For numerical data types that expect one decimal place, i understand this, but i don't get why i am getting this error for string-like data types such as var char, etc. more suggestions appreciated.


    Originally posted by Pat Phelan
    You need to change the datatype of the column. This can be done using SQL Enterprise Mangler or the ALTER TABLE ALTER COLUMN command from SQL Query Analyzer.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This requires the "digital ball pein" to fix it, because you can't change the type of a TEXT, NTEXT, or IMAGE column. The following script demonstrates:
    PHP Code:
    CREATE TABLE dbo.foo2 (
       
    fooId    INT        IDENTITY
    ,  thingie    TEXT
       
    )
    GO

    ALTER TABLE dbo
    .foo2
       ALTER COLUMN thingie VARCHAR
    (20)    -- ServerMsg 4928Level 16State 1Line 1
    --                                         Cannot alter column 'thingie' because it is 'text'.
    GO

    CREATE TABLE dbo
    .foo3 (
       
    fooId    INT        IDENTITY
    ,  thingie    VARCHAR(20)
       )
    GO

    INSERT INTO dbo
    .foo3 (thingie)
       
    SELECT thingie
          FROM dbo
    .foo2 
    The short answer is that you must build a new table, and copy the data from the old table into the new table. Then you should be good to go.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    hi. thanks again. i droped the table as you suggested and redesigned it
    with the IPAddress row as varchar(30) instead of text and ran the insert statement and sql is still complaining about anything that comes after the first decimal point in the IP address. for and inserted IP of 121.111.12.1 sql complains that there is an "incorrect syntax near '.12'".
    i am really stumped. if you have other suggestions, much appreciated.

  6. #6
    Join Date
    Mar 2004
    Posts
    12
    You never showed us what your insert statment looked like. I ran this and it worked fine.

    insert into foo3 ([thingie]) values('121.111.12.1')
    Nick

  7. #7
    Join Date
    Mar 2004
    Posts
    12
    Here's the whole thing(ie) ;-)

    CREATE TABLE dbo.foo3 (
    fooId INT IDENTITY
    , thingie VARCHAR(20)
    )
    GO
    insert into foo3 ([thingie]) values('121.111.12.1')
    GO
    SELECT [fooId], [thingie] FROM [TESTDB].[dbo].[foo3]
    GO
    Nick

  8. #8
    Join Date
    Mar 2004
    Posts
    12
    It would help if you posted the actual SQL command it's performing.

    From what you have said it looks like you aren't quoting the IP string.

    Since it's a VARCHAR field, you should insert/update the IP with single quotes around it.


    Sloppy oracle-centric SQL warning!

    insert into iplist using select 'SERVERNAME','121.111.12.1' from other_table
    Howard Nugent
    Sr. Business Application Analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •