SQLServerCentral / Discuss Content Posted by Yakov Shlafman / Article Discussions / Article Discussions by Author / Reverse Engineering Alias Data Yypes in SQL Server 2000 / Latest Posts

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

yakov shlafman-228008 — Mon, 17 Sep 2007 06:30:00 GMT

Hi pcwc66,

you can replace the old ADT with a new ADT and then replace the new ADT with another ADT which has the same name as the old ADT but has the definition of the new ADT. The procedure provided in the article allows you to do it.

There is no way to suppress SQL Server from checking ADT existence in other SQL Server objects.

Please read more on ADT in coming parts of this article.

Regards

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

pcwc66 — Fri, 14 Sep 2007 12:22:00 GMT

Hi Yakov,

The idea to replace the old ADT with a new ADT is good. However, for maintenance purpose, I'm thinking whether I can replace the old ADT with a new ADT and then replace the new ADT with another ADT which has the same name as the old ADT but has the definition of the new ADT.

Replacing ADT for columns can follow the article and write a not too complicated script to go thru all tables in a database. Writing a script to replace ADT in stored procedures may be complicated. If there is a way to suppress SQL Server to check ADT existence in other SQL Server objects, then othe SQL Server objects may not need to be replaced.

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

yakov shlafman-228008 — Fri, 14 Sep 2007 09:31:00 GMT

Hi pcwc66,

you have a very intresting question.

Could you please provide a sample of you stored procedure,

what do you want to accomplish and I will be glad to answer.

Regards

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

pcwc66 — Thu, 13 Sep 2007 15:28:00 GMT

If the ADT is also used in stored procedures, is there a way to drop the ADT without changing the stored procedure first?

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

Alexander Kuznetsov — Tue, 19 Jun 2007 14:00:00 GMT

I prefer to use C++ approach - I define macros in a *.h file and have them replaced by actual definition by the preprocessor. For instance, I used to have BIGGEST_VARCHAR defined as VARCHAR(8000). WHen I migrated to 2005, I just replaced that with VARCHAR(MAX) in only one place in my ConstantDefinitions.h file. Next time I generated my deployment script, it had VARCHAR(MAX) wherever I had BIGGEST_VARCHAR in my source.

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

Vic Kirkpatrick-173212 — Tue, 19 Jun 2007 12:30:00 GMT

I had to deal with ADT's in a previous nightmare job. Oh what a total pain it was! Just use the system types. The biggest pain was having to script removal of the use of the types by every table that used one when needing to change the definition of the type. There were some benefits to using the ADTs, but the drawbacks were much larger.

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

noeld — Tue, 19 Jun 2007 11:08:00 GMT

Other drawbacks of the ADT:* When you need to "change" the type the effort is more than with native* When dealing with temporary tables you have to have them in model by the time you restart SQL Server or use a startup procedure to add them to tempdb. This is harder than native datatypes* replication has issues with some of these types* Because it hides implemetation details it forces you to find out the definition.* Like posted above not many providers deal with this successfully* Linked servers can be trashed because of thisBecause of all the above (and possibly more that I may be missing) I would vote for the use of ADT as a BAD parctice!Cheers!

RE: Reverse Engineering Alias Data Yypes in SQL Server 2000

WILLIAM MITCHELL — Tue, 19 Jun 2007 09:13:00 GMT

Are ADT's compatible with the current release of Visual Studio .NET?

A couple years ago I had done some C# programming with .NET 2003 and a SS2000 db that used UDT's but .NET did not recognize the UDT's. That actually made programming harder, since you had to go look up the definition for the UDT and use a native .NET data type.

Does Visual Studio .NET 2005 allow you to use ADT's, and how is that done?

Reverse Engineering Alias Data Yypes in SQL Server 2000

Yakov Shlafman — Fri, 25 May 2007 14:46:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/3038.asp