'Incorrect Syntax near' Error in Create Type in 2005

Hot Topic

How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Report Model Folders
Transfer data : DTS, replication or Bulk Insert ?
Merge Replication - Weird Conflicts for no reason
High CPU utilization during full-text population in SQL 2005
RS.exe and executables
Problem creating SQL Server Mobile Subscription
Wait for Recieve Fails to recieve message when called from .Net sqlClient
The syntax of the SQL the DataSet Design Surface generates
stored procedure that performs a search is too cumbersome

SQL Server

Selecting maximum from a varchar field in a data flow
sending "Empty string" to a textbox on the report
re-establishing mirror
Problem attaching a database
“AcquireConnection method call to the OLEDB Connection Manager for SQL Server failed” Error
Storing images in sql server 2005
"Invalid Object Name" error in "Database Setup" step of "Configuration Manager"
Adding SDF data into a Listview - C#
Potential workaround for SP1 failure
How to run only certain tasks

'Incorrect Syntax near' Error in Create Type in 2005

This is getting really frustrating. I've searched and tried about everything I can think of. I have an assembly added to my database by using Sql Management Console. The assembly is called TestUDT. I have defined 4 structs within that assembly, Person, Transaction, Payment, and Seller. The assembly was added to SqlServer 2005 with dbo as the owner, the permission set is SAFE, and the assembly is signed. The structs are within the Test namespace.

When ever I type the following in a Query window, just checking the syntax fails, let alone trying to execute it.

CREATE TYPE dbo.Seller EXTERNAL NAME TestUDT.[Test.Seller]

The error is:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'TestUDT'.


What am I doing wrong

Thanks
]Monty[

Answer this question

'Incorrect Syntax near' Error in Create Type in 2005

  • ASGMikeG

    OK, I figured this out.

    The database existed in the original SQLServer 2000 database. When I upgraded to 2005, the compatibilty level of the database remained at 2000. After changing the compatibility level to 2005, the command now works.

    Important safety tip: remember to have the compatibility set to 2005 on the database if you're going to create a CLR type.

    Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

    ]Monty[

  • Dina Holan MSFT

    I'm sorry that I can't help directly, but the same syntax works fine for me here. You sure you don't have any "strange" characters in the syntax, or....

    Niels

  • muraliK

    Thanks for adding the doc bug. I tried to do the feedback thing, but of course here at work we have Lotus Notes (ugh!) and all kinds of security policy, and I was never able to get a dialog or email to come up to give the feed back. For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.

    Thanks
    ]Monty[

  • merc

    >For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.


    Thanks for the suggestion. I've forwarded your idea to the customer feedback team.

    Regards,

  • CNH

    >>Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

    Hi Monty,
    Thank you for pointing out this error in the documentation.  I've created a documentation bug which will be fixed in a future refresh of Books Online. 

    Just as an FYI, you can create documentation bugs by clicking the Send Feedback button at the top of a topic.  Your comments are used to automatically create a bug that is assigned to the appropriate writer.  For SQL Server 2005, we will be releasing quarterly updates to Books Online, so you can expect to see your feedback incorporated in a fairly reasonable amount of time.

    Regards,


  • 'Incorrect Syntax near' Error in Create Type in 2005
Answer this question