none
Getting incorrect syntax while creating a table type in sql server 2008 R2

    Question

  •  I am trying to create a table type in sql server 2008 R2 for use in TVP in stored procs. But I am getting this error as

    Incorrect syntax near the keyword 'AS'.

    CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT );
    GO

    This is the MSDN example. Please help me in understanding what is going wrong.

    Sunday, July 07, 2013 9:14 AM

Answers

  • Don't worry about the database compatibility mode. At least not for this error. When I set my database compatibility mode on SQL Server 2008 back to 90 (SQL Server 2005) or 80 (SQL Server 2000), the sample still works correctly. You will only get the error message if you connect to a real SQL Server 2005.

    Gert-Jan

    Monday, July 08, 2013 5:49 PM

All replies

  • Hi Pulkit,

    That is perfect, i even tried myself it is working fine. Can you please check you do not have wild character before and after the query that may be causing the problem.


    Regards Harsh

    Sunday, July 07, 2013 9:24 AM
  • Looks like  you connected to SS2005 server or the database is set 80 or 90 compatibility level

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 07, 2013 9:26 AM
    Answerer
  • The example is just fine on SQL Server 2008 and later (even in an older compatibility mode)

    If you run this on SQL Server 2005, you get your error message (about "AS").

    If you run this on SQL Server 2000 (and earlier?) you get a different error message (about "TYPE").

    In other words, your SQL Server version doesn't support user defined table types.


    Gert-Jan

    Sunday, July 07, 2013 10:38 AM
  • Hi all, 

    thanks for replying. What do you exactly mean by the database is set 80 or 90 compatibility level and how to check?

    Sunday, July 07, 2013 6:46 PM
  • Run these queries:

    SELECT @@Version

    SELECT name, compatibility_level from sys.databases where name = 'MyDbName'

    to verify SQL Server version and the compatibility level of the database.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, July 07, 2013 9:19 PM
    Moderator
  • Don't worry about the database compatibility mode. At least not for this error. When I set my database compatibility mode on SQL Server 2008 back to 90 (SQL Server 2005) or 80 (SQL Server 2000), the sample still works correctly. You will only get the error message if you connect to a real SQL Server 2005.

    Gert-Jan

    Monday, July 08, 2013 5:49 PM