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. PeterYou need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).

-PatP|||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:CREATE TABLE dbo.foo2 (
fooId INT IDENTITY
, thingie TEXT
)
GO

ALTER TABLE dbo.foo2
ALTER COLUMN thingie VARCHAR(20) -- Server: Msg 4928, Level 16, State 1, Line 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.foo2The 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|||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.|||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')|||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|||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