Question/Issue with User Defined Data types

kfilipowsky

I have created a command line application used to promote select database objects between environment for developers. This is working very well with one exception.

I create a DB object for testing as follows:
sp_addtype @typename='testusertype', @phystype='datetime', @nulltype='not null',@owner='dbo'

It is a standard here that all objects must be owned by 'dbo'

When I migrate the user type with my utility, it is created on the target server but the owner is set to the Id running my command line utility, not dbo as it existed in its original form.


Following is an excerpt from the SQL generated by Redgate's API
sp_addtype N'testusertype', 'datetime', 'NOT NULL'

note the absense of @owner option. The source object is dbo.testusertype and target object ends up being filipowsky_adm.testusertype

Can you offer any advise here? If this an issue with the API or am I missing something in my code?



Karl Filipowsky
Pfizer Animal Health | Global Shared Services
Desk: 269-833-0990 | Cell: 734-216-4392 | Fax: 269-833-2255

Brian Donahue

Hello Karl,

I've had a look into this and I think it may be a design issue with the SQL Compare Engine. Because it does not fiddle with object ownership, migrating objects that were created with implicit ownership as the currently-logged-in-user, maybe it made more sense to strip the ownership from user-defined types as well. I'll raise this with the developers, but I have a feeling they are going to tell me the same thing.

kfilipowsky

It sounds simple enough to be to append a "@owner ='dbo'" to the SQL your API generates. Do you see any issues with this? I suppose I'll look into that this afternoon.

Brian Donahue

It should be okay to do that -- provided the login you use can create objects as DBO...

kfilipowsky

OK, would you mind suggest the best place(class) to do what I suggested. The ExecutionBlock class doesn't have any 'set' accessors and wasn't really designed to have user's manipulating the SQL the API generates.

if not, we'll just drop UDTs from the automation plan, we only have a few of those a year anyway.

Brian Donahue

Hi,

No, the executionblock is read-only. Either you need to make sure you connect to the database and execute the block with a user that's part of the DBO role, or save the script to a file and manipulate it and run it outside of the Toolkit.