'function sequence error' in BCP

'function sequence error' in BCP

Post by Rick Charne » Fri, 07 Feb 2003 00:29:56



In SQL Server 2000, I am using BCP's 'queryout' keyword to write the
result set of a stored procedure to a TXT file, but am getting a function
sequence error".  The stored proc runs fine on its own in Query Analyzer.  
My cmd line:

bcp "execute mydbname..myprocname" queryout mainframe.txt -S
myservername -U myid -P mypw -e mainframe.err -c -t"\t"

my stored proc is pretty simple:

SET NOCOUNT ON
TRUNCATE TABLE loc_feed

INSERT INTO loc_feed (....)
SELECT ...........
FROM loc_mainframe_feed
WHERE .......

SELECT *
FROM loc_feed

Any ideas?  Thanks much.

 
 
 

'function sequence error' in BCP

Post by Ron Talmag » Fri, 07 Feb 2003 15:19:56


Rick,

I tried a simplified version of what you sent,
bcp "execute pubs..pr_foo" queryout mainframe.txt -S myserver -U myid -P
mypassword -e mainframe.err -c -t"\t"
and it worked fine.

pr_foo does the same thing in a copy of the pubs database that your proc
does.

There must be something else going on. Your bcp syntax is fine, the proc is
OK. Have you tried it on other machines, or on other procs?

Ron
--
Ron Talmage
SQL Server MVP


Quote:> In SQL Server 2000, I am using BCP's 'queryout' keyword to write the
> result set of a stored procedure to a TXT file, but am getting a function
> sequence error".  The stored proc runs fine on its own in Query Analyzer.
> My cmd line:

> bcp "execute mydbname..myprocname" queryout mainframe.txt -S
> myservername -U myid -P mypw -e mainframe.err -c -t"\t"

> my stored proc is pretty simple:

> SET NOCOUNT ON
> TRUNCATE TABLE loc_feed

> INSERT INTO loc_feed (....)
> SELECT ...........
> FROM loc_mainframe_feed
> WHERE .......

> SELECT *
> FROM loc_feed

> Any ideas?  Thanks much.


 
 
 

'function sequence error' in BCP

Post by Gert E.R. Draper » Fri, 07 Feb 2003 16:14:56


I tried the same and can not repro it either. This is what I did:

use tempdb
go
create table t1(c1 int not null)
go

create proc spbcp
as
set nocount off
truncate table t1

insert into t1(c1)
select id
from sysobjects
where type = 'S'

select *
from t1
go

exec spbcp
go

bcp "exec tempdb.dbo.spbcp" queryout c:\temp\gertd\bcp.dat -S
(local)\dev -T -c -t"\t" -e c:\temp\gertd\bcp.err

Starting copy...

19 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1

What kind of data types do you have in the table? Any blobs or sql_variant?
What is the version of BCP.EXE (BCP -v)

bcp -v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
Version: 8.00.382

Does the error show immediately or after a couple of rows?
If it shows immediately, use ODBC Trace (ODBCAD32.EXE) and turn on tracing
(make sure you have no other ODBC apps running before and while you do this,
and run your BCP command. Maybe that will tell use what is wrong in your
case.


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2003 All rights reserved.


> Rick,

> I tried a simplified version of what you sent,
> bcp "execute pubs..pr_foo" queryout mainframe.txt -S myserver -U myid -P
> mypassword -e mainframe.err -c -t"\t"
> and it worked fine.

> pr_foo does the same thing in a copy of the pubs database that your proc
> does.

> There must be something else going on. Your bcp syntax is fine, the proc
is
> OK. Have you tried it on other machines, or on other procs?

> Ron
> --
> Ron Talmage
> SQL Server MVP



> > In SQL Server 2000, I am using BCP's 'queryout' keyword to write the
> > result set of a stored procedure to a TXT file, but am getting a
function
> > sequence error".  The stored proc runs fine on its own in Query
Analyzer.
> > My cmd line:

> > bcp "execute mydbname..myprocname" queryout mainframe.txt -S
> > myservername -U myid -P mypw -e mainframe.err -c -t"\t"

> > my stored proc is pretty simple:

> > SET NOCOUNT ON
> > TRUNCATE TABLE loc_feed

> > INSERT INTO loc_feed (....)
> > SELECT ...........
> > FROM loc_mainframe_feed
> > WHERE .......

> > SELECT *
> > FROM loc_feed

> > Any ideas?  Thanks much.

 
 
 

'function sequence error' in BCP

Post by Rick Charne » Sat, 08 Feb 2003 06:05:12


Thanks VERY much for the suggestions.  I've found the problem.  Inside
the SELECT segment of my INSERT..SELECT is the following line:

CASE WHEN addr2 <> '' THEN addr1 + ' ' + addr2 ELSE addr1 END,

Using Query Analyzer to execute the stored procedure that contains this
SELECT segment it runs fine.  But executing it with BCP causes my
Function Sequence Error.  Clearly it doesn't like the single-quotes.

What can I do?  The program I use to write my procs, CAST, generates an
error if I use double-quotes.  We're in SQL Server 2000.  Thanks for any
help.


> I tried the same and can not repro it either. This is what I did:

> use tempdb
> go
> create table t1(c1 int not null)
> go

> create proc spbcp
> as
> set nocount off
> truncate table t1

> insert into t1(c1)
> select id
> from sysobjects
> where type = 'S'

> select *
> from t1
> go

> exec spbcp
> go

> bcp "exec tempdb.dbo.spbcp" queryout c:\temp\gertd\bcp.dat -S
> (local)\dev -T -c -t"\t" -e c:\temp\gertd\bcp.err

> Starting copy...

> 19 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total        1

> What kind of data types do you have in the table? Any blobs or sql_variant?
> What is the version of BCP.EXE (BCP -v)

> bcp -v
> BCP - Bulk Copy Program for Microsoft SQL Server.
> Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
> Version: 8.00.382

> Does the error show immediately or after a couple of rows?
> If it shows immediately, use ODBC Trace (ODBCAD32.EXE) and turn on tracing
> (make sure you have no other ODBC apps running before and while you do this,
> and run your BCP command. Maybe that will tell use what is wrong in your
> case.


> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2003 All rights reserved.



> > Rick,

> > I tried a simplified version of what you sent,
> > bcp "execute pubs..pr_foo" queryout mainframe.txt -S myserver -U myid -P
> > mypassword -e mainframe.err -c -t"\t"
> > and it worked fine.

> > pr_foo does the same thing in a copy of the pubs database that your proc
> > does.

> > There must be something else going on. Your bcp syntax is fine, the proc
> is
> > OK. Have you tried it on other machines, or on other procs?

> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP



> > > In SQL Server 2000, I am using BCP's 'queryout' keyword to write the
> > > result set of a stored procedure to a TXT file, but am getting a
> function
> > > sequence error".  The stored proc runs fine on its own in Query
> Analyzer.
> > > My cmd line:

> > > bcp "execute mydbname..myprocname" queryout mainframe.txt -S
> > > myservername -U myid -P mypw -e mainframe.err -c -t"\t"

> > > my stored proc is pretty simple:

> > > SET NOCOUNT ON
> > > TRUNCATE TABLE loc_feed

> > > INSERT INTO loc_feed (....)
> > > SELECT ...........
> > > FROM loc_mainframe_feed
> > > WHERE .......

> > > SELECT *
> > > FROM loc_feed

> > > Any ideas?  Thanks much.

 
 
 

'function sequence error' in BCP

Post by Rick Charne » Sat, 08 Feb 2003 06:41:14


Upon further research...nope, it has nothing to do with the quotes.  It
has to do with overusing column addr2 inside my searched CASE function.  
I have it both inside the initial boolean expression (CASE WHEN addr2...)
~and~ inside the result expression (THEN ... addr2).  One definition I've
seen of Function Sequence Error is that you've attempted a SQL operation
that depends on the result of a previous and incompleted operation.  And
I think I've kind of done that here.  Well, at least whatever BCP is
passing my proc to seems to think so.

Any ideas on how to rewrite this?  Thanks much.



> Thanks VERY much for the suggestions.  I've found the problem.  Inside
> the SELECT segment of my INSERT..SELECT is the following line:

> CASE WHEN addr2 <> '' THEN addr1 + ' ' + addr2 ELSE addr1 END,

> Using Query Analyzer to execute the stored procedure that contains this
> SELECT segment it runs fine.  But executing it with BCP causes my
> Function Sequence Error.  Clearly it doesn't like the single-quotes.

> What can I do?  The program I use to write my procs, CAST, generates an
> error if I use double-quotes.  We're in SQL Server 2000.  Thanks for any
> help.


> > I tried the same and can not repro it either. This is what I did:

> > use tempdb
> > go
> > create table t1(c1 int not null)
> > go

> > create proc spbcp
> > as
> > set nocount off
> > truncate table t1

> > insert into t1(c1)
> > select id
> > from sysobjects
> > where type = 'S'

> > select *
> > from t1
> > go

> > exec spbcp
> > go

> > bcp "exec tempdb.dbo.spbcp" queryout c:\temp\gertd\bcp.dat -S
> > (local)\dev -T -c -t"\t" -e c:\temp\gertd\bcp.err

> > Starting copy...

> > 19 rows copied.
> > Network packet size (bytes): 4096
> > Clock Time (ms.): total        1

> > What kind of data types do you have in the table? Any blobs or sql_variant?
> > What is the version of BCP.EXE (BCP -v)

> > bcp -v
> > BCP - Bulk Copy Program for Microsoft SQL Server.
> > Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved.
> > Version: 8.00.382

> > Does the error show immediately or after a couple of rows?
> > If it shows immediately, use ODBC Trace (ODBCAD32.EXE) and turn on tracing
> > (make sure you have no other ODBC apps running before and while you do this,
> > and run your BCP command. Maybe that will tell use what is wrong in your
> > case.


> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > You assume all risk for your use.
> > Copyright ? SQLDev.Net 1991-2003 All rights reserved.



> > > Rick,

> > > I tried a simplified version of what you sent,
> > > bcp "execute pubs..pr_foo" queryout mainframe.txt -S myserver -U myid -P
> > > mypassword -e mainframe.err -c -t"\t"
> > > and it worked fine.

> > > pr_foo does the same thing in a copy of the pubs database that your proc
> > > does.

> > > There must be something else going on. Your bcp syntax is fine, the proc
> > is
> > > OK. Have you tried it on other machines, or on other procs?

> > > Ron
> > > --
> > > Ron Talmage
> > > SQL Server MVP



> > > > In SQL Server 2000, I am using BCP's 'queryout' keyword to write the
> > > > result set of a stored procedure to a TXT file, but am getting a
> > function
> > > > sequence error".  The stored proc runs fine on its own in Query
> > Analyzer.
> > > > My cmd line:

> > > > bcp "execute mydbname..myprocname" queryout mainframe.txt -S
> > > > myservername -U myid -P mypw -e mainframe.err -c -t"\t"