/*=====================================================================

  File:      InstallVB.sql for UTF8 SQLCLR Example
  Summary:   Registers a VB SQLCLR based UDT for storing UTF8 encoded strings.
  Date:		 March 31, 2004

---------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
======================================================= */
USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ResumeFullName') AND ([type] = 'FN'))
DROP FUNCTION [dbo].[ResumeFullName];
GO

IF EXISTS (SELECT * FROM sys.indexes WHERE NAME = N'IX_utf8test_ustr')
DROP INDEX utf8test.IX_utf8test_ustr;
GO

IF EXISTS (SELECT * FROM sys.tables WHERE name = N'utf8test') 
DROP TABLE [dbo].[utf8test];
GO

IF EXISTS (SELECT * FROM sys.tables WHERE name = N'ResumeNames') 
DROP TABLE [dbo].[ResumeNames];
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = N'Utf8String') 
DROP TYPE [Utf8String];
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'UTF8String') 
DROP ASSEMBLY UTF8String;
GO

DECLARE @SamplesPath nvarchar(1024)

-- You may need to modify the value of the this variable if you have installed 
-- the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 
        'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 
        'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY UTF8String 
FROM @SamplesPath + 'UTF8String\VB\UTF8String\bin\Utf8String.dll';
GO

CREATE TYPE [Utf8String] 
EXTERNAL NAME [UTF8String].[Microsoft.Samples.SqlServer.Utf8String];
GO