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"
by