March 13, 2009 at 8:36 am
Hi,
I'm submitting the following command to my SQL 2008 server.
if exists(select * from dbo.systypes where name = N'date')
exec sp_droptype N 'date'
The following error is shown :-
Msg 15036, Level 16, State 1, procedure sp_droptype, Line 14
The data type 'date' does not exist or you do not have permission.
I have tried multiple accounts, changed the security and still it will not work.
Could you let me know where I am going wrong.
Thanks.
Nick
March 13, 2009 at 9:17 am
I dont think it is a permission error, as you have said you checked, it looks like you are passing in the string 'Date' and using this as a data type, are you able to post a copy of the procedure?
also just to check, is this for sql 2008?
March 13, 2009 at 9:24 am
Hi,
Thanks for your reply. To confirm this is SQL 2008.
The procedure is the same as the first submission, a simple drop and recreation of the date data type.
Nick
March 13, 2009 at 9:31 am
As far as i am aware sp_droptype is to drop alias data types, ones that have been user created, as Date is a system datatype in 2008., you will not be able to drop the system data type.
Maybe someone else can shed a bit more light on this..
March 13, 2009 at 2:01 pm
In SQL Server 2008, sp_droptype is deprecated. You should use DROP TYPE instead. With that said, sp_droptype only drops types that are in the dbo schema. Date is a system type as of SQL Server 2008 and it's in the sys schema. So dbo.date doesn't exist, which is the reason sp_droptype is failing. Because date is a system type, you don't have permissions to drop it, even if you specified DROP TYPE sys.date;
K. Brian Kelley
@kbriankelley
March 27, 2009 at 7:41 am
Hi,
I removed said components from the script and remarkbly the process work correctly.
Many thanks for you help!.
Nick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply