You are currently offline, waiting for your internet to reconnect

FIX: Generate SQL Script in Enterprise Manager Generates Scripts in Wrong Sequence if the Table has User-Defined Function as Computed Column

This article was previously published under Q289551
BUG #: 351235 (SHILOH_BUGS)
SYMPTOMS
If you use the Generate SQL Script option from the SQL Server Enterprise Manager against a table that has a user-defined function as a computed column, you must select the Generate Scripts for all dependent Objects option under the Formatting tab to script out the user-defined function as well. However, the Transact-SQL statements in the script that drop and re-create the function and the table are in the wrong order. So, when you attempt to re-create the table by executing the scripts, the following error message occurs:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'objectname'
CAUSE
SQL Server does not generate the scripts in the correct order.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
Manually change the orders of the DROP and CREATE statements for the table and the user-defined function, because the table creation requires the existence of the user-defined function.

The correct sequence is:
  1. Drop the table.
  2. Drop the user-defined function.
  3. Create the user-defined function.
  4. Create the table.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 1.
MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a user defined function:
    CREATE FUNCTION dbo.fn_GetCurrentNTLogin ()  RETURNS VARCHAR(512)  AS  BEGIN	DECLARE  @LoginName as varchar(512)	select  @LoginName = loginame from master..sysprocesses where spid = @@spid	RETURN @LoginNameEND					
  2. Create a table that uses the user-defined function:
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Customers]GOCREATE TABLE [dbo].[Customers] (	[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Age] [smallint] NULL ,	[UniqueID] AS (newid()) ,	[DateChanged] AS (getdate()) ,	[SQLUserID] AS (user_id()) ,	[ModifiedBy] AS ([dbo].[fn_GetCurrentNTLogin]()) ) GO					
  3. Open the SQL Server Enterprise Manager, select the Customers table, right-click AllTasks, point to Generate SQL Scripts, and under the Formatting tab select Generate Scripts for all dependent Objects to generate the script.
  4. Run this script on a different database. It is better to create a new temporary database and then run the generated script.
The following error message occurs:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_GetCurrentNTLogin'.
Here are the scripts generated by the SQL Server server:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetCurrentNTLogin]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_GetCurrentNTLogin]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Customers]GOCREATE TABLE [dbo].[Customers] (	[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Age] [smallint] NULL ,	[UniqueID] AS (newid()) ,	[DateChanged] AS (getdate()) ,	[SQLUserID] AS (user_id()) ,	[ModifiedBy] AS ([dbo].[fn_GetCurrentNTLogin]()) ) ON [PRIMARY]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE FUNCTION dbo.fn_GetCurrentNTLogin ()  RETURNS VARCHAR(512)  AS  BEGIN	DECLARE  @LoginName as varchar(512)	select  @LoginName = loginame from master..sysprocesses where spid = @@spid	RETURN @LoginNameENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO				
Here are the scripts that work correctly:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Customers]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetCurrentNTLogin]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[fn_GetCurrentNTLogin]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE FUNCTION dbo.fn_GetCurrentNTLogin ()  RETURNS VARCHAR(512)  AS  BEGIN	DECLARE  @LoginName as varchar(512)	select  @LoginName = loginame from master..sysprocesses where spid = @@spid	RETURN @LoginNameENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ONCREATE TABLE [dbo].[Customers] (	[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,	[Age] [smallint] NULL ,	[UniqueID] AS (newid()) ,	[DateChanged] AS (getdate()) ,	[SQLUserID] AS (user_id()) ,	[ModifiedBy] AS ([dbo].[fn_GetCurrentNTLogin]()) ) ON [PRIMARY]GO				
Properties

Article ID: 289551 - Last Review: 11/06/2003 15:31:34 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbbug kbfix kbsqlserv2000sp1fix KB289551
Feedback