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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
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 the Microsoft 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 @LoginName
END
  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]
GO

CREATE 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]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customers]
GO

CREATE 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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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 @LoginName
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET 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]
GO

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]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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 @LoginName
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON


CREATE 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
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB289551
©2003 Microsoft Corporation. All rights reserved.