Create type - incorrect syntax near type - MS SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create type - incorrect syntax near type - MS SQL

Author

Topic

organista18
Starting Member

4 Posts

Posted - 2010-03-08 : 08:20:45

Hi everyone!

I have a problem with creating types in the MS SQL Server Management
Studio 2005 Express under Microsoft SQL Server 2005 Enterprise. When i
try to execute this query:

CREATE TYPE T_CENA
FROM varchar(11) NOT NULL ;

I have an error received from MS SQL Server Management Studio, like
this: "Incorrect Syntax near TYPE".

When i try to add manually this type, its working fine. And when i try
to generate script after manually added this, i get something like
this SQL script in query window:

EXEC sp_addtype ssn, 'varchar(11)', 'NOT NULL';
GO

How to deal with this? MS SQL does not want to take the Create Type ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 08:42:33

You need to change the compatibility of the database to 90

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:44:49

check your current db version and compatibility level using below queries

SELECT @@VERSION

EXEC sp_dbcmptlevel 'your database name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

organista18
Starting Member

4 Posts

Posted - 2010-03-10 : 05:20:23

Thanks! But there is an another problem. MS SQL Server 2000 i CAN'T change compatibility level to 90. I was generated scripts in SQL Server Management Studio 2005 under SQLServer 2005 with compatibility to SQL Server 2000, and the types are generated ok (exec sp_addtype...).

But i have some new problems:
i have in the sql script line like this:

CREATE TABLE [dbo].[GNIAZDA_STANOWISKA] (
[ID_GNIAZDA_STANOWISKA] [T_TID] IDENTITY(1, 1) NOT NULL,
[ID_FIRMY] [T_TID] NOT NULL,
[ID_GNIAZDA] [T_TID] NOT NULL,
[ID_STANOWISKA] [T_TID] NOT NULL
)
ON [PRIMARY]
GO

And the error, that I was recaived from SQL Server Management Studio is like this:

Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('

Can anyone help me?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-10 : 05:41:08

How you have been created type T_TID AS for identity column it should be int, bigint, smallint, tinyint kind of type.

Vabhav T

organista18
Starting Member

4 Posts

Posted - 2010-03-10 : 06:08:34

I have been created this type with:
EXEC sp_addtype T_TID, 'int', 'NOT NULL';

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-10 : 07:56:41

If you are using SQL SERVER 2005 then it will work fine as such

Vabhav T