Technical: Microsoft – SQL Server – v2000 – BCP – Error – FUNCTION SEQUENCE ERROR

Technical: Microsoft – SQL Server – v2000 – BCP – Error – FUNCTION SEQUENCE ERROR

Introduction

Trying to get data out of SQL Server and hoping to use the quickest path.  One traditional tool is bcp.

Code – Transact SQL

Code – Transact SQL – dbProduct

Create database dbProduct and create a lone table dbo.product.


set noexec off
go

use [master]
go

if db_id('dbproduct') is null
begin

	print 'Creating DB dbproduct ...' 	

	exec('create database [dbproduct]')
	exec('alter database [dbproduct] set recovery simple');

	print 'Created DB dbproduct' 	

end
go

use [dbproduct]
go

/*
	drop table [dbo].[product]
*/
if object_id('dbo.product') is not null
begin

	set noexec on

end
go

/*
	drop table dbo.product;
*/

create table dbo.product
(

 	  [id] bigint not null identity(1,1)
	, [productName] varchar(600) not null
	, [size] varchar(80) null
	, [price] money not null

	, constraint PK_product primary key
		([id])

	, constraint Unique_ProductName unique
		([productName])

	, [addedBy] sysname not null
		constraint defaultproductAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultproductAddedOn default getdate()

)

set noexec off
go

Code – Transact SQL – dbSales

Create database dbSales and create a lone table dbo.order and dbo.orderDetail.



set noexec off
go

use [master]
go

if db_id('dbSales') is null
begin

	print 'Creating DB dbSales ...' 	

	exec('create database [dbSales]')
	exec('alter database [dbSales] set recovery simple');

	print 'Created DB dbSales' 	

end
go

use [dbSales]
go

if object_id('dbo.[order]') is not null
begin

	set noexec on

end
go

/*

	drop table dbo.[orderDetail];
	drop table dbo.[order];

*/

/*

	exec sp_help 'dbo.Order'

	exec sp_help 'dbo.OrderDetail'
*/

create table dbo.[order]
(
 	  [id] bigint not null identity(1,1) 

	, constraint PK_Order primary key
		([id])

	, [addedBy] sysname not null
		constraint defaultOrderAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultOrderAddedOn default getdate()

)
go

set noexec off
go

if object_id('dbo.[orderDetail]') is not null
begin

	set noexec on

end
go

create table dbo.[orderDetail]
(
 	   [OrderID] bigint
 	,  [sequenceID] int not null

	,  [productID] int not null

	,  [NumberofItems] int not null

		  constraint defaultOrderDetailNumbreofItems default 1

	,  [addedBy] sysname not null

		  constraint defaultOrderDetailAddedBy default SYSTEM_USER

	, [addedOn] datetime not null

		  constraint defaultOrderDetailAddedOn default getdate()

	, constraint PK_OrderDetail primary key
		(
			  [OrderID]
			, [SequenceID]
               )

	, constraint FK_OrderDetail foreign key
		(
			  [OrderID]
                )
	        references dbo.[Order]
	        (
			  [id]
                )

)
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.ufn_computedCost

Create Scaler function dbo.ufn_computedCost.



use [dbSales]
go

if object_id('dbo.ufn_computedCost') is not null
begin

	set noexec on

end
go

/*

	drop function dbo.ufn_computedCost

*/

create function dbo.ufn_computedCost
(
	  @productID   int
	, @NumberofItems  int
)
returns money
as
begin

	declare @cost money

	select @cost = 
			@NumberofItems * tblProduct.[price]
	from   [dbProduct].dbo.product tblProduct
	where  tblProduct.[id] = @productID

	return (@cost)

end
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.OrderDetail.cost

On the dbo.OrderDetail table, create a new column (cost) and bind it to the user function dbo.ufn_computedCost.



use [dbSales]
go

/*
	alter table dbo.orderDetail
	  drop column [cost]
*/

if not exists
	(

	    select name
	    from   syscolumns tblColumn
	    where  tblColumn.id = object_id('dbo.orderDetail')
	    and    tblColumn.name = 'cost'

	)
begin

	print 'Adding new column dbo.orderDetail - cost ... '

		alter table [dbo].[orderDetail]
		   add [cost]
			as dbo.ufn_computedCost(
                                                    [productID]
                                                  , [NumberofItems]
                                               )

	print 'Added new column dbo.orderDetail - cost'

end
go

Populate Tables

Let us populate the tables

Populate tables – [dbProduct].dbo.Product



set nocount on;
go

delete from [dbproduct].dbo.product;
go

set identity_insert [dbproduct].dbo.product on
go

	insert into [dbproduct].dbo.product
	([id], [productName], [size], [price])
	values(1, 'Frosted Flakes Cereal', '15 oz',2.98)

	insert into [dbproduct].dbo.product
	([id],[productName], [size], [price])
	values(2, 'Kellogg''s Corn Flakes Cereal', '24 oz', 4.67)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(3, 'Milk - Vitamin D','1 Gallon', 3.65)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(31, 'Chobani Non-Fat Greek Yogurt','15/6 oz', 15.99)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(41, 'Donsuemor Madeleines French Cakes','28 oz', 8.13)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(51, 'Turon','15 pieces', 11.85)

        insert into [dbProduct].[dbo].product
	([id],[productName],[size], [price])
	values(52, 'empanada','7 pieces', 7.00)

go

set identity_insert [dbproduct].dbo.product off
go

Populate tables – [dbSales].dbo.Order and [dbSales].dbo.OrderDetail



set nocount on;
go

delete from [dbSales].dbo.[orderDetail];
delete from [dbSales].dbo.[order];
go

set identity_insert [dbSales].dbo.[order] on

	insert into [dbSales].dbo.[order]
	([id])
	values (1)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 1, 1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 2, 3
	)

        --------------------------------------------------------
	insert into [dbSales].dbo.[order]
	([id])
	values (2)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		2, 1, 1
	)

       -----------------------------------------------------------

	insert into [dbSales].dbo.[order]
	([id])
	values (3)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID], [NumberofItems]
	)
	values
	(
		3, 1, 51,1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID],[NumberofItems]
	)
	values
	(
		3, 2, 52,2
	)

go

set identity_insert [dbSales].dbo.[order] off
go

Bcp Data

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:



"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

Output:

bcp-good

Bcp Data – Failed

Let us imagine that a few weeks later or we are are in the process of moving to a new system and we try to bcp again, but upon issuing an identical BCP Command, we are now getting an error.

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:



"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

 

Error:

Error – Textual


SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error

Error – Image

bcp-failed

Error Diagnosis

An error that reads “Function sequence error” seems intimidating. But, no problem.

We issued a select statement against the target table for our bcp:

select * from dbsales.dbo.orderDetail

And, got back an helpful error message:

Server: Msg 208, Level 16, State 1, Procedure ufn_computedCost, Line 24
Invalid object name 'dbProduct.dbo.product'.

Fix

Our fix was to move the dbProduct database to our new system.

In Microsoft SQL Server it is sometimes difficult to find cross database dependencies;  partly because Transact SQL does not allow us to specify those relationships and constraints.

Using the code pasted below, we tried relating the dbo.OrderDetail.productID column in the dbSales database to the id column in the dbProduct.dbo.product table.



     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[product]
       (
    	  [id]
       )

But, you will get an error stating “cross-database foreign key references are not supported.



Server: Msg 1763, Level 16, State 1, Line 2
Cross-database foreign key references are not supported. 
Foreign key 'dbProduct.dbo.product'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

Please keep in mind that if you try to reference an entirely missing database object, you will get a different error.

In the example below, we tried referencing a missing object (dbProduct.dbo.productMissing):



     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[productMissing]
       (
    	  [id]
       )

And, get an error stating “references invalid table ….”


Server: Msg 1767, Level 16, State 1, Line 1
Foreign key 'FK_OrderDetail_Product' references invalid table 'dbProduct.dbo.product1'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

More research might reveal different root causes:

  • Someone might have renamed the dbProduct.dbo.product table
  • In our case, the dbProduct database had not yet been moved to our new system

Conclusion

Yes,  error messages are sometimes unpleasant.  And, quick Google searches might find problems that a bit worse and laiden with unrelated riddles.

But, steady and progressive remediation steps wins the day!

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s