Microsoft KB Archive/289551

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

Article ID: 289551

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.



Steps to Reproduce Behavior
  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   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  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. Run this script on a different database. It is better to create a new temporary database and then run the generated script.</li></ol>

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

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB289551

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.